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

Display folder size in a graphical way using SQL Server

Goal

To obtain the size of folders and their path in graphical way. To have the output of tree command, dir command and the folder size in only one output.

My way of achieving it

Using tsql, xp_cmdshell in order to call the tree, dir and powershell.exe command s.  The desire to have something like that came when I have to troubleshoot and see who is the biggest consumer in case of free space issues. Of course that multiple approaches and solutions can solve this question but I stopped on using the tree command because I liked the look of the output. The first output shows the information as the tree command does while the other tries to sort them by size in order to easy see the biggest consumer. One problem with this is that for locations where sql server doesn’t have access the size returned is 0. Even so I decided to finish the script and use it like this because of the output. The script has also a deep switch that will let you decide how deep you want to traverse the folders, maximum is 3.

The output of the script looks like this

sqlservertreelikeoutput

The code can be downloaded from here

Windows 2012 disk information – displaying disk information

Goal

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.

disk_config

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

SQL Server troubleshooting – tempdb utilization

Goal

To obtain information about tempdb database from utilization perspective

My way of achieving it

Using some of the DMV/DMF that come with SQL Server and overall advises (scripts taken from different sources over internet that all refers to SQL Server DMVs in Action Better Queries with Dynamic Management Views by Ian W. Stirk)  from peers colleagues from internet customized to fit my needs  and my own way of troubleshooting. For me the most important thing while troubleshooting is to have the result of the investigation side by side with what it means since in most of the cases I am not dealing with this everyday and my memory about this topic is as good as the number of occurrences I had throughout my working experience. In simple terms for tempdb database as for other user databases we can find information about usage using specific DMVs and DMFs. The utilization information (tempdb) provided by SQL Server comes under the form of

  • present utilization
    • current space occupied by all the objects that uses tempdb – user objects, internal objects, version store, mixed extent
    • current space each session is currently using in tempdb because of running tasks
    • current space each session is currently using in tempdb because of row versioning
  • past utilization
    • used space in tempdb by all the sessions since they were established

The output of the script is ordered by the space still used and all the columns along the table gives me all the information that I need in order to decide the next steps or to say whom, when,  why in regards to tempdb utilization.

tempdbtroubleshooting

The script can be downloaded from here