SQL Server Information – backups not compliant with the backup naming convention


To obtain a list of backups that have a name different than the backup naming convention and the space occupied by these backups

My way of achieving it

Using tsql, xp_cmdshell and xp_fileexists. From time to time, at least in my case, I am doing backups as part of a ticket or a request and I am naming them in 100% of the cases something that is different than what the scheduled backups uses for those backups. In time I realized the my footprint is quite big and if among the team there are other colleagues doing the same then the footprint is even bigger. Because of this I created this script that will tell us which backups looks like NOT being compliant or backups that are not taken by the scheduled jobs or using the right naming convention and provides us information about those files like:

  • path to the backup
  • if the file is still on disk
  • size of the file
  • delete commands

The assumptions that I made were that usually we are taking full backups, differential backups, tlog backups and on some servers we might have log shipping configured. Because of these we might have 4 naming conventions for our backups or more. The script starts by creating a table containing the naming conventions discovered based on the backup name length, number of numbers in the backup name and the number of occurrences. Based on this table and based on the fact that if occurrences is smaller than 10 is deciding if the backup is compliant or not.

By default the script is dealing with backup history for the last 14 days. If you want to go back in time more than 14 days then you must modify the line below

SET @days = 14 — enter here the number of days for which you want to search among backup history

in the script.

Please be aware that this setting together with how often you take the backups increases the execution time.

The output of the script is bellow


The output of the script from a server where we have some backups jobs configured looks like below



The only thing that I want to mention here is that this script is providing information only and the decision of doing something with the output is entirely something that you must decide and is very dependent of the each infrastructure environment. All the results set were left in the output because by seeing the output you can easily confirm that indeed those type of backups are for sure not compliant with the backup policy from your environment.

The script can be downloaded from here

SQL Server database restore – preparations and tests that we must perform before starting the restore


To obtain information about the target database files , to check the backup file that we have to use, to estimate the space required and to have a list with steps done and a list with steps to do

My way of achieving it

Using linked server and xp_cmdshell. The restore operation of a SQL Server database is something that requires most of the time careful attention especially when it comes to big databases when the restore operations is time consuming and we would like to have all in place before the restore. Not only that requires our full attention, the restore requires also preparation which is in many cases time consuming and prone to errors and mistakes or something that usually I forget to check. Because of this I decided to create a script that will check some of the things required for a potentially successful restore. The script performs the following verifications:
– checks if the bak file is accessible from the target instance
– checks the drive information of the target instance for space available for the restore
– checks if target instance is older than source instance since backups from newer versions of SQL cannot be restored on old versions
– checks the full bak file for information about the size of the files that will be restored
– check if target database exists on target instance
– check if we have space for individual database files restores and if not provides some alternative locations for relocation
– generate the restore command, is very basic one that needs to be changed in case target database doesn’t exist on target instance

Before using the script we must modify the @sourceinstance, @targetinstance, @sourcedb and @targetdb with the right information.

Th output of the script is below


The script can be downloaded from here

SQL Server Instance – generate tsql to meet tempdb best practices and display the current configuration


Generate tsql code required to meet tempdb best practices configuration and display the current configuration

My way of achieving it

Using SSMS, tsql, xp_cmdshell

The backbone for this script was a script made a while ago by VijayRod, and reviewed by AkshayM that was checking tempdb configuration by comparing the current configuration to best practices recommended by Microsoft at that time. Because sometime we need if configuration is not compliant to modify the tempdb settings I decided to use their script and add the code that will generate the tsql commands used to configure tempdb database to meet the requirements. This version of the script has some limitations and assumes the following
– when it comes to tempdb databases that have files created in multiple locations ( different drives ), by default the script will take in consideration only the biggest drive when it comes to create the files.
– it assumes that the free space left on the drive where the files will be created will be only 1 GB from the drive capacity. If you want to leave more space then replace the 1024 in the script in this line – SELECT @availablespace=(MAX(diskspacefreeinmb)-1024)/@suggestedDataFilecount FROM @dbfilesdrivemountpoint WHERE filetype = ‘DATA’ –
– it assumes that the maximum size will be the initial size + 10MB. If you want to have a maximum size bigger then replace 10 in the script in this line – SELECT @maxsize = CONVERT(nvarchar,(@availablespace+10))+’MB’ — assumes a maximum size of initial size + another 10 MB

The output of the script looks like below depending on the current tempdb database configuration


The script can be downloaded from here

Display group membership (including nested groups) of a domain account using SQL Server


To obtain a list of groups that a domain account is member of.

My way of achieving it

Using SSMS and Ad Hoc Distributed Queries. In order to use AD Hoc Distributed queries temporary you must enable it using sp_configure.

Sometimes when we need to troubleshoot why a domain account doesn’t have the requested permissions we have to check his group membership. Most of the time the access to SQL server is given by making an user member of a specific active directory domain group and have that group added as a login or by adding that domain account as a login. Although most of the time this action will solve the request there might be cases when the users say that they don’t have the same rights as other users. This post address this situation by discovering the group membership of those domain accounts. Although we can use net user command with domain switch in a command prompt window

– net user domain_account /domain

the problem is that this will not return any nested groups.

When comparing 2 domain accounts for differences that they might have in SQL Server in regards to permissions we want to be sure that those accounts have the same group membership.

The output of the script is below and can be downloaded from here



SQL Server Instance Information – physical location, volume name, disk name and number, disk information


Obtaining information about the SQL Server physical location where the databases are and displaying for those locations the volume name, disk name and disk information

My way of achieving it

Using SSMS, xp_cmdshell and DISKPART.

In order to not use detail keyword in the script when constructing the xp_cmdshell and diskpart command I decided to us the varbinary representation of it in the script.
SELECT CONVERT(VARBINARY(MAX), ‘detail’) AS [VARBINARY], ‘varbinary representation of DETAIL keyword’ AS Info
SELECT CONVERT(VARCHAR(MAX), 0x64657461696C)AS [VARCHAR], ‘varchar representation of 0x64657461696C’ AS Info

VARBINARY – 0x64657461696C – varbinary representation of DETAIL keyword
VARCHAR – detail – varchar representation of 0x64657461696C

Before any comments about the script I want to say how important is to verify the script before execution and to run it first only on a test server or virtual machine and to run it on other systems only after you create an encrypted version of it.

Sometimes when

– we have to increase a disk where SQL Server databases are, we need to provide information about that disk to other team in order for them to increase the disk

– we have to find more information about a disk from SAN team in case we have some performance issues and those issues are tracked back to I/O latency

– we want to just display the information of disk, volumes, type, size, free in only one window from inside SSMS

The idea of having something like this came to live after I was searching how to map disks and volumes in powershell and I found the script posted by Adam Conkle ( Thanks for the script ) , here, https://gallery.technet.microsoft.com/DiskPartexe-Powershell-0f7a1bab. I realized that I can use the same approach that he uses to do the same but from inside SQL Server.

The script that I am using to obtain the output below can be downloaded from the link provided at the end of this post.


The problem is that diskpart requires administrators privileges which means that the SQL Service account must be a member of local administrators group on the server in order for the script to function or the implementation of a proxy account that has administrative privileges at OS level. More than that is advisable to have the script created as a SP and encrypt it in order to be sure that no one will replace those keywords, detail disk or detail volume with other ones like delete which can compromise or in other words to destroy the server. I have tried to replace the keywords to its varbinary representation because I considered the script much safer than having the script using the DETAIL keyword.

The script can be downloaded from here

SQL Server cluster – checking if SQL Server clustered instance is running on preferred node


Obtaining information about the SQL Server clustered instances and if they are running on the preferred node

My way of achieving it

Using SSMS, xp_cmdshell and WMIC. When we have to check and see if our clustered instances are running on the preferred node we can use different approaches and each approach has its own benefits in comparison with the other approaches. In my case because I am used to work more with SQL Server, the usage of SSMS, xp_cmdshell and WMIC is more appealing than everything else and I chose it to find that information.

The script is using:

– 1 WMIC query that uses MSCluster_ResourceGroupToResource – used to find all the groups that are related to SQL Server clustered instances

– 1 WMIC query that uses MSCluster_ResourceGroupToPreferredNode – used to find the preferred node for all the groups

The output returned by the first query is used to generate a result set of group names and the name of the SQL Server clustered instance that is found in each group.

GroupName  InstanceName
SQL11 SQL11\I11

The output above is used to actually generate the script that we will have to ran in SSMS after enabling the SQLCMD mode. Below is an example of the generated script.


DECLARE @preferrednodes TABLE (line nvarchar(500))
DECLARE @InstanceInformation TABLE (InstanceName nvarchar(100), RunsON nvarchar(50), IsPreferredNode nvarchar(3), Comments nvarchar(max))
DECLARE @groupsandinstance TABLE (GroupName nvarchar(500), InstanceName nvarchar(100))
DECLARE @GroupName nvarchar(50)


INSERT INTO @groupsandinstance VALUES (‘SQL Server (MSSQLSERVER)’,’APPDKBA1162′)
INSERT INTO @groupsandinstance VALUES (‘SQL11′,’SQL11\I11’)

SELECT @GroupName = GroupName FROM @groupsandinstance WHERE InstanceName = @@SERVERNAME
INSERT INTO @preferrednodes EXEC xp_cmdshell ‘wmic /namespace:\\root\mscluster path MSCluster_ResourceGroupToPreferredNode’
UPDATE @preferrednodes SET line = REPLACE(line,CHAR(13),”)

IF (SELECT COUNT(line) FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’) > 1
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT TOP 1 CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”

SELECT * FROM @InstanceInformation

INSERT INTO @InstanceInformation SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)) AS RunsON,” AS IsPreferredNode,(select ERROR_MESSAGE()) AS Comments
SELECT * FROM @InstanceInformation




Below you can see the output of the script


The script can be downloaded from here

Interpreting sp_WhoIsActive stored procedure output for beginers

I will start my first post by apologizing in advance for any mistakes that most probably I will do but I hope that I will learn and educate myself on the way.

The idea behind this post came to me after I first heard about the stored procedure that Adam Machanic wrote and after I saw how useful was when troubleshooting or seeing what is happening to the SQL Server. Because depending on our role in the company and our day to day activities,  I realized that I need to have something that will refresh the meaning of the output till this output will become a second nature for me.  This was my attempt to take some of the information that he already made available in his blog ( http://sqlblog.com/blogs/adam_machanic/archive/2011/04/01/a-month-of-monitoring-part-1-of-30-a-brief-history-of-monitoring.aspx ) and put them in a format that in the beginning was easier for me to understand and communicate it to other colleagues. As with everything that I will post here this was my attempt and of course it has some flaws, some shortcomings but overall I believe it makes some sense for someone that is learning the output of this stored procedure and how to use it in the beginning. In order to use it we have to follow these steps

1. Create the stored procedure using the latest version of the SP from here http://sqlblog.com/files/default.aspx

2. Create a powershell script using the code provided at the end of this post.

3. Run the stored procedure with the parameters that you want but using this output column list. This is a prerequisite because the last columns depending on the parameters received by the SP will contain more than one line and the script cannot parse it correctly. We want to have those columns at the end in order to select all the columns expect those ones.

exec sp_whoisactive
@output_column_list = ‘[session_id][dd hh:mm:ss.mss][dd hh:mm:ss.mss (avg)][physical_io][reads][physical_reads][writes][tempdb_allocations][tempdb_current][CPU][context_switches][used_memory]

4. Run the powershell script but not using the ISE. We will be prompted to select the command that you ran in SSMS and press enter after you made it available in the clipboard

PS C:\Users\Administrator\Downloads> .\sp_whoisactive10august2015.ps1
Select the command that you ran in SSMS and copy it in order to be available in the clipboard (Ctrl+C).
Press Enter to continue …:

5. After pressing enter we will have to provide the output of the command  by providing also the column names. Usually I am selecting only one row and all the column headers except the additional_info, sql_text and sql_command since these as we mentioned before, sometimes, have more lines and are not parsed right by the script

6. After pressing enter again the output is parsed and it provides more information about the columns and what those means

Below is a picture with step 4, 5 and 6 and because the script is not displaying the query the first line seems to be out of context since we are not pasting the query that is captured in the columns (sql_text or sql_command).


The script can be downloaded from here