• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Frequency of AutoShrink

Hi,

I have AUTOGROW and AUTOSHRINK db settings on . can anybody confirm how often AUTOSHRINK will run, Ive read that SQL will run SHRINK evrey 30 mins or so. I have had sql trace running since morning and no shrinks have take place on the DB although there is 1708MB still available.
If some one could shed some light on  this I would appreciate it.
Thanks
0
anwarmir
Asked:
anwarmir
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
regbesCommented:
Hi anwarmir,

do not use auto shring as it is a performance killer and if you r DB once needed the space chances are it will need it again

HTH

R.
0
 
imran_fastCommented:
AUTO_SHRINK only reduces the size of the transaction log if the database is set to SIMPLE recovery model or if the log is backed up.

The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with the above comments: do NOT enable auto-shrink. it is simply a performance killer during those I/O operations.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anwarmirAuthor Commented:
Hi imran_Fast

1)  are you saying Autoshrink does not shrink the data file when in Simple recover Model only the Log file. My understanding was I am usiing the SImple recovery Model the Log file would not grow???

2) So currently my transaction Log file is 203MB the Suto shrink will only run when this file is 75% full.


3) If point 2 is correct can I check the space used by the log file.

Thanks
0
 
imran_fastCommented:
1. No, the statement was only for log file.

2. yes

3. check DBCC SQLPERF in books online
0
 
anwarmirAuthor Commented:
Thanks Imran_Fast...Just need some clarification on point 1. Why is the Transaction log growing in Simple recovery Model...I am getting confused.
0
 
anwarmirAuthor Commented:
Is there any case where we would need to shrink the data file manually?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is there any case where we would need to shrink the data file manually?
there can be reasons to do so.
for example, you have moved a big table /index from a filegroup to another. hence, the old filegroup/files will be quite empty, and might stay quite empty, you can proceed to shrink a data file manually.

>1. Why is the Transaction log growing in Simple recovery Model...I am getting confused.
transaction log will not grow in simple recovery mode, except to a "minimum" size that handles the biggest transaction.
0
 
anwarmirAuthor Commented:
Thanks angeIII

>transaction log will not grow in simple recovery mode, except to a "minimum" size that handles the biggest transaction.
is the minimum size 512KB by any chance..If the min size is determined by internal requirements why would autohrink be detremental to performance
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>is the minimum size 512KB by any chance..
no. it can be "unlimited" if the transaction never completes (commit/rollback)

>If the min size is determined by internal requirements why would autohrink be detremental to performance
as it's the transaction size (user) that determines the size, it's not a internal requirement.

>why would autohrink be detremental to performance
as the "minimum" size varies from transaction to transaction, trying to shrink the fill all the time will make it grow all the time again.
simply a waste of I/O operations...

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now