August 15, 2008 at 7:36 am
I had this same issue with one of my db. Apparently I had left Truncate log script for this particular db which fired before my Transaction Log backup script. Forgot to remove the db name from Truncate script which caused the issue.
August 15, 2008 at 8:48 am
Interesting. Even though you had a completely different cause for this problem, it amounted to the same thing. Somewhere, somehow the log was being truncated. In my case it was a purge through the application which we had no control over. I scheduled an extra full dump for immediately after the purge and the problem is solved. Well - the application shouldn't be truncating the log in a production environment but they do and getting a full dump after a purge is never a bad idea.
August 17, 2008 at 2:45 pm
fully review all operations that occur over the weekend before the log backup happens on the monday, something is interrupting your backup chain. Does your index rebuild or maintenance plan initiate a full backup afterwards as part of the process. This could be another way round the problem, although only a sticky plaster rather than a cure
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2008 at 5:44 pm
If the purge process is periodically issue a backup log with truncate_only, this is going to eventually break as this has been marked for depreciation in future versions of SQL Server. Also, if the purge process is issuing a backup log with truncate_only, why can't it be modified to actually backup the transaction log? That, ultimately, would be the best solution.
😎
August 18, 2008 at 3:33 am
If the backup log has been issue with Truncate_ONLY after any t-log, the Fist_LSN of the backup set with be set to the same of LAST_LSN. So at that time we cant perform the T-log backup in SQL 2005. We need to perform a full backup of that particular db and then only t-log can be taken.
Ex:
TestingL2008-08-18 14:55:562008-08-18 14:55:5625000000025000000.0025000000025000000.0025000000004400000.00
TestingL2008-08-18 14:55:242008-08-18 14:55:2425000000004400000.0025000000025000000.0025000000004400000.00
TestingD2008-08-18 14:54:302008-08-18 14:54:3025000000004400000.0025000000006000000.0025000000004400000.00
Regards
Kumar
August 18, 2008 at 7:43 am
I'd run profiler at this time and figure out exactly what is being run. Then you can make a plan to work with it.
August 18, 2008 at 8:22 am
Currently the situation is under control with an additional full dump being run after the time of the application initiated purge. The point about backing up the log with truncate_only eventually breaking is well taken. This message appears in the errorlog at the time of the purge so it's clear (now) what's happening.
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
We did not write the application and there are no options on the screen where the purge is set up except how far back to purge. I just checked a server that has a newer version of the application and the database is set to simple recovery mode but the purge still truncates the log anyway. The vendor's website recommends both truncating the log and setting the recovery mode to simple. I will pursue putting in some sort of change request to the application.
May 15, 2009 at 3:59 am
Hi,
We are experiencing the same problem.
A full backup occurs every evening at 7pm. Transaction logs run fine until 00:00 and then begin to fail, and continue to do so until the next full backup is taken.
On checking the error log i have found the following error message occurring at exactly 00:00:
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
Further to this the only other maintenance that runs each night at 00:00 is a Database Integrity Check. This has been set up using a maintenance plan.
I am not sure with a Database Integrity Check may be trying to change the recovery model from full to simple and back.
Does anyone know what the inner workings of the Integrity check maintenance plan are?
A point to mention: there are 2 databases on this server. The Integrity Check is done on both databases, but the transaction log backups only start failing on one of them.
the database on which transaction logs continue is a tiny DBA database that only us (the DBAs) use and stores DBA type stored procedures etc.
The database on which the transaction log backups fail is main database for a user application on the server which has live user activity.
Any ideas appreciated.
Thanks...:-)
May 15, 2009 at 7:21 am
By any chance the Application is Purging Data using Truncate table command?
-Roy
May 15, 2009 at 1:17 pm
An integrity check will not truncate the log. Check and make sure there's no custom script, no step added to the job.
I would suggest that you run profiler (or a server-side trace) over the time period where the truncation happens so that you can see exactly where the truncation is coming from and who's running it.
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 15, 2009 at 1:18 pm
Roy Ernest (5/15/2009)
By any chance the Application is Purging Data using Truncate table command?
Why are you asking?
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 15, 2009 at 1:29 pm
That would not be in the transaction log, would it? Would that cause a problem with Log Shipping? I know that you cannot truncate a table that is replicated. So just trying to clarify.
-Roy
May 15, 2009 at 1:55 pm
Roy Ernest (5/15/2009)
That would not be in the transaction log, would it?
Contrary to popular belief, TRUNCATE TABLE is a logged operation. It's minimally logged (page deallocations), but it is logged. It does not break log shipping, log backups or even (as far as I'm aware) database mirroring. The problem with replication isn't that the log reader won't find the log record for it (it will) but that replication is done change by change and the procs on the subscriber handle single row inserts/updates/deletes.
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 15, 2009 at 2:01 pm
I was not sure about that. Now I know for sure. Thanks for the info Gail. Learning everyday..:-)
-Roy
May 15, 2009 at 6:21 pm
i would suggest lets run a command like
while(1=1)
select * from master.dbo.sysprocesses
wait for delay 00:00:15
this is a snippet , syntax not checked and then run at night and populate to some table or to a file.
by this we will get the whole list and also find out which spid is trying to use and some more details. Though i am not sure why an app dev guy will need to run backup log with truncate_only but if it , then thats a CRAP. you cant afford to break log chain every day
go and raise this to your management and to the vendor..
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply