Databases locked IN LOAD State and SQLServer Service not starting
-
Hi I have a SQL problem and was hoping you wiz'es could help.
I run;
Windows 2003 Std
SQL 2000 - 8.00.760 (Intel X86)
Backup Exec 9.1
I recently installed the SQL Agent addon for Backup exec in attempt to backup my SQL databases. On the first backup to truncate the SQL log the job failed and the databases were left in a LOAD state.
Stopping me from starting the SQLServer
The SQL Errorlog;
2005-10-29 15:47:31.43 spid4 Starting up database 'master'.
2005-10-29 15:47:31.58 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-10-29 15:47:31.58 spid5 Starting up database 'model'.
2005-10-29 15:47:31.60 spid4 Server name is 'FABNAV'.
2005-10-29 15:47:31.60 spid9 Starting up database 'pubs'.
2005-10-29 15:47:31.60 server SQL server listening on 192.168.25.7: 1433.
2005-10-29 15:47:31.60 server SQL server listening on 127.0.0.1: 1433.
2005-10-29 15:47:31.60 spid10 Starting up database 'Northwind'.
2005-10-29 15:47:31.60 spid8 Starting up database 'msdb'.
2005-10-29 15:47:31.60 spid11 Starting up database 'FBB'.
2005-10-29 15:47:31.60 spid12 Starting up database 'testing'.
2005-10-29 15:47:31.63 spid10 Bypassing recovery for database 'Northwind' because it is marked IN LOAD.
2005-10-29 15:47:31.63 spid5 Bypassing recovery for database 'model' because it is marked IN LOAD.
2005-10-29 15:47:31.63 spid8 Bypassing recovery for database 'msdb' because it is marked IN LOAD.
2005-10-29 15:47:31.65 spid12 Bypassing recovery for database 'testing' because it is marked IN LOAD.
2005-10-29 15:47:31.66 spid9 Bypassing recovery for database 'pubs' because it is marked IN LOAD.
2005-10-29 15:47:31.66 spid5 Database 'model' cannot be opened. It is in the middle of a restore.
It seems logical to me that I should be able to reset the status of the dbs to a non LOAD state, but thats beyond my knowlegde and I fear I may make matters worse (if possible).
I have some engineers coming next week but any help would be greatfully received.
Matt 🙂 getting nervous now!
-
I'm not sure if it will work but if you read the folowing article and try putting the database into emergency mode you may be able to recover the data.
http://www.sqlservercentral.com/columnists/bknight/unmarksuspect.asp
Reseting the status as far as I'm aware won't work.
On an aside we use Veritas at work and after several issues with its sql server backup agents I won't let them near my databases. I've found a far safer and more reliable option is to schedule a backup from within sql server to a another server and let the backups pick it up from there. Also if you use the AOFO option make sure that the sql server data and log files are not inlcuded in the backup veritas warns that if you don't it can corrupt your database files.
hth
David
-
Thanks for the info..
In the end I setup another SQL server and recreated the problem on it. Then I Rebuilt the Master DB and then corrected the state of each sub DB using the Query Analyisor and the recover command thus resetting the states to normal from Loading. Once fixed I Copied the repaired DBs to live and thank god it all worked.
Thanks again for the backup advice that sound real good solution. It seems a bit of a con being sold SQL Agent! I have noticed that my Transaction log file size is not reducing weven though the log backups are running - If I look in Enterprise Manager I can see only 120MB of the 32GB log file is used - any ideas how I purge this?
-
I believe to solve the database marked as IN LOAD all you need to do is restore that database WITH RECOVERY.
Once you do that the status resets and the transaction log file will be back to normal after first backup is run against database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply