May 4, 2004 at 6:54 pm
SQL 2k, Win 2K
I have a production database that is usually 23GB. This includes 15GB for the largest table. After running DBREINDEX on the large table (own data file) the space usage on the data file goes to about 30GB (15GB for data etc and 15GB for freespace). I realise that the free space is a result of the space required for DBREINDEX to rebuild indexes etc. The backup for this database is approximately 20GB.
Is it possible to restore this database on a different server and have a smaller footprint? I don't want the 15GB of freespace for the largest table. I won't be running DBREINDEX on this server and so the space will never be required. This server has limited space and having multiple copies of this database being restored daily with the 30GB is proving to be a problem. I know I can shrink the DB/File after the restore but that would mean having enough space to get the restore done.
I did find a very vague hit regarding this issue on a some SQL Server forum and someone had thought that is was possible. Is it?
Thanking you in advance.
May 4, 2004 at 7:26 pm
The restore process will restore the database as it was when backed up.
May 6, 2004 at 12:07 am
run dbcc shrinkfile after restoring to reduce the size of the file. Or you can run this prior to backing up. Recommend shrinking in 500k or 1gig increments.
May 6, 2004 at 1:38 am
Unfortunately shrinking after the restore requires me to have the available space on the server in the first place in order to do the restore. We are currently restricted on space.
Shrinking before backing up will undo a lot of what DBREINDEX does. My experience of shrinking databases/files is that it tends to cause fragmentation and undoes a lot of what DBREINDEX or INDEXDEFRAG fixes.
I guess the answer is get more disk space or stop running DBREINDEX!!
May 6, 2004 at 4:02 am
I have written a lot of posts lately on this issue. My recommendation would be:
1. Set a realistic fill factor for the indexes so that you do not have to reindex so often, hopefully once a week.
2. Think about splitting the tables up so that you only run a reindex on one seventh of the tables each day over the period of a week. That way you still reindex weekly but in batches.
3. Switch to bulk logging l=mode before you reindex and switch back to full afterwards. This should save you about 25% (has for me in the past).
Alternatively, target the indexes that are need reindexing and reindex your reference tables less frequently.
A fellow dba has asked microsoft why they cannot reduce the amount of logging during reindexing but they were not very responsive. Maybe if enough people complain, they will do something about it in the future.
Anyhows, hope this helps you out a bit.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply