SQL Transaction log drive out of space worst case senerio....

Hey Guys,

Just doing some research on worst case senerio for a question that my boss asked of me.  I have a SQL db that gets hit hard every once in a while and the transcation log grows rapidly.  I've got a script that shrinks the log three times a week which I might increase in frequency.  The problem is that the drive the trans log is on, is relative small (10GB), Although I'm getting more space for this drive in a couple of months once we bring our new SAN online.  Until then I'm stuck with what I have for space at the moment (As a side note, the DB and trans logs are on different drives).

So, in a worst case senerio, what would happen to the SQL DB if the transaction log grows and fills the drive completely?  In other words, the trans log grew to full drive capacity and ran out of space and couldn't grow anymore but the server was still getting hit with transactions and know was around to mannually shrink the log in time.  For the sake of argument, lets saying drive space alerting failed to notifiy people or no one could respond fast enough to shrink the log.

Is the DB going to crash hard or will SQL just stop processing requests until the log is shrunk?

Thanks,

Pete J.
LVL 1
PJan8724Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
actually, if you implement regular full and transaction log backups, the transaction log will no longer grow constantly.
if you think you don't need a regular transaction log backup (usually several times a day), switch to full backups only and change the database mode to simple recovery, this will also keep the transaction log from growing constantly
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
regarding the q, any transaction will fail when the transaction log cannot grow.
you can still read as long as you want, but no update/delete/insert
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
PJan8724,
> So, in a worst case senerio, what would happen to the SQL DB if the
> transaction log grows and fills the drive completely?

No operations will take place in the sql server, it will display a message like Log full
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Saqib KhanConnect With a Mentor Senior DeveloperCommented:
I think SQL Server will slow down and will answer to client Requests with unbelieveably Slow Speed.

What is the Recovery Mode in your SQL Server?

are you doing backups on Daily Bases? if yes then it should backup and truncate your LOG automatically everynight or so when you do back up, look into Maintaince Plan of SQL Server to schedule Back ups and Log.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Only normal select operations will take place
0
 
PJan8724Author Commented:
Hey guys,

Okay....  So the DB vendor supported because of the way my company does things.  I'm just in charge of making sure that the database is backed up, pretty droll I know.  However the DB is set up for full recovery model, per the vendor when they initially set up the db.  The maint plan backs up the log at 12am everynight and the database shortly there after, but a lot of processing seems to happen after the log and db is backed up because of night time operations hence the reason for the shrink script I have put in place.  

We're not using point in time, so I swithing to a simple recovery model would probably work.  Looks like I'm going to have to contact the vendor to find out what they suggest on this.

But its pretty much what I thought that DB won't crash or become corrupt, it will just stop processing updates\deletes\inserts but will still respond to reads albeit slowly at best.

Just out curiousity, what a good drive size for the transaction log on a 30GB DB that is expected to grow too 300GB over time.

0
 
PJan8724Author Commented:
Thanks for all the help guys,

Sorry about the goofy point splitt but everyone here answered my questions and I felt everyone deserved points for it.

Thanks again

Pete J.
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.

All Courses

From novice to tech pro — start learning today.