August 8, 2012 at 11:39 pm
Hello All,
We have a DB for which the Tran log became full. The DB is in simple recovery model. We had a bulk transaction which filled up the log.
So I changed the recovery model to Full. Took a Full Backup and then took a Transaction log backup hoping that Log file would be truncated. But The log does not get truncated. It is kind of mysterious? DO you have any recomendations?
What might be the cause log is not getting truncated?
August 9, 2012 at 12:03 am
na1774 (8/8/2012)
So I changed the recovery model to Full. Took a Full Backup and then took a Transaction log backup hoping that Log file would be truncated. But The log does not get truncated.
Do you mean log file (.ldf) size not reduced?
If yes, you need to shrink the file.
August 9, 2012 at 12:31 am
Hi, Thanks for the reply.
I donot want the log file size to be reduced. I want to truncate the log file. When I run dbcc sqlperf(logspace) the log % full is 99.889. Even after taking the log backup still the %full is 99.89.
By the way I am using sql server 2008.
August 9, 2012 at 12:37 am
Please run this:
DBCC OPENTRAN()
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'dbname'
August 9, 2012 at 4:56 am
i think can u truncate the logfile Right...so please Use this below command...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Thanks,
SQL server DBA
August 9, 2012 at 12:02 pm
Thank you for the reply.
But I am using SQL serve 2008.
BACKUP LOG <DBNAME> WITH TRUNCATE_ONLY is no longer usable in 2008. I already tried that.
August 9, 2012 at 12:05 pm
Suresh B. (8/9/2012)
Please run this:
DBCC OPENTRAN()SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'dbname'
Have you done this yet? If so, what are the results?
August 9, 2012 at 10:53 pm
I'm undoubtedly not as experienced as some of the guys answering here, but I've had similar issues with log files resolved by changing to full recovery model, shrinking, then changing back to simple recovery mode.
August 10, 2012 at 3:33 am
We just upgraded to SQL Server 2008.
The Script was:
DBCC SHRINKFILE(Test, 1)
BACKUP LOG Test WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Test, 1)
GO
The error was:
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
In My experience,I have modified that code to subsequent code and it works fine.because above code shows the Error in SQL SERVER 2008.
USE [master]
GO
ALTER DATABASE [BULKDATABASE] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(BULKDATABASE_Log, 1)
ALTER DATABASE [BULKDATABASE] SET RECOVERY FULL WITH NO_WAIT
GO
NOTEL:TRUNCATE_ONLY is not supported or discontinued from SQL Server 2008.As all mention best and good way to truncate is scheduling log backup according to your transaction voulme..
SQL server DBA
August 10, 2012 at 5:48 am
Truncate log doesn't work on SQL 2008. The replacement is simple recovery model.
Please read through 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
August 10, 2012 at 6:55 am
My guess is that a checkpoint within SQL Server needs to happen to recognize the transaction is complete and will flush the data out of the transaction log. Are you sure the job is done and committed?
August 10, 2012 at 11:06 am
i've had siutations like this where a simple recovery DB has its log get out of control and i need to flip to full recovery in order to do log backup.
in those cases, when i really don't care about restore ability at all I backup to nul 🙂
backup database/log x to disk = 'NUL'
In your case it sounds like an open transaction or something like that... Like Markus/Gila/Lynn talk about, checkpoint it and/or check for open transactions
August 10, 2012 at 11:31 am
There is never any need to switch to full recovery to manage the log. All switching to full does is add extra requirements before the log can be reused and in some cases cause extra logging.
In simple recovery a checkpoint marks the log reusable if nothing else needs the log.
In full recovery a checkpoint is not enough and log backup is required to mark the log reusable if nothing else needs the log.
If the log isn't getting reused, the solution involves identifying what else needs log records and is preventing the truncation.
See the article I posted earlier and also this one. 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
August 10, 2012 at 11:33 am
Jeff Kelly-310227 (8/10/2012)
in those cases, when i really don't care about restore ability at all I backup to nul 🙂backup database/log x to disk = 'NUL'
Rather just switch to simple recovery if you don't care about backing up the log. That backup to NUL is particularly nasty, it breaks the log chain but SQL doesn't know that and allows future log backups which will be unusable.
The only safe use for backup to NUL is testing backup throughput.
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
August 10, 2012 at 12:05 pm
These are DB's that have been in simple recovery mode, the log gets full for some 'unknown' reason and we could not shrink it or get it back under control.
The quick (hack/non proper way) way to solve this in the past for me has been to flip to full, backup db to nul, backup log to nul, shrink as needed. (again this for simple recovery DB's that are completely disposable and have no backup plan in place)
With that said, there are likely proper/better ways to do this, and your link definitely shows those (thanks, will be saving and using next time for sure!)
A good take away from this just because 'you' (me in this case) understand the implications of something, doesn't mean others do, and one must be care when offering advise on forums like these.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply