March 9, 2011 at 9:33 am
Hi All,
Today I woke with an issue that my temp db is full and when I check the log it says
2011-03-09 00:00:03.80 spid18s This instance of SQL Server has been using a process ID of 7788 since 2/20/2011 12:43:33 AM (local) 2/20/2011 5:43:33 AM (UTC). This is an informational message only; no user action is required.
2011-03-09 02:42:09.17 spid214 Error: 17053, Severity: 16, State: 1.
2011-03-09 02:42:09.17 spid214 N:\var\mssql\data\templog.ldf: Operating system error 112(There is not enough space on the disk.) encountered.
2011-03-09 02:42:11.24 spid214 Error: 9002, Severity: 17, State: 4.
2011-03-09 02:42:11.24 spid214 The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2011-03-09 02:42:12.19 spid215 Error: 9002, Severity: 17, State: 4.
but when i tried to look into log_reuse_wait_desc column I am not able to log into the instance so only option i got is to restart the service so i went config mgr and restarted the service and everything work fines.
But I am trying to troubleshoot find out what caused the issue and why I am not warned on spotlight about this. I tried using SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC----but it gives latest information i need which spid or proceess made temp db crashed and where can we look for this details.
Any help is really appreciated.
Thanks.
March 9, 2011 at 6:24 pm
Check out this post http://www.sqlservercentral.com/Forums/Topic1073545-146-1.aspx#bm1075557. However you will not be able to find information you are looking for if you restarted the services as after restart dynamic management views do not have historical data.
You can try to use default server trace (the one that has been used around time of the problem) and check spid214 and spid215 there.
March 10, 2011 at 3:31 pm
Thanks for the reply and I really appreciate that.
your post helped me a lot..
but there are only default trace files after the sql server has started but not of the trace files before.
since it happened on a prod server the app team want to know why it happen and avoid in future.
your query is helping for now to find out issues on temp db on diff servers.
thanks once again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply