December 13, 2012 at 6:01 am
When AutoShrink will occur in SQL server 2008? When autoshrink set true...
But in SQL server 2000, we can schedule the auto shrink .. In the same Manner is it possible to schedule autoshrink in sql server 2008?
Please Suggest!!!
Saranya
December 13, 2012 at 6:32 am
Do you have a good reason to use AutoShrink? Because it will mess up the fragmentation of your indexes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2012 at 8:08 am
Autoshrink will shrink the database when there's 25% space available, per http://msdn.microsoft.com/en-us/library/bb522682.aspx.
But it's almost always a really, really, really bad idea to turn on autoshrink.
You can also schedule a database/file shrink to run at a regular interval through an SQL Agent job. Very easy to do. Details here: http://msdn.microsoft.com/en-us/library/ms189080.aspx
Again, this is a really bad idea and I recommend that you not do it.
Shrinking a database should be done only when it absolutely has to. It has significant impact on performance issues (index fragmentation), file level issues (physical fragmentation), can have a negative effect on backup/recovery plans, and a few other issues.
I can't stress too much that automatically shrinking a database, either through a scheduled job or through autoshrink, is really, really bad.
- 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
December 13, 2012 at 9:49 pm
Its not Recommended only .. However One DB size is 800GB, but free space available is 400GB.
When started shrinking the free space its taking more than 2-3Days . After 2-3 days we are shrinking upto 5-10GB Space alone... There is no User transaction to DB like select or Insert...
How i Can recover Free space?? Highly Suggest me!!
December 13, 2012 at 11:28 pm
saranya.priyadharshini (12/13/2012)
How i Can recover Free space?? Highly Suggest me!!
DBCC ShrinkFile. There's no other way (other than copying the data to a new DB and dropping the old one)
Let me guess, lots of LOB data? That makes shrink very slow.
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
December 13, 2012 at 11:57 pm
GilaMonster (12/13/2012)
saranya.priyadharshini (12/13/2012)
How i Can recover Free space?? Highly Suggest me!!DBCC ShrinkFile. There's no other way (other than copying the data to a new DB and dropping the old one)
Let me guess, lots of LOB data? That makes shrink very slow.
or moving the table(or rebuiding clustered index) to the new filegroup ?
-----------------------------------------------------------------------------
संकेत कोकणे
December 14, 2012 at 12:50 am
Let's i will try ...
December 14, 2012 at 3:09 am
sanket kokane (12/13/2012)
GilaMonster (12/13/2012)
saranya.priyadharshini (12/13/2012)
How i Can recover Free space?? Highly Suggest me!!DBCC ShrinkFile. There's no other way (other than copying the data to a new DB and dropping the old one)
Let me guess, lots of LOB data? That makes shrink very slow.
or moving the table(or rebuiding clustered index) to the new filegroup ?
And then? Shrink the old filegroup? Will still have the problem with lob pages making the shrink slow.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply