Infrastructure at your Service

Microsoft Archives - Blog dbi services

Stéphane Haby

SQL Server Tips: How many different datetime are in my column and what the delta?

By | Database management, Development & Performance, SQL Server | No Comments

Few months ago, a customer asks me for finding in a column, how many rows exist with the same date & time and the delta between them. The column default value  is based on the function CURRENT_TIMESTAMP and used as key as well. This is obviously a very bad idea but let’s go ahead… This anti pattern may lead to a lot of duplicate keys and the customer wanted to get a picture of the…

 
Read More
Stéphane Haby

SQL Server Tips: Drop a database-user attached to a service…

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Few weeks ago, I have a little issue when I try to drop a database-user without login Unfortunately, I do a little mistake at the beginning… I receive like every morning a report if all AD logins (computers, groups, users) registered on SQL server instances are in the AD with the useful command sp_validatelogins This report indicates that a computer name dbi\server_name$ was no more in the AD. I drop the login without problem and…

 
Read More
Stéphane Haby

SP2 for SQL Server 2016 is available with new helpful DMVs

By | Database Administration & Monitoring, Development & Performance, SQL Server, Technology Survey | No Comments

Last month (April 24, 2018), the Service Pack 2 for SQL Server 2016 was released and distributed. This Service Pack has new DMVs, already available in SQL Server 2017 RTM. In this article, I will just write few words about 2 DMVs (sys.dm_db_log_stats & sys.dm_db_log_info) and a new column (modified_extent_page_count) in the DMV sys.dm_db_file_space_usage that I presented during our last event about SQL Server 2017. I think they are really helpful for DBA. It’s also…

 
Read More
Stéphane Haby

SQL Server Security: Are your databases ready for the GDPR?

By | Database Administration & Monitoring, SQL Server, Technology Survey | No Comments

The GDRP (General Data Protection Regulation), voted in 2016, will be applied in the European Union soon (May 25, 2018). We heard a lot of this new directive. The goal is to improve the protection and confidentiality of personally identifiable information for every European citizen. Quid of Switzerland? Read the article of Gregory here. A personal data is information that identifies a natural person, directly or indirectly. It can be a name, a photograph, an…

 
Read More
Stéphane Haby

SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended?

By | Database Administration & Monitoring, Development & Performance, SQL Server, Technology Survey | No Comments

This article is an additional article to my precedent about “SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?” The goal is to see after I suspend the rebuild, if I can run a traditional rebuild without the option RESUME.   I start an index rebuild with the option Resumable: ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] REBUILD WITH (RESUMABLE = ON, ONLINE = ON) GO   In a new query window,…

 
Read More
Stéphane Haby

SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

By | Database Administration & Monitoring, Development & Performance, SQL Server, Technology Survey | No Comments

Last week, I presented this option in our internal event, the dbi xChange. My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild? I was not able to answer because I forgot to do this test. The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference). I always use…

 
Read More
Stéphane Haby

SQL Server 2017: TRIM not only a replacement of RTRIM and LTRIM

By | Database Administration & Monitoring, Development & Performance, Technology Survey | 4 Comments

Last month, I present in the IT-Tage 2017 in Frankfurt am Main, a session about SQL Server 2017 overview. During my session I made a demo on one of the new string T-SQL Commands: TRIM. My demo was very simple but I have also 2 very good questions. Before, I write about these question, I will show you what is TRIM. This function is a replacement of RTRIM and LTRIM to delete all space character…

 
Read More
Stéphane Haby

SQL Server Tips: an orphan user owns a database role

By | Database Administration & Monitoring, SQL Server | No Comments

A few days ago, I conduct an audit to detect all orphan’s windows accounts in a database and I was surprise to have an error during the drop user query.   The first step is to find all orphan’s windows accounts in a database USE [dbi_database] GO /*Step1: Search the orphan user */ SELECT * FROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid WHERE b.sid IS NULL AND a.type In (‘U’,…

 
Read More
Stéphane Haby

SQL Server Tips: Deactivate the Customer Experience Improvement Program (CEIP)

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Before SQL Server 2016, you had the possibility to check the case “Send Windows and SQL Server Error Reports….” during the installation if you want to be a part of the Customer Experience Improvement Program (CEIP). In SQL Server 2016, after the installation, all of the CEIP are automatically turned on. Why? SQL Server and SQL Azure share the same code now. On Azure, this service existed since a long time. It collects a large…

 
Read More
Stéphane Savorgnano

Pass Summit 2017

By | Database Administration & Monitoring | No Comments

Today starts the Pass Summit 2017 taking place in Seattle. After a small fly over the Ocean, more than 10 hours… yesterday, and a nice jet lag which avoid me to sleep later than 4AM this morning, I arrived to the Convention Center in Seattle where the Pass takes place. I start this first day by the session of Itzik Ben-Gan: T-SQL Tips and Tricks. As part of the session, Itzik spoke about batch processing…

 
Read More