error during shrink file after restore on test
-
Hello,
I did backup and restored to test..
bak file has about 13GB.
database has allocated mdf file for 40GB and log file for 5GB... I wanted shrink this mdf, because there is 25GB free, but it wasnt possible ..
I had error like this
shrink failed for datafile a severe error occurred
what can I do ?
one idea is first shrink file on production server , then backup and then restore...
but is there any possibility how to do on test now ?
Thanks
-
I know this is reaching, but was there another process already shrinking the database on test?
-
Sorry, I forgot to address your other point. Yes, you could shrink the production database. However, you should first ask yourself why. It's the size it is for a reason and if the DBA put thought and planning into the size to handle, for example, a year's worth of data, you want to leave it alone. If it's just going to grow again, you're better off not shrinking it in the first place.
If you're in a crisis situation where you're nearly out of space on the volume (although it doesn't sound like you are) then you should seek a solution like talking to your SAN administrator and getting some more space allocated. You'd also want to monitor your disk space a little more closely so you don't end up in a crisis.
Also, when you shrink a data file, it has this nasty tendency to fragment your indexes to 99%. Generally speaking, you want to avoid shrinking your databases.
-
tony28 (3/5/2016)
Hello,I did backup and restored to test..
bak file has about 13GB.
database has allocated mdf file for 40GB and log file for 5GB... I wanted shrink this mdf, because there is 25GB free, but it wasnt possible ..
I had error like this
shrink failed for datafile a severe error occurred
what can I do ?
one idea is first shrink file on production server , then backup and then restore...
but is there any possibility how to do on test now ?
Thanks
please provide full details of all error messages
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
tony28 (3/5/2016)
Hello,I did backup and restored to test..
bak file has about 13GB.
database has allocated mdf file for 40GB and log file for 5GB... I wanted shrink this mdf, because there is 25GB free, but it wasnt possible ..
I had error like this
shrink failed for datafile a severe error occurred
what can I do ?
one idea is first shrink file on production server , then backup and then restore...
but is there any possibility how to do on test now ?
Thanks
I agree with the others that shrinking the database is probably unnecessary. I'll also state that it will be fruitless. It will cause massive fragmentation... rebuilding the clustered indexes will cause the database to grow because any index over 128 extents (that's just 8MB) will cause a new index to be created before the old one is dropped. In the case of a clustered index, the leaf level of the index IS the data in the table. Your freshly shrunk database will have to regrow by the size of your largest clustered index plus somewhere between 10 and 20%. Reorganizing the clustered index won't cause the MDF file to grow, but it will cause the logfile to grow, a lot.
If you make the mistake of rebuilding all the indexes on a table in a single command, then the growth will be even worse.
Of course, shrinking the logfile after such growth is a relatively trivial exercise but why not just avoid the exercise to begin with? 40GB vs 25GB isn't life threatening to the server. If it is, you need to do something about the server or shrinking and all the hell that goes with it will become more of a full time job that you need it to be.
I'd also recommend that you run DBCC CHECKDB on the production version of the database before you transfer it in any manner, especially since you've just come across an error.
Last but not least, take Perry's advice and copy'n'paste the exact error. Subtle differences in what the error says can make a night'n'day difference in determining what is actually wrong.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
thanks for the reply.. I am not sure if somebody knows what I want...so sorry for my english
Because this is test server and data will not be inserted there, I want to have almost same size of MDF like I have data, so free space in MDF is useless..
And this wasnt possible due to this error. in attachment you can see
Normally never I did shrink on production, like you wrote, why, not needed. On production storage is for this time enough space ... only increase.
but just ask to yourself... do you mean that I need free space on test server? No.
So my idea was during weekend if production is stopped, try do shrink production database , do backup, restore on test .. but not sure if will be possible shrink on production..I did not try and maybe will not try.. :]
Or try to solve this error.
Thanks
-
tony28 (3/7/2016)
thanks for the reply.. I am not sure if somebody knows what I want...so sorry for my englishBecause this is test server and data will not be inserted there, I want to have almost same size of MDF like I have data, so free space in MDF is useless..
And this wasnt possible due to this error. in attachment you can see
Normally never I did shrink on production, like you wrote, why, not needed. On production storage is for this time enough space ... only increase.
but just ask to yourself... do you mean that I need free space on test server? No.
So my idea was during weekend if production is stopped, try do shrink production database , do backup, restore on test .. but not sure if will be possible shrink on production..I did not try and maybe will not try.. :]
Or try to solve this error.
Thanks
I agree. DON'T try the shrink in production but DO try a DBCC CHECKDB on the production database.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (3/7/2016)
tony28 (3/7/2016)
thanks for the reply.. I am not sure if somebody knows what I want...so sorry for my englishBecause this is test server and data will not be inserted there, I want to have almost same size of MDF like I have data, so free space in MDF is useless..
And this wasnt possible due to this error. in attachment you can see
Normally never I did shrink on production, like you wrote, why, not needed. On production storage is for this time enough space ... only increase.
but just ask to yourself... do you mean that I need free space on test server? No.
So my idea was during weekend if production is stopped, try do shrink production database , do backup, restore on test .. but not sure if will be possible shrink on production..I did not try and maybe will not try.. :]
Or try to solve this error.
Thanks
I agree. DON'T try the shrink in production but DO try a DBCC CHECKDB on the production database.
I did on production server without errors..
I did on test server, where I restore this backup and got few errors in few tables..(but I tried shrink, so maybe it could be due to this. )
so I am going to search what means these errors and what do now.. or better first try do restore again?
DBCC results for 'TABLE1'.
There are 12 rows in 1 pages for object "TABLE1".
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 408440579, index ID 1, partition ID 72057595300872192, alloc unit ID 72057595305656320 (type In-row data). Page (1:1747266) was not seen in the scan although its parent (1:226195) and previous (1:1747265) refer to it. Check any previous errors.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 408440579, index ID 1, partition ID 72057595300872192, alloc unit ID 72057595305656320 (type In-row data), page (1:1747266). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 408440579, index ID 1, partition ID 72057595300872192, alloc unit ID 72057595305656320 (type In-row data): Page (1:1747266) could not be processed. See other errors for details.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 408440579, index ID 1, partition ID 72057595300872192, alloc unit ID 72057595305656320 (type In-row data). Page (1:1747267) is missing a reference from previous page (1:1747266). Possible chain linkage problem.
There are 774966 rows in 11925 pages for object "TABLE2".
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TABLE2' (object ID 408440579).
DBCC results for 'TABLE3'.
There are 3484338 rows in 28389 pages for object "TABLE3".
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data), page (1:1153376). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data): Page (1:1153376) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data). Page (1:1153376) was not seen in the scan although its parent (1:78301) and previous (1:1152599) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data). Page (1:1153377) is missing a reference from previous page (1:1153376). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data). Page (1:4944623) is missing a reference from previous page (1:4944622). Possible chain linkage problem.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data), page (1:4944622). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data): Page (1:4944622) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 472440807, index ID 1, partition ID 72057595301199872, alloc unit ID 72057595305984000 (type In-row data). Page (1:4944622) was not seen in the scan although its parent (1:1729496) and previous (1:4944621) refer to it. Check any previous errors.
DBCC results for 'TABLE4'.
There are 884721 rows in 13267 pages for object "TABLE4".
CHECKDB found 0 allocation errors and 8 consistency errors in table 'TABLE4' (object ID 472440807).
BCC results for 'TABLE5'.
There are 3544 rows in 51 pages for object "TABLE5".
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3878767) is missing a reference from previous page (1:3878766). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3879283) is missing a reference from previous page (1:3879282). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3878548) was not seen in the scan although its parent (1:347938) and previous (1:3878547) refer to it. Check any previous errors.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3879282) was not seen in the scan although its parent (1:347938) and previous (1:3879281) refer to it. Check any previous errors.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3878138) was not seen in the scan although its parent (1:344320) and previous (1:3878137) refer to it. Check any previous errors.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3879286) was not seen in the scan although its parent (1:347938) and previous (1:3879285) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3879287) is missing a reference from previous page (1:3879286). Possible chain linkage problem.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data), page (1:3878138). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data): Page (1:3878138) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data), page (1:3878548). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data): Page (1:3878548) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data), page (1:3878766). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data): Page (1:3878766) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data), page (1:3879282). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data): Page (1:3879282) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data), page (1:3879286). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data): Page (1:3879286) could not be processed. See other errors for details.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3878139) is missing a reference from previous page (1:3878138). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3878549) is missing a reference from previous page (1:3878548). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 536441035, index ID 1, partition ID 72057595345829888, alloc unit ID 72057595508621312 (type In-row data). Page (1:3878766) was not seen in the scan although its parent (1:347938) and previous (1:3878765) refer to it. Check any previous errors.
DBCC results for 'TABLE6'.
There are 1476583 rows in 22466 pages for object "TABLE6".
CHECKDB found 0 allocation errors and 20 consistency errors in table 'TABLE6' (object ID 536441035).
DBCC results for 'TABLE7'.
There are 6293 rows in 178 pages for object "TABLE7".
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1877685837, index ID 1, partition ID 72057595306770432, alloc unit ID 72057595311554560 (type In-row data), page (1:1499947). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 1877685837, index ID 1, partition ID 72057595306770432, alloc unit ID 72057595311554560 (type In-row data): Page (1:1499947) could not be processed. See other errors for details.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1877685837, index ID 1, partition ID 72057595306770432, alloc unit ID 72057595311554560 (type In-row data). Page (1:1499948) is missing a reference from previous page (1:1499947). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1877685837, index ID 1, partition ID 72057595306770432, alloc unit ID 72057595311554560 (type In-row data). Page (1:1499947) was not seen in the scan although its parent (1:1497708) and previous (1:1499946) refer to it. Check any previous errors.
DBCC results for 'TABLE8'.
There are 158545 rows in 9296 pages for object "TABLE8".
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TABLE8' (object ID 1877685837).
CHECKDB found 0 allocation errors and 36 consistency errors in database 'Database'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Database).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-
OK, so restore again wasnt helpful ...
So I did this below... I am still talking about TEST Server..
ALTER DATABASE Database
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (Database,repair_allow_data_loss)
ALTER DATABASE Database
SET MULTI_USER;
DBCC CHECKDB showed without errors, all was fixed..
I did shrink successful from 40GB to 15GB ...
So job is done..
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply