?
Solved

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

Posted on 2006-05-04
7
Medium Priority
?
389 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:PJan8724
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 16606280
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
 
LVL 143

Expert Comment

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

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 16606295
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Assisted Solution

by:Saqib Khan
Saqib Khan earned 400 total points
ID: 16606302
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16606303
Only normal select operations will take place
0
 
LVL 1

Author Comment

by:PJan8724
ID: 16607263
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
 
LVL 1

Author Comment

by:PJan8724
ID: 16607288
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

864 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