October 18, 2010 at 9:14 am
ALL:
We have SQL Server 2005 SP3 on Windows 2003/32bit. Last weekend the SQL Server Engine shutdown because "hard drive controller errors", but SQL Server Agent has been running.
This morning, hard drives are back and SQL Server is running. But some databases cannot open.
Right click database, backup/restore/attach are greyed out. It can be take offline, but cannot be online back. click "Properties", it returns error something like:
..... SQL Server unclean shutdowm. ... The log file [C:\....] is uncorrect.
The data file and T-log file are there.
How to bring the database onine?
Thanks
October 18, 2010 at 9:25 am
Can you create same database on other Server with same size (MDF and LDF) and then bring the database offlien and replace the MDF and LDF and see if database comes online.
October 18, 2010 at 9:47 am
Nary:
I think so. However I wish I can do something on existing databases.
Thanks
October 18, 2010 at 10:27 am
Take a look at the Create Database syntax in Books Online (or on MSDN). It has an option for re-creating a log file when you create a database from an MDF file. Try detaching the databases, stopping and restarting the SQL service (to have a clean stop/start cycle), and then using that option for creating from a file. That should fix the messed up log files by replacing them. You may need to delete the existing log files (after the detach) in order to do this, if the new ones will be at the same location and file name.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 12:23 pm
GSquared (10/18/2010)
Take a look at the Create Database syntax in Books Online (or on MSDN). It has an option for re-creating a log file when you create a database from an MDF file. Try detaching the databases, stopping and restarting the SQL service (to have a clean stop/start cycle), and then using that option for creating from a file. That should fix the messed up log files by replacing them.
However if the DB was not cleanly shut down, which the OP's error indicates was the case, the attach will fail and he'll be left with an unattached database file that's a fair bit harder to fix than one that is attached.
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
October 18, 2010 at 12:25 pm
YTZ (10/18/2010)
Right click database, backup/restore/attach are greyed out. It can be take offline, but cannot be online back. click "Properties", it returns error something like:..... SQL Server unclean shutdowm. ... The log file [C:\....] is uncorrect.
What is the exact error message?
Are there any other errors in the SQL error log?
The data file and T-log file are there.
Check the permissions, make sure that the SQL server service account has full rights to the files.
Please do not for the moment detach the databases as that is very likely to simply make the problem worse.
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
October 18, 2010 at 12:25 pm
GilaMonster (10/18/2010)
GSquared (10/18/2010)
Take a look at the Create Database syntax in Books Online (or on MSDN). It has an option for re-creating a log file when you create a database from an MDF file. Try detaching the databases, stopping and restarting the SQL service (to have a clean stop/start cycle), and then using that option for creating from a file. That should fix the messed up log files by replacing them.However if the DB was not cleanly shut down, which the OP's error indicates was the case, the attach will fail and he'll be left with an unattached database file that's a fair bit harder to fix than one that is attached.
The error quoted indicated the problem was with the log file. If the data file is also corrupted (was it on the same drives?), yes, that's a different issue with a different solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 12:31 pm
GSquared (10/18/2010)
The error quoted indicated the problem was with the log file.
Correct. However the error indicates that the database was not cleanly shut down (all committed transactions written to disk, all uncommitted transactions rolled back). The OP mentioned "SQL Server unclean shutdowm"
In the case of an unclean shutdown, if the DB is detached and the log deleted, the database will not reattach, no matter if the FOR_ATTACH_REBUILD_LOG is specified or not. The DB will have to be hacked back into the server before the fix (likely a forced log rebuild) can be done.
If the data file is also corrupted (was it on the same drives?), yes, that's a different issue with a different solution.
There's no indication that the data file is corrupt and I'm not talking about that possibility at all.
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
October 19, 2010 at 12:19 pm
Since you definitely know more about this than I do, I'll bow out on this one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply