August 1, 2012 at 12:51 pm
We have a DB that seems like it's way larger than it should be.
So I ran sp_spaceused. Results:
database_namedatabase_sizeunallocated space
ULTIPRO_WSI93386.63 MB22054.04 MB
reserveddataindex_sizeunused
48311728 KB29567176 KB18468296 KB276256 KB
How can I reclaim the reserved data?
This is a vender's DB so I can't do too much to change it schematically.
August 1, 2012 at 12:55 pm
I think you mean you want to reclaim the unused? "Reserved" is the data+index.
What are you autogrowth options set to? Is the database set to grow by a percentage or set size in MB?
August 1, 2012 at 2:00 pm
It's set to grow by 10 percent.
August 2, 2012 at 6:11 am
run dbcc sqlperf(logspace)
see if you DB's log file is huge then you can clear log file. make sure that you have a full backup or log file not required.
never shrink the Data file for space.
does the disk does not have sufficient free space?
Regards
Durai Nagarajan
August 2, 2012 at 6:21 am
krypto69 (8/1/2012)
It's set to grow by 10 percent.
Set that to a size in MB instead. At almost 50 gigs, the next time your DB needs to grow, it will grow by 5 GB. This will become exponentially worse over time.
I also noticed someone suggested checking your log file. I second that. What recovery model is the database set to?
August 2, 2012 at 6:40 am
thanks for answering/helping...
recovery model is set to full
August 2, 2012 at 6:59 am
krypto69 (8/2/2012)
thanks for answering/helping...recovery model is set to full
so logs will not clear/reused untill a log backup happens.
check the log file size and act accordingly.
Regards
Durai Nagarajan
August 2, 2012 at 7:04 am
forgot to mention scott 's thought is good allocate the growth size that fits your reuirement.
dont reduce it do low as db growth requires lot of resources and time. check it in sql log in the past for the past db growth and finalize.
if you decide to shrink the db dont forget to reorganize or rebuild the index.
Regards
Durai Nagarajan
August 2, 2012 at 7:07 am
durai nagarajan (8/2/2012)
dont reduce it do low as db growth requires lot of resources and time. check it in sql log in the past for the past db growth and finalize.
Yes. I was making an assumption that you already had an idea of what size in MB to grow by based on past growth. You can get this from the log and there's probably even a DMV you can query to see the growth over time. Since you're growing by a % you want to see how much the files actually grew by, not how much the requested.
August 2, 2012 at 10:17 am
awesome thanks guys for all the help
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply