Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 autoshrink on is a good pracitce?

Author  Topic 

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2008-07-22 : 03:24:09
Dear AlL,
autoshrink on is a good pracitce on a 24x7 envorinment? as our environment is a big environment and only inbetween 2:00am to 3:00 am is the non peak hours.
so is it a good practice? and please suggest me the possible databasepropertyex which are good to set to on and which are good to set off.
thankyou very much. the link is provided by Kh and thanks a lot KH
http://msdn.microsoft.com/en-us/library/ms186823.aspx

Arnav
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-07-22 : 03:26:38
NO!
There is a reason for the DB to grow. Never let it autoshrink on a production database.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2008-07-22 : 05:01:38
See -
[url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]
[url]http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx[/url]
[url]http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx[/url]

And don't shrink it manually either.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2008-07-22 : 05:06:40
In addition...
Arnav, please, please see if you can get yourself some SQL admin training or get a good admin book and read it, before you accidentally break something that you cannot fix.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

PietG
Starting Member

1 Post
Posted - 2009-01-19 : 13:19:02
quote:
Originally posted by GilaMonster

See -
[url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]
[url]http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx[/url]
[url]http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx[/url]

And don't shrink it manually either.

--
Gail Shaw
SQL Server MVP

Gail,

Our DB is set to recovery mode of FULL and the log file grew bigger than the DB i.e. 32GB log and 30GB DB. I made a transaction log backup and the shrinked the DB. The DB is now 28GB and the LOG FILE only 109MB. How else can I control the size of the logfile when my recovery mode is set to FULL ?

Piet Grobler (You used to work for me)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-19 : 13:41:36
quote:
Originally posted by PietG

quote:
Originally posted by GilaMonster

See -
[url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]
[url]http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx[/url]
[url]http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx[/url]

And don't shrink it manually either.

--
Gail Shaw
SQL Server MVP

Gail,

Our DB is set to recovery mode of FULL and the log file grew bigger than the DB i.e. 32GB log and 30GB DB. I made a transaction log backup and the shrinked the DB. The DB is now 28GB and the LOG FILE only 109MB. How else can I control the size of the logfile when my recovery mode is set to FULL ?

Piet Grobler (You used to work for me)


Backup your transaction log on a frequent basis! We backup ours every 15 minutes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

revdnrdy
Posting Yak Master

220 Posts
Posted - 2009-01-19 : 13:41:59
Hello;

You can schedule more frequent backups to get a handle on how big your logfile grows.

By default, log files are designed to grow in size forever, until you perform a log backup. However the log backup operation itself does not physically reduce the size of the log file... instead it just "frees up" the parts of the log that's not currently in use.

After a backup, when transactions reach the physical end of the log file, they will "wrap around" to the beginning of the file to use that free space, instead of increasing the size of the file.

r&r
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-19 : 13:42:12
I don't even use autoshrink in a test environment. I never use autoshrink.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts
Posted - 2009-01-19 : 14:09:22
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-01-19 : 18:46:21
quote:
Originally posted by PietG


How else can I control the size of the logfile when my recovery mode is set to FULL ?


Back it up regularly. Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

quote:
Piet Grobler (You used to work for me)

FNB? So many people over many years, I forget names.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site