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


Shrink DB


Shrink DB

Author
Message
Nita Reddy
Nita Reddy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 409
I have 300 GB user database I can see around 150 free space on data file.

When I tried to shrink database my MDF file still shows me 300 GB how to shrink this.

I ran DBCC command and shrink Database but no impact.
stephen.christie
stephen.christie
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 11094
Hi,
Try to shrink file instead of database, this would make the file shrink.
If this does not help, then you must see what the initial size of database(datafile) is set to.
Nita Reddy
Nita Reddy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 409
I did that when I do shrink file I am getting this error below:

A server error occured on the current command. The results, if any, should be discarded File ID 1 of database ID 7 canot be shrunk as it is either being shrunk by another process or is empty
stephen.christie
stephen.christie
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 11094
Hi,

Ok This means that your original job to shrink is still running in the background.

Do a sp_who2 and a sp_lock to see which process is holding that database. then once you have that then you can do a dbcc inputbuffer (spid) to check the command that is running.

You are going to have to wait for it to stop, don't restart sql server as you will get a corruption on the databases.

Hope this helps
Nita Reddy
Nita Reddy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 409
Nope nothing is been running, I checked earlier and I didnow as well.
stephen.christie
stephen.christie
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 11094
Hi

What is the error code it gives you with the last error??
Steve Jones
Steve Jones
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: Administrators
Points: 143770 Visits: 19424
Are you trying to shrink this down to 150MB? You want to keep free space in the file in general.

The shrinking can be slow or fast, depending on the load your DB experiences and the locks that might be held on files. It can take some time.

Can you restart the instance and then try shrinking if you really need to?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MANU-J.
MANU-J.
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7180 Visits: 8766
Nita,

Please make sure AUTOSHRINK option is turned OFF for the database in question.

Also, try shrinking database file in chunks rather than specifying a big size in one shot. Use dbcc shrinkfile(fileid,DesiredSize)

Size--Specify the desired size which is 150,200 MB or 1 GB(it is based on the server I/O subsystem) less than the current size.

For 300 GB database specify dbcc shrinkfile(1,299000) first if it works fine and completes in less than 10 minutes than go ahead with reducing the shrink size during later executions.

Perform this activity when database is used at minimum.

Manu
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