June 1, 2014 at 10:30 am
Error I'm getting:
"Could not allocate a new page for database 'Tempdb because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup'".
How do I determine what/which transaction is causing this so that I can kill that transaction and release the tempdb space instead of having to restart the sql server?
June 1, 2014 at 3:21 pm
I have this problem on a server for a client every few months and I find sp_whoisactive to be the best method. You can download the latest version here, [11.11]. I simply change the "use" statement to tempdb, if you have to go through change management to "create" objects in master or anything.
There is a column in execution just "exec sp_whoisactive" of tempdb_allocations and I generally find that it is a connection/session that has been active for quite some time (days, weeks, etc.) and the value is well above any other transactions showing. However, mileage may vary according to environments.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 2, 2014 at 12:19 am
The first order of business would be to add more space to tempdb, unless you truly know that this is a one-time or rare event. Given that you can't readily identify the session consuming enough space to tip the database over, it would seem that you can't say that that this is a one-time event.
Even if you kill the query chewing up the bulk of tempdb space (provided it is only one query), whoever or whatever ran that query will likely do so again, putting you right back where you began.
That being said, this query will let you know which sessions are consuming space in tempdb (the DMV referenced only reports space used in tempdb):
SELECT * FROM sys.dm_db_session_space_usage AS ddssu
WHERE ddssu.user_objects_alloc_page_count > 0
ORDER BY ddssu.user_objects_alloc_page_count DESC
Note: each page is 8kB.
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply