Cannot shrink database after restore to test server
-
I have transferred a db to a test server and now the db on the test server is swelled up to 20GB and I cannot shrink it. We have limited space and processing power on the test server, so I routinely delete a lot of data in order to get it to fit.
What I have done:
1) backup the 20GB db on production. backup size is 16GB.
2) restore to a copy of prod db on production.
3) delete a lot of historical data.
4) backup the copy of prod db. the backup db size is now down to 10GB.
5) transfer backup to test server.
6) restore from backup.
the database swells up to 20GB after the restore. i cannot shrink the database (apparently). i left the shrink running overnight and it was still running 12+ hours later. i cancelled it. and tried the restore again. this time i deleted the test db, recreated a 10GB empty one, and restored. the shrink still seems to be doing the same thing now. it's been running for 2 hours.
what am i missing here? is it not possible to shrink the db down past the original size of prod (20GB)?
-
I'm not sure why the shrink is hanging, 20GB isn't that big and shouldn't take that long. However if you want to shrink the database to a size smaller than the original minimum size specified at creation you have to use DBCC SHRINKFILE rather than SHRINKDATABASE.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
FYI, I misspoke. 20GB isnt the original size of the prod db, it's the current size. The original size is probably 1GB or so.
I might try the SHRINKFILE tomorrow. I have a corrupt oracle db right now.....
-
But I thought Oracle was UNBREAKABLE?!>!#@(*&$!
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Oh but it is....it's a raid controller error!
-
When you say it swelled to 20GB, do you mean that after restoring it to the TestDB you added more data?
If you restored the db to the test server and the db had 20GB when you moved it, then the minimum size of the db is 20GB and you won't be able to shrink below that.
If this is the case, next time create the database as an empty DB with a small size. Then restore the database with the WITH REPLACE command. Then the minimum size will still be small and you can shrink the db.
-SQLBill
-
No data was added. The emtpy test db is created as 10GB, when I restore the backup from the prod copy that I deleted the data in, the test db swells up to 20GB.
So it seems like I did like you are saying except for the WITH REPLACE cmd. I'll try that next.
THANKS
-
The WITH REPLACE option merely tells SQL to overwrite any existing files of the same name with what is in the backup. The DBCC SHRINKFILE allows you to "resize" the data files smaller than their original minimum size.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Just a couple more things to add ...
1) Did you change recovery from 'Full' to 'Simple' on the test server after the restore ?
2) Have you performed a backup/truncate_only on the transaction log prior to the 'shrink' ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply