Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bcobb2350
Starting Member
3 Posts |
 Posted - 2014-04-29 : 09:53:45
|
| 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
3 Posts |
Posted - 2014-04-29 : 11:35:26
|
| 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 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-29 : 11:48:29
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
bcobb2350
Starting Member
3 Posts |
Posted - 2014-04-30 : 07:18:51
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-30 : 12:03:21
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
SQL 2005 Tran Log Backup