December 4, 2012 at 5:00 am
Hello everone!
I've done something stupid with a database server. I was trying to read the transaction log of the database, but the file was in use. Being unfamiliar with how SQL server works, I figured I should just stop the SqlServer service, read the transaction log, then restart the service. Taking the database offline is not a problem since it's not used by anyone else at its current location.
The problem is, bringin the services back online did not bring the associated database, call it XYZ, back online. When I try to connect to it now I just get a generic "The database XYZ is not accessible (objectexplorer)". Checking the error log yields two error messages. In chronological order:
Error: 18456, Severity: 14, State: 38
Login failed for user 'username'. Reason: Failed to open the explicitly defined database. (CLIENT: x.x.x.x)
After restarting the services I tried solve the problem by taking the database offline, then back online. It came offline fine, but then I got an error message saying the disk was full or the transaction log could not be read when trying to bring it back online. I kept seeing references in the logs to the transaction log being full, from before the database died. It was almost 100Gb and the disk was nearly full, so I tried deleting it to fix the problem. Not sure if that was a great idea... It still doesn't work at any rate.
After restarting the computer the database no longer has the offline icon on its name, but it still doesn't work.
Any suggestions on how to get me out of this mess?
December 4, 2012 at 7:00 am
What is the exact state of the database (the state_desc column in Sys.databases ) and what are all of the messages relating to that DB in the error log?
Edit: Am I reading that right, you deleted the ldf file? If so restore from backup.
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
December 4, 2012 at 7:36 am
Yeah, you're reading it right, I deleted it. I thought it was just a log file :blush:
I tried restoring from backup a while ago, but I got an error saying the database was in use. I'll check the state of the database.
December 4, 2012 at 8:32 am
daniel.kling (12/4/2012)
Yeah, you're reading it right, I deleted it. I thought it was just a log file :blush:
Errrr... just as in the log of transactions that is the portion of the DB that ensured durability and consistency...
I tried restoring from backup a while ago, but I got an error saying the database was in use. I'll check the state of the database.
Make sure no connections are using the DB (including yours), then restore.
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
December 4, 2012 at 10:23 am
Thanks for the help! 🙂
I managed to solve the issue. I had put the database into emergency mode, and single_user mode before and for some reason when I tried to use the restore function I got an error message about missing files and it wouldn't complete.
In the end I ran DBCC CHECKTABLE which restored the transaction log and fixed the table. It seems to work still and the data is still intact as far as I can tell.
I might add that your "Errr...."-line spread some joy around the office here. Lesson learned. Don't mess with the transaction log, even if it takes up the entire harddrive it lives on.
December 4, 2012 at 11:52 am
By rebuilding the tran log you may have lost transactional consistency and possibly even structural consistency in the database, depending what was running before you deleted it. Rebuilding the log is a last resort for when you have no backups.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-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
December 4, 2012 at 12:40 pm
Yeah, I read that but I couldn't get the restore to work, it kept giving me an error about missing files. Had it been a production server I would probably have made the effort to see about getting it fixed, but this server is just used for local testing and the data has no real value. The main problem I had was not being able to connect to the database at all and that seems ok now.
We do have a lot of problems with the transaction log growing very large, however. As I said earlier, the file was almost 100GB when I restarted the server earlier today, filling the entire hdd it was on.
December 4, 2012 at 1:36 pm
Managing Transaction Logs[/url]
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
December 4, 2012 at 2:08 pm
Excellent, will read through that stuff 🙂 Thanks!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply