Transaction Log Growth

Question

Post reply

Transaction Log Growth

arrjay

Hall of Fame

Points: 3455
More actions
October 8, 2014 at 3:38 am

#312046

Hi All - I have recently set up an alert which I want to test but not sure how. I have an alert on if the transaction log grows and runs out of disk space an email will be fired.
What I'm after is a query which will cause the t-log to grow and take up all the disk space. Any ideas?
(this is pre-production so no impact to live)
Thanks.
--------------------------------------------
Laughing in the face of contention...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Try updating every row in your largest table. Rinse and repeat until your transaction log is full.

John

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks for this but I don't have much data in my test db.

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

Laughing in the face of contention...

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 3

If you don't have much data,

first go to the options and restrict the log size for the db to a small value.

and then try doing delete operation within a transaction, which causes log to grows fast.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Create a WHILE loop without an exit and keep updating a row over and over. It won't be fast, but you'll see the transaction log grow. Make sure you use different values each time, maybe the counter for which loop of the WHILE you're in.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Super Cat SSCertifiable Points: 7332 More actions · Answer 5

How are you managing the T-logs?

If this happens frequently then I would increase the disk drive.

Otherwise maybe instead of sending a Mail.

It could trigger off

1 backup the DB

2 setting the db to SImple

3 Shrink log file

4 Set db to Full mode

5 Backup the db again

6 Send mail to say this has happened.

If this happens overnight this should solve any potential outage.

You can always investigate in the morning.

T-logs backups should now be running.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

What exactly are you trying to monitor, the amount of space the log is taking up or the amount of free space on that drive?

arrjay Hall of Fame Points: 3455 More actions · Answer 7

Hi All - Thanks for your feedback. I ended up creating a table for over 2 billlion rows and preformed a number of updates on it. (I thought there could be a recursive loop that I would perform)

I was basically testing our monitoring to ensure I would be alerted if the disk ran out of space... and it worked.

Thanks all.

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

Laughing in the face of contention...