February 16, 2010 at 2:48 pm
Is it possible to shrink as part of a database maintenance plan?
February 16, 2010 at 2:52 pm
I am not going to say whether or not it is - I am just going to say that you should not even consider it. Shrinking database files will cause performance problems - and is not recommended as a normal scheduled option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 16, 2010 at 3:11 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2010 at 4:00 pm
Possible? Yes. Advisable? Almost never.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2010 at 12:47 pm
I work with a database that often shows up to 50% free space, shrinking releases space but thanks for the replies!
February 17, 2010 at 1:55 pm
Ser Tharg (2/17/2010)
I work with a database that often shows up to 50% free space, shrinking releases space but thanks for the replies!
Okay - releases space to be used by what? If the database files just grow again - what have you gained by shrinking other than slowing your system down? What happens when your database needs to grow again, but something else has taken it?
Just remember, every time you shrink your data files - you have to rebuild ALL of your indexes because the shrink operation just fragmented all of your indexes. Rebuilding your indexes will cause the database to grow (if you shrink it too far).
Oh well...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2010 at 2:02 pm
Just keep in mind extending a file is an expensive operation for your system.
Depending on your db usage, 50% free space may be desired state because of processing needs.
How many GB are you talking about ?
How many rows fit into a single page ?
Are you using uniqueidentifiers for clustered key column ?
Is that log file free space ?
or
is it data file free space ?
In your system under disk constraints and pressure ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 17, 2010 at 3:27 pm
Here is a reply from Paul Randal (at the time the Dev Lead, Microsoft SQL Server Storage Engine) about why is shrinking a database not a good idea.. and I quote
Don't get me started on this. There's a good reason the database had to grow its files. If you're managing your database competently, you should only ever shrink if you're making a database read-only or have deleted a large amount of info that you won't need the space again.
Basically, shrink causes fragmentation. Don't do it.
Quoted from this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53085&whichpage=2
February 17, 2010 at 3:40 pm
Lamprey13 (2/17/2010)
Don't get me started on this. There's a good reason the database had to grow its files. If you're managing your database competently, you should only ever shrink if you're making a database read-only or have deleted a large amount of info that you won't need the space again.
Basically, shrink causes fragmentation. Don't do it.
Quoted from this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53085&whichpage=2%5B/quote%5D
And I would disagree with shrinking a database if you are setting it to read only. Why would you fragment the indexes then set the database read only so you can no longer fix the fragmentation?
There are reasons to shrink database files, there just is no good reason to schedule it on a regular basis. As Paul mentioned - if you have deleted a large amount of information and you do not expect to need that space again. If you are creating a test/dev system from a copy of live - you might end up purging a lot of data that isn't needed in those environments.
However, I see people schedule a shrink after copying their database file production every day. That just doesn't make sense, because tomorrow you need that space available for the restore - so why shrink? I've seen some people schedule a shrink of the transaction log - again, the transaction log is going to grow again, so why shrink?
And finally, best practice is to dedicate storage to your database. Storage specifically designed and configured for database file and transaction logs. Why would you try to free up space to the OS when that storage is supposed to be dedicated to your databases? It just does not make any sense to do that...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2010 at 4:10 pm
Jeffrey Williams-493691 (2/17/2010)
And I would disagree with shrinking a database if you are setting it to read only. Why would you fragment the indexes then set the database read only so you can no longer fix the fragmentation?<snip>
I would agree with you, but I don't think the intent would be to shrink the db, set it to read-only and then walk away. I guess I took it for granted that one would shrink the db and then do what makes sense (aka rebuild indexes, statistics, etc) before making it read-only.
February 17, 2010 at 4:16 pm
Lamprey13 (2/17/2010)
Jeffrey Williams-493691 (2/17/2010)
And I would disagree with shrinking a database if you are setting it to read only. Why would you fragment the indexes then set the database read only so you can no longer fix the fragmentation?<snip>
I would agree with you, but I don't think the intent would be to shrink the db, set it to read-only and then walk away. I guess I took it for granted that one would shrink the db and then do what makes sense (aka rebuild indexes, statistics, etc) before making it read-only.
Okay, I can see performing a shrink in that scenario. However, it really would depend on how often you are going to perform that process. If you are refreshing the database daily - what space are you saving? You still need that space available tomorrow when you restore again...
If you are doing something like that once a month - maybe. But again, aren't you going to need that storage available the next time you restore? In fact, won't you need more storage available the next time because your production system has grown?
However, if it is a one time operation - say for an archive database that will never be refreshed, then yeah I can see doing this. But, would you schedule it on a regular basis as part of a maintenance plan? Nope...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 18, 2010 at 12:47 am
Lamprey13 (2/17/2010)
Jeffrey Williams-493691 (2/17/2010)
And I would disagree with shrinking a database if you are setting it to read only. Why would you fragment the indexes then set the database read only so you can no longer fix the fragmentation?<snip>
I would agree with you, but I don't think the intent would be to shrink the db, set it to read-only and then walk away. I guess I took it for granted that one would shrink the db and then do what makes sense (aka rebuild indexes, statistics, etc) before making it read-only.
That's exactly how we publish out biztalk archives.
We only keep 14 weeks worth of data online, but publish a copy every 3 months on an archive server that only serves this purpose.
We restore the prod database, shrink it, then rebuild all indexes, grant the needed auth. and put it in read_only mode.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 18, 2010 at 1:07 am
I read many articles and in forums that often shrinking the DB is not a good practise...and I agree to it considering the impact DB may have in long run....
Somebody also suggested that its better to have dedicated disk (I consider it to be SAN) for data and log files..
But...what would be better solution to gain space again if one dont have dedicated disk? and the local disk is almost full and we badly need a space inorder to DB function properly?
I have one Instance where tempdb grows to approx 20-25 GB, the operations being performed at that time are reindexing, update stats etc..
What could be the best solution to avoid shrink of tempdb in above scenario?
How much will be impact on instance if tempdb is shrinked atleast twice in a week in above case?
Any comments/suggestions to avoid tempdb shrink are most welcome.
PS: Although I am not giving any suggestion to OP and asking my own questions in this post...I thought so because its releated to OPs case.
February 18, 2010 at 1:20 am
jshailendra (2/18/2010)
PS: Although I am not giving any suggestion to OP and asking my own questions in this post...I thought so because its releated to OPs case.
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2010 at 1:27 am
I thought so :-)..someone will ask me to post in new post.
Gail..please respond to below post..Thanks in advance
http://www.sqlservercentral.com/Forums/Topic867792-1550-1.aspx
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply