SQL Server full backups – size, space required

SQL Server backups started to fail now and then due to space issues. There is something that I can do to make it work?

The answer to that question is obvious and we just need to allocate more space to the location where we save the backups. But how much space we need to allocate? The answer is to check the space used by the last full backup for all the databases as a starting point and multiply it with the retention period.


In order to have that value we can use the below script to see the sum of all the latest full backups taken on our instance. Because of the reasons below the size that we see might not be the total size that we need but it is still a good estimation of what we might need for a successfull backup.

  • databases were created and for them we haven’t taken yet a backup
  • databases were backed up in the past but the file is not available on the disk to check the size

The script give us a real number when the last full backup of all the databases was successfully and the files are still on disk.

In our environment that full backup file receives _full while in other environments the naming convention might be different which is why the script must be modified a little bit. Also if we want to have an estimation about latest differential we need to modify the script. In red below you can see where you need to modify for the desired results.

SELECT bmf.physical_device_name as path FROM
(select media_set_id = MAX(bs.media_set_id) FROM sys.databases d LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = d.name AND bs.is_copy_only = 0 AND bs.type =’D‘ GROUP BY d.Name, bs.type) r
LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON r.media_set_id = bmf.media_set_id where bmf.physical_device_name is not null and bmf.physical_device_name like ‘%_full%’

The script output looks like below

and you can download it from here

Check and Register SPN for our SQL Server Instances


To check if our SQL Server instance registered its SPN and to register if not.

My way of achieving it

Using powershell.

Sometimes when troubleshooting connection issues we end up realizing that it was caused due to Kerberos and the solution is to check for SPN and to create it in case is not there. Because the creation requires the knowing of the port under which SQL Server instance is listening and also the service account under which the service is running I found useful the usage of a script that will generate and check the existence and the creation of this SPN. The script can be executed in powershell console and I prefer the one that you can open while you are using SSMS and from contextual menu you choose Start Powershell.

The output of the script looks like below

The script can be downloaded from here

SQL Server – map drives to disk numbers


To obtain the disk number of all the drives together with drive size and free space.

My way of achieving it

Using powershell and CIM_StorageExtent.

For a long period now I tried different methods and approaches in order to map the drives in Windows to their respective disk numbers. Today starting from what Granger Godbold said here, https://rvdnieuwendijk.com/2012/05/29/powershell-function-to-get-disk-scsi-lun-number/ , I finally managed to have that information. The class that I used is CIM_StorageExtent. I started with this Get-WmiObject CIM_StorageExtent  and after I filtered the output to not show me the Memory like objects I noticed that maybe the objects there appears in a specific order and I checked my assumption

Disk #1, Partition #0|214748364800|
Disk #6, Partition #0|214748364800|
Disk #7, Partition #0|214748364800|
Disk #8, Partition #0|214748364800|
Disk #9, Partition #0|214748364800|
Disk #10, Partition #0|214748364800|
Disk #11, Partition #0|268435456000|
Disk #12, Partition #0|53684994048|
Disk #13, Partition #0|53684994048|
Disk #14, Partition #0|53684994048|
Disk #15, Partition #0|53684994048|
Disk #2, Partition #0|429498826752|
Disk #16, Partition #0|53684994048|
Disk #17, Partition #0|53684994048|
Disk #18, Partition #0|53684994048|
Disk #19, Partition #0|53684994048|
Disk #20, Partition #0|161059176448|
Disk #21, Partition #0|53684994048|
Disk #22, Partition #0|161059176448|
Disk #23, Partition #0|53684994048|
Disk #24, Partition #0|53684994048|
Disk #25, Partition #0|53684994048|
Disk #3, Partition #0|644249288704|
Disk #26, Partition #0|53684994048|
Disk #27, Partition #0|53684994048|
Disk #28, Partition #0|107372085248|
Disk #29, Partition #0|53684994048|
Disk #30, Partition #0|161059176448|
Disk #31, Partition #0|53684994048|
Disk #32, Partition #0|53684994048|
Disk #33, Partition #0|161059176448|
Disk #34, Partition #0|53684994048|
Disk #35, Partition #0|966376030208|
Disk #4, Partition #0|214748364800|
Disk #36, Partition #0|966376030208|
Disk #37, Partition #0|1234814631936|
Disk #38, Partition #0|966376030208|
Disk #39, Partition #0| 1070596096|
Disk #5, Partition #0|214748364800|
Disk #0, Partition #0|209715200000|
Disk #0, Partition #1| 90248839168|


It looks like all the drives, the ones containing “\” appears in the following order:

  • disk order , partition 0
    • disk 0, partition 0
    • disk 1, partition 0
    • disk 39, partition 0
  • disk order, partition order
    • disk 0, partition 1
    • disk 0, partition 2
    • disk 0, partition n
    • disk 1, partition 1
    • disk 1, partition n
    • …..

This is the order that I noticed when I had ONLY SIMPLE VOLUMES. Maybe the order will be differently in case we have spanned volumes.

But because usually the servers in my environment are not using spanned volumes I decided to make a script to take advantage of what I just saw. The output of the script looks like below

The script can be downloaded from here

Update datatype of primary key in SQL Server table


To generate the tsql commands that we can use to alter the primary key datatype.

My way of achieving it

Using powershell to check some of the relations between the tables. As with all other things that I am posting here, this, is far from being a final script. It is just something that you can use in order to check the level of work required to change the data type of a primary key. Sometimes in our job we are asked to perform this type of activity and although it seems like a trivial task there are hidden things that make it very difficult. I tried to show as many things that I could for which the change of datatype might fail but I am sure that depending on the database structure the script will still fail even after we will perform the steps described in the output of the script.  The output of the script must be followed and changed accordingly to your requirements. In the script I changed the data type of InqNo column from int to numeric(18,0).

The script requires the instance name, database name and table name

$instancename = “UCPHMA”
$databasename = “Inquiry2012”
$tablename = “Inquiries”

The output of the script can be seen below and as you can see asks for dropping and recreating some indexes among other things.

IMP – Those actions needs to be very good planned when it comes to the production servers and big tables due to all the problems that dropping an index creates and all the problems that creating an index creates. But in case the tables are small then the impact will not be big. As with every script, first needs to be tested in a controlled environment and used only after the operation was successful there.

Very important is to check the script output before executing it.

The script can be downloaded from here

Split strings using tsql – a different method


To split a string separated by comma or other char using tsql

My way of achieving it

Using dynamic sql

The method that I will describe here is working good enough for the situations in which we want to split a char no longer than 50k chars and it has the benefit of less typing and more intuitive approach but this is only my opinion about it.  Most of the approaches are using CLR, XML, numbers table, CTE and others and are very good explained here (http://sqlperformance.com/2012/07/t-sql-queries/split-strings). The code that I am using to split the string is below

declare @databases nvarchar(max)
set @databases=’a,id,xyz,abcd,abcde,sa,foo,bar,mort,splunge,bacon’
–SET @databases = REPLICATE(@databases, POWER(10, 3));
select @databases=’select ”’+replace(@databases,’,’,”’ union all select ”’)+””

If you need to split the string based on a different char then replace comma in the replace function with the new char, for example

select @databases=’select ”’+replace(@databases,’|’,”’ union all select ”’)+””

It seems that in the form above the dynamic sql is very dangerous (was made very clear to me here, http://sqlperformance.com/2016/03/sql-server-2016/string-split#comment-121269 )  and I changed it to

select @string ='select '''+replace(replace(@string,'''',''),',',''' union all select ''')+''''

which removes the possibility of running some bad code but it also breaks words like don’t or won’t.

Sqlcmd formatting for better vizualization (part 2)


To keep when possible the same display as SSMS GUI has for the output of a SELECT statement.

My way of achieving it

By creating a SP that will generate a dynamic tsql for the select statement or by rewriting the select statement.

I haven’t come up with the SP yet but I created the code that will generate the dynamic tsql that we can run to format the output. The select must be simple more exactly the columns provided in the select statement otherwise it will break the code.  More than that, some type of columns and chars need more attention and when those are displayed, this might break the formatting also. As an example you can see first a result of select name,create_date,compatibility_level from sys.databases followed by a result of select name,create_date,owner_sid,compatibility_level from sys.databases which prove the formatting challenges if some of the columns contains some kind of chars.


Nevertheless the code can be modified to deal with these situations and format them properly.

The code can be downloaded from here

Windows 2012 disk information – displaying disk information


To obtain disk information like number, size, partition style, drive label and drive letter

My way of achieving it

Using powershell. I must start by saying that this requires windows 2012. The necessity of having something like this is my desire to create a SQL installation script that eventually will install a SQL Server from one end to the other. The only parameters that the script will require will be the account used for the services and the instance name. Everything else will be discovered automatically. The script from this post is my attempt of discovering the disks available on a server and formatting them accordingly. Till now the script was tested only against simple volumes. The script contains a few functions, one that discover all the disks, another one that discover all available disks (RAW format) , another one that prepare the disk for a stand alone installation of SQL Server and the last one which is preparing the disk for a clustered SQL server installation.

Below is the output generated by the script.


As in the case of any actions that change the data, the format functions should be used with caution. The steps for running the script in a safe manner will be to execute the script and call the functions in the following order

  • all_disks
  • av_disks
  • formatclusterdisk or formatclusterdisk with the whatif parameter
  • formatclusterdisk or formatclusterdisk without the whatif only after checking that indeed the disk is RAW.

The script can be downloaded from here

Windows cluster – checking if services or applications are running on preferred node


To obtain information about the location where cluster services or applications are running now and if they are running on the preferred node.

My way of achieving it

Using powershell. From time to time we want to know if everything is running on the preferred node on our cluster. Although the best practice will be to already have a powershell script that I can run on our server in some cases if the script is not there already or if the creation of the scripts is forbidden or just because I find more easier to copy paste the script in powershell console I decided to create something that might be considered like  an one-liner for this job.

The output of the script is below


The script can be downloaded from here

SQL Server database backup – generating backup commands to multiple locations


To generate the backup commands for a full backup or a differential backup

My way of achieving it

Using xp_cmdshell and powershell. More often than we would like due to growth of the databases and creation of other databases the backup of our databases fails because of insufficient free space. Although most of the time the solution is to increase the disk where we save our backups there are exceptions from this rule

  • we really need to take a full backup or a differential one because we need to create a new log chain.
  • we really need to take a backup as a prerequisite of a change
  • we really need to take a full backup in order to free the space that other backups used to recover the database occupies on disk.  Some of the backup retention implementation will keep on disk all the backups since the last full backup.
  • we really need to take the backup according to the configured schedule, this schedule is created in such a way that will allow us to recover the database in some specific terms.

The script is generating the backup commands for locations found in backup history but also on the locations where SQL server keeps its database files.

The script requires

  • database name
  • backup type, full or differential
  • native or other type of backup, the script has syntax for litespeed but it can be modified to generate the commands using the syntax of your backup tool

The script identifies the size of the last full backup or differential one and uses that size as an estimation for the next backup.

The output of the script is shown below.



The script can be downloaded from here


SQL Server Instance – database files location, disk, mount point, size, free space, xp_fixeddrives looklike


To obtain information about the locations were database files are, disk, mountpoint and to display the capacity, free space and space occupied on that drive by the database files

My way of achieving it

Using xp_cmdshell and powershell. The reason behind this was the fact that although xp_fixeddrives is great sometimes we need more information. This script is nothing more than an attempt to create something that can be used when we have to answer questions like

  • where my files for my databases are stored
  • the location is a mount point or not
  • what is the size of the drive where my database files are
  • how much in percentage the database files occupies from that drive

The output of the script looks like below


For me this type of information is useful when we have to show to other teams that actually the SQL Server is not the major consumer of that location or when we have to identify the disk number where the database files are in case we have to ask the storage team performance logs for it.

The script can be downloaded from here