Siberian Khatru (10/26/2016)
As a bit of background, I've just been given charge of maintaining an existing (and thoroughly neglected) SharePoint database server. Having started regularly integrity checks, my attention has turned to the recovery models which are currently mixed between Simple and Full. The powers that be have deemed it that they are not interested in up to the minute restoration of SharePoint databases (I think they're crazy, but I digress) and the only backups they've had have been via an appliance (AppAssure if it matters) that backs things up. Whatever, that's their choice and all my own production databases are set to Full with regular tran log backups as well so I am otherwise set.Anyway, many of the log files for the databases currently in Full recovery are predictably huge. Switching a couple to Simple, I see that the log files have truncated, making 98-99% of the space free. So here's my question then; Can I run a DBCC SHRINKFILE (or equivalent Management Studio Command) on these log files without causing index fragmentation? I think not, but I figured it's best to ask first and not regret it as this is a prod server. Also, what should I set the files to be shrunk to? Is there any way to tell this so I don't experience VLF issues later as the log grows to a more normal size for the Simple recovery model?
I'm still learning so I'm sorry if this is obvious to anyone else.
Sharepoint is an application that depends on several databases. These databases all depend on each other and also depend on settings in the Sharepoint application itself. Microsoft advises to backup Sharepoint with a farm backup (don't know if this name is correct). The backup should include the (Sharepoint system) database backups as well as configuration settings and sites from the application. There are also site content database that contains the data of each site. These databases can be backupped independend of the farm backup. Look at Sharepoint documentation on the requirements/best practices for the recovery model of the different type of databases.
Back to your question: Without a LOG backup of a database in FULL recovery the LOG will never release it's space. Only by performing a LOG backup (or swith to SIMPLE recovery as you did) the space will be released. Btw: I hope you understand that by switching to SIMPLE recovery you disabled the possibility to perform a point-in-time restore!
A database LOG file does not contain any data. Thus a LOG shrink will never cause index fragmentation. It could lead to fragmentation of the file itself when the file needs to grow again. To find how large your LOG file should be is depending on the activities perormed on the database. Even in SIMPLE recovery mode long running transactions and/or transactions modifying a lot of data need a lot of log space.
From my experience most databases have sufficient space in the LOG if it is about 1/3 of the DATA size. But like I said above, it basically depends on the actions. Some databases only require very small LOG files, while other sometimes having the LOG the same size as the DATA.
You can shrink the LOG to a rather small size and enable autogrowth. Check the size after a week or month to determine the actual required size. Add an additional 10% to get on the safe side. Next shrink and resize the LOG to the desired size to minimize the VLF's (see this post from Kimberly Tripp[/url])