Archive

Archive for July 2, 2011

SQL DBA – SHRINK DATABASE & SHRINK FILE – SQL Server

July 2, 2011 3 comments

While working with huge databases with large tables there are times when you get your database and log files expand ridiculously and eating up the entire disks.
This happens when you are either on an:
– OLTP environment and doing lots of DMLs (INSERTS, UPDATES or DELETES) or
– OLAP environment when you are running huge SQL queries that goes on expanding your tempdb and finally eats up your entire drive space.

There are lot of ways to better configure your disks and databases there to avoid such situations. But these kind of situations come and you have to approach the DBA or become a DBA yourself.

Check the LOG Size and Space used, here:

DBCC SQLPERF (LOGSPACE)

Check if your database has adequate space available to free up:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Before shrinking the log file, it better to backup your transaction logs, as shown below:

BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(database_name_log, 2)

OR, you can simply change the Database recovery mode to Simple, and run the above mentioned DBCC statement.

tempdb can be freed up by simply restarting SQL Server. When SQL Server shuts down it deletes the tempdb and on restart it automatically creates tempdb database. Thus you get fresh tempdb with minimal space. This is not advisable on production servers and should be handled by experts.

Check this KB article for backing up the transaction logs and shrinking the database or log files: http://support.microsoft.com/kb/272318

More here on MSDN links:-
DBCC SHRINKDATABASE: http://msdn.microsoft.com/en-us/library/ms190488.aspx
DBCC SHRINKFILE: http://msdn.microsoft.com/en-us/library/ms189493.aspx