Log file R drive- What is consuming it

Question

Log file R drive- What is consuming it

Viewing 4 posts - 16 through 18 (of 18 total)

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

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 1

coolchaitu - Friday, December 8, 2017 6:42 AM
Brandie Tarvin - Friday, December 8, 2017 6:32 AM
coolchaitu - Friday, December 8, 2017 6:24 AM
Brandie Tarvin - Friday, December 8, 2017 5:37 AM
Aside the R drive, how is your log file set up? With a maximum size? Or with Auto Grow?
And how much of that 100 is being taken up by the log file for this one database verses the regular database files and other database log files?
What about the system DBs? Are they on the same drive? If so, have you looked at tempdb to see how big it is compared to the others?
It is setup to auto grow. System dbs are on different drive. How to find out what consumed the log drive
You haven't answered the rest of my questions.
The log file is taking full 100gb

Okay. So the log file for this database is the only thing on the drive.

Here's some last resort troubleshooting options (really last resort if this is production). Stop everything on the DB. Set the DB single user. Backup the database (FULL) and the transaction log. Run checkpoint against the database (if it allows you to get into it). Truncate the log file (don't shrink it unless truncate doesn't work. Please note that checkpoint doesn't always happen immediately. You may have to wait a while.)

Turn database back on to multi-user and run a server side trace to monitor transactions against the database. This will help you identify the offending transaction.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

coolchaitu SSChampion Points: 10315 More actions · Answer 2

Brandie Tarvin - Friday, December 8, 2017 7:06 AM
coolchaitu - Friday, December 8, 2017 6:42 AM
Brandie Tarvin - Friday, December 8, 2017 6:32 AM
coolchaitu - Friday, December 8, 2017 6:24 AM
Brandie Tarvin - Friday, December 8, 2017 5:37 AM
Aside the R drive, how is your log file set up? With a maximum size? Or with Auto Grow?
And how much of that 100 is being taken up by the log file for this one database verses the regular database files and other database log files?
What about the system DBs? Are they on the same drive? If so, have you looked at tempdb to see how big it is compared to the others?
It is setup to auto grow. System dbs are on different drive. How to find out what consumed the log drive
You haven't answered the rest of my questions.
The log file is taking full 100gb
Okay. So the log file for this database is the only thing on the drive.
Here's some last resort troubleshooting options (really last resort if this is production). Stop everything on the DB. Set the DB single user. Backup the database (FULL) and the transaction log. Run checkpoint against the database (if it allows you to get into it). Truncate the log file (don't shrink it unless truncate doesn't work. Please note that checkpoint doesn't always happen immediately. You may have to wait a while.)
Turn database back on to multi-user and run a server side trace to monitor transactions against the database. This will help you identify the offending transaction.

Thanks Brandie

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

So, did you ever find the offending transaction?

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

coolchaitu SSChampion Points: 10315 More actions · Answer 4

Jeff Moden - Wednesday, May 2, 2018 6:23 PM
So, did you ever find the offending transaction?

Yes Jeff