As time goes by, the disk space requirements for your database may fluctuate greatly. You can set SQL Server to automatically expand a database when it requires more space; and you can also set it to "shrink" when it can reclaim allocated but unused space. Sometimes it makes sense to enable autogrow and autoshrink events, and sometimes it doesn't. (See KB #315512 for information about enabling each of these options.)
Shrinking Data Files (*.MDF / *.NDF)
If you haven't enabled the auto-shrink setting, there may be times when your database has been cleared of significant amounts of data, and you'd like to reclaim the space on disk that the partially empty MDF file is taking up. To do this, you can use the DBCC SHRINKDATABASE command. See this MSDN entry for the technical documentation on the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:
DBCC SHRINKDATABASE
DBCC SHRINKFILE
(A lot of people assume that if you delete a lot of rows or drop a table, that the database size will shrink automatically, but this is not the case. In fact, it is rare that you would want to do this; see Tibor's article for more information.)
And see these articles for more information on shrinking SQL Server databases:
SQL Server Architecture: Shrinking Databases
Creating and Maintaining Databases: Shrinking a Database
Shrinking Log Files (*.LDF)
Another problem you might be having is that the transaction log is filling up too quickly, or staying far too large.
See KB #110139 to understand why the transaction log might fill up.
See KB #317375 for information about full or unexpectedly growing log files.
See KB #873235 for information about preventing unexpected log file growth.
Note that the transaction log is expected to shrink and grow depending on what kind of work you're doing in your database, so the size it expands to might be normal. In this case, shrinking itwhile recovering disk spacemight actually hurt performance... because the log might have to expand again the next time such a transaction runs, but the database is first going to have to expand the file to make room.
For information on how and why the log file grows and empties, based on your recovery model and backup strategy, see the following article:
SQL Server 2000 Backup and Restore
Depending on your recovery model, you might be able to simply run this command to manually truncate the log:
| BACKUP LOG <database name> WITH TRUNCATE_ONLY |
This empties the log file but does not reduce its size. You will then want to use DBCC SHRINKFILE to reduce the size of the individual LDF file(s). See this MSDN article for more information on shrinking a transaction log:
SQL Server Architecture: Shrinking the Transaction Log
For official Knowledge Base information on shrinking the log, see KB #256650 (SQL Server 7.0 / MSDE 1.0) and KB #272318 (SQL Server 2000 / MSDE 2000).
Moving Data / Log Files If you find that your data and log files are outgrowing their disk(s), see KB #224071 for information about altering file locations using sp_detach_db / sp_attach_db. You might solve your problem simply by separating the data files from the log files and splitting them between drives; this will often yield better performance as well, since the two resources aren't contending for disk i/o.
Other information For some other tips on recovering space or alleviating 'out of space' error messages, see some of the suggestions we have for tempdb (which you could apply to any SQL Server database) in Article #2446.
[ Comment, Edit or Article Submission ]
