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

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.