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

Is it safe to Shrink Database file in SQL server 2005 Expand / Collapse
Author
Message
Posted Saturday, September 29, 2012 12:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 2:00 AM
Points: 21, Visits: 150

Hi All,

I had a table (Size = 10GB) with Millions of records that was storing ShowXML plan through SQL Server Profiler Tarce, due to this my db size increased.

So i right click on this table then delete it, but size of DB is same but it should decreased as i deleted 10GB Table.

So I'm planning to use "DBCC SHRINKDATABASE" and i want to know is it safe to Shrink Database file in SQL server 2005.

Due to increase of DB size it is taking long time for backup.

Thanx

Neel
Post #1366164
Posted Saturday, September 29, 2012 1:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Shrinking won't make your backups any faster, because backups only read allocated extents, not empty space.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366169
Posted Saturday, September 29, 2012 10:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 2:00 AM
Points: 21, Visits: 150
Hi Gail,

Thanx for your reply.

As i already explained in my post why i want to Shrink DB.

I have deleted Table that is occupied 10GB space in DB, so why DB showing same size after deletion of this 10GB table.

Pls help me to reduce this 10 GB space from SQL DB.

Post #1366228
Posted Sunday, September 30, 2012 3:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
You said you want to shrink because your backups are taking too long. Shrinking will not reduce the duration of your backups.

How big is that DB? How long will it take for the normal data growth to reuse that 10GB?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse