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.