Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Out of space on the C drive of your SQL Server and ways to reclaim disk space


By:   |   Last Updated: 2009-08-06   |   Comments (5)   |   Related Tips: 1 | 2 | More > Database Administration

Problem

It happens to all DBAs - your C drive has filled up and your database is about to crash or has stopped processing. Perhaps your SQL error log is spamming and has filled up the entire C drive before you've had the chance to address the underlying issue. Your primary concern is getting your SQL Server back online. Here are some steps you can follow to free up some space in a hurry.

In a perfect world our C drive would be optimally configured, almost static, but in the real world we often have many files on the C drive including system databases, log files, BCP files etc...

Solution

Here are some tips including a little script to use in an emergency or as a maintenance task when you encounter a Zero free space issue. In my experience as a DBA I've found the most common cause of C drive filling up is an over active SQL error log. Error logs that are over 2Gb are not uncommon and when you have 5 or more of these they can fill a drive quickly. You can delete the non-current error logs directly from the file system or you can use SQL to elegantly cycle them using the sp_cycle_errorlog system stored proc. Running this command 6 -8 times in rapid succession will clear out the old error logs and free up much needed space for you.

There may be redundant mdf and ldf files sitting on the C drive which can be removed. An example would be where you have moved Tempdb from the C drive but the old Tempdb datafiles are still sitting there. These can be safely deleted once you have confirmed they aren't being used.

You may also find old stack dumps, crash dumps, mini dumps etc in the \LOG directory. Examples are SQLDump0001.log; SQLDump0001.mdmp. These can also be safely deleted to free up space in an emergency. In some cases I have found over a Gb of crash dumps. This is especially true of a development server where the DBA's only involvement after the initial build is when the server crashes and the DBA is asked to fix it.

Roaming user profiles can take up lots of disk space too. Check the 'C:\documents and settings' folder for user profiles who should not be there like ex employees and old service accounts. I've seen some very large user profiles in my time.

If you've done all the above and you're still out of space then its time to analyse the \temp directories and .tmp files and see what you can delete here but be careful as this can have consequences. Developers are known for using \temp diretories and files in their applications.

One last thing - empty the Recycle Bin!

Checklist to free space

  1. cycle the SQL errorlog to remove large error log files from the C drive - see script below
  2. clear out old mdf and ldf files from the \Data directory
  3. clear out old stack dumps and crash dumps from \LOG directory
  4. remove any redundant backup files
  5. delete old roaming user profiles from C:\documents and settings
  6. check any \temp directories and .tmp files for files to delete
  7. empty the Recycle Bin

Some SQL code to assist you

-- check free space
EXEC master..xp_fixeddrives
--drive MB free
------- -----------
--  C     0 <------------- HELP !!!!!
--  F     115360
--  G     253473
 
-- lets recycle the SQL Errorlog a few times to claim some space back
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog
GO

-- check free space again
EXEC master..xp_fixeddrives
--drive MB free
------- -----------
--  C     2500
--  F     115360
--  G     253477 

Next Steps


Last Updated: 2009-08-06


get scripts

next tip button



About the author
MSSQLTips author Alan Cranfield Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, December 09, 2017 - 1:59:42 AM - Krishna Kumar Rai Back To Top

 Very nice article

 


Tuesday, December 17, 2013 - 8:12:35 AM - sathesh Back To Top

Thanks Alan , Great Help :)


Monday, September 30, 2013 - 11:04:50 PM - Xoan Vu Back To Top

Thanks you so much. This problem made me tired and pending my work.


Tuesday, January 15, 2013 - 10:18:48 AM - Girish Back To Top

eh..

Thanks a lot. This gives me a quick way to deal with error_log files.

 

-Girish


Thursday, March 22, 2012 - 6:20:04 PM - Ray Back To Top

User profiles are a hidden trove.  I found one former employee profile that had a couple of Full Backups on the desktop (a little over 10GB on 30GB disk).

This has become my first check when I am looking for space on C:.


Learn more about SQL Server tools