Tempdb

--Flytta tempdb filer 
USE master 
go 
ALTER DATABASE tempdb modify FILE (NAME = tempdev, filename = 
'T:\Mssql\Data\tempdb.mdf') 
go 
ALTER DATABASE tempdb modify FILE (NAME = templog, filename = 
'T:\Mssql\Log\templog.ldf') 
go 
-- Efter SQL Server omstart så måste original tempdb filerna raderas
--Se vilken query som tar plats i tempdb
; 
WITH task_space_usage 
AS ( 
-- SUM alloc/delloc pages

SELECT session_id, 
request_id, 
Sum(internal_objects_alloc_page_count)   AS alloc_pages, 
Sum(internal_objects_dealloc_page_count) AS dealloc_pages 
FROM   sys.dm_db_task_space_usage WITH (nolock) 
WHERE  session_id <> @@SPID 
GROUP  BY session_id, 
request_id) 
SELECT TSU.session_id, 
TSU.alloc_pages  1.0 / 128   AS [internal object MB space], 
TSU.dealloc_pages  1.0 / 128 AS [internal object dealloc MB space], 
EST.text, 
-- Extract statement from sql text

Isnull(NULLIF(Substring(EST.text, ERQ.statement_start_offset / 2, CASE 
WHEN 
ERQ.statement_end_offset < ERQ.statement_start_offset THEN 
0 
ELSE( 
ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 
END), 
''), 
EST.text)                     AS [statement text], 
EQP.query_plan 
FROM   task_space_usage AS TSU 
INNER JOIN sys.dm_exec_requests ERQ WITH (nolock) 
ON TSU.session_id = ERQ.session_id 
AND TSU.request_id = ERQ.request_id 
OUTER apply sys.Dm_exec_sql_text(ERQ.sql_handle) AS EST 
OUTER apply sys.Dm_exec_query_plan(ERQ.plan_handle) AS EQP 
WHERE  EST.text IS NOT NULL 
OR EQP.query_plan IS NOT NULL 
ORDER  BY 3 DESC;