4

I have a SQL Server 2005 instance where my tempdb i apparently holding on to objects that are not in use anymore. The database has 24gb total and is apparently unable to go below 8gb in use. For various reasons I'm having a hard time getting permission to just restart the instance and reset tempdb.

When I run dbcc opentran on each database using exec sp_msforeachdb 'dbcc opentran (?)' I get back that there are no open transactions in any of the databases.

On the other hand when I run the query

select log_reuse_wait_desc from sys.databases where name = 'tempdb'

I get back ACTIVE_TRANSACTION.

When I query from sys.dm_exec_requests I get back no active requests other than my current one and the normal background system tasks.

When I query from sys.dm_db_task_space_usage I'm getting a list of sessions and every other column (except database_id) is 0.

The objects appear to be normal tempdb objects with names such as:

#0AF29B96
#0B5CAFEA
#0BE6BFCF
#0C50D423

I have two questions. First is there somewhere else that I should be looking as to why these objects still exist? And second is it safe to delete them?

Edit -- 4/10/2013 2PM Based on the link provided by @Shawn Melton I ran the following two queries.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
    (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],
    SUM(internal_object_reserved_page_count) AS [internal object pages used],
    (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB],
    SUM(user_object_reserved_page_count) AS [user object pages used],
    (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

With the following output:

free pages   free space in MB  internal object pages used internal object space in MB  user object pages used user object space in MB
------------ ----------------- -------------------------- ---------------------------- ---------------------- -----------------------
1829424      14292.375000      320                        2.500000                     80                     0.625000

size in MB
-------------
22490.062500

@MrDenny suggested service brokers so I queried sys.dm_broker_queue_monitors.

I got back 2 rows and got back two inactive queues located in MSDB. I then used the first example query in BOL for this dm.

SELECT t1.name AS [Service_Name],  t3.name AS [Schema_Name], t2.name AS [Queue_Name],  
    CASE WHEN t4.state IS NULL THEN 'Not available' 
        ELSE t4.state 
        END AS [Queue_State],  
    CASE WHEN t4.tasks_waiting IS NULL THEN '--' 
        ELSE CONVERT(VARCHAR, t4.tasks_waiting) 
        END AS tasks_waiting, 
    CASE WHEN t4.last_activated_time IS NULL THEN '--' 
        ELSE CONVERT(varchar, t4.last_activated_time) 
        END AS last_activated_time ,  
    CASE WHEN t4.last_empty_rowset_time IS NULL THEN '--' 
        ELSE CONVERT(varchar,t4.last_empty_rowset_time) 
        END AS last_empty_rowset_time, 
( 
    SELECT COUNT(*) 
    FROM sys.transmission_queue t6 
    WHERE (t6.from_service_name = t1.name) 
) AS [Tran_Message_Count] 
FROM sys.services t1
INNER JOIN sys.service_queues t2 
    ON ( t1.service_queue_id = t2.object_id )   
INNER JOIN sys.schemas t3 
    ON ( t2.schema_id = t3.schema_id )  
LEFT OUTER JOIN sys.dm_broker_queue_monitors t4 
    ON ( t2.object_id = t4.queue_id  AND t4.database_id = DB_ID() )  
INNER JOIN sys.databases t5 
    ON ( t5.database_id = DB_ID() )

And got back the following.

Service_Name                                                              Schema_Name  Queue_Name                    Queue_State     tasks_waiting  last_activated_time  last_empty_rowset_time  Tran_Message_Count
------------------------------------------------------------------------- ------------ ----------------------------- --------------- -------------- -------------------- ----------------------- -------------------
InternalMailService                                                       dbo          InternalMailQueue             INACTIVE        0              Apr  7 2013  8:10AM  Apr  7 2013  8:10AM     0
ExternalMailService                                                       dbo          ExternalMailQueue             INACTIVE        0              Apr  7 2013  8:10AM  Apr  7 2013  8:10AM     0
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService   dbo          QueryNotificationErrorsQueue  Not available   --             --                   --                      0
http://schemas.microsoft.com/SQL/Notifications/EventNotificationService   dbo          EventNotificationErrorsQueue  Not available   --             --                   --                      0
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker              dbo          ServiceBrokerQueue            Not available   --             --                   --                      0

This suggested database mail so I thought I would double check, even though there were no tasks and a 0 message count. DatabaseMail is turned on and used, however there are no unsent messages.

  • You might try using sp_WhoIsActive (download here sqlblog.com/files/folders/release/tags/who+is+active/…). Run it as "EXEC sp_WhoIsActive @get_transaction_info = 1". Should show you who/what has tempdb. – user507 Apr 10 '13 at 17:24
  • Great idea and something I should have thought of myself. Unfortunately all I'm getting back is a session with no active query that has 240 under tempdb_current. Not sure if that is kb, or pages. – Kenneth Fisher Apr 10 '13 at 17:34
  • Can you not find out who/what has the session and ask them to kill it (if it is application); or just kill it yourself. – user507 Apr 10 '13 at 17:41
  • Like I said above, I can't find a session that is using up any reasonable amount of space. About 8gb is tied up and that one session I found with sp_WhoIsActive went away after about 2 minutes. Currently when I run it I get no rows returned. – Kenneth Fisher Apr 10 '13 at 17:44
  • Are you using SQL Service Broker? Do you have open dialogs? – mrdenny Apr 10 '13 at 18:16
1

Below is the code that will help you to see what those objects are along with the row count:

SELECT OBJECT_NAME(id), rowcnt 
    FROM tempdb..sysindexes 
    WHERE OBJECT_NAME(id) LIKE '#%' 
    ORDER BY rowcnt DESC


SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb,
SUM (User_object_reserved_page_count +
internal_object_reserved_page_count +
version_store_reserved_page_count +
unallocated_extent_page_count +
mixed_extent_page_count) * 8 as total_space
FROM sys.dm_db_file_space_usage

Below code will help you to shrink tempdb

USE [tempdb] 
GO 
DBCC FREEPROCCACHE  -- this will free up all cache plans
GO 
USE [tempdb] 
GO 
DBCC SHRINKFILE (N'tempdev' , 10000) -- will shrink the mdf file to 10GB, you can specify log file as well
GO 

There might be some open transactions in tempdb that the allocation and de-allocation of the objects is not occuring smootlhy. Have a look at here to see whats going on under the hood in tempdb.

| improve this answer | |
  • The first query you listed returns back a number of indexes. The second just tells me that I have one tempdb file (no harassment please I didn't set up this instance) and it's using about 8gb of the 24gb it has. Tempdb is sized the way I want it (about 90% of the drive set aside for it). I have no interest in shrinking it. I just need it free for other processes. – Kenneth Fisher Apr 10 '13 at 17:41
  • Sorry, I should have mentioned that the indexes listed are all empty. – Kenneth Fisher Apr 10 '13 at 17:55
  • I have Edited my answer with some more info. – Kin Shah Apr 10 '13 at 21:13
  • I'm afraid the link didn't provide any additional information from what's above. – Kenneth Fisher Apr 11 '13 at 13:28
  • @Kin - the madiera.com link doesn't go where expected – Zach Smith Jul 19 '17 at 11:57
0

I was finally able to get approval to re-start the instance. This of course cleared tempdb. Thanks to everyone for all of your help. Hopefully this won't happen again.

| improve this answer | |
0

do not shrink tempdb unless it is absolutely need

a restart of SQL will always reset tempdb to its original state so you haven't fixed the cause only the symptom

| improve this answer | |
  • This adds nothing that wasn't covered in existing answers. VtC. – Michael Green Apr 22 '18 at 23:34

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.