Share via

Four Tips for SQL tuning for SharePoint Part 2 - Shrink DB

  • Article

SharePoint DB’s - To shrink or not to shrink the data file,
this is the question:

SQL Server provides you the ability to shrink the DB or
shrink the files [MDF, NDF & LDF].

Shrinking the log file/LDF is quite different from shrinking
the data file/MDF, NDF.

Usually we do not have to shrink the log file (as long as we
have a good maintenance plan of backups). We just do not want to see the log
file growing out of control.

Shrinking the data file
however, is an option that when triggered, will remove unused space from the DB
file and free space on the hard drive

In SharePoint activities such as deleting items and
libraries, might create unused space, especially the "move site".
So if shrinking is such a useful thing, and it frees space on my hard drive,
why not do it?

Well, shrinking a data file has a big downside; it causes
index fragmentation in the DB. Index fragmentation has a huge negative impact
on the DB performance. Therefore, shrinking should be avoided or should be
rarely used.

Do's and don’ts.

  1. Don’t - do not use the auto shrink option in the database property.
    run this T-SQL script to identify if Auto Shrink is ON

  2. Do - shrink your DB only if you have more than 50% of
    free space on the data file, or after a massive deletion.

  3. Don’t - do not shrink log file. Shrink it only if it grew
    as a result of one time maintenance that caused it to grow beyond the usual
    working size. There is a correct way to shrink the log file, keep in mind the
    multiple VLFs problem.

  4. Don’t - the action of shrinking requires high load of CPU
    resources and memory, it might take quite a bit of time to complete, give it a
    lot of thought when you decide to use it.

  5. Do - after shrinking the DB run an index maintenance
    plan; if you are using SharePoint 2007 SP2 and above use the internal
    SharePoint job for defragging indexes, otherwise use the SQL maintenance plan.

Oren B.

Comments

  • Anonymous
    January 01, 2003
    Hello Glenn, Thanks for clearing the idea on shrinking the database. Recently, My team did shrinking the database of Project server reporting & we noticed the performance issue. Great !!

  • Anonymous
    January 01, 2003
    Hello Glenn, The general recommendation is not to shrink the DB. The shrink process does not impact the backup so after a shrink you don’t have to be wary for the LSN’s. I will recommend you to have a schedule backup process for the full and log. You must not forget that the shrink action generate records in the T-LOG file. So be careful and use shrink only if you really need

  • Anonymous
    January 01, 2003
    Hello Glenn, Great article !

  • Anonymous
    January 01, 2003
    I also like to kick of a full database backup after the shrink so that I can maintain the LSN's of the database, this is a must if using Full or Bulk Recovery Mode.

  • Anonymous
    September 24, 2014
    Part 1 – MAX DOP:
    http://blogs.technet.com/b/sqlpfeil/archive/2012/02/01/four-tips-for-sql-tuning-for