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 |
|
JaybeeSQL
Posting Yak Master
112 Posts |
 Posted - 2012-07-08 : 08:42:17
|
| Hi all, Needed to defrag some indices today and the log file has swelled from 1.5gb to 150gb, DBCC Shrinkfile is having no effect as the logspace reads 96% used. I'd rather not backup log as this might break the log shipping chain (this is a primary db).Any ideas how I can get this log file back down to size? I can rebuild log shipping again if theres absolutely no other way to preseve the LSN.Cheers,JB |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-07-08 : 10:00:56
|
| Did you rebuild all indexes - or did you rebuild just those indexes that needed to be rebuilt?How large is the largest index?How large is the database?How often are you going to be rebuilding your indexes?If the log file needs to be 150GB to support these operations, then that is how large it needs to be. You could reduce how large it gets by performing more frequent log backups during the rebuild (yes, this will have to be shipped also).The only way to reduce the log size at this point is to perform a log backup to mark the VLF's as available. Once that is done, then you will be able to shrink the file again. However, the log file will grow again if you are rebuilding your indexes on a regular basis. |
 |
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2012-07-08 : 11:43:14
|
| Cheers Jeff,Question, should it be Backup Log with Truncate? Or to a backup device? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-07-09 : 14:07:52
|
| Backup Log with Truncate is deprecated and will not work in SQL Server 2008. That is not a good way to manage transaction logs - and it will not work for a log shipped database because that would break the log chain and therefore, break log shipping.Perform a normal transaction log backup - then you will be able to shrink the file. However, if you are going to rebuild your indexes on a regular basis (it is recommended that you rebuild indexes that need to be done only - on a regular basis) then you will run into this problem again.You need to size the transaction log large enough to handle these processes. |
|
|
|
|
|
|
Huge log file