Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trio2shine
Starting Member
3 Posts |
 Posted - 2012-05-31 : 12:37:37
|
| I've inherited a bit of mess in our Sharepoint databases.We have various databases running various recovery models.Our largest database the crawl database is running in SIMPLE and is growing quite large 65GB database and 17GB log. My best guess at this point would be to run a shrink on this as I can't see why this has grown to the size it has?Secondly for some reason the remaining databases are set to full and no log backups were taking place and some of the logs have grown out of control. I've asked our sys admin to begin performing log backups but if I understand correctly this will only truncate the log and I'll still need to run a shirnk against these logs to free up some disk space?Forgive my lack of knowledge (just getting started). From what I've read running shrinks can be quite tasking on the database and cause fragmenation amoung other issues. My last question would be should I do all of this after hours if at all?Thanks in advance. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-05-31 : 14:37:40
|
| First issue - I would use the Disk Usage report (right-click on the database, select Reports, Standard Reports, Disk Usage). See how much space is available in the database. If there is a lot of unused space, you could consider shrinking the file as a one time operation - but my guess is that the database won't have a lot of space available.Second issue...once the transaction log backups have been scheduled and running, monitor the size of the log backups over time. Identify the largest log backup in that time frame (probably a week - but might need to monitor for a lot longer depending on what kind of processes you have running). Once you have that identified, then you can perform a one time shrink of the log files to a size a bit larger than that largest log backup.Shrinking a transaction log file will not cause any issues with fragmentation - but, if you shrink it too small you will suffer performance issues as the file grows out again.You should also validate the autogrowth settings - and set them to a reasonable size in MB instead of the default percentage. |
 |
|
|
trio2shine
Starting Member
3 Posts |
Posted - 2012-06-01 : 11:21:16
|
thanks so much for the response.It's really odd, before I got your reply we ran a full backup on the CrawlStoreDB and then switced the model to FULL. I then ran a shrink on the log file and it went from 17GB to around 560KB. I come in this morning and for some reason (need to talk to my admin) no log backups ran but a full did run. Now the issue is the log grew from 560KB to 66GB !?!?!The stats from the report read Data Space used by Data files: Space reserved 62.36GB Space Used 33.89GBThe log file on the other hand shows a ton of unallocated space. When the log backup runs is that going to actually resolve this issue or is there something more I need to do? Thanks in advance. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-06-01 : 14:15:45
|
| After you switched from simple recovery model to full recovery model - you have to perform a full backup to start the log chain again. Without that, subsequent log backups will fail - and as you found out, the transaction log will just continue to grow.Once you have frequent transaction log backups running, the log file will stabilize at the size it needs to be for the transactions you are running during that timeframe. There are situations where the transaction log cannot be truncated - which will cause it to grow and sometimes continue to grow. For example, if you have an open transaction that begins at 8am and doesn't commit until 10pm - the transaction log cannot be truncated until after the commit.Looks like you have a bit over half the database used. Whether or not you shrink that database depends on whether or not you are going to use the available space again. I would leave it alone because you will probably use that space in less than a year and would just have to grow the database again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-01 : 16:11:00
|
| If the transaction log regularly grows to a certain size, then don't bother shrinking it. Either leave it in SIMPLE recovery, or make more frequent transaction log backups.Are you mirroring or replicating those databases? That could prevent the log from clearing after a log backup is made. |
|
|
|
trio2shine
Starting Member
3 Posts |
Posted - 2012-06-04 : 15:31:50
|
| Thanks everyone, FULL recovery with more frequent log backups it is. |
|
|
|
|
|
|
|
|
Sharepoint Backups