<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Faster way to release the Unused Space back...
18 posts, Page 1 of 2
1
2
»»
Faster way to release the Unused Space back to Disk
Rate Topic
Display Mode
Topic Options
Author
Message
Mac1986
Mac1986
Posted Tuesday, December 18, 2012 2:08 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180,
Visits: 512
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
anthony.green
anthony.green
Posted Tuesday, December 18, 2012 2:16 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 2:19 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
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
crazy4sql
crazy4sql
Posted Tuesday, December 18, 2012 2:23 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876,
Visits: 3,731
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
Mac1986
Mac1986
Posted Tuesday, December 18, 2012 2:24 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180,
Visits: 512
Its the LOBs we have a huge column on few tables, is there a better way to maintain LOBs?
Post #1397618
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 2:30 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
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
Mac1986
Mac1986
Posted Tuesday, December 18, 2012 2:32 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180,
Visits: 512
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
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 2:40 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
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
Mac1986
Mac1986
Posted Tuesday, December 18, 2012 2:53 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180,
Visits: 512
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
GilaMonster
GilaMonster
Posted Tuesday, December 18, 2012 3:17 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
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 »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
