September 30, 2008 at 12:16 am
Hello Gurus,
I have a database in ms sql 2000 which was 134 gb. i did a dbcc shrinkfile on that. It went down to 125gb.
The sum of the data size and index size of all the user tables is about 40gb. The transaction log is 18 gb. But when i check the mdf size it is 107 gb. I have 2 questions..
Where does the additional 67 gb is coming from. Am sure it is not the stored procedures or sys tables which is that big..!
Additional question is that if i do a shrinkfile (dbcc shrinkfile )without any parameters, what it will do? I mean is it a shrinkfile truncateonly or notruncate??
Cheers
Amit
September 30, 2008 at 2:44 am
whenever using dbcc shrink log file, you have to specify the parameter how much you need the ldf file,
before shrinking ldf file, you have to mention the with_truncate olny
September 30, 2008 at 3:28 am
Regarding Space issue you you run DBCC spaceused and check the unused space and you run DBCC UPDATEUSAGE(0) on your database then check the size now.
When you give no parameter to DBCC SHRINKFILE If the file is log file it will try to shrink the file to default size. If the file is a data file It will shrink upto used space in the data file.
I hope it will help you.
Rajesh Kasturi
September 30, 2008 at 5:15 am
Hi There,
I have run the dbcc updateusage but nothing changed. My question though is that what makes the size of the database so large.
I run a sp_spaceused, the results are as follows:
DatabaseName DB_Size UnallocatedSpace
======== ========== =============
DBNAME 125362.06 MB 9885.56 MB
Reserved data index_size unused
========== ========== ========= =========
99789568 KB 36344120 KB 3667096 KB 59778352 KB
What is the space i can recovered from. Where is the 65 GB since i understand that the index+data size is about to 40gb.
Is the "UnallocatedSpace" the only space that i can recover?
Thanks for replying...
Cheers
Amit
October 1, 2008 at 10:22 pm
Hi Friends,
I badly need help on this. If you have any replies, please do post.
Thanks
Amit
October 3, 2008 at 5:30 am
amit (9/30/2008)
Hi There,I have run the dbcc updateusage but nothing changed. My question though is that what makes the size of the database so large.
I run a sp_spaceused, the results are as follows:
DatabaseName DB_Size UnallocatedSpace
======== ========== =============
DBNAME 125362.06 MB 9885.56 MB
Reserved data index_size unused
========== ========== ========= =========
99789568 KB 36344120 KB 3667096 KB 59778352 KB
What is the space i can recovered from. Where is the 65 GB since i understand that the index+data size is about to 40gb.
Is the "UnallocatedSpace" the only space that i can recover?
Thanks for replying...
Cheers
Amit
The important figures here are:
DB_size - this is the physical size of all the database files added together (including the log file)
Unallocated space -the free space in the database
Reserved - the space used in the database
So, data file size - log file size - reserved = unallocatedspace, so yes this is the amount of space you can recover from a shrink.
so I would not bother with a shrink here, its not worth it for two main reasons:
only get 8Gb back which the database will want back sometime anyway.
the reserved space is made up of data + index + unused. Your unused is very high. This is unused space within the data pages, and this high value suggests to me either large row sizes thus wasting space with the pages or severe fragmentation. Are you rebuilding indexes? If not do so, and if you are your previous shrinks will have fragmented the database, as shrinks just move all the data to the front of the database without regard to maintaining contiguous data.
try rebuilding indexes and see if that compacts your data giving more free space within the database. Then only shrink it if you have a high unallocated space value and you actually need this space back on the drive, otherwise leave as is. If you shrink, try the truncate_only option first.
Check up on dbcc shrinkfile in books online
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply