SQL Server Performance Forum – Threads Archive

How should I shrink if autoshrink is off

I have read that I shouldn’t be using AutoShrink on my production databases. If I turn this option off, how (and how often) should I shrink the databases? Thanks!

You can shrink the DB or you can shrink files (data or log file) seperatly . Check BOL for DBCC SHRINKDB and DBCC SHRINKFILE . –Rajiv

Is your recovery mode FULL or SIMPLE? Are you also already doing periodic log backups? On my production server we do log backups throughout the day on a set schedule, and each night after our nightly backup, the job also does the shrink for the log and for the data.

After confirm Druer suggestion, read DBCC shrinkdb in Books on Line, or use search to find out similars questions in this forum. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Do you have space issues on the hard disk?
If so then you must re-order and archive old files that are not required, in general it is better to assess the size for a period of time and set it to the database.http://www.databasejournal.com/features/mssql/article.php/3339681 for your information. On our production servers we have disabled the auto-grow and we do have monitoring services in place to alert when the database is nearing to 85% of its size. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for all the feedback so far. Our recovery model is SIMPLE and we do a full database backup once daily and after any significant changes are made by our internal guys. We import alot of records into our databases each day and we often have 20 or more production databases per server (all with similar record / work loads) so it could eat up the space in a hurry depending on the data that is coming in. I currently don’t do any log (only) backups.

So, even if you shrink in short you will have few disk space. May be is time to buy more disks. With simple configuration sql use lest space than full.
And, if you run defrag, etc., that grow the database. So I think you need more space or you will loose performance not running defrag.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I believe the problem could be that you are not performing Tlog backups and Log is increasing rapidly when any transactions or bulk insert or optimization jobs are scheduled on the databases.
If you use SIMPLE recovery model also you must assign as size to Tlog in order to avoid any auto-grow in unusual manner. I agree with Luis to add more physical disks and memory to catchup the performance, also think about Archiving the data on the databases for space concerns. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I don’t think shrinking db or tran log is a good idea. Read the links I posted in another thread. Loading a lot of data in one transaction produces huge amount of log and neither frequent tran log backups nor simple recovery moded helps, because long running transactions are not free to be backed-up and log space released for reuse untill transaction is completed (comitted or rolled-back). In this case, solution is to split your data load in smaller batches. Then you will have shorter running transaction and their space will be availabe for reuse after each checkpoint (expect for current not so big transaction).

Here are the links:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894&SearchTerms=log
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018&whichpage=1

Also check:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10095&whichpage=2&SearchTerms=long,running,transaction

]]>