<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 2 of 2
««
1
2
Faster way to release the Unused Space back to Disk
Rate Topic
Display Mode
Topic Options
Author
Message
dan-572483
dan-572483
Posted Tuesday, December 18, 2012 9:50 AM
Old Hand
Group: General Forum Members
Last Login: Friday, June 14, 2013 3:11 PM
Points: 338,
Visits: 993
This is the second time? If you shrink a database at all, it should be one-off job following a one-off large data deletion. If it grows "too large" again, then the DB needs extra space for its operations.
One thing to look at is if the autogrow settings are too large. If you have a 5GB DB set to grow 5GB at a time and it results in 48% free space, then you might want to shrink again (to a size that that includes a more reasonable amount of free space), then set the autogrowth smaller to a setting that allows for growth but makes growths unlikely to happen very often.
Post #1397892
Mac1986
Mac1986
Posted Friday, December 21, 2012 1:29 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:07 AM
Points: 180,
Visits: 516
I have done the things that you have suggested but still in the next ETL run, again the Datafile size is boosted up and used space by the file is way less.
Can you please suggest. What can be done now..
Post #1399257
crazy4sql
crazy4sql
Posted Friday, December 21, 2012 1:33 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 876,
Visits: 3,742
why dont you change your recovery model to "bulk logged" while your ETLs are scheduled?
----------
Ashish
Post #1399260
GilaMonster
GilaMonster
Posted Friday, December 21, 2012 1:34 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
Mac1986 (12/21/2012)
I have done the things that you have suggested but still in the next ETL run, again the Datafile size is boosted up and used space by the file is way less.
Can you please suggest. What can be done now..
If the space in the data file is used up and the file grows during ETL, then leave the data file alone, it needs to be the size it is.
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 #1399262
GilaMonster
GilaMonster
Posted Friday, December 21, 2012 1:35 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
crazy4sql (12/21/2012)
why dont you change your recovery model to "bulk logged" while your ETLs are scheduled?
Not going to affect the size or usage of the data file in any 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 #1399263
crazy4sql
crazy4sql
Posted Friday, December 21, 2012 1:37 AM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 876,
Visits: 3,742
Not going to affect the size or usage of the data file in any way.
sorry for my Friday hangover, I thought the complain is about log file :)
----------
Ashish
Post #1399266
Mac1986
Mac1986
Posted Wednesday, January 02, 2013 3:53 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 4:07 AM
Points: 180,
Visits: 516
I have 2 quick questions on Data file size usage behavior
1)
I see that Indexes have heavy fragmentation levels:
This effects the DML Performance for sure, but will this allow the data file size to grow extra than actual consumed space?
2)
Will adding few indexes at appropriate places
reduce the Data file to not pre capture so much disk space than actual consumed space.?
Post #1402112
Bhuvnesh
Bhuvnesh
Posted Thursday, January 03, 2013 2:54 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562,
Visits: 3,453
Mac1986 (1/2/2013)
1)
I see that Indexes have heavy fragmentation levels:
This effects the DML Performance for sure, but will this allow the data file size to grow extra than actual consumed space?
NO there is no 'extra' or 'actual consumed' kind of thing in index fragmentation level. framentation happens due to page split whch is dependent on fill factor.
Mac1986 (1/2/2013)
(2)
Will adding few indexes at appropriate places
reduce the Data file to not pre capture so much disk space than actual consumed space.?
NO. index addition always cost you space/disk and it helps you to retrieve the data faster but slow down your DML operation
for more details see this link
http://blogs.msdn.com/b/pamitt/archive/2010/12/23/notes-sql-server-index-fragmentation-types-and-solutions.aspx
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1402259
« Prev Topic
|
Next Topic »
18 posts, Page 2 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.
