Solved

Frequency of AutoShrink

Posted on 2006-11-07
10
285 Views
Last Modified: 2007-12-19
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
Comment
Question by:anwarmir
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17889148
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 17889154
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17889197
I agree with the above comments: do NOT enable auto-shrink. it is simply a performance killer during those I/O operations.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:anwarmir
ID: 17889718
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
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 250 total points
ID: 17889784
1. No, the statement was only for log file.

2. yes

3. check DBCC SQLPERF in books online
0
 

Author Comment

by:anwarmir
ID: 17889896
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
 

Author Comment

by:anwarmir
ID: 17889922
Is there any case where we would need to shrink the data file manually?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17889965
>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
 

Author Comment

by:anwarmir
ID: 17890610
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17929265
>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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 28
SQL Query 2 34
MSSQL - Lock Row from reading by other programs 9 40
Parse this column 6 27
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question