July 31, 2014 at 8:30 am
The error being produced just smells of an issue trying to transfer data across the wire and that there is some sort of transfer issue.
Is the backend device attached via iscsi is is it fiber?
It's a tough sell, but have the connections between the server and the backend been checked/verified for error or fault? I have seen faulty connections cause things like this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 8:34 am
SQLRNNR (7/31/2014)
The error being produced just smells of an issue trying to transfer data across the wire and that there is some sort of transfer issue.Is the backend device attached via iscsi is is it fiber?
It's a tough sell, but have the connections between the server and the backend been checked/verified for error or fault? I have seen faulty connections cause things like this.
Fiber. Yes connections have been verified OK.
This entire issue cropped up a few weeks before this when the vendor, daily would give us transaction logs to apply to our copy of the database to keep us up-to-date. Randomly transaction logs would fail to apply due to the same error. They would give us a new copy of the log and it would apply. We finally got to a point where that wouldn't work anymore so we had them give us a copy of the full db.
July 31, 2014 at 8:37 am
I'll try to think on it some more. I'm sure this odd error has you frustrated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 8:43 am
SQLRNNR (7/31/2014)
I'll try to think on it some more. I'm sure this odd error has you frustrated.
Yes. I've never seen anything like this before. It does smell of a timing issue copying the file from the NAS to the server I tend to agree. Probably due to the sheer size of the backup. It is SQL2008R2 compressed but still 142 gig in size.
July 31, 2014 at 8:48 am
Markus (7/31/2014)
SQLRNNR (7/31/2014)
I'll try to think on it some more. I'm sure this odd error has you frustrated.Yes. I've never seen anything like this before. It does smell of a timing issue copying the file from the NAS to the server I tend to agree. Probably due to the sheer size of the backup. It is SQL2008R2 compressed but still 142 gig in size.
Is that a single file? Can you change the backup to backup to multiple files? Might help you eliminate that as an issue.
I've seen misconfigured NIC teaming cause some odd errors but don't think that applies here.
July 31, 2014 at 8:52 am
Gazareth (7/31/2014)
Markus (7/31/2014)
SQLRNNR (7/31/2014)
I'll try to think on it some more. I'm sure this odd error has you frustrated.Yes. I've never seen anything like this before. It does smell of a timing issue copying the file from the NAS to the server I tend to agree. Probably due to the sheer size of the backup. It is SQL2008R2 compressed but still 142 gig in size.
Is that a single file? Can you change the backup to backup to multiple files? Might help you eliminate that as an issue.
I've seen misconfigured NIC teaming cause some odd errors but don't think that applies here.
Good idea. Next time I have them give us a db backup I will have them split up the files.
Only thing is when we had transaction logs have the failure applying they were between 2-6 gig in size. So, if that is the case I'd have to have a backup consisting of 146 files... which is unrealistic.
July 31, 2014 at 10:46 am
Markus (7/31/2014)
Gazareth (7/31/2014)
Markus (7/31/2014)
SQLRNNR (7/31/2014)
I'll try to think on it some more. I'm sure this odd error has you frustrated.Yes. I've never seen anything like this before. It does smell of a timing issue copying the file from the NAS to the server I tend to agree. Probably due to the sheer size of the backup. It is SQL2008R2 compressed but still 142 gig in size.
Is that a single file? Can you change the backup to backup to multiple files? Might help you eliminate that as an issue.
I've seen misconfigured NIC teaming cause some odd errors but don't think that applies here.
Good idea. Next time I have them give us a db backup I will have them split up the files.
Only thing is when we had transaction logs have the failure applying they were between 2-6 gig in size. So, if that is the case I'd have to have a backup consisting of 146 files... which is unrealistic.
I am less inclined to think the size of the backup is affecting it unless it is a size limit of anything over 2Gb is susceptible.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 2:11 pm
Nothing about this db is easy... the import failed and when i tried to save the file that shows the error the Import/Export wizard got an error and closed.
Then I try and run a DBCC CHECKDB and got this error"
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
No filegroup is marked as read only..... jeesh... more researching to be done.
July 31, 2014 at 2:15 pm
How much freespace are you dealing with? How much freespace on the volume that houses tempdb?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 2:18 pm
SQLRNNR (7/31/2014)
How much freespace are you dealing with? How much freespace on the volume that houses tempdb?
187 Gig of free space on the drive.
July 31, 2014 at 2:25 pm
Markus (7/31/2014)
SQLRNNR (7/31/2014)
How much freespace are you dealing with? How much freespace on the volume that houses tempdb?187 Gig of free space on the drive.
That message can be thrown if there is not enough free space to house the sparse file created for the snapshot of the database that is used during checkdb. It should be able to proceed without doing a snapshot, iirc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 1, 2014 at 7:16 am
Well... the plot thickens.... This morning I ran a Check integrity on the newly created database with more than half of the tables in it populated..... I have just sent an email to the company that hosts this database for us to see if they run Check Integrity utility against the live database. So, I don't know if the corruption was in the database at their data center or the import process is corrupting the data.... the drive this is on is a brand new drive on our brand new SAN.... I highly doubt the corruption is on our SAN. Anyone have any thoughts on this?
Msg 8928, Level 16, State 1, Line 2
Object ID 2021582240, index ID 0, partition ID 72057594047234048, alloc unit ID 72057594053459968 (type In-row data): Page (1:2209915) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 2021582240, index ID 0, partition ID 72057594047234048, alloc unit ID 72057594053459968 (type In-row data), page (1:2209915). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8928, Level 16, State 1, Line 2
Object ID 2021582240, index ID 0, partition ID 72057594047234048, alloc unit ID 72057594053459968 (type In-row data): Page (1:3343873) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 2021582240, index ID 0, partition ID 72057594047234048, alloc unit ID 72057594053459968 (type In-row data), page (1:3343873). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8928, Level 16, State 1, Line 2
Object ID 2021582240, index ID 0, partition ID 72057594047234048, alloc unit ID 72057594053459968 (type In-row data): Page (1:9545978) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 2021582240, index ID 0, partition ID 72057594047234048, alloc unit ID 72057594053459968 (type In-row data), page (1:9545978). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 6 consistency errors in table 'CFR_tbl_attempt' (object ID 2021582240).
CHECKDB found 0 allocation errors and 6 consistency errors in database 'DBTEST3'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBTEST3).
August 1, 2014 at 7:21 am
That looks like IO subsystem, the page headers are invalid, not something an import could do. Doesn't have to be the drives, could be anything in the IO stack.
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
August 1, 2014 at 7:23 am
GilaMonster (8/1/2014)
That looks like IO subsystem, the page headers are invalid, not something an import could do. Doesn't have to be the drives, could be anything in the IO stack.
Yikes... OK... I will talk with our SAN team.
August 1, 2014 at 4:42 pm
Update. I was able to get a new large drive allocated to another server running SQL2008R2 and I copied the 140gig db backup to it and it successfully restored into that SQL Server. Storage team says back end storage and IO are all OK.... hum..... RUnning Integrity Check on the db on the new SQL Server now. Bizzare
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply