Transaction Log filling, and the proper response

Question

Post reply

Transaction Log filling, and the proper response

chuck.forbes

SSCarpal Tunnel

Points: 4294
More actions
April 3, 2017 at 10:07 am

#320073

One of our oldest databases was recently consuming 9Gb of space for TempDB, when it regularly consumes ~1Gb. The device where TempDB is stored has 10Gb of space, so I started thinking about what the proper response is, in cases where you consume all available space for TempDB. Then I saw this article this morning:
https://www.sqlskills.com/blogs/paul/sqlskills-sql101-switching-recovery-models/
Prior to this, I thought that I would have to revert to a restart of the instance to reclaim TempDB space. Is the response referenced in this article (switch to SIMPLE and execute a CHECKPOINT) a viable option that others have used? We have the authority to backup the databases immediately following something like this, and switch back to FULL, so that's not an issue.
Just wondering from others if this is an actual option for my toolset, in the case of an emergency.
Thanks,
--=Chuck

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

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

Bill Talada SSChampion Points: 11954 More actions · Answer 1

I don't see a connection between a large tempdb and switching to SIMPLE mode. Tempdb never shrinks except on restart.

Switching to SIMPLE during a critical time really freaks me out. That is the time when you want your logs.

Most tempdb expansion has occurred during cartesian join queries in my experience. I'd prefer to kill the user connection rather than any other option.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

chuck.forbes - Monday, April 3, 2017 10:07 AM
One of our oldest databases was recently consuming 9Gb of space for TempDB, when it regularly consumes ~1Gb. The device where TempDB is stored has 10Gb of space, so I started thinking about what the proper response is, in cases where you consume all available space for TempDB. Then I saw this article this morning:
https://www.sqlskills.com/blogs/paul/sqlskills-sql101-switching-recovery-models/
Prior to this, I thought that I would have to revert to a restart of the instance to reclaim TempDB space. Is the response referenced in this article (switch to SIMPLE and execute a CHECKPOINT) a viable option that others have used? We have the authority to backup the databases immediately following something like this, and switch back to FULL, so that's not an issue.
Just wondering from others if this is an actual option for my toolset, in the case of an emergency.
Thanks,
--=Chuck

What exactly is the problem here? The title is "Transaction Log filling, and the proper response" but your description concerns TempDB. Do you mean TempDB log?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chuck.forbes SSCarpal Tunnel Points: 4294 More actions · Answer 3

Derr, yes, the TempDb mdf file is quickly filling. In my naïve brain, I just think of all related files in TempDB as transaction log files.

In lieu of a reboot, I guess the solution I read from that web posting was:

1) Switch to SIMPLE
2) Checkpoint
3) Resize the TempDB file to a smaller size
4) Switch to FULL
5) Full backup of all databases

--=Chuck

Sue_H SSC Guru Points: 90860 More actions · Answer 4

By default, tempdb is in simple recovery mode already. I may be wrong but I thought that a checkpoint would be done by the system if the log is 70% full but you can also issue one yourself and see if it makes a difference. If this is just the log, it might help to check the log_resue_wait_desc in sys.databases. You may want to look for other things like open transactions, queries that have spilled to tempdb, reindexing, RCSI isolation levels. You could also look at some running queries to see what is taking up the space.
You can find some here: Troubleshooting Insufficient Disk Space in tempdb
I realize you said for emergencies but I'd probably still want to know what caused the issue if possible so some proactive things could be explored. You could try a manual checkpoint as the first course of action but it also may not resolve the issue

Sue

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

chuck.forbes - Monday, April 3, 2017 10:57 AM
Derr, yes, the TempDb mdf file is quickly filling. In my naÃ¯ve brain, I just think of all related files in TempDB as transaction log files.
In lieu of a reboot, I guess the solution I read from that web posting was:
1) Switch to SIMPLE
2) Checkpoint
3) Resize the TempDB file to a smaller size
4) Switch to FULL
5) Full backup of all databases
--=Chuck

I think you're confusing tran logs & TempDB. More coffee recommended 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chuck.forbes SSCarpal Tunnel Points: 4294 More actions · Answer 6

ChrisM@Work - Monday, April 3, 2017 11:00 AM
chuck.forbes - Monday, April 3, 2017 10:57 AM
Derr, yes, the TempDb mdf file is quickly filling. In my naÃ¯ve brain, I just think of all related files in TempDB as transaction log files.
In lieu of a reboot, I guess the solution I read from that web posting was:
1) Switch to SIMPLE
2) Checkpoint
3) Resize the TempDB file to a smaller size
4) Switch to FULL
5) Full backup of all databases
--=Chuck
I think you're confusing tran logs & TempDB. More coffee recommended 😉

It's a terribly incorrect title, and it looks like there's no way to edit it. Could we just end this thread, and if after reading the links above I still have questions, I'll start a new post with a much more accurate title, and a better worded question?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

April 3, 2017 at 12:29 pm

#1936390

That sounds like a plan.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I see the request to end the thread but I think it is essential to the topic at hand: Tempdb is growing and what is causing it.

Here is an article to help trap what is causing the growth.

http://bit.ly/FileSizeChange

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events