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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy