Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Freeing up some room Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 12:51 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 701, Visits: 1,732
We have a DB that seems like it's way larger than it should be.

So I ran sp_spaceused. Results:

database_name	database_size	unallocated space
ULTIPRO_WSI 93386.63 MB 22054.04 MB

reserved data index_size unused
48311728 KB 29567176 KB 18468296 KB 276256 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.



Post #1338764
Posted Wednesday, August 1, 2012 12:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:56 AM
Points: 358, Visits: 902
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?
Post #1338766
Posted Wednesday, August 1, 2012 2:00 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 701, Visits: 1,732
It's set to grow by 10 percent.


Post #1338794
Posted Thursday, August 2, 2012 6:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1339081
Posted Thursday, August 2, 2012 6:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:56 AM
Points: 358, Visits: 902
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?
Post #1339085
Posted Thursday, August 2, 2012 6:40 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 701, Visits: 1,732
thanks for answering/helping...

recovery model is set to full



Post #1339100
Posted Thursday, August 2, 2012 6:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1339115
Posted Thursday, August 2, 2012 7:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1339121
Posted Thursday, August 2, 2012 7:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 10:56 AM
Points: 358, Visits: 902
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.
Post #1339127
Posted Thursday, August 2, 2012 10:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 701, Visits: 1,732
awesome thanks guys for all the help


Post #1339344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse