Auto Shrink 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 » Auto Shrink In SQL Server 2008

11 posts, Page 1 of 2

1 2 »»

Auto Shrink In SQL Server 2008

Rate Topic

Display Mode

Topic Options

Author

Message

shohelr2003

Posted Monday, November 05, 2012 4:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:32 AM
Points: 124, Visits: 324

Dear,

1. I like to auto shrink my database. But I dont know how to do it.

2. My current DB size is 2 GB. I want that if my database increases by 20%, it will shrink the database.

If you have better suggestion than my scenario, suggest me.

Please help me. Thank you for your time.

Regards,
Akbar

Post #1380979

anthony.green

Posted Monday, November 05, 2012 4:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Better scenario is do not shrink the database at all, it causes a whole heap of potential problems and fixing them will only make the database bigger again, so you in a catch 22 situation.

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

shohelr2003

Posted Monday, November 05, 2012 5:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:32 AM
Points: 124, Visits: 324

please suggest me, what can I do. Which approach should I practice?

Post #1380998

anthony.green

Posted Monday, November 05, 2012 5:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

What is your reasoning behind wanting to shrink the database?

The approach I would take it to leave it as is and do not shrink unless there is a valid business reason.

Post #1381000

GilaMonster

Posted Monday, November 05, 2012 8:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 37,726, Visits: 29,987

Don't regularly shrink your database. This is not MS Access that required a compact and shrink often. shrink is not compression. Free space in a SQL database is not harmful.
A data growing means that you're adding data to it, shrinking in that case is not going to reduce space used and is just going to slow things down.

Do not autoshrink. Do not shrink regularly.

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

IgorMi

Posted Monday, November 05, 2012 8:30 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 1,879, Visits: 1,452

shohelr2003 (11/5/2012)

Hi,
2GB or 2TB. If 2GB then I'll send you a flash memory of 32GB and your problem is resolved :), I'm kidding, I suppose your database has grown up to 2TB.
So a data compression approach can help you here. If your system is OLAP then it will help you much on reducing the storage space if you simply apply a PAGE-type of compression for all tables. If you have OLTP system, then do the data compression carefully. The following link can help you a lot: http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
With OLTP systems combining PAGE and ROW compression for tables can be more useful rather than applying only one type.
If you have big tables, then make a deeper analysis: see the indexes scans and updates and then
decide how to compress them, and do it manually if you have big tables. Data compression affects the index rebuild.
Also consider some other aspects from the paper...

Regards
IgorMi

Post #1381121

shohelr2003

Posted Monday, November 05, 2012 9:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:32 AM
Points: 124, Visits: 324

Thank you for your kind help.

Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.

Pls suggest me.

Post #1381357

shohelr2003

Posted Monday, November 05, 2012 9:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:32 AM
Points: 124, Visits: 324

thank you for your kind information.

Post #1381358

GilaMonster

Posted Monday, November 05, 2012 10:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 37,726, Visits: 29,987

shohelr2003 (11/5/2012)

Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.

A large transaction log will not cause performance problems. Free space in the data file will not cause performance problems.

I suggest you investigate the actual cause of the performance problems and leave the log alone.

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

shohelr2003

Posted Tuesday, November 06, 2012 12:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:32 AM
Points: 124, Visits: 324

GilaMonster (11/5/2012)

shohelr2003 (11/5/2012)

Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.

thank you for your suggestion.

Post #1381403

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions