December 30, 2015 at 8:58 am
Hi,
I have a database with a .mdf file of 182GB. I was trying to setup a maintenance plan and the backup job failed with this message:
"Read on "G:\SQLDATA\Prod1_Content.mdf" failed: 23(Data error (cyclic redundancy check).) BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:15:00 PM Finished: 2:19:53 PM Elapsed: 292.611 seconds. The package execution failed. The step failed.
I believe that there is something wrong in the database file. So I used DBCC CheckDB ('Prod1_Content') to check the database. The result is:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:355872) with latch type SH. 23(Data error (cyclic redundancy check).) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:355872) with latch type SH. 23(Data error (cyclic redundancy check).) failed.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 23 pages from (1:355872) to (1:363959). See other errors for cause.
CHECKDB found 2 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 1 consistency errors in database 'Prod1_Content'.
So, please tell me if this is the data page's error on the .mdf file or it is the tempDB size error. How to fix these 2 consistency errors or this is something else not fixable in SQL Server.
Thanks,
December 30, 2015 at 4:16 pm
Unable to read and latch page (1:355872) with latch type SH. 23(Data error (cyclic redundancy check).) failed.
Step #1: ensure any process that deletes old backup files is killed and blocked from running until you resolve the issue. You may need backup files older that you think.
Take a look in msdb.dbo.suspect_pages (Books Online: https://msdn.microsoft.com/en-us/library/ms174425%28v=sql.105%29.aspx ) to see if any corruption has been recorded.
Looks like a PFS page may be damaged, or you may have connectivity issues with your storage. Are there any errors in the Windows system error log that point to an issue with where the file is stored? Does the following command return data without error?
DBCC PAGE('Prod1_Content', 1, 355872) WITH TABLERESULTS;
If not, you can try to restore just the page by reviewing the "Performing Page Restores" topic in Books Online:
https://msdn.microsoft.com/en-us/library/ms175168%28v=sql.105%29.aspx
...although your problem may be a damaged PFS page, which is a metadata page that cannot be be restored except as part of a whole-database, whole-filegroup, or whole-file restore. Note: there's nothing wrong with attempting a page-level restore even if it may fail due to the type of page being restored. If your entire database is in one file, your only option in the event of metadata corruption is to restore the entire database. It is possible to back up (and subsequently restore) corrupt pages, so you should re-run DBCC CHECKDB after any restoration attempts. If the page is still bad after a restore, then you'll need to restart the restore with an earlier backup file.
-Eddie
Eddie Wuerch
MCM: SQL
December 31, 2015 at 2:23 am
That's an irreparable error. You'll need to restore from your last good backup to fix that.
PFS and other allocation pages cannot be single-page restored, you'll need to restore the entire 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
January 6, 2016 at 12:49 pm
Hi Eddie and Gail,
Thank you for the reply and suggestion.
I’ve detached the database and created a new database. As you suggested, I’m trying to restore the database with a backup file. Now the problem is that the backup file is from other server with different database name. When I restore the database in GUI, I got the error message of "the Prod1_Content database is in use".
When I use the T-SQL to restore:
restore database Prod1_Content
from disk = D:\backup\DevDone1_Content_backup_2016_01_05_165457_1497560.bak'
with
move 'E:\SQLDATA\Prod1_Content' to 'E:\SQLDATA\Prod1_Content.MDF',
move 'E:\SQLLOGS\Prod1_Content_log' to 'E:\SQLLOGS\Prod1_Content_log.ldf'
I got the database name error:
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing Prod1_Content' database.
Is there a way to restore the database from server\database to different server\database by using the GUI or T-SQL ?
Thanks again,
January 6, 2016 at 12:56 pm
From that message, the Prod1_Content database exists on the server and you're trying to overwrite it. Is that correct?
If that's not what you intend, double-check the database and file names.
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
January 6, 2016 at 1:23 pm
Hi Gail,
Yes, the database Prod1_Content was the corrupted one. I detached it and renamed the .mdf and .ldf files.
So, let me clarify my question.
When created a new database, can I use the same database names: Prod1_Content and the file names: Prod1_Content.mdf and Prod1_Content.ldf?
When I restore with a different database's backup file, how can I let the processing to restore the data to Prod1_Content from the other backup file (say: 'Devdone1_Content.bak')?
Thanks,
January 6, 2016 at 2:27 pm
No, that's not what I said.
The error message says that you are trying to either overwrite an *existing* database that is currently attached to the instance, or you're trying to overwrite files for a database that's currently attached to the instance. The detached and renamed database doesn't count, it's detached.
If that's not what you intend, triple-check the DB name and the file names you've specified in the 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
January 8, 2016 at 12:32 pm
Hi Gail,
Yes, the old database was detached and data/log files were renamed. As I know, detaching a database actually removes the entry for that database. What I was doing again is creating a new database and restoring data to it. So, I created a new database with the same name(include the .mdf, ldf name) of the backup database, restored from the backup file and it is succeed this time. Then, I rename the new database to the old name that was in the instance. So everything works from the maintenance plan to the apps using the database.
Thanks again for the reply and helps,
lw
January 8, 2016 at 1:42 pm
You don't need to create a new database and then restore over. Doing so is why you got an error, the database in the backup didn't match t\he one you'd created. You can just restore the back file to the database name you need and skill the unnecessary step.
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
January 8, 2016 at 2:28 pm
Hi Gail,
I tried it as you said. But after I detached the old database and was trying to restore to it, I got the "database is in use" error when I used the GUI. I still don't know why.
Thanks,
January 8, 2016 at 2:59 pm
Is it possible that some *other* database on your server (or some other instance??) has an active database with those file names?
I've had cases for example on our dev server where the developers have restored multiple copies of the same database with different names, or renamed a database and then restored it under the original name, etc, etc... and so you end up with the file names not matching the databases. It's not a huge deal in and of itself - but it can lead to tricky situations like what it sounds like you're dealing with.
Go into each database and run this: select * from sys.database_files and look for the file names under the column physical_name. The last thing you want to do is wipe out another database while trying to restore this one.
January 9, 2016 at 4:54 am
lw1990 (1/8/2016)
I tried it as you said. But after I detached the old database and was trying to restore to it, I got the "database is in use" error when I used the GUI. I still don't know why.
The only reasons you will get that error message are the ones I listed in a previous post.
The error message says that you are trying to either overwrite an *existing* database that is currently attached to the instance, or you're trying to overwrite files for a database that's currently attached to the instance. The detached and renamed database doesn't count, it's detached.
So either the DB name you entered was an existing DB, or one of the file names you listed existed and belonged to an existing 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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply