Out of space on the C drive of your SQL Server and ways to reclaim disk space
Written By: Alan Cranfield -- 8/6/2009
-- read/post comments
-- print --
Rating:
Rate
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_errorlogGO -- check free space again EXEC master..xp_fixeddrives--drive MB free ------- ----------- --C 2500 --F 115360 --G 253477
|
Next Steps
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
