Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

TempDB transaction log filled up and I had to restart SQL Server as a result. After the restart, is there any way to see what caused the transaction log to fill up in the first place?

share|improve this question
1  
You are going to have to provide some more details and information to make this an answerable question. A few questions right away 1.) Define filled up - How big did it grow? Was it really used space? 2.) Define "AFTER" an instance restarted - immediately after? As in restart and then a ms later - BAM! FULL! - or are we talking minutes/hours/seconds? 3.) What was running on the instance? 4.) What was the activity? –  Mike Walsh Nov 27 '13 at 23:51
1  
Unless you have running a server side trace or some monitoring, you would not get much info to do a root cause analysis. Since you restarted sql server, all the DMV data is flushed out. You should have connected via DAC and then gathered useful data and then restarted sql server (though I would consider this a last resource). –  Kin Nov 28 '13 at 0:05
1  
I'm an idiot tonight... Seeing @Kin's comment made me realize what you were asking.. You mean see what filled it up before the restart.. D'Oh! Probably not, unless you have a monitoring tool, etc. in place. –  Mike Walsh Nov 28 '13 at 0:37

2 Answers 2

Short answer is - Not Really as Kin got at.

That said the default server side trace, if enabled as it is by default, may be of help to you here. Look in the "LOGS" directory in SQL Server and look in the SQL Server installation directory for your instance. It doesn't show you everything that happened, unfortunately. But it could show you the times of the autogrowths to compare against (that is included in the trace). It can also show you hash warnings and sort warnings which could be a cause of TempDB growth. You can also see object creations and deletes for any objects for certain explictly created objects. You can see more about what is included and how to view/query this here.

Kin's answer is also good about looking at what is going on before you restart. Now one note here is you may not need to use the DAC to connect (This link describes what the DAC is). If you are connected or can connect, next time you can run some queries to at least get a sense for what is going on at the time of the event:

  1. SP_Whoisactive is a great script to show you what is going on at that moment, you can also see who is consuming TempDB at that time using the TempDB_Writes/TempDB_Current columns to see the allocation in TempDB.
  2. The scripts in this article are excellent at showing what is consuming space in TempDB. Also this question discusses it with some other queries.

Trick is seeing how TempDB is being used and addressing it over time in your processes and ensure TempDB is appropriately sized and the drives are appropriate for your needs.

share|improve this answer
3  
Also see this question and this question. –  Aaron Bertrand Nov 28 '13 at 2:00

Short Answer is : NO.

Unless you have running a server side trace or some monitoring, you would not get much info to do a root cause analysis. Since you restarted sql server, all the DMV data is flushed out. You should have connected via DAC and then gathered useful data and then restarted sql server (though I would consider this a last resource).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.