I have a SQL Environment of which I am sysadmin. This Environment includes the Company data warehouse. I conduct many different jobs from this Environment, and it is also Another sysadmin which has access to it.

Seeing that it contains the data warehouse, some users from the production team has access to it as well.

The underlying raw data is very spread out. The data comes from many different sources, which sometimes requires excessive use of OUTER JOINs, Temp Tables, Procedures etc. in order to fetch the data of interest.

I have seen that some users in the production team has sometimes done massive queries, resulting in TempDB growing uncontrollably by 50+ GB, eventually resulting in the server shutting down (thus far, we have the SQL servers and TempDB on the same drive).

Is there a way to prohibit the production team of using too big queries, while not putting the same restrictions on the sysadmins, as there is sometimes neccessary to conduct TempDB extensive queries given the complex raw data structure?

I do not want to hinder the production team of using/creating Stored procedures, Temp Tables etc. I just want to investigate if there is an option to restrict execution time / TempDB usage.

  • You could write a sql job which will check for long running queries of a certain type (exec_requests) and then use the session ID to KILL it after a certain amount of time has passed. This is not ideal but might fit your current situation Jun 19 '17 at 7:31
  • @StijnWynants Thanks for replying! Suppose a Query uses 5 GB of TempDB. If I were to stop that Query using session kill - would that decrease the TempDB size by 5 GB? I am trying to fully grasp TempDB, but from my understanding TempDB only decreases in size if data has been successfully transfered to a permanent table, or the SQL server is restarted. Perhaps I've misunderstood? If not, this may be a valid workaround. I suppose one can make exceptions to not kill a Query if it is a sysadmin who has conducted the Query.
    – Cenderze
    Jun 19 '17 at 7:36
  • If the session ends, the data is released so the space is returned to freespace Jun 19 '17 at 7:49

You cannot limit some users for use the certain amount of tempdb only, instead, you can put the limit to it's max size.

Anyway, putting tempdb at the same disk with your system files is a bad idea.

You can monitor who is consuming your tempdb making smth like this:

    create view [dbo].[vw_tempdb_usage] 
    select session_id, 
           cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
           cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
           cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
           cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
           cast(sum(internal_objects_alloc_page_count - 
                    internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
           cast(sum(user_objects_alloc_page_count - 
                    user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
    from sys.dm_db_task_space_usage 
    group by session_id
    having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count + 
          user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0;


    CREATE TABLE [dbo].[tempdb_details](
        [dt] [datetime] NOT NULL,
        [spid] [smallint] NOT NULL,
        [db] [nvarchar](128) NULL,
        [loginame] [nchar](128) NOT NULL,
        [hostname] [nchar](128) NOT NULL,
        [program_name] [nchar](128) NOT NULL,
        [login_time] [datetime] NOT NULL,
        [internal_objects_diff_Gb] [decimal](10, 2) NULL,
        [user_objects_diff_Gb] [decimal](10, 2) NULL,
        [cmd] [nchar](16) NOT NULL,
        [txt] [nvarchar](max) NULL,
        [Statement] [nvarchar](max) NULL,
     CONSTRAINT [PK_tempdb_details_dt_spid] PRIMARY KEY CLUSTERED 
        [dt] ASC,
        [spid] ASC


    insert into dbo.tempdb_details
               (dt, spid, db, loginame, hostname, program_name, 
                login_time, internal_objects_diff_Gb, 
                user_objects_diff_Gb, cmd, txt, Statement)          
    select GETDATE() as dt,
           db_name(p.dbid) as db,
           txt.text as txt,
           CASE WHEN encrypted = 1 THEN '-- ENCRYPTED'
                   WHEN p.stmt_start >= 0
                   THEN substring(txt.text, stmt_start/2 + 1,
                                  CASE stmt_end
                                       WHEN -1 THEN datalength(txt.text)
                                       ELSE stmt_end/2 - stmt_start/2 + 1
              END AS Statement
    from dbo.vw_tempdb_usage v join sys.dm_exec_sessions s 
            on v.session_id = s.session_id
         join sys.sysprocesses p
            on v.session_id = p.spid
         cross apply sys.dm_exec_sql_text(p.sql_handle)  txt   
    where v.internal_objects_diff_Gb > 1 or v.user_objects_diff_Gb > 1;

Here I created a view using sys.dm_db_task_space_usage that shows me in real time who is using more than 1Gb of tempdb now. Then I put the query that reads from this view in a job running every 5 minutes, so it captures every query along with it's owner if it consumes more than 1Gb. Then it's up to you how you want to react to it. My actions are simple: tempdb has a limited size (100Gb) so it's impossible to overflow it. Every violator is just rolling back receiving smth like this:

Could not allocate space for object 'dbo.Large Object Storage System object: 422539438653440' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. error.

At the same time every attempt to overflow it is captured by my job. So I can easily see who it was and what he was doing, and take actions.

  • I've had occasions where TempDB grows too big and causes disc shortage on the drive where I have my SQL services set up (SQL server, analysis server, reporting server etc.). From my understanding, I'm using 10 % autogrow at the moment. Simply removing the auto-growth would hinder the server from shutting down at least? I'll test the queries provided of course when I am able to. This was just a question that popped up in my head just now.
    – Cenderze
    Jun 19 '17 at 13:57

I had a similar issue with a certain kind of query causing tempdb to explode over 400GB.

This is the way we solved it.

We used the following query from Pinal Dave (https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/) to identify statements and their sessionID

st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,   --- USE THIS ONE FOR THE KILL
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC

Alter the where clause to the values you want to filter on. (Time/Reads/Writes/SQLText/AmountOfPages in tempdb)

Store this value into a @SessionIDParam and then use dynamic sql to kill the session.

You can run this job every minute.

  • What is the correlation between total_elapsed_time and tempdb usage? We have dozens of clients that querying the database for hours just not fetching all the results in their Excel/Access, meanwhile I saw the query that made tempdb growth over 100Gb in just 6 minutes
    – sepupic
    Jun 19 '17 at 9:41
  • Then you have to use another thing to filter in your where clause. If you know the query, you can put the t.text in your where clause & kill it when it runs longer then a certain amount of time. Jun 19 '17 at 9:43
  • I repeat, the query can make no tempdb usage at all, but its duration easily exceeds 1000000
    – sepupic
    Jun 19 '17 at 9:45
  • Do you know which query it is? certain text? You can also take reads & writes instead of time, this you will have to evaluate for yourself. Jun 19 '17 at 9:47
  • 2
    Your code does not evaluate tempdb usage at all, and I repeat, to overflow 100Gb of tempdb 6 minutes are quite enough
    – sepupic
    Jun 19 '17 at 9:49

I'm not clear why the server "shuts down", but if your disk is full of files - including the files of tempdb - I see it may be unusable, at least until you restart it.

My idea therefore - but I don't know if this will work, and particularly without restarting for changes to the system, which you don't want to do during the working day - my idea is that for normal use, you set tempdb to assume a given file size, but not to grow beyond a limit. Maybe not to grow at all. Then your users can't blow up tempdb and kill the server. But neither can you.

When you want to use the system yourself without such a restriction on tempdb (if you need to), then you just change the tempdb file size settings, and then, after your work, you change the setting back.

...If you're allowed to do that. (If it grows, you may be not able to shrink it, without restarting.)

If you're not allowed to do it directly, then there are things such as "disk quota" and even "thin provisioning" outside SQL Server that you could use to enforce disk use limits. But those probably won't work when tempdb is on the same hard disk as your data databases - can't discriminate them. Unless you can set a quota on size of individual files... (If the disk is FAT32 then that's 4 gigabytes anyway. You can give a database more than one file if you have to. Even tempdb, and that may be recommended.)

If you don't have an extra disk, it's technically possible with third-party software (I think) to set up a "RAM disk" - basically a program reserves a lot of RAM and then pretends it is a disk - and put the tempdb files in this RAM disk. If that disk fills up, it doesn't matter to your real data files. However, it means you are using up RAM just for that behaviour. So this is probably not good.

Or, if your bad outcome is that the real databases need to grow and they can't - because the disk filled up with tempdb - then you could avoid that with a maintenance task of making each database grow before it needs to. For instance, overnight, any database with less than 20% free space in it, you resize that file to be larger. Then, if tempdb eats the whole server, the other databases still have an allowance of free space that you gave to them first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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