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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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.
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).