Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Out of Space

It seems that often I see posts that say “I’m out of space on my drive, what do I do?” The answer, as with most anything in SQL Server is, it depends.

Backups

The most common issue I see for running out of space is the lack of deleting database backup files. The maintenance plan wizard instructs you to perform backups, and defaults to making a separate file for each backup, but it doesn’t necessarily clue in the “Accidental DBA” to remove old backups.

You need a maintenance cleanup task here and set it to remove old backups, but be sure that you have a new, good backup first. In other words, the maintenance cleanup comes after the full backup, and then only when that task completes with success.

You can delete old backup files manually, but think about how many you need to keep. Most people probably can get by with 1 or 2.

IIS Log Files

I know lots of people that have IIS on the same box with SQL Server for small to medium web sites. Unfortunately IIS doesn’t have a maintenance cleanup task. It creates new log files on a daily or weekly basis, and those will fill up your disk when you are least expecting it.

These are in the LogFiles folder under System32 on your system drive. Set a reminder in Outlook to trim these down occasionally.

Transaction Log

Another very common issue is that transaction log growing to fill all disk space. This happens because so many new DBAs assume that a full backup also includes a log backup. It doesn’t, for multiple reasons, and it doesn’t matter if it should or no; it doesn’t.

What you need to do here is first make sure you have time, and then clear the log. That’s a log backup with truncate in versions 2000 and prior. It’s setting to Simple mode in 2005 and above and then . Be sure you set the mode back to Full.

Once you have space back, I’d recommend you take a full backup. You can get by with a diff, or maybe wait until your next scheduled one,but I’m conservative. I’d recommend a full backup right then. Once that’s done, you need to get a good size for the transaction log. Unfortunately that requires some trial and error.

The size of your log file depends on the load your database experiences in terms of changes, but also on your log backup schedule. A log backup allows the space in the log to be resused. If you produce 10MB of changes an hour, and back up once an hour, you need a log file that’s 10MB. Actually you’d want it larger to account for a larger load some hours, but you get the idea. If you backup the log once a day, you’d need a 240MB log ( plus pad).

So, what I’d recommend is that you schedule hourly log backups. See how large the backup files are and then size your log file appropriately. As far as the initial size, you can run a DBCC SHRINKFILE to make your log file smaller. I’d lower it to 1GB to start with. That’s just a guess, and you might want to set it to 10% of your data files, but disk space cheap, and 1GB will accommodate lots of databases.

Summary

There are other reasons, like your data file growing, but I’m not trying to cover every situation. If you’re new to SQL Server, and you have a full disk, check these items first. If none of these help you, post a note in our forums and someone will answer.

Comments

Posted by cmille19 on 28 September 2009

On a busy system with many files/databases I find it very helpful to visualize space usage in order to see which files or file types are consuming the largest amount of space. To do so, I've used a SourceForge, utility called WinDirStat for a number of years. The utility also has a portable version,but generally I run the utility from my workstation using a UNC path i.e. \\servername\c$.

portableapps.com/.../windirstat_portable

Lastly, IIS isn't the only program that likes to generate lots of log files, some 3rd party SQL backup programs will create log files on the C drive also. Using a utility like WinDirStat I'm able to find these files quickly, delete them and then setup a maintenance job to delete the files on a scheduled basis.

Posted by wnylibrarian on 29 September 2009

It really does “depend.” For our back ups we have to perform two. 1) We must back up a resource folder created by the software vendor and 2) The SQL Server back up. What we found works best for us is doing transaction log back ups hourly throughout the business day. Then nightly we do a full database back up and clear out the transaction logs. Then we run a batch file that zips down the resource folder and the full database back up and ship it off to another drive. We originally had 8 zip-files and we were soaking up real estate even with everything zipped. We’ve since backed that off to 4 zip-files before we clear then and start a new archive. It “depends,” and it works for us.

Leave a Comment

Please register or log in to leave a comment.