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 orshrink the files [MDF, NDF & LDF].
Shrinking the log file/LDF is quite different from shrinkingthe data file/MDF, NDF.
Usually we do not have to shrink the log file (as long as wehave a good maintenance plan of backups). We just do not want to see the logfile growing out of control.
Shrinking the data filehowever, is an option that when triggered, will remove unused space from the DBfile and free space on the hard drive
In SharePoint activities such as deleting items andlibraries, 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 causesindex fragmentation in the DB. Index fragmentation has a huge negative impacton the DB performance. Therefore, shrinking should be avoided or should berarely used.
Do's and don’ts.
Don’t - do not shrink log file. Shrink it only if it grewas a result of one time maintenance that caused it to grow beyond the usualworking size. There is a correct way to shrink the log file, keep in mind themultiple VLFs problem.
Don’t - the action of shrinking requires high load of CPUresources and memory, it might take quite a bit of time to complete, give it alot of thought when you decide to use it.
Do - after shrinking the DB run an index maintenanceplan; if you are using SharePoint 2007 SP2 and above use the internalSharePoint job for defragging indexes, otherwise use the SQL maintenance plan.
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.
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
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 article !