SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log file R drive- What is consuming it


Log file R drive- What is consuming it

Author
Message
coolchaitu
coolchaitu
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 1520

Good Morning Experts.

We have a SQL Server instance. All the user databases log files are on R drive. We have been getting alerts that R drive is 99% full, 100% full. I am interested to know what is consuming the R drive. Could you please advise. Any script to check what is consuming the R drive?


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816273 Visits: 46258
coolchaitu - Thursday, November 16, 2017 10:01 PM

Good Morning Experts.

We have a SQL Server instance. All the user databases log files are on R drive. We have been getting alerts that R drive is 99% full, 100% full. I am interested to know what is consuming the R drive. Could you please advise. Any script to check what is consuming the R drive?



How big is the drive and is anything else using the drive? Also, how often are you taking log file backups? And, what are the initial size and growth settings set to? And finally, do you have any form of log shipping or replication running?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
coolchaitu
coolchaitu
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 1520
Jeff Moden - Thursday, November 16, 2017 10:16 PM
coolchaitu - Thursday, November 16, 2017 10:01 PM

Good Morning Experts.

We have a SQL Server instance. All the user databases log files are on R drive. We have been getting alerts that R drive is 99% full, 100% full. I am interested to know what is consuming the R drive. Could you please advise. Any script to check what is consuming the R drive?



How big is the drive and is anything else using the drive? Also, how often are you taking log file backups? And, what are the initial size and growth settings set to? And finally, do you have any form of log shipping or replication running?

Nothing else is using the drive. It is dedicated to user databases log files. We take log file backups every 30 minutes. No log shipping or replication. This issue is happenning only from last 2 days. Is there a way to find out the spid/transaction that is eating up the drive

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125259 Visits: 18852
Have you established which database's log file is growing? Has anything changed in the last two days? Maybe you've imported (or deleted or updated) a lot of data in one go. Maybe you've started doing index maintenance. Or perhaps you have an overnight batch process? If you look at the date of the transaction log file on disk, that will tell you when it last grew - was anything happening at that time?

What do you get if you run this?
SELECT name, log_reuse_wait_desc
FROM sys.databases


John
GilaMonster
GilaMonster
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859462 Visits: 48575
coolchaitu - Friday, November 17, 2017 12:07 AM

Is there a way to find out the spid/transaction that is eating up the drive

It won't be a single session. The log grows if it can't be marked reusable by a log backup. First thing to do is see which DB's log file is large, and check why it can't be marked reusable. See John's post for details.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816273 Visits: 46258
coolchaitu - Friday, November 17, 2017 12:07 AM
Jeff Moden - Thursday, November 16, 2017 10:16 PM
coolchaitu - Thursday, November 16, 2017 10:01 PM

Good Morning Experts.

We have a SQL Server instance. All the user databases log files are on R drive. We have been getting alerts that R drive is 99% full, 100% full. I am interested to know what is consuming the R drive. Could you please advise. Any script to check what is consuming the R drive?



How big is the drive and is anything else using the drive? Also, how often are you taking log file backups? And, what are the initial size and growth settings set to? And finally, do you have any form of log shipping or replication running?

Nothing else is using the drive. It is dedicated to user databases log files. We take log file backups every 30 minutes. No log shipping or replication. This issue is happenning only from last 2 days. Is there a way to find out the spid/transaction that is eating up the drive


Yes but we need to know the answer to my other questions to help guide us to what the most likely cause is. What are the settings for the initial file sizes and growth settings of the offending log file (hopefully, you've narrowed it down to that... Check out the sys.master_files if you haven't) and how big is the drive?

Also, if it only changed in the last 2 days, then really carefully find out if someone added some new code or, like the others have stated, have you recently just started something like index maintenance?

We need answers to those questions to be able to proceed.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4603 Visits: 917
If transactional log backups have not been taken for a long time after full backup , the log keeps growing. Can be another reason apart from what experts have said above.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816273 Visits: 46258
@coolchaitu ,

I see you've flagged two posts as the answer, so I'm curious... What did you actually come up with? What was it that was causing your R: drive to fill up?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
coolchaitu
coolchaitu
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 1520
Jeff Moden - Monday, November 20, 2017 4:00 PM
@coolchaitu ,

I see you've flagged two posts as the answer, so I'm curious... What did you actually come up with? What was it that was causing your R: drive to fill up?

It was a huge ETL load that was causing

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816273 Visits: 46258
coolchaitu - Tuesday, November 21, 2017 10:45 PM
Jeff Moden - Monday, November 20, 2017 4:00 PM
@coolchaitu ,

I see you've flagged two posts as the answer, so I'm curious... What did you actually come up with? What was it that was causing your R: drive to fill up?

It was a huge ETL load that was causing


Thanks for the feedback. That still begs to question how big the R drive is because even a "huge" ETL load should not be causing extreme log file usage. Unless your R drive is only a couple of gig in size, that ETL process may have some serious problems.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search