Naveen's Weblog

Bridge to future

Shrink Database and Files in MSSQL

Posted by codingsense on December 22, 2008


To increase your MSSQL database performance use Shrink database and files.

Each MDF file will have its LDF (LOG) file with it. Any transaction made to the database will lead to increase of size in MDF as well as LDF files. Over a period of time you will notice that eventhough your database has less data, the file size of both MDF and LDF will be large which leads to slower processing of queries and lead to other maintainance problems.

So MSSQL has given an option to remove unused spaces.

To shrink the database and files through Management studio you can follow the below steps

  • Open Object explorer
  • Right click on the database that needs to be shrinked
  • Tasks
  • Shirnk Database
  • Shrink Files

The following are the commands to shrink Database and Files through T-SQL.

Shrink Database :

dbcc ShrinkDatabase(DatabaseName)

The database name is the name that is seen in the object explorer of Management studio.

Shrink Files :

dbcc ShrinkFile(Logical Name)

The logical name of the database can be seen using the following query
Select name from sysfiles

Here 2 rows will be listed first one will represent the logical name of MDF file and other is for LDF file. Execute the DBCC query twice with both the names to shrink both MDF and LDF files, like

dbcc ShrinkFile(DB)

dbcc ShrinkFile(DB_LOG)

In my case i was working on a Email Project and the database would increase rapidly, so i make a stored procedure and gave user the option to shrink the database. In the button click event i called the stored procedure which was

Create PROCEDURE [dbo].[ShrinkEmail]
AS
BEGIN
 DBCC ShrinkDatabase(Emails)
 DBCC ShrinkFile(Emails)
 DBCC ShrinkFile(Emails_LOG)
END

This reduced my LOG file from size 7GB to 504KB and MDF file changed from 2.8 GB to 2.3 GB. 🙂

Caution: There are many discussions going over the net whether the MSSQL performance increases or decrease by shrinking the databases and files. So please do refer the following links and take appropriate decision.

http://technet.microsoft.com/en-us/library/ms189493.aspx
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/

Happy Learning 🙂

Advertisements

8 Responses to “Shrink Database and Files in MSSQL”

  1. atoutsweb said

    Thank you ! I my case, the database name was changing, is use this script :

    DECLARE @dbName varchar(50)
    DECLARE @dbNameLOG varchar(50)
    set @dbName = DB_NAME()
    set @dbNameLOG = DB_NAME() + '_LOG'
    DBCC ShrinkDatabase(dbName)
    DBCC ShrinkFile(dbName)
    DBCC ShrinkFile(dbNameLOG)

  2. Tanise said

    Doesn’t it include overhead time in shrinking and de-shrink it??? any performance related issued????

    • codingsense said

      Hi Tanise,

      Sorry for the delay.

      No I have used it at some places where there is lots of database operation. If you do many operations then you will find that the database size and the log files have grown too big, and which in turn degrades the performance.

      I have seen the shrinking on big files and it works faster without any issue.

      Thanks,
      Codingsense

  3. testo said

    You do not need to call DBCC SHRINKFILE after you call DBCC SHRINKDATABASE, because the latter shrinks the files too

    DBCC SHRINKDATABASE (Transact-SQL)
    Shrinks the size of the data and log files in the specified database.
    http://technet.microsoft.com/en-us/library/ms190488.aspx

  4. testo said

    A shrink does not necessarily improve DB performance. Fragmentation of indexes might increase, and this could slow down the queries.

    A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
    http://technet.microsoft.com/en-us/library/ms189493.aspx

  5. Lippy said

    I think you need to read the blog at http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/ before making claims about performance improvements and database shrinks. Read Brent Ozar too. This is stuff to go into with your eyes wide open.

    • codingsense said

      Lippy,

      I went through the post. Yeah you are right. Thanks for the update :). I will edit my post accordingly.

      Thanks,
      Naveen

  6. Hi Naveen –

    Thank you very much for linking to my blog post about why it is a bad idea to shrink database. I hate to come to your blog and complain/nag you but in this case I think it is important.

    I would like to suggest that you consider altering this post a lot or perhaps removing it. The opening statement is flawed where you say “To increase your MSSQL database performance use Shrink database and files.” –> This not only will NOT increase performance, but it will likely lead to performance problems – by shrinking a data file you are effectively fragmenting your indexes, hurting performance.

    It is really a worse practice to EVER shrink a database – unless there are very specific situations where you have to shrink the database after a migration to a development environment or removing a bunch of unnecessary data.

    Please read the blog post of mine you link to and some of the posts I link to and reconsider. This is dangerous advice.

    – Mike Walsh
    SQL Server MVP, Consultant

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: