May 2, 2011 at 10:46 pm
Hi,
I have a transaction log that has grown over 2GB on a SQL Server 2008 server and I can't dump the log. I tried leveraging BACKUP LOG WITH TRUNCATE_ONLY command but get a message that the log is in use and there is not much space. I also learned from another blog that BACKUP LOG WITH TRUNCATE_ONLY command only works on SQL server 2005.
Any help is appreciated. Thanks.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 2, 2011 at 11:38 pm
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
May 3, 2011 at 12:18 am
Dear Gila Thanx...
Can we set the VLFs on our own wish...or May We know how much VLFs is currently using by our log file.
If yes:Will this number be same for all databases log file?;-)
Thanks
May 3, 2011 at 12:33 am
Please post new questions in a new thread, don't hijack other people's threads.
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
May 3, 2011 at 12:39 am
Forgive Me..
Thanks
May 3, 2011 at 9:39 am
GilaMonster (5/2/2011)
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Thanks Gail! The article is very informative.
I ran select log_reuse_wait_desc , * from sys.databases and my log_reuse_wait_desc is REPLICATION. We do have Transactional Replication on this database and it is the culprit. The Log Reader Agent is running so I am not sure why it's not marking the replicated portions of the log inactive for reuse? At this point I am not sure I can do anything but wait for the next CHECKPOINT.
Your thoughts.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 3, 2011 at 9:40 am
It could be that the log reader is falling behind. Start checking the replication agents' logs.
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
May 3, 2011 at 10:08 am
GilaMonster (5/3/2011)
It could be that the log reader is falling behind. Start checking the replication agents' logs.
I think it has. I will check the logs.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
May 3, 2011 at 10:17 am
you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate it
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 3, 2011 at 10:31 am
Syed Jahanzaib Bin hassan (5/3/2011)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
What command?
May 3, 2011 at 11:31 am
Syed Jahanzaib Bin hassan (5/3/2011)
you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate it
The database is in Simple Recovery. Hence any log backup will fail. You cannot take a log backup of a database in Simple Recovery model.
Even if it were possible, it would not help in the slightest, as the output of sys.databases shows the cause of the log not being reused is replication, not log backups. Hence the solution is to fix the replication, not flair around guessing wildly.
Oh, and BACKUP LOG WITH TRUNCATE_ONLY does not work on SQL 2008 or SQL 2008 R2, no matter how many log files there are nor whether there's been a log backup first, nor what recovery model the DB is in. It was deprecated in SQL 2005 and removed in SQL 2008
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
May 3, 2011 at 11:44 am
GilaMonster (5/3/2011)
Syed Jahanzaib Bin hassan (5/3/2011)
you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate itThe database is in Simple Recovery. Hence any log backup will fail. You cannot take a log backup of a database in Simple Recovery model.
Even if it were possible, it would not help in the slightest, as the output of sys.databases shows the cause of the log not being reused is replication, not log backups. Hence the solution is to fix the replication, not flair around guessing wildly.
Oh, and BACKUP LOG WITH TRUNCATE_ONLY does not work on SQL 2008 or SQL 2008 R2, no matter how many log files there are nor whether there's been a log backup first, nor what recovery model the DB is in. It was deprecated in SQL 2005 and removed in SQL 2008
Edited to protect the "inocent". Or whatever.
May 3, 2011 at 12:00 pm
MostInterestingMan (5/3/2011)
GilaMonster (5/3/2011)
It could be that the log reader is falling behind. Start checking the replication agents' logs.I think it has. I will check the logs.
Could be anything from huge transactions to excessive VLFs to IO contention.
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
May 4, 2011 at 9:55 am
Replication is a very complex topic. There are numerous tuning options you have if it isn't keeping up and that is causing the tlog growth. Probably best to get a professional in to help you - you can FUBAR things pretty easily where replication is concerned.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 4, 2011 at 11:25 am
TheSQLGuru (5/4/2011)
Replication is a very complex topic. There are numerous tuning options you have if it isn't keeping up and that is causing the tlog growth. Probably best to get a professional in to help you - you can FUBAR things pretty easily where replication is concerned.
Thanks for the suggestion but I work in a small shop and we can't afford consultants.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply