SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL 2005 Tran Log Backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bcobb2350
Starting Member

USA
3 Posts

Posted - 04/29/2014 :  09:53:45  Show Profile  Reply with Quote
I have a SQL 2005 instance with two SQL Agent backup jobs. The full runs at 1:00 am and the tran log runs every 15 minutes.

The tran log backups ran perfectly until the nightly full backup. Then, the job ran once more. It failed after that with the error - 'BACKUP LOG cannot be performed because there is no current database backup.'

If SQL thought the last full backup wasn't valid, why did it run the tran log backup job once after the full backup?

Help?

Bill Cobb

bcobb2350
Starting Member

USA
3 Posts

Posted - 04/29/2014 :  11:35:26  Show Profile  Reply with Quote
Answered my own question. I do a full backup at 3:00 am - it takes about 42 minutes. I do a DBCC Shrinkfile on the log file at 3:00 am. There's one log backup between the full backup and the DBCC shrink file. The DBCC Shrinkfile script puts the DB in SIMPLE Recovery mode. Even though it switches it back to FULL, there has to be a full backup before the tran log backups will work.

The solution - run the DBCC Shrinkfile before the full backup.

Bill Cobb
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37310 Posts

Posted - 04/29/2014 :  11:48:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you shrinking the log file? It should never be done automatically. And I completely disagree with switching the recovery model to SIMPLE as that breaks the log chain and limits your data recovery options.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bcobb2350
Starting Member

USA
3 Posts

Posted - 04/30/2014 :  07:18:51  Show Profile  Reply with Quote
Tara,

You're probably right. I have to admit I am (in a large part) a Google DBA.

The scripts I've ran across always put the DB in SIMPLE recovery mode before doing DBCC SHRINKFILE on the log file.

Are you saying that is not 'best practice' and I should leave the DB in FULL recovery mode while doing the DBCC SHRINKFILE on the log file?

I 'inherited' a SQL instance that did an Index Rebuild every night. The Rebuild expanded the Log file and it was in danger of running out of disk space.

My SHRINKFILE was a defensive move. This instance is on an old DELL server that was brought online in 2006. There is no (financial) possibility it will get more disk space.

I'm open to suggestions. How would you handle this situation?

I appreciate your response and your honesty.

Bill Cobb
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37310 Posts

Posted - 04/30/2014 :  12:03:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'm saying that you should not have a scheduled job that runs DBCC SHRINKFILE. Shrinking the files should be done manually and on very, very rare occasions.

If you are in danger of running out of disk space, then you need to correct that situation. Either stop rebuilding the indexes or add more storage. You could instead reorganize the indexes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000