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 12»»

Faster way to release the Unused Space back to Disk Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 2:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
Hi,
I have few databases that have 8 data files allocated with 300 GB each and used only 110 - 120 GB each. So, I wanted to release the unused space back to the disk so that other databases can use the space.

I'm using DBCC Shrinkfile option and its taking 7-8 hours per data file.
Is there any other faster way to release the space back to the disk?

Please suggest
Post #1397610
Posted Tuesday, December 18, 2012 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
Shrink in small chunks, but then also remember to perform index and statistic maintenance as index fragmentation will be shot to pieces.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1397613
Posted Tuesday, December 18, 2012 2:19 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 @ 8:37 AM
Points: 40,596, Visits: 37,053
DBCC Shrinkfile is the only way to release unused space back to the OS.

Let me guess, either lots of heaps or lots of LOB columns?



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 #1397615
Posted Tuesday, December 18, 2012 2:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 882, Visits: 4,144
just modify the initial space and auto-growth option.

If you right click on your database and select properties, then go to files. You will see the initial size details of your data-file. Just modify this by leaving "actual+20-30GB space" in the initial size of your data or log file. Do it one by one, not all file at same time (ideal time is when full backup is finished).

Then, change the auto-growth in MB instead of %. I will advice to review your database growth and then give sufficient amount of mb in auto-growth as well. Because auto-growth also cause the IO and sometime impact performance when luck is not favoring you. So if you see too much auto-growth then configure with sufficient amount of space (1 gb or 2gb)


----------
Ashish
Post #1397617
Posted Tuesday, December 18, 2012 2:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
Its the LOBs we have a huge column on few tables, is there a better way to maintain LOBs?
Post #1397618
Posted Tuesday, December 18, 2012 2:30 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 @ 8:37 AM
Points: 40,596, Visits: 37,053
No, it's just that LOB columns massively slow down shrinks because of the way they pages link together.


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 #1397620
Posted Tuesday, December 18, 2012 2:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
I mean this is 2nd time I'm doing this. Its pain to check on the Shrinkfile SPIDS if there are any blockings or something. It takes 6-7 hours for 1 data file and I have 8 files to go on 1 database. I have 6 databases on different servers like that in same condition.

In your experience, how can I identify to maintain these huge databases that gets fragmented too often.
Post #1397621
Posted Tuesday, December 18, 2012 2:40 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 @ 8:37 AM
Points: 40,596, Visits: 37,053
Why are you ending up with so much free space that's not going to be reused?


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 #1397626
Posted Tuesday, December 18, 2012 2:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
Actually we took extra space while deploying the databases and implementing the application. And now we need that space for new deployments in that server. So, long story short we need the space which is not used.

Also, what is size that can be on a general note can be determined as LOB? Can you possibly give an approx number?

what are the preferred Database settings for these huge Databases.
Post #1397630
Posted Tuesday, December 18, 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: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Mac1986 (12/18/2012)
Also, what is size that can be on a general note can be determined as LOB? Can you possibly give an approx number?


Huh?
LOB = Large Object. Nvarchar(max), Varchar(max), varbinary(max), XML data types.

what are the preferred Database settings for these huge Databases.


Defaults unless you have a good reason to change work most of the time.



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 #1397644
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse