April 6, 2009 at 11:22 am
Here is the situation. A client was running a database on SQL 2000 MSDE on a windows XP machine. During a recent storm, the machine crashed, and when they rebooted it SQL server would not start.
They decided to upgrade to SQL 2005 Express instead of fixing 2000 MSDE. 2005 would not install, so they backed up their data, formatted and reinstalled windows, and installed SQL 2005.
Now the database will not attach in SQL 2005 Express - they get a message saying something like
"The log scan number (xxxx) passed to log scan in database is not valid".
The LDF file is dated the day before the MDF file, which leads me to believe the log didn't save correctly
They are zipping the files and sending them to me - I have SQL 2000 and 2005 here.
Given that the database was in Simple Recovery, and no changes were being made for quite some time before the crash, I am hopeful that I can recover something.
Any suggestions on where I should start? They have a backup....from December.....so if all else fails we can use that, but I hate to lose months of data.
April 6, 2009 at 11:34 am
wayne.mcdaniel (4/6/2009)
Now the database will not attach in SQL 2005 Express - they get a message saying something like"The log scan number (xxxx) passed to log scan in database is not valid".
This is the first time I have seen this type of error though I have little experience.
Check out this link, it says its a corruption on your log file.
http://support.microsoft.com/kb/940126.
- I have SQL 2000 and 2005 here.
If you have a good backup, that's what I would do restore it from your good backup.
but you wouldn't stop there would you?. Take the same file, the one which is corrupted, to your test servers and try to rebuild the log and see what you can do. Since, you are not much worried about the loss of data, I think at the most you would lose only couple of days worth data I am not so sure though.
I think Paul would be interested, to discuss this in his blog 🙂
April 6, 2009 at 1:31 pm
Yeah, the log file's corrupt - one guess would be a drive wasn't honoring write-through requests to the log file. Anyway, without going back to your backup, you'll need to rebuild the transaction log. In 2005 you do this using emergency-mode repair.
See CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 6, 2009 at 1:48 pm
I have the files now. They will not attach under SQL 2000 or 2005 directly.
I tried deleting the log file and using sp_attach_single_db under SQL 2000, this is the result I get
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test1'. CREATE DATABASE is aborted.
Msg 9003, Level 20, State 6, Line 1
The LSN (2140:577:1) passed to log scan in database 'test1' is invalid.
I tried attaching the data file under SQL 2005 with the "FOR_ATTACH_REBUILD_LOG" option, and I get this similar message.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Test1'. CREATE DATABASE is aborted.
Msg 9003, Level 20, State 9, Line 1
The log scan number (2140:577:1) passed to log scan in database 'Test1' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
April 6, 2009 at 2:08 pm
ok - you need to hack the database back into the server. See this blog post: TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 6, 2009 at 2:41 pm
Thanks for the advice.
I put another set of files in that folder and attached them in SQL 2005. Shut down SQL server, deleted the log file and put in my suspect database file. Started SQL 2005 again, and the database came online.
When I ran DBCC CheckDB, this is what I got
****lots of tables with no problems****
DBCC results for 'rpsAudit'.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:19645) in object ID 1828969642, index ID 1, partition ID 401338331168768, alloc unit ID 71895982415675392 (type LOB data). Expected value 95_PCT_FULL, actual value 100_PCT_FULL.
There are 40295 rows in 392 pages for object "rpsAudit".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'rpsAudit' (object ID 1828969642).
****lots more tables with no problems....****
CHECKDB found 0 allocation errors and 1 consistency errors in database 'RPS'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (RPS).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It looks like this isn't anything critical...that audit table is somewhat useful, but we'll live if there is some data loss in that. I reran CHECKDB with REPAIR_ALLOW_DATA_LOSS and everything is all good now.
Thanks for the help, I couldn't have asked for a better source of advice. I was actually scrolling through your blog in my reader....makes sense you would know which ones to look for.
My only other question is, when can we get you and Kimberly to come visit us at Devlink? http://www.devlink.net/
April 6, 2009 at 3:44 pm
Cool. That error is benign and you can safely repair it without any data loss (although you need to use that option to repair it).
Shoot me an email with devlink in otherwise I'll forget (replying to this during a break teaching a SQL class at MSFT)
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply