Advertisements
Home > DBA Stuff > SHRINK DATABASE & SHRINK FILE – SQL Server

SHRINK DATABASE & SHRINK FILE – SQL Server


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

Advertisements
  1. Michael Wade
    July 4, 2011 at 2:22 pm

    Hi, great post – really informative! What about a post on SQL jobs? I have been trying to find a job that allows me to work with SQL and this one seems to be quite useful: SQL Jobs

  1. May 28, 2014 at 7:32 pm
  2. July 8, 2011 at 12:00 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: