Today bang at midnight I got a call that one of the drives on a Production Instance was running out of space. The reason for it was the tempdb size. The tempdb datafile was ~200 GB in size and used space was only 3 MB. Now the task at hand was to bring down the tempdb database size.
The easiest approach to bring down the tempdb database size is to restart the SQL Server instance, since tempdb gets re-created every time the instance is restarted. But it was Business hours for the customer and we decided that restarting the SQL Server instance would be the last option. So the DBA started trying to shrink the tempdb.
1 | DBCC SHRINKFILE ('tempdev', 1024) |
The query executed successfully but the size of the database did not change.
The next obvious step would be to check for any open transaction on tempdb.
1 | SELECT * FROM sys.dm_exec_requests WHERE database_id = 2 |
No open transactions! Alright, any process holding locks on tempdb?
1 2 | select * from sys.dm_tran_lockswhere resource_database_id= 2 |
No locks! Then I stumbled upon this article on MSDN. There was a mention of sys.dm_db_session_space_usage DMV which helps to track the number of page allocation and deallocation by each session on the instance. Hence tried my luck with this query.
1 2 | select * from sys.dm_db_session_space_usagewhere user_objects_alloc_page_count<> 0 |
The output was a pleasant surprise.
The last two session ids were of the DBA trying to shrink the database. The first one was of one of the application which was in sleeping status. Thinking that I found the culprit, I asked my team to reach out the Application team to check if this session can be killed. Since I was eager to catch some precious Friday night sleep, told my team “Guys, kill the SPID and then shrink it. Everything should be fine.” Without a second thought logged from the server and slept off peacefully.
Welcome Saturday. Since I did not get any calls overnight, I just peeped into see if the issue was resolved. No! “Pradeep, we deleted one old backup file on the drive and now the drive has some free space left. But the tempdb is not shrinking yet” was the response from my team when I called up. Wow! This issue was getting very interesting.
Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there on it.
1 2 | SELECT * FROM tempdb..sys.all_objectswhere is_ms_shipped = 0 |
The is_ms_shipped column would be 1 for all the system objects. The output of this query looked like this.
As guessed, there were plenty of user tables on tempdb. But why are they here? Most likely some user executed a stored procedure which made the tempdb grow and eventually that session got terminated because tempdb ran out of space. Most importantly when a stored procedure is run cached objects are created on tempdb. These cached objects are in turn associated with a query plan. These objects are still present in tempdb because the query plan of the run away query is still present in the Procedure Cache. By design, these cached tables are not deleted, instead they are truncated so that these tables can be reused when the stored procedure is executed again. This article has more details on this topic.
The only way to get rid of these cached objects is to clear the Procedure Cache. It is not a good idea to clear the procedure cache, since it will force the stored procedures to be recompiled and thereby negatively affecting performance. But for me, tempdb size was more of a concern than the stored procedures being recompiled. I went ahead and cleared the Procedure Cache using the following command.
1 | DBCC FREEPROCCACHE |
I was jumping alone!
Bang. DBCC SHRINKFILE now did what it was supposed to do. tempdb was finally shrunk!
Источник: http://www.sqldbadiaries.com/2010/11/13/tempdb-please-allow-me-to-shrink-you/
