Shrink DB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Shrink DB

Shrink DB

Rate Topic

Display Mode

Topic Options

Author

Message

Nita Reddy

Posted Wednesday, July 30, 2008 7:34 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:42 PM
Points: 423, Visits: 315

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.

Post #543466

stephen.christie

Posted Wednesday, July 30, 2008 7:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:50 AM
Points: 420, Visits: 10,963

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.

Post #543478

Nita Reddy

Posted Wednesday, July 30, 2008 8:05 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:42 PM
Points: 423, Visits: 315

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

Post #543509

stephen.christie

Posted Wednesday, July 30, 2008 9:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:50 AM
Points: 420, Visits: 10,963

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

Post #543646

Nita Reddy

Posted Wednesday, July 30, 2008 12:28 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:42 PM
Points: 423, Visits: 315

Nope nothing is been running, I checked earlier and I didnow as well.

Post #543801

stephen.christie

Posted Wednesday, July 30, 2008 1:14 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:50 AM
Points: 420, Visits: 10,963

Hi

What is the error code it gives you with the last error??

Post #543852

Steve Jones - SSC Editor

Posted Wednesday, July 30, 2008 2:24 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:00 AM
Points: 31,410, Visits: 13,728

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

Post #543904

MANU-J.

Posted Wednesday, July 30, 2008 3:26 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 1,654, Visits: 8,563

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

Post #543931

« Prev Topic | Next Topic »

Permissions