Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log File full / Database Down Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 6:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 2, 2013 6:47 AM
Points: 3, Visits: 3
I am a newbie and we ran a script that made our logfile drive go to 100%. We tried to do a restore of the database and now the database will not come up. Any suggestions would be appreciated.
Post #1423841
Posted Monday, February 25, 2013 7:46 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
What is the status of your databse as per management studio?

What error you have in your error log?


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1423847
Posted Monday, February 25, 2013 7:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:00 AM
Points: 465, Visits: 1,036
Why did you restore a dB ?

Instead you would have been taken a log backup that should cleaned the log file.

Is it a production database ?



--SQLFRNDZ
Post #1423848
Posted Monday, February 25, 2013 7:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 2, 2013 6:47 AM
Points: 3, Visits: 3
We are currently not getting any errors . The database status show (inRecovery). There is a lot of read on the log file directory.
Post #1423851
Posted Monday, February 25, 2013 7:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 2, 2013 6:47 AM
Points: 3, Visits: 3
It is a production database. We restored it since the transaction we ran did not complete and we had to remove it.
Post #1423852
Posted Monday, February 25, 2013 8:02 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
How did you restore the database?.

While restoring did you add with norecovery?


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1423855
Posted Tuesday, February 26, 2013 12:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
Whats current Log size? and database size?
if its showing Inrecovery then wait for some time let database recover and then shrink database log file or change recovery mode to simple
Post #1423896
Posted Tuesday, February 26, 2013 3:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
Please read through this - Managing Transaction Logs and - http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Don't just shrink or change recovery models without analysing and knowing the effects of the actions.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1423979
Posted Wednesday, February 27, 2013 2:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 2,044, Visits: 3,061
Hmm, now that the db is in recovering status, you may not be able to (temporarily) add another log file on a different drive to allow the db to keep processing while you clean up.

Instead, see if there is another db(s) with large log file(s) that you can afford to do w/o for a few seconds/minutes while you move their log file(s) to another drive.

That is, your log drive needs free space to do work in. It's full. You can free up space on that drive by moving one (or more) of the other log files to a different drive temporarily).

That might (or might not) allow the original db to complete recovery.

If you want to try moving an existing db's log to another drive temporarily, let me know, and I can help you build a script to do it. NOTE: you should use ALTER DATABASE, NOT detach to do this.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1424761
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse