April 6, 2005 at 6:41 am
I have a database that we have backed up. Its 'used' size is about 42GIG (give or take a few)
I restored this database to another server and it's used space is only about 26GIG.
2 Questions:
1) Why is this the case?
2) How can I fix the original database to be 'more correct' in size?
I greatly appreciate all of your thoughts!
April 6, 2005 at 7:53 am
Run sp_spaceused against your database and your restored database.
Sample output:
database_name database_size unallocated space
----------------------- ------------------ ------------------
master 86.56 MB 23.77 MB
reserved data index_size unused
--------------- -------------- ---------------- ------------------
16680 KB 11720 KB 1528 KB 3432 KB
Look at the data and index_size fields these should be the same on both databases.
If you have any negative values for unallocated space then you should run sp_spaceused @updateusage = 'TRUE' this is the same as DBCC UPDATEUSAGE.
Lukas Botha
April 7, 2005 at 6:44 am
I ran the updateusage='true' option and it did correct the negative values.
However, it is still much larger than it should be. I see the data and indexes on one server is say 25GIG FOR DATA, AND 17 GIG FOR INDEXES.
On the other server (that I restored to) it is 20GIG AND 14GIG.
There is almost a 10 GIG difference that I can't seem to account for?
Does anyone have any additional thoughts?
Let me know.
April 7, 2005 at 12:09 pm
There's no need to 'fix' the database. It's most likely due to fragmentation. My guess is that you probably do not reindex or recreate tables when fragmentation gets high. To prove the point (check to make sure al of you data is present) why not run a select count(*) in all user tables on each server and compare the results. My guess is that the counts will match exactly if both systems are inactive for the test.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 7, 2005 at 12:20 pm
Rudy,
Thanks for the reply! I have inherited quite a mess here, but we do index EVERY NIGHT (thats a completely separate issue at this point). The restore was directly from the EXACT database, but its physically taking less space.
My thought is backing up the db, and then restoring it directly over top what I have to see if that would remedy the problem. Also, a thought is maybe it has to do with block allocation on the disk, but I'm not sure how I could check this particular setup. I've not seen anything like this before with DB's, hence why I posted.
Its very frustrating because one of our servers is running out of space now (becuase it had 10GIG FREE, now its 'using' it)...
At any rate, if you or anyone else has any ideas on why this is the case...I suspect some type of fragmentation, but we do reindex every night...
Thanks again to all that might have an idea...every idea is a good one.
April 7, 2005 at 12:24 pm
Are you re-indexing or dropping and recreating indexes ? Also, fragmentation can occur at the data page level as well, not just index pages. Here's one link to get you started ...
http://www.sqlservercentral.com/columnists/chedgate/fragmentation101.asp
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 7, 2005 at 12:51 pm
It's actually a maintenance plan that is doing the work. So, its a reindex. I figure this might be a 'disk' fragementation issue or at least be tied to that.
When I see the 'used' space that different, (and actually different at the index level as well) I can only assume its a disk frag issue. I've not seen anything else that would point me in the right direciton over the years.
My biggest concern is that there may be some type of corruption just waiting to 'bite me' at the wrong time. Has anyone seen this scenario where it could be a file corruption issue?
April 8, 2005 at 12:35 am
If you are concerned about possible corruption you can always run DBCC CHECKDB to verify your concerns.
What you can also check is DBCC SHOWCONTIG, this should give you a good idea of what fragmentation your database is currently experiancing. You can run on both DB's and compare the results.
Thats all ideas I have for now...
Lukas Botha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply