When AutoShrink will occur in SQL server 2008?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » When AutoShrink will occur in SQL server...

When AutoShrink will occur in SQL server 2008?

Rate Topic

Display Mode

Topic Options

Author

Message

saran.dharsh

Posted Thursday, December 13, 2012 6:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 25, Visits: 348

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

Post #1396146

Koen Verbeeck

Posted Thursday, December 13, 2012 6:32 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

Do you have a good reason to use AutoShrink? Because it will mess up the fragmentation of your indexes.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1396159

GSquared

Posted Thursday, December 13, 2012 8:08 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1396210

saran.dharsh

Posted Thursday, December 13, 2012 9:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 25, Visits: 348

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!!

Post #1396473

GilaMonster

Posted Thursday, December 13, 2012 11:28 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639, Visits: 29,893

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 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 #1396489

sanket kokane

Posted Thursday, December 13, 2012 11:57 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

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 ?

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1396496

saran.dharsh

Posted Friday, December 14, 2012 12:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 25, Visits: 348

Let's i will try ...

Post #1396509

GilaMonster

Posted Friday, December 14, 2012 3:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639, Visits: 29,893

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 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 #1396538

« Prev Topic | Next Topic »

Permissions