SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Freeing up some room


Freeing up some room

Author
Message
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
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.



Scott D. Jacobson
Scott D. Jacobson
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 1017
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?
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
It's set to grow by 10 percent.



durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 2775
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
Scott D. Jacobson
Scott D. Jacobson
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 1017
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?
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
thanks for answering/helping...

recovery model is set to full



durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 2775
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
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 2775
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
Scott D. Jacobson
Scott D. Jacobson
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 1017
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.
krypto69
krypto69
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 2444
awesome thanks guys for all the help



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search