July 29, 2012 at 2:42 am
Hi All, One of my database's log space is 99% and is not coming down.
Actions taken
1) DBCC SHRINKFILE(ABC_LOG,512); -> NO LUCK
2) LOG BACKUP AND THEN DBCC SHRINKFILE(ABC_LOG,512); -> NO LUCK
3) CHANGE THE RECOVERY MODE TO SIMPLE AND LEFT IT ALONE FOR FEW HOURS SO THAT IT WOULD RELEASE THE USED SPACE.-> NO LUCK
Is there any other way to get the 99% down?
July 29, 2012 at 2:55 am
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Also this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 1:17 am
Thanks Gila Monstor. I solved it finally.
Initial Situation: Log space used 236 GB, 99.9% space used and 18328 VLFs. We still had 50 GB left on the mount point.
Steps Taken:
1)Shrink the log file to 1 GB or 2 GB.No Luck.
2)Take log back up and then try to shrink itNo Luck.
3)Since the log back was taken and the database is in Simple recovery mode, I expected the used log space will be released eventually and waited for 14 hours to see if the space is release. No luck.
-->The 3rd try was failing because the CDC was not functioning as expected and there was no failure shown in the job history and SQL Server logs. No Luck.
4)Then I stretched the log space for 6 GB more and then in less than half hour the space was released and I was able to shrink the log. Now the log space used is 0.9 MB: Succeeded
July 30, 2012 at 2:31 am
Step 6: Grow your log back out to a sensible size for the operations needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply