March 2, 2009 at 9:08 am
HI -
i received two files (data file and log file) from a database that is suspect.
i executed this script on my pc to create the database:
Create database test
on
( name = test,
filename ='c:\DB\test.mdf',
--path
SIZE = 377296KB,
filegrowth = 10 )
log on
( name ='test_Log',
filename='c:\DB\test_Log.ldf',
size =1024KB)
go
(this is to create a datafile with the exact size of my mdf file)
when i go to see the size of the datafile, that was created now , instead of 377296KB it as this size 377344KB does any body know why?
that i stop SQL Server service and change this new mdf file created ,for the one that i have received from the production server.
I restart SQL Server service
I than, execute the folowing comands:
exec sp_condigure 'allow updates',1
reconfigure with override
go
update master..sysdatabases set status=32768 where name ='test'
go
exec sp_condigure 'allow updates',0
reconfigure with override
go
than i restart SQL Server service.
The status of the database is now (suspect\emergency mode) instead of emergency mode only
then i run the comand:
dbcc rebuild_log ('test','location i whant') and i receive the folowing message:
could not open FCB for invalid file id 0 ....
my database is with this status :
(suspect/emergency mode)
I Can not recover this database.....
Note - I have this database on production server , not detached yet, and i have the status of Suspect on the productiion server.
what can i do to have the database to it's normal state again?
tks,
Pedro
March 2, 2009 at 9:27 am
Do you have up-to-date backups of this database?
Please look in the SQL error log on your production server and look for messages related to this database. There's a reason it's suspect and we need to know why before we can help. Post any messages from the error log relating to this DB.
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
March 2, 2009 at 9:37 am
Hi-
I do not have access to the Log of the SQL Server .
This server is in Africa and i'm in Europe.
The person that is there can not send the log.
March 2, 2009 at 9:41 am
There must be someone who has access to the server. The errorlog's just a text file. Without knowing why the DB's suspect, it's hard to offer a solution, and the reason will be in the error log.
Do you have a 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
March 2, 2009 at 9:45 am
the backup was corrupted.
i trie to attach the database to and it gived me the folowing error:
I/O error (torn page) detected during read at offset 0000000000000000 in file 'C:\Documents and Settings\pribeirote.teste\Ambiente de trabalho\Mala\mala 2\Cópia de teste\teste.mdf'.
the comand i executed was:
exec sp_attach_db 'teste','path1',
'path2'
March 2, 2009 at 9:46 am
Moved to data corruption.
As Gail mentioned the error log usually has an error code and description that can explain many of the reasons why a database gets suspect.
If it's a large text file (and it could be), have someone look for "suspect" in the log and snip out the portions you need.
March 2, 2009 at 1:05 pm
pedro.ribeiro (3/2/2009)
I/O error (torn page) detected during read at offset 0000000000000000 in file 'C:\Documents and Settings\pribeirote.teste\Ambiente de trabalho\Mala\mala 2\Cópia de teste\teste.mdf'.
If I'm reading that correctly the database header page is damaged. That's an irreparable error and there is no way around it. If that is the case (and I'm hoping that Paul Randal will drop in shortly to correct me) then the only solution is restore from backup. Do you have any undamaged backups at all?
I'd still really like to see relevant portions of the error 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
March 2, 2009 at 1:25 pm
Yup - the file header page (page 0 in each data file) in the primary data file is corrupt.
Btw - you're running SQL 2000 and this is the 2005-specific forum - maybe Steve moved it to the wrong forum?
You tried the hack-the-suspect-database trick but you swapped in the corrupt log file - try it without the corrupt log file (rebuild the log then run a full repair_allow_data_loss). 2000 is a lot more dodgy for this procedure than 2005/2008.
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
March 2, 2009 at 1:37 pm
Paul Randal (3/2/2009)
You tried the hack-the-suspect-database trick but you swapped in the corrupt log file - try it without the corrupt log file (rebuild the log then run a full repair_allow_data_loss). 2000 is a lot more dodgy for this procedure than 2005/2008.
Is that going to work with the database header page damaged?
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
March 2, 2009 at 1:42 pm
It will at least get the new log file and get the database attached. The next thing I was going to suggest is turning off torn-page protection to see if enough of the file header page is usable that SQL Server can open the database - I've used this trick before to get around damaged GAM pages in small databases. No guarantees though...
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
March 3, 2009 at 7:49 am
Hi- Tks Paul
I can not connect to the SQL server on the production server by the SQL Query analiser.
I'm think i'm going to buy a licence of "Stellar Phoenix SQL Recovery" to see if this program can recover the data file that i have.
Do you think that it's a good ideia?
Do you know any good software to recover files ffrom databases that is free?
tks,
Pedro
March 3, 2009 at 9:49 am
error log of the production server:
2009-03-02 19:59:25.03 server Copyright (C) 1988-2002 Microsoft Corporation.
2009-03-02 19:59:25.03 server All rights reserved.
2009-03-02 19:59:25.03 server Server Process ID is 3848.
2009-03-02 19:59:25.03 server Logging SQL Server messages in file 'C:\Programas\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2009-03-02 19:59:25.03 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2009-03-02 19:59:25.04 server SQL Server configured for thread mode processing.
2009-03-02 19:59:25.04 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2009-03-02 19:59:25.07 server Attempting to initialize Distributed Transaction Coordinator.
2009-03-02 19:59:25.07 server Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
2009-03-02 19:59:25.07 spid4 Starting up database 'master'.
2009-03-02 19:59:25.23 spid4 Server name is 'USER-A64384215B'.
2009-03-02 19:59:25.23 spid5 Starting up database 'msdb'.
2009-03-02 19:59:25.23 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2009-03-02 19:59:25.23 spid6 Starting up database 'model'.
2009-03-02 19:59:25.23 spid8 Starting up database 'pubs'.
2009-03-02 19:59:25.23 spid9 Starting up database 'Northwind'.
2009-03-02 19:59:25.23 spid11 Starting up database 'SGCT'.
2009-03-02 19:59:25.23 server SQL server listening on 127.0.0.1: 1433.
2009-03-02 19:59:25.23 server SQL server listening on TCP, Shared Memory, Named Pipes.
2009-03-02 19:59:25.23 server SQL Server is ready for client connections
2009-03-02 19:59:25.31 spid12 Starting up database 'SGT'.
2009-03-02 19:59:25.48 spid12 Error: 823, Severity: 24, State: 6
2009-03-02 19:59:25.48 spid12 I/O error (torn page) detected during read at offset 0000000000000000 in file 'C:\SGT.mdf'..
2009-03-02 19:59:25.51 spid12 udopen: Operating system error 32(O processo não pode aceder ao ficheiro porque este está a ser utilizado por outro processo.) during the creation/opening of physical device C:\SGT_log.ldf.
2009-03-02 19:59:25.51 spid12 FCB::Open failed: Could not open device C:\SGT_log.ldf for virtual device number (VDN) 2.
2009-03-02 19:59:25.51 spid11 Error: 823, Severity: 24, State: 6
2009-03-02 19:59:25.51 spid11 I/O error (torn page) detected during read at offset 0000000000000000 in file 'c:\SGCT.mdf'..
2009-03-02 19:59:25.53 spid11 Error: 823, Severity: 24, State: 6
2009-03-02 19:59:25.53 spid11 I/O error (torn page) detected during read at offset 0000000000000000 in file 'c:\SGCT_log.ldf'..
2009-03-02 19:59:25.54 spid11 Device activation error. The physical file name 'c:\SGCT_log.ldf' may be incorrect.
2009-03-02 19:59:25.54 spid12 Device activation error. The physical file name 'C:\SGT_log.ldf' may be incorrect.
2009-03-02 19:59:25.54 spid12 Device activation error. The physical file name 'C:\SGT_log.ldf' may be incorrect.
2009-03-02 19:59:25.60 spid6 Clearing tempdb database.
2009-03-02 19:59:26.04 spid6 Starting up database 'tempdb'.
2009-03-02 19:59:26.09 spid4 Recovery complete.
2009-03-02 19:59:26.09 spid4 SQL global counter collection task is created.
March 3, 2009 at 9:59 am
pedro.ribeiro (3/3/2009)
I'm think i'm going to buy a licence of "Stellar Phoenix SQL Recovery" to see if this program can recover the data file that i have.Do you think that it's a good ideia?
Not unless they have a trial version where you can see how much (if any) data can be recovered. I've had very bad experiences with the 'data recovery' apps that I've tried
Do you know any good software to recover files ffrom databases that is free?
That's not something you're likely to find free. Writing something that can decipher the SQL data structure and put rows and tables back together is not trivial even on a database without corruption. I don't know of any good non-free programs.
Did you try Paul's suggestion of hacking the DB in without that log file and then rebuilding it? (on your test box, not prod)
I'll be honest, with damage to the headers of two data and log files, I don't hold out much hope, but it's worth a try
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
March 3, 2009 at 11:27 am
I don't know of any programs that will do a better job that CHECKDB with REPAIR_ALLOW_DATA_LOSS - if I could do a better job than that (which I wrote) then I'd have written the tool by now.
Your primary problem is getting the database attached and accessible THEN worry about salvaging some data.
How damaged are the backups? Can you try restoring it WITH CONTINUE_AFTER_ERROR on a 2005 server? (this functionality isn't on 2000 which you're running)
PS Steve - if you're watching, can you move this to the 7.0/2000 forum please?
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
March 4, 2009 at 1:43 am
Thank you very mutch for reply master Paul Randal.
I'm sorry , about talking about a program to fix this problem.
This is because i have not mutch time, and i fill a litle worried.
I will trie to do exactly what you say.
I have the datafile and the log file of the database in Europe (pleople in Africa stoped the SQL Server Service and copied the two files to send me) then they restarted their SQl Server service, sow that database don't need to be detached.
They can not connect to the database trought Query analiser to make a DBCC.
I have a recent backup master Paul, but it's corrupt. How can i trie to make a restore of it in SQL Server 2005 with the comand that you told me?
Could please you give me a good link to read on how to make this restore (the sintax)?
tks,
Pedro
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply