• Skip to content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

Header Right

  • Home
  • Blog
  • Services and Pricing
  • Speaking and Training
  • Contact Us
  • About Us

Do What?

October 25, 2007 by Kevin3NF Leave a Comment

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

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: backup, Fries

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Search

Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Encryption
  • EntryLevel
  • Fries
  • HADR
  • Index
  • Install
  • Oracle
  • Performance
  • Personal
  • PowerShell
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Training
  • TSQL
  • TSQL2sday
  • Uncategorized
  • vendor code modify
  • vNext

Copyright © 2018 · Genesis Sample on Genesis Framework · WordPress · Log in

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.