May 3, 2011 at 1:37 am
Hi,
There was a sudden increase in the size of the production database. The database size grew by around 1GB & the transaction log file size also grew by around 1.3 GB. Under normal circumstances the database grows at the rate of 10-20 MB per day. I did check the size of tables & the indexes, there was not much change. We make a backup of transaction logs every hour & i came to notice that between size of the backup file at 4PM was 1.38 GB whereas under normal circumstances size of backup file ranges from 1MB-16MB.
How can I come to know what transaction has caused the sudden increase in the database size.
regards,
Nithin
May 3, 2011 at 2:03 am
Looping
Thanks
May 3, 2011 at 2:25 am
Did an index rebuild job run around that time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2011 at 2:50 am
Nope. Did not rebuild the index yesterday.
May 3, 2011 at 3:17 am
to get an overall idea.
what is the overal size of the database and transaction log?
what are the autogrow values of the files?
what is the free space of the database and the transaction log?
May 3, 2011 at 3:47 am
Database Size:10,417 MB Space Used:10238 MB
Log File Size: 1,524 MB Space Used: 22 MB
Auto Growth for Data File:By 10 percent, unrestricted growth
Auto Growth for Log File: By 10 percent, unrestricted growth
Data File Free Space: 177 MB
Log File Free Space:1501MB
May 3, 2011 at 3:56 am
Can u give sql server Log and Transaction Log of that time...
Thanks
May 3, 2011 at 4:16 am
Date,Source,Severity,Message
05/02/2011 23:00:10,Backup,Unknown,Log was backed up. Database: This is an informational message only. No user action is required.
May 3, 2011 at 4:34 am
any operation performed that was not a part of daily routine
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 3, 2011 at 4:44 am
No operations were performed. All jobs are scheduled towards the End Of Day.
May 3, 2011 at 4:50 am
Some operation made your log grow until you filled the disk.
Unless you were running a trace at that time there is no way to know what made it grow.
The log full generated an error though, so the culprit most likely got an error.
Since this is an exceptional growth you just have to find who/what did something out of the ordinary.
While we are at it, change the 10% growth to a fixed amount in MB, that'll keep you from having bad surprise.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply