Do what?
So….I’ve got this job that backs up the T-log every 15 minutes (SQL 2005). Works like a champ across all servers.
Out of the blue, it starts failing with a “No full backup detected”, despite the fact that one occurred less than an hour ago. Its recorded in the msdb system tables and the file is right where the full backup job left it.
The customer had decided for reasons unknown to create a job that does this (every hour):
Use MyDB
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
BACKUP LOG MyDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
go
I kid you not. And no…there is not a second file to EMPTY the log contents into.
For those of you new to the game, when you Truncate a T-log, the sequence is broken and SQL Server barfs on future log backups until you run a full.
Kudos to my co-worker (code name Freakshow) for catching this as I was headed to the ERRORLOG to see what was going on. He nailed in 10 seconds what would have taken me 7 minutes. And then he knew it was a new job while I was still reading the log. Scary smart, that dude.
No fries, but ketchup squirted all over that client.
Kevin3NF
Leave a Reply