February 11, 2020 at 2:24 am
Thanks for the assistance in advanced.... i am trying to create my temdb with 4 data files and one log file. with the size being 100GB for each file
when i run
use tempdb
select * from sys.master_files where database_id=2 this returns 19+ files that i can NOT seem to get ride of... i have rebooted many times and still those files seem to show up.
Any suggestions are more than welcome and i have ran the below code and still not much luck the files are created but when i look in the properties of tempdb they are not there...
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'Tempdb_LOG', NEWNAME= N'Tempdb_Log', FILENAME= N'H:\MSSQL_TempLogs\Tempdb_log.ldf', SIZE= 50000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'Tempdb1', NEWNAME= N'Tempdb_Data1', FILENAME= N'H:\MSSQL_TempData\Tempdb_data1.mdf', SIZE= 100000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'TempdbData2', FILENAME= N'H:\MSSQL_TempData\Tempdb_data2.ndf', SIZE= 100000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'TempdbData3', FILENAME= N'H:\MSSQL_TempData\Tempdb_data3.ndf', SIZE= 100000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'TempdbData4', FILENAME= N'H:\MSSQL_TempData\Tempdb_data4.ndf', SIZE= 100000MB, FILEGROWTH= 10000MB);
Thanks
DHeath
DHeath
February 11, 2020 at 9:51 am
Don't modify the mdf file of tempdb, I have seen it do some very crazy things when the MDF file has been modified.
Revert the mdf file back to the original settings Logicalname of "tempdev", filename of "<whateverpath>\tempdb.mdf"
Then you may need to start in single master only mode as the other files will want to be created, so you would then want to go and remove the files so that it thinks it only has the mdf and ldf.
Then add in the 3 NDF's only
Restart in normal mode and all should be fine.
This has happened to me a number of times on servers I have inherited.
February 11, 2020 at 1:53 pm
Antony.Green, thanks for the reply...
I have never had to "revert" backward are there any special commands or anything to the sort? As you mention you said single master mode i am assuming you mean single user mode. When you say go and remove the files from where they are NOT in the physical location you see in the attachment so will going into single user mode clear that up?
I appreciate your assistance just need a bit more clarity.
DHeath
DHeath
February 11, 2020 at 2:30 pm
reverting backwards is the same as reverting forwards, just back to the original setting.
The <VALUE> field needs replacing with whatever sys.database_files, sys.master_files thinks the name of the tempdb mdf file current is called.
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'<VALUE>', NEWNAME= N'tempdev', FILENAME= N'H:\MSSQL_TempData\tempdb.mdf', SIZE= 100000MB, FILEGROWTH= 10000MB);
You could try single user (/m) switch, but that will want to create all the files tempdb thinks it has, so it may crash as it needs space for all the files to create. The /f switch for minimal mode may be the better option so it starts with minimal config, I always call this master only mode, someone probably called it that before and it stuck.
You then need to issue a number of ALTER DATABASE tempdb REMOVE FILE commands so that the tempdb is back to normal 1 MDF, 1 LDF. Then add in the 3 required NDF's. Restart without any switches.
Once you fixed the MDF file though you may/maynot need to do all the switching etc as it may revert to its last good config, again as I say I have seen it do very strange things if the MDF has been modified from default.
February 11, 2020 at 2:49 pm
Mr. Green, thanks again and much appreciated
altho i must ask...how does the sys.master_files clean up itself...is that something SQL will do or something i need to do after i revert? I was hoping i can manually get rid of that but it doesn't seem like that will be the case. Thanks Again
DHeath
DHeath
February 11, 2020 at 3:16 pm
It should sort itself out when it you do all the needed "remove file" "add file" "alter file" commands. You cant modify the system tables anymore (not that you should of back in 2000/SQL7) so you need to do it based on the commands issued.
February 11, 2020 at 4:13 pm
Thank you SIR...... much appreciated
DHeath
DHeath
February 28, 2025 at 6:01 am
Find below steps to fix the tempdb issues.
1)DBCC SHRINKFILE ('tempdev', 1024)
The query executed successfully but the size of the database did not change.
2)SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
No open transactions! Alright, any process holding locks on tempdb?
3)select * from sys.dm_tran_locks where resource_database_id= 2
No locks! 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.
4)select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0
Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there
5)SELECT * FROM sys.tables where is_ms_shipped = 0
Check for user tables on tempdb
After performing all the above steps, Space is very big constraint than execute the below command to freeup the cache.
6)DBCC FREEPROCCACHE
7)DBCC SHRINKFILE ('tempdev', 1024)
USE AdventureWorks2012;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO
SELECT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,s.plan_handle,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
ORDER BY MaxElapsedTime DESC
-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
GO
--Clearing all plans from the plan cache
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
--Clearing all cache entries associated with a resource pool
SELECT * FROM sys.dm_resource_governor_resource_pools;
GO
DBCC FREEPROCCACHE ('default');
GO
8)-- Session usage in tempdb
/*
In addition to looking at how much space is used by each of the object types within
tempdb, you can also see how much space each session has used. This can be useful
for identifying the job or person that s using most of tempdb or is displaying a pattern
of usage that could be causing space problems now or in the future.
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id
, ec.connection_id
,ec.plan_handle
, es.login_name
, es.host_name
, st.text
, su.user_objects_alloc_page_count
, su.user_objects_dealloc_page_count
, su.internal_objects_alloc_page_count
, su.internal_objects_dealloc_page_count
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es
ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
outer apply sys.exec_cached_plans cp
WHERE su.session_id > 50 order by
9)
Current SQL server Temp DB statement-allocated and deallocated space on TempDB
SELECT ssu.session_id,
(ssu.internal_objects_alloc_page_count + sess_alloc) as allocated,
(ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated
, stm.TEXT
from sys.dm_db_session_space_usage as ssu,
sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm ,
(select session_id,
sum(internal_objects_alloc_page_count) as sess_alloc,
sum (internal_objects_dealloc_page_count) as sess_dealloc
from sys.dm_db_task_space_usage group by session_id) as tsk
where ssu.session_id = tsk.session_id
and ssu.session_id >50
and ssu.session_id = req.session_id
and ssu.database_id = 2
order by allocated DESC
9)
Solution:
If you are not able to release space from TempDB because entire Space in TempDB will be unallocated space, Try the below T-SQL
USE TempDB
GO
DBCC FREEPROCCACHE
/*Clears the procedure cache*/
GO
DBCC DROPCLEANBUFFERS
/*Forces all dirty pages for the current database to be written to disk and cleans the buffers*/
GO
DBCC FREESYSTEMCACHE ('ALL')
/*Releases all unused cache entries from all caches*/
GO
DBCC FREESESSIONCACHE
/*Flushes the distributed query connection cache*/
GO
CHECKPOINT
/*Writes all dirty pages for the current database to disk*/
GO
Now you will be able to shrink TempDB as all the caches will be free in TempDB
select * from sys.dm_db_file_space_usage
select * from sys.dm_db_session_space_usage
select* from sys.dm_db_task_space_usage
10) statement consuming space on tempdb
use TempDB
GO
SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC
11)--------long running query in temp db
use TempDB
GO
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
----Worker table information
select * from sys.dm_tran_active_transactions s1 inner join sys.dm_tran_database_transactions s2
on s1.transaction_id = s2.transaction_id
--I would hope to kill the sessions and have the objects cleaned up. Then I could shrink tempdb. However,
select * from sys.dm_tran_session_transactions
returns no rows. So these "transactions" aren't bound to any sessions, right? So now what do I do? Well, I went to figure out which sessions are owning these worktable objects:
select session_id, database_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count,
waittime,lastwaittype,
login_time, last_batch, status, cmd, loginname, sql_handle
from sys.dm_db_task_space_usage s1 inner join sys.sysprocesses s2
on s1.session_id = s2.spid
12)
Please open a new query window in Sql Server Management Studio. Please paste the entire script in the query window and then please Execute the script.
A sample output is indicated below for reference.
-- checks if tempdb files are created of equal size
IF (EXISTS( SELECT name,
size,
physical_name
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
AND size <> (SELECT MAX(size)
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS')))
BEGIN
SET @bSuggestions = 1;
PRINT N'File sizes of tempdb data files do not appear to be equal. '
+ N'Please verify initial size is same for all tempdb data files.';
END
March 1, 2025 at 8:36 pm
Find below steps to fix the tempdb issues.
1)DBCC SHRINKFILE ('tempdev', 1024)
The query executed successfully but the size of the database did not change.
2)SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
No open transactions! Alright, any process holding locks on tempdb?
3)select * from sys.dm_tran_locks where resource_database_id= 2
No locks! 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.
4)select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0
Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there
5)SELECT * FROM sys.tables where is_ms_shipped = 0
Check for user tables on tempdb
After performing all the above steps, Space is very big constraint than execute the below command to freeup the cache.
6)DBCC FREEPROCCACHE
7)DBCC SHRINKFILE ('tempdev', 1024)
USE AdventureWorks2012; GO SELECT * FROM Person.Address; GO SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'SELECT * FROM Person.Address%'; GO
SELECT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount,s.plan_handle, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec ,query_plan FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t ORDER BY MaxElapsedTime DESC
-- Remove the specific plan from the cache. DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000); GO
--Clearing all plans from the plan cache DBCC FREEPROCCACHE WITH NO_INFOMSGS;
--Clearing all cache entries associated with a resource pool
SELECT * FROM sys.dm_resource_governor_resource_pools; GO DBCC FREEPROCCACHE ('default'); GO
8)-- Session usage in tempdb /* In addition to looking at how much space is used by each of the object types within tempdb, you can also see how much space each session has used. This can be useful for identifying the job or person that s using most of tempdb or is displaying a pattern of usage that could be causing space problems now or in the future. */
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id , ec.connection_id ,ec.plan_handle , es.login_name , es.host_name , st.text , su.user_objects_alloc_page_count , su.user_objects_dealloc_page_count , su.internal_objects_alloc_page_count , su.internal_objects_dealloc_page_count , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st outer apply sys.exec_cached_plans cp WHERE su.session_id > 50 order by
9) Current SQL server Temp DB statement-allocated and deallocated space on TempDB SELECT ssu.session_id, (ssu.internal_objects_alloc_page_count + sess_alloc) as allocated, (ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated , stm.TEXT from sys.dm_db_session_space_usage as ssu, sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm , (select session_id, sum(internal_objects_alloc_page_count) as sess_alloc, sum (internal_objects_dealloc_page_count) as sess_dealloc from sys.dm_db_task_space_usage group by session_id) as tsk where ssu.session_id = tsk.session_id and ssu.session_id >50 and ssu.session_id = req.session_id and ssu.database_id = 2 order by allocated DESC 9) Solution:
If you are not able to release space from TempDB because entire Space in TempDB will be unallocated space, Try the below T-SQL
USE TempDB GO DBCC FREEPROCCACHE /*Clears the procedure cache*/ GO DBCC DROPCLEANBUFFERS /*Forces all dirty pages for the current database to be written to disk and cleans the buffers*/ GO DBCC FREESYSTEMCACHE ('ALL') /*Releases all unused cache entries from all caches*/ GO DBCC FREESESSIONCACHE /*Flushes the distributed query connection cache*/ GO CHECKPOINT /*Writes all dirty pages for the current database to disk*/ GO Now you will be able to shrink TempDB as all the caches will be free in TempDB
select * from sys.dm_db_file_space_usage
select * from sys.dm_db_session_space_usage
select* from sys.dm_db_task_space_usage
10) statement consuming space on tempdb use TempDB GO
SELECT t1.session_id, t1.request_id, t1.task_alloc, t1.task_dealloc, t2.sql_handle, t2.statement_start_offset, t2.statement_end_offset, t2.plan_handle FROM (Select session_id, request_id, SUM(internal_objects_alloc_page_count) AS task_alloc, SUM (internal_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id) AS t1, sys.dm_exec_requests AS t2 WHERE t1.session_id = t2.session_id AND (t1.request_id = t2.request_id) ORDER BY t1.task_alloc DESC
11)--------long running query in temp db use TempDB GO SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;
----Worker table information select * from sys.dm_tran_active_transactions s1 inner join sys.dm_tran_database_transactions s2 on s1.transaction_id = s2.transaction_id
--I would hope to kill the sessions and have the objects cleaned up. Then I could shrink tempdb. However,
select * from sys.dm_tran_session_transactions
returns no rows. So these "transactions" aren't bound to any sessions, right? So now what do I do? Well, I went to figure out which sessions are owning these worktable objects:
select session_id, database_id, user_objects_alloc_page_count, user_objects_dealloc_page_count, internal_objects_alloc_page_count, internal_objects_dealloc_page_count, waittime,lastwaittype, login_time, last_batch, status, cmd, loginname, sql_handle from sys.dm_db_task_space_usage s1 inner join sys.sysprocesses s2 on s1.session_id = s2.spid
12) Please open a new query window in Sql Server Management Studio. Please paste the entire script in the query window and then please Execute the script. A sample output is indicated below for reference.
-- checks if tempdb files are created of equal size
IF (EXISTS( SELECT name, size, physical_name FROM tempdb.sys.database_files WHERE type_desc = 'ROWS' AND size <> (SELECT MAX(size) FROM tempdb.sys.database_files WHERE type_desc = 'ROWS'))) BEGIN SET @bSuggestions = 1; PRINT N'File sizes of tempdb data files do not appear to be equal. ' + N'Please verify initial size is same for all tempdb data files.'; END
Where did you copy this from? The link no longer works.
You also realize that this thread is 5 years old?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 1, 2025 at 8:36 pm
Find below steps to fix the tempdb issues.
1)DBCC SHRINKFILE ('tempdev', 1024)
The query executed successfully but the size of the database did not change.
2)SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
No open transactions! Alright, any process holding locks on tempdb?
3)select * from sys.dm_tran_locks where resource_database_id= 2
No locks! 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.
4)select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0
Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there
5)SELECT * FROM sys.tables where is_ms_shipped = 0
Check for user tables on tempdb
After performing all the above steps, Space is very big constraint than execute the below command to freeup the cache.
6)DBCC FREEPROCCACHE
7)DBCC SHRINKFILE ('tempdev', 1024)
USE AdventureWorks2012; GO SELECT * FROM Person.Address; GO SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'SELECT * FROM Person.Address%'; GO
SELECT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount,s.plan_handle, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec ,query_plan FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t ORDER BY MaxElapsedTime DESC
-- Remove the specific plan from the cache. DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000); GO
--Clearing all plans from the plan cache DBCC FREEPROCCACHE WITH NO_INFOMSGS;
--Clearing all cache entries associated with a resource pool
SELECT * FROM sys.dm_resource_governor_resource_pools; GO DBCC FREEPROCCACHE ('default'); GO
8)-- Session usage in tempdb /* In addition to looking at how much space is used by each of the object types within tempdb, you can also see how much space each session has used. This can be useful for identifying the job or person that s using most of tempdb or is displaying a pattern of usage that could be causing space problems now or in the future. */
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id , ec.connection_id ,ec.plan_handle , es.login_name , es.host_name , st.text , su.user_objects_alloc_page_count , su.user_objects_dealloc_page_count , su.internal_objects_alloc_page_count , su.internal_objects_dealloc_page_count , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st outer apply sys.exec_cached_plans cp WHERE su.session_id > 50 order by
9) Current SQL server Temp DB statement-allocated and deallocated space on TempDB SELECT ssu.session_id, (ssu.internal_objects_alloc_page_count + sess_alloc) as allocated, (ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated , stm.TEXT from sys.dm_db_session_space_usage as ssu, sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm , (select session_id, sum(internal_objects_alloc_page_count) as sess_alloc, sum (internal_objects_dealloc_page_count) as sess_dealloc from sys.dm_db_task_space_usage group by session_id) as tsk where ssu.session_id = tsk.session_id and ssu.session_id >50 and ssu.session_id = req.session_id and ssu.database_id = 2 order by allocated DESC 9) Solution:
If you are not able to release space from TempDB because entire Space in TempDB will be unallocated space, Try the below T-SQL
USE TempDB GO DBCC FREEPROCCACHE /*Clears the procedure cache*/ GO DBCC DROPCLEANBUFFERS /*Forces all dirty pages for the current database to be written to disk and cleans the buffers*/ GO DBCC FREESYSTEMCACHE ('ALL') /*Releases all unused cache entries from all caches*/ GO DBCC FREESESSIONCACHE /*Flushes the distributed query connection cache*/ GO CHECKPOINT /*Writes all dirty pages for the current database to disk*/ GO Now you will be able to shrink TempDB as all the caches will be free in TempDB
select * from sys.dm_db_file_space_usage
select * from sys.dm_db_session_space_usage
select* from sys.dm_db_task_space_usage
10) statement consuming space on tempdb use TempDB GO
SELECT t1.session_id, t1.request_id, t1.task_alloc, t1.task_dealloc, t2.sql_handle, t2.statement_start_offset, t2.statement_end_offset, t2.plan_handle FROM (Select session_id, request_id, SUM(internal_objects_alloc_page_count) AS task_alloc, SUM (internal_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id) AS t1, sys.dm_exec_requests AS t2 WHERE t1.session_id = t2.session_id AND (t1.request_id = t2.request_id) ORDER BY t1.task_alloc DESC
11)--------long running query in temp db use TempDB GO SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;
----Worker table information select * from sys.dm_tran_active_transactions s1 inner join sys.dm_tran_database_transactions s2 on s1.transaction_id = s2.transaction_id
--I would hope to kill the sessions and have the objects cleaned up. Then I could shrink tempdb. However,
select * from sys.dm_tran_session_transactions
returns no rows. So these "transactions" aren't bound to any sessions, right? So now what do I do? Well, I went to figure out which sessions are owning these worktable objects:
select session_id, database_id, user_objects_alloc_page_count, user_objects_dealloc_page_count, internal_objects_alloc_page_count, internal_objects_dealloc_page_count, waittime,lastwaittype, login_time, last_batch, status, cmd, loginname, sql_handle from sys.dm_db_task_space_usage s1 inner join sys.sysprocesses s2 on s1.session_id = s2.spid
12) Please open a new query window in Sql Server Management Studio. Please paste the entire script in the query window and then please Execute the script. A sample output is indicated below for reference.
-- checks if tempdb files are created of equal size
IF (EXISTS( SELECT name, size, physical_name FROM tempdb.sys.database_files WHERE type_desc = 'ROWS' AND size <> (SELECT MAX(size) FROM tempdb.sys.database_files WHERE type_desc = 'ROWS'))) BEGIN SET @bSuggestions = 1; PRINT N'File sizes of tempdb data files do not appear to be equal. ' + N'Please verify initial size is same for all tempdb data files.'; END
Where did you copy this from? The link no longer works.
You also realize that this thread is 5 years old?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy