Database goes into recovery when log is backed up

Question

Database goes into recovery when log is backed up

MostInterestingMan

SSCertifiable

Points: 6291
More actions
March 30, 2010 at 8:01 am

#220971

All,
I have a db that goes into recovery each night when we backup the log. I have never experienced this before and the problem began last week after we restored a new production copy to our dmz environment.
Any help is appreciated.
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What is the exact script that is running to backup the log?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vidya_pande SSCertifiable Points: 5275 More actions · Answer 2

Database may go into recovery while restore. Never seen while backing up. :hehe: Please share the command

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

vidya_pande (3/30/2010)
Database may go into recovery while restore. Never seen while backing up. :hehe:

It's possible. There's a documented option to backup to do just that.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 4

select 'BACKUP LOG ' + @dbname + ' WITH TRUNCATE_ONLY'

exec ('dbcc shrinkdatabase (' + @dbname + ',TRUNCATEONLY)')

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why the hell are you truncating your log and shrinking your database?

Please read through this - Managing Transaction Logs[/url]

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

I am not sure while but somehow this is a part of the maintenance plan in the dmz I inherited in the last few weeks. Basically the db's are set to simple recovery and the first task after restoring a production copy is to dump the log.

Thanks the article and blog - very helpful reading.

costa,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

cos_ta393 (3/30/2010)
Basically the db's are set to simple recovery and the first task after restoring a production copy is to dump the log.

If the database is in simple recovery, you don't need to do anything at all to the log. It's auto-truncated. Just delete that maintenance plan

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 8

Absolutely. I am working on a new plan now.

Thanks for your input - really appreciated.

costa,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends