Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is AutoShrink Logged ? Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 10:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:05 PM
Points: 2,834, Visits: 8,544
I have a database that suddenly had a huge transaction log , too big for the free space on disk drive. I know there was a good amount of free space in the database itself. I found that AutoShrink is set to "true" for some reason, and I suspect it ran and created the huge t-log ?

I can see that the last t-log backup ran yesterday at 4:00 pm, then it failed every 15 minutes afterwards due to low disk space until I made room for it , when it created a 7 Gig backup file.

I have the default trace, and my own server side trace, but don't see what caused such a big transaction log backup file shortly after 4:00 pm.

I don't know why autoshrink is on .... I am pretty new at this particular position.



Post #1437991
Posted Tuesday, April 2, 2013 10:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
All operations that change the database in any way are logged, no exceptions.


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 #1437992
Posted Tuesday, April 2, 2013 10:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 5,886, Visits: 13,047
all shrink events appear in the default trace and you can see them listed under database - right click - reports - standard reports - disk usage.

Reindex jobs are the most frequent causes of large tran log usage.


---------------------------------------------------------------------

Post #1438000
Posted Tuesday, April 2, 2013 10:36 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 5,886, Visits: 13,047
and if this is production I would turn autoshrink off. when you look at the report it will be obvious why from the constant shrinks and re-growths I expect you will find listed there.


EDIT - and i think i just totally misinterpreted your question! but anyway if the shrink happened around that time, the event will have been recorded.


---------------------------------------------------------------------

Post #1438002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse