June 18, 2009 at 9:34 am
I copied database (just copied mdf and ldf) file thru windows explorer from server 1 to server 2. It is across the domain. The database is perfect in server 1. But when I attach the same mdf file in server 2 I am getting fatal errors in sql. When I do checkDB I get lot of db inconsistent errors.
I am not able to "copy database" from Task in DB itself because of remote connectivity issues throu IP. So I just copied the mdf itself after detaching it temporarily. I tried several times.
Is it a server hardware issue in server 2?
Thanks for your help
June 18, 2009 at 9:39 am
Are you, by chance - copying from a SQL Server 2000 system to a SQL Server 2005 system? Have you tried a backup/restore instead of copying the files?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 18, 2009 at 9:44 am
Both are identical versions. When we did backup and restore I think it didnt move the views and stored proc.
Thanks for your help
June 18, 2009 at 9:44 am
Sorry Sql server 2005
June 18, 2009 at 9:51 am
Please can you run the following and post the full results
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
I would guess that it's a hardware issue, specifically the IO subsystem, as that's the cause of 99% of corruption problems.
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
June 18, 2009 at 10:03 am
jvs (6/18/2009)
Both are identical versions. When we did backup and restore I think it didnt move the views and stored proc.Thanks for your help
Not sure why you would think this - a backup backs up everything in a database. Nothing is left out.
Anyways, run the integrity check on the source system and validate that these issues don't exist there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 18, 2009 at 10:06 am
Here is the dbcc checkdb
Msg 8928, Level 16, State 1, Line 1
Object ID 343672272, index ID 0, partition ID 72057594067484672, alloc unit ID 72057594193772544 (type In-row data): Page (1:9270) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 343672272, index ID 0, partition ID 72057594067484672, alloc unit ID 72057594193772544 (type In-row data), page (1:9270). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716297 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'MASTERxxx' (object ID 343672272).
Msg 8928, Level 16, State 1, Line 1
Object ID 389576426, index ID 0, partition ID 72057594039566336, alloc unit ID 72057594160676864 (type In-row data): Page (1:7380) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tranxxxx' (object ID 389576426).
Msg 8928, Level 16, State 1, Line 1
Object ID 421576540, index ID 1, partition ID 72057594040745984, alloc unit ID 72057594194231296 (type In-row data): Page (1:103210) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 421576540, index ID 1, partition ID 72057594040745984, alloc unit ID 72057594194231296 (type In-row data), page (1:103210). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716297 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 421576540, index ID 1, partition ID 72057594040745984, alloc unit ID 72057594194231296 (type In-row data). Page (1:103210) was not seen in the scan although its parent (1:144827) and previous (1:121418) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 421576540, index ID 1, partition ID 72057594040745984, alloc unit ID 72057594194231296 (type In-row data). Page (1:119858) is missing a reference from previous page (1:103210). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 421576540, index ID 1, partition ID 72057594040745984, alloc unit ID 72057594194231296 (type In-row data): Page (1:153641) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 421576540, index ID 1, partition ID 72057594040745984, alloc unit ID 72057594194231296 (type In-row data), page (1:153641). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716297 and -4.
Msg 8976, Level 16, State 1, Line 1
----------------------
CHECKDB found 0 allocation errors and 3 consistency errors in table 'xxx2' (object ID 453576654).
Msg 8928, Level 16, State 1, Line 1
Object ID 795149878, index ID 1, partition ID 72057594059554816, alloc unit ID 72057594193969152 (type In-row data): Page (1:27889) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 795149878, index ID 1, partition ID 72057594059554816, alloc unit ID 72057594193969152 (type In-row data), page (1:27889). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716297 and -4.
Msg 8976, Level 16, State 1, Line 1
CHECKDB found 0 allocation errors and 37 consistency errors in database 'xxx'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (xxx).
June 18, 2009 at 10:07 am
It is fine in Source server. The command doesnt give any errors.
All queries run perfect
June 18, 2009 at 10:15 am
Well, what that shows is that you either have a problem with the destination I/O system, or the copy corrupted the mdf file.
I would recommend trying a restore from known good backup and see if you still have this issue. If you do, then you should focus on the I/O system as the problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 18, 2009 at 10:25 am
Thank you so much.
This is the third time it did it. Orginally the database worked fine for 2 days and then corrupted.
Also I am getting the following error in the application event viewer
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
-------------
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
-------------------
Any idea???
The sql reporting server services crashes often...
June 18, 2009 at 10:30 am
jvs (6/18/2009)
This is the third time it did it. Orginally the database worked fine for 2 days and then corrupted.
I would take a long, hard look at that IO subsystem. Repeated corruption is very often an IO problem
Also I am getting the following error in the application event viewer
Those aren't errors. They're just saying that a config setting changed.
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
June 18, 2009 at 10:33 am
If you have restored (or detached/attached) this database multiple times and it is fine at first, then gets corrupted a few days later I would be talking to the server guys or SAN guys and telling them to get it fixed.
That is not an issue with the database or SQL Server - it is an issue with the hardware.
As for your additional messages, those are not a problem it just means that Agent was reconfigured.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 18, 2009 at 10:50 am
Do you have page checksums enabled for the database? Are you seeing 823 or 824 errors in the errorlog? These would be proof that it's the I/O subsystem and avoid any arguments with your I/O subsystem admin.
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
August 22, 2010 at 9:30 pm
Hi ,
we execute
USE [mvaprod]
GO
DBCC CHECKDB(N'mvaprod') WITH NO_INFOMSGS
Error occur
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "DynamicWorkflowStatistics", index ID 0, partition ID 6656845414400, alloc unit ID 6656845414400 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'DynamicWorkflowStatistics' (object ID 101575400).
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "Security_Form_Control_Assoc", index ID 0, partition ID 11671940300800, alloc unit ID 11671940300800 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Security_Form_Control_Assoc' (object ID 178099675).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'mvaprod'.
Reason :Because table used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables.
Solution : First execute below command
USE mvaprod;
GO
DBCC UPDATEUSAGE (N'mvaprod') WITH NO_INFOMSGS;
GO
Second execute below command
USE [mvaprod]
GO
DBCC CHECKDB(N'mvaprod') WITH NO_INFOMSGS
I hope this is perfect answer .
Qazi Saif Hussain
Mphasis, an hp company
Pune India,
saif.qazi0532@gmail.com:-)
August 23, 2010 at 2:23 am
Please post new questions in a new thread. Thank you.
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply