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 |
|
chris_cs
Posting Yak Master
223 Posts |
 Posted - 2009-09-07 : 04:15:59
|
| Hi all,I've just taken ownership of a Db that looks like it has had no real admin done on the log file. There are no regular backups taken so it has grown to quite a large size.The size of the data file is about 9.7Gb and the log file is about 7.6Gb. This, I'm assuming is way too big.I took a backup of the file and tried shrinking it but with no success. What else can I try to reduce the size? Also, is there a best practice ratio that the size of the log file should be? |
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-09-07 : 04:55:22
|
| It seems like the auto growth is set to a % of the file size which is a BAD practice and the log wasn't set to a proper initial size which will cause the log to have large number of VLFs which will in turn cause fragmentation. Try to find out the number of VLFs using DBCC LOGINFO. Please not that the number of rows returned is equal to the number of VLFs( more than 50 is a red flag). A best practice regarding Transaction log creation/maintenance is to create the log in 8 GB chunks(thanks to Kimberly Tripp). Hope that helps. |
 |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2009-09-07 : 05:16:15
|
| Thanks for the reply. I just ran the DBCC LOGINFO command and 384 rows were returned so I'm a little concerned. I ran a full log backup with the option to truncate the log, but the same number of rows are being returned. What should I do to try and reduce this number? |
|
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-09-07 : 11:56:10
|
| Not sure if you id this already, did you backup the transaction log? Also, 384 VLFs are way too many. Let us know. |
|
|
|
farax_x
Starting Member
3 Posts |
Posted - 2009-09-08 : 12:50:26
|
| if u want to get rid of log 1- backup your db2- set recovery model to : SIMPLE (ALTER DATABASE [db] SET RECOVERY SIMPLE WITH NO_WAIT)3- shrink to size that u want :USE [db]GODBCC SHRINKFILE (N'db_Log' , 1)GO4 - set recovery model back |
|
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-09-08 : 14:14:11
|
| farax_x, the method you suggested breaks the continuity of the log. Please make a note of that. |
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2009-09-09 : 04:46:25
|
| I ran a backup of the transaction log and it didn't have an affect on the amount of VLFs. If I use the method suggested by farax_x, would the only downside of that mean I wouldn't be able to perform point in time recovery? |
|
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-09-09 : 16:41:53
|
| Yes. |
|
|
|
|
|
|
Transaction log size