October 26, 2016 at 6:09 am
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.
October 26, 2016 at 6:36 am
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.
Having worked with a SharePoint system for a while, I'll try to give you some pointers.
First, shrinking the logs will fragment indexes, so you'll need to plan for some index operations afterwards.
Second, as far as the size of the transaction logs, SharePoint didn't seem to appreciably grow the log files on the server I maintained, except when the SharePoint admin would do some maintenance operations (cleaning up old document stores, etc.) Kimberly Tripp on SQLSkills.com has a couple good posts about sizing and growth rates for your transaction logs (http://www.sqlskills.com/blogs/kimberly/category/transaction-log/[/url])
As for what you should shrink them too, that you're going to have to determine yourself, because it largely depends on the use of the database in question. I believe most of mine were around a couple GB or so and we were only taking log backups every 2 hours. Best suggestion I can make, would be to shrink them as far down as possible, immediately resize to 4-8GB or so, then set a reasonable growth rate and deal with the potential performance hit when the log needs to grow. After a while, the logs will hit the size they need to be for normal operations and stop growing.
Jason
October 26, 2016 at 6:46 am
jasona.work (10/26/2016)
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.
Having worked with a SharePoint system for a while, I'll try to give you some pointers.
First, shrinking the logs will fragment indexes, so you'll need to plan for some index operations afterwards.
Second, as far as the size of the transaction logs, SharePoint didn't seem to appreciably grow the log files on the server I maintained, except when the SharePoint admin would do some maintenance operations (cleaning up old document stores, etc.) Kimberly Tripp on SQLSkills.com has a couple good posts about sizing and growth rates for your transaction logs (http://www.sqlskills.com/blogs/kimberly/category/transaction-log/[/url])
As for what you should shrink them too, that you're going to have to determine yourself, because it largely depends on the use of the database in question. I believe most of mine were around a couple GB or so and we were only taking log backups every 2 hours. Best suggestion I can make, would be to shrink them as far down as possible, immediately resize to 4-8GB or so, then set a reasonable growth rate and deal with the potential performance hit when the log needs to grow. After a while, the logs will hit the size they need to be for normal operations and stop growing.
Jason
Thanks for the reply. It surprises me that shrinking only the log file would cause index fragmentation. Not doubting you, it just seems like it would be a non issue so I'm glad I asked first! Re-indexing is going to be problematic as it is handled (so they tell me) by SharePoint itself via the Health Advisor. I tried to do a reorganize and these failed for lack of exclusive locks it said, but I've got to investigate that further. I do have it connected to my SQL Monitor installation and sure enough, the monitor does not report excessive fragmentation so I guess it's good although some complain it is sluggish at times. The sizes of these SP databases are much larger than our other production server databases -- by an order of magnitude. We aren't in a space crunch (yet) and this server is to be migrated in the (reasonably) near future so my task is to keep it up and running til then. I've already pitched to them that it is imperative to tend to their new installation's databases from the get-go -- as opposed to 3 years after the fact.
Again, I appreciate your input and advice. Thanks!
October 26, 2016 at 6:56 am
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])
October 26, 2016 at 6:59 am
jasona.work (10/26/2016)
First, shrinking the logs will fragment indexes, so you'll need to plan for some index operations afterwards.
Careful, now. Shrinking the logs has no effect on fragmentation of indexes. That's only if you shrink the MDF file.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2016 at 6:59 am
jasona.work (10/26/2016)
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.
<snip>...First, shrinking the logs will fragment indexes, so you'll need to plan for some index operations afterwards. <snip>...Jason
Shrinking DATA files will cause index fragmentation,shrinking LOG files will not cause index fragmentation.
October 26, 2016 at 7:00 am
Jeff Moden (10/26/2016)
jasona.work (10/26/2016)
First, shrinking the logs will fragment indexes, so you'll need to plan for some index operations afterwards.Careful, now. Shrinking the logs has no effect on fragmentation of indexes. That's only if you shrink the MDF file.
Damned, I need to type faster :w00t:
October 26, 2016 at 7:11 am
Siberian Khatru (10/26/2016)
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?
First, shrinking log files will has nothing to do with fragmenting indexes. And, even if you make a mistake, you won't necessarily experience any issues related to VLFs (although it could be affecting things more than you know)... until someone tries to do a restore, which can be slowed down quite a bit by too many relatively tiny VLFs.
My recommendation, especially since you've set the recovery model to SIMPLE, is to figure out what size the log file should be and, if less than 8GB, make sure the growth setting is set to a given number of MB (rather than %), shrink the log file to 0 (during a quiet time) and then immediately grow it to the desired size. This will build a super clean, well formatted (VLFs) log file that could actually help performance a bit and will certainly help performance of any restores that come to pass.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2016 at 7:13 am
Jeff Moden (10/26/2016)
jasona.work (10/26/2016)
First, shrinking the logs will fragment indexes, so you'll need to plan for some index operations afterwards.Careful, now. Shrinking the logs has no effect on fragmentation of indexes. That's only if you shrink the MDF file.
D'oh. I wasn't sure, did a quick Google search and didn't bother to do more than read a couple quick summaries as I wasn't 100% sure if it did or didn't. I know shrinking the MDFs will fragment indexes...
Thanks for correcting me on this!
Back to the OP, I did some digging when I was handling the SharePoint DBs, and MS expects you to leave them alone. Don't manage the indexes, don't add indexes, don't remove indexes, don't change tables, nothing.
As for the size of your DBs, it might be worth finding out how the SP admins have configured the site collections. In our case, we had many different site collections, each of which had its' own dedicated site collection database. This kept the overall size of individual DBs down, which made backups rather easier (if we had had one big site collection DB, it probably would've been around 1-1.5TB, and our 3rd party backup software would've choked on it...)
It might be worthwhile for both them and you to look at, if possible, splitting the various sites into their own DBs (all using the same SharePoint farm and backend SQL Server.)
Jason
October 26, 2016 at 7:27 am
Yes, I do realize that switching these all to SIMPLE will render point in time recovery as impossible. I made it a point to mention that to the proponents SEVERAL times, and they didn't flinch at all.
Ok, so to recap a bit then, shrinking only the log FILE will not cause fragmentation, which is as I originally thought. My first task then would be to switch all of these databases (nearly a hundred) to SIMPLE to stop the unchecked growth. Then I need to shrink the log files themselves and set the starting size according to how big they might become (if I can ascertain that somehow), setting the autogrowth to a value that minimizes the level of VLF growth to the extent that is possible. Is that about right?
I'm kind of torn here as to whether or not to bother other than to switch to SIMPLE. I would guess at this point that leaving the log files as is after being switched to SIMPLE recovery model would not be harmful as an alternative to screwing with it too much, especially since it will be migrated after a while. I won't be doing anything to SharePoint in terms of indexing, neither creating, altering nor deleting them and leaving the re-indexing to SharePoint itself. I am at least doing weekly integrity checks now and it is being monitored by SQL Monitor so if an index does get a bit squirrelly, I can deal with that at that time.
Thanks folks, much obliged.
October 26, 2016 at 8:05 am
Siberian Khatru (10/26/2016)I'm kind of torn here as to whether or not to bother other than to switch to SIMPLE. I would guess at this point that leaving the log files as is after being switched to SIMPLE recovery model would not be harmful as an alternative to screwing with it too much, especially since it will be migrated after a while.
Most folks will certainly recommend just leaving the log files as they are. I'll say "It Depends" and now may be a great opportunity to fix some previous sins especially if the log files were original built using the SQL Server defaults.
You might be able to get a bit of a performance increase if you rebuilt the logs with more sane settings. The BIG thing is planning for the inevitable... the need for an urgent restore even if it's not point-in-time. Even "instant file initialization" won't help in the area of log files during a restore. They shouldn't be overly large and they shouldn't have the bazillion VLFs that the default settings create. Both slow restores down a lot.
You could write a script to rip through all the databases to do this at the same time that it sets the databases to SIMPLE.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2016 at 8:16 am
p.s. As for ascertaining the recommended size for the log files, you could write a script to (for each DB)...
1. Set the SIMPLE recovery model.
2. Change the initial size and growth of each log file to 100MB each.
3. Shrink the log file to 0 (don't worry about pregrowing it here, it'll grow)
4. Set the FULL recovery model.
5. Wait for a week.
After a week passes, the log files will have grown because of the FULL recovery model. It won't be a perfect guesstimate but if you programmatically divide the size of the log files by the number of days the log file has been allowed to grow, that might make a good initial size.
1. Set the SIMPLE recovery model.
2. Shrink the log file to 0
3. Change the initial size of the log file using the "average daily" thing I spoke of above rounded down to the nearest 100MB. Change the growth to some reasonable number to prevent the rampant creation of VLFs.
4. Drink beer. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2016 at 8:17 am
Jeff Moden (10/26/2016)
Siberian Khatru (10/26/2016)I'm kind of torn here as to whether or not to bother other than to switch to SIMPLE. I would guess at this point that leaving the log files as is after being switched to SIMPLE recovery model would not be harmful as an alternative to screwing with it too much, especially since it will be migrated after a while.
Most folks will certainly recommend just leaving the log files as they are. I'll say "It Depends" and now may be a great opportunity to fix some previous sins especially if the log files were original built using the SQL Server defaults.
You might be able to get a bit of a performance increase if you rebuilt the logs with more sane settings. The BIG thing is planning for the inevitable... the need for an urgent restore even if it's not point-in-time. Even "instant file initialization" won't help in the area of log files during a restore. They shouldn't be overly large and they shouldn't have the bazillion VLFs that the default settings create. Both slow restores down a lot.
You could write a script to rip through all the databases to do this at the same time that it sets the databases to SIMPLE.
Good points. I am going to do the switch first (in the midst of that actually) and then come back through once I have an idea how these logs are used. remember, they have been untouched for YEARS so who knows right now. I look at this as a learning opportunity 🙂
October 26, 2016 at 11:44 am
Jeff Moden (10/26/2016)
p.s. As for ascertaining the recommended size for the log files, you could write a script to (for each DB)...1. Set the SIMPLE recovery model.
2. Change the initial size and growth of each log file to 100MB each.
3. Shrink the log file to 0 (don't worry about pregrowing it here, it'll grow)
4. Set the FULL recovery model.
5. Wait for a week.
After a week passes, the log files will have grown because of the FULL recovery model. ...<snip>
This could blow the size (and fill up the filesystem) quickly on a busy system!! Unless you perform regular LOG backups (which is not mentioned in the above steps ;-)). Regular LOG backups determine the final size of the LOG backup, depending on the frequency of the backups.
I would skip step 4 and leave the database in SIMPLE recovery. After a week I would determine the size as to the size after a week plus an additional 20% or 30%.
October 26, 2016 at 11:57 am
HanShi (10/26/2016)
Jeff Moden (10/26/2016)
p.s. As for ascertaining the recommended size for the log files, you could write a script to (for each DB)...1. Set the SIMPLE recovery model.
2. Change the initial size and growth of each log file to 100MB each.
3. Shrink the log file to 0 (don't worry about pregrowing it here, it'll grow)
4. Set the FULL recovery model.
5. Wait for a week.
After a week passes, the log files will have grown because of the FULL recovery model. ...<snip>
This could blow the size (and fill up the filesystem) quickly on a busy system!! Unless you perform regular LOG backups (which is not mentioned in the above steps ;-)). Regular LOG backups determine the final size of the LOG backup, depending on the frequency of the backups.
I would skip step 4 and leave the database in SIMPLE recovery. After a week I would determine the size as to the size after a week plus an additional 20% or 30%.
I believe Jeff's goal (other than getting to the "drink beer" step) was to ascertain how much log space is consumed during a normal period of time - one week in this case. Taking regular log backups will cause the backed up portion of the log to be available for re-use, which will negate the goal of the exercise.
I fully agree that having regularly-scheduled log backups is essential for any database in the full recovery model. However, it would eliminate the ability to see how much log space is actually used. This would become the size to use for setting the new initial log file size when the exercise is over.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply