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

Shrink database Expand / Collapse
Author
Message
Posted Thursday, October 14, 2010 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:54 PM
Points: 208, Visits: 1,025
hello experts,
how can i know before shrinking the maximum size which can be relaeased by shrinking a database.

thanks in advance

Post #1004542
Posted Thursday, October 14, 2010 10:39 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: Today @ 2:25 AM
Points: 42,759, Visits: 35,853
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/



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 #1004594
Posted Friday, October 15, 2010 2:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:54 PM
Points: 208, Visits: 1,025
ya but the things is, we dont have much space in disk, we have around 600 databases in our UAT server, so for getting space m trying to remove older backups, thinking to shrink databases, right now we dont have approval for adding extra disk space so i have been told to do management for the space of disk even i need to shrink DBs or delete older DBs, so is there any way for the same what asked before and to know also which databases are useless or not in use so that i can delete them from our UAT server.
Post #1004959
Posted Friday, October 15, 2010 2:25 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: Today @ 2:25 AM
Points: 42,759, Visits: 35,853
Use sp_spaceused

As for unused databases, no easy way.



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 #1004963
Posted Friday, October 15, 2010 2:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
To find out if a DB is not used anymore set it offline and wait for someone to complain :)
If noone complains, then you have one less DB to worry about.

Seriously though, one way to know if a DB is not in use is to take a trace (only audit login/logout) and see if there is any login activity other than the maintenance jobs.

Let it run a few days to be sure (week-end included) then if there are no activity do a full backup, zip it, archive it and drop the DB after leaving it offline for some time.
Post #1004971
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse