Troubleshooting Microsoft SQL full data or log volumes

If you have administered any SQL servers no doubt you have come across a transaction log file (or sometimes data) that has filled up your drive and have to work out what to do.

First you should determine if the logs files suddenly grew abnormally which can happen if someone runs a complex query. Hopefully there are some monitoring history for you to reference to determine what has happened. The following resolution is really only recommended when the log or data files have grown due to some once off process such as a database having half of its data removed or some unique update that grew the log file to a huge size.

The following code comes from this page and contains a lot more detail for those who want more information.

You can use this query to display the current size of the DB files and how much free space each has.

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

To do this open up your SQL Management Studio and click on ‘New Query’.

SQLQuery

Then paste the code into the new window and select what database you wish to run the code on with the drop down box highlighted in the image below.

SQLQuery2

When you click the ‘Execute’ button you will see a result at the bottom of the window looking something like this,

SQLQuery3

What this result shows us is that the database has a data file and a log file and in this example the data file is roughly 23 gigabytes in size with 4 gigabytes of that being free space while the log file is 9 gigabytes with 9 gigabytes free (in fact only 40 megabytes is currently in use). To say that the log file is overly large and is wasting space is difficult to say but in general this file will grow in shrink internally as a normal function however if this log file has been, for example 1 gigabyte in size for the past 6 months and grew to 9 gigabytes overnight then it is possible that the file could be shrunk to recover some space however the log file will grow again if it needs to (under the assumption that you have not disabled the auto grow options).

So if you make the decision to shrink the file here are the steps to follow.

Right click on the database you wish to shrink. Select tasks -> shrink -> files option.

SQLshrink

For this example I am shrinking the log file for this database so I need to change the ‘File type’ drop down box to Log as shown below and click OK (or select an appropriate ‘Shrink file to’ option first if you like).

SQLshrink2

Now with any luck the file has shrunk in the file system and recovered some of your drive space.

As a side note you may be thinking to yourself why didn’t I just go into the shrink file dialog box to see what the ‘Available free space’ was for the database. Firstly this is a more dangerous approach as you are leaving yourself open to accidentally clicking OK and shrinking a file that may not require it and secondly you can check multiple databases more quickly using the script and the drop down menu (still trying to work out how to simply show all databases in one script and if I ever work it out I will update my post).

Thanks for reading.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s