SQL Lion

SELECT
      [name]                  AS [Logical File Name],
      CASE type_desc
            WHEN 'ROWS' THEN 'Data'
            WHEN 'LOG'  THEN 'Log'
      END                     AS [File Type],
      physical_name           AS [File Path],
      [size]                  AS [File Size],
      CASE growth
            WHEN 0 THEN 'Enabled'
            ELSE 'Disabled'
      END                     AS [Auto Growth]
FROM tempdb.sys.database_files

USE tempdb
GO
EXEC sp_spaceused

SELECT
	DDB_NAME(database_id) as [Database Name],
	[Name] as [Logical Name],
	[filename] as [File Name],
	[size_on_disk_bytes] / 1024 as [Size (in KB)]
FROM
	sys.dm_io_virtual_file_stats(2, 1)
	-- 2(tempdb database id), 1(tempdb data file id)
		inner join
	sys.sysfiles
		on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

SELECT
	DB_NAME(database_id)    as	[Database Name],
	[Name]                  as	[Logical Name],
	[filename]              as	[File Name],
	[size_on_disk_bytes] / 1024   as	[Size (in KB)]
FROM sys.dm_io_virtual_file_stats(2, 2)
	-- 2(tempdb database id), 2(tempdb log file id)
	inner join
	sys.sysfiles
		on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

SELECT
	DB_NAME(database_id)    as	[Database Name],
	[Name]                  as	[Logical Name],
	[filename]              as	[File Name],
	[num_of_bytes_written] / 1024  as    [Space Used (in KB)]
FROM sys.dm_io_virtual_file_stats(2, NULL)
	-- 2(tempdb database id), NULL(all the files related to tempdb)
		inner join
	sys.sysfiles
		on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

SELECT
	DB_NAME(database_id)    as	[Database Name],
	[Name]                  as	[Logical Name],
	[filename]              as	[File Name],
	[size_on_disk_bytes] / 1024   as    [Total Size (in KB)],
	[num_of_bytes_written] / 1024 as    [Space Used (in KB)],
	([size_on_disk_bytes] - [num_of_bytes_written]) / 1024 as [Free space left (in KB)]
FROM sys.dm_io_virtual_file_stats(2, NULL)
	-- 2(tempdb database id), NULL(all the files related to tempdb)
      	inner join
      sys.sysfiles
      	on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

SELECT
	DB_NAME(database_id)				as [Database Name],
	SUM (user_object_reserved_page_count) * 8       as [User Objects (in KB)],
	SUM (internal_object_reserved_page_count) * 8   as [Internal Objects (in KB)],
	SUM (version_store_reserved_page_count) * 8     as [Version Store (in KB)],
	SUM (mixed_extent_page_count)*8                 as [Mixed Extent (in KB)]
FROM sys.dm_db_file_space_usage group by database_id

SELECT
	sys.dm_exec_sessions.session_id as [Session ID],
	DB_NAME(database_id) as [Database Name],
	host_name as [System Name],
	program_name as [Program Name],
	login_name as [User Name],
	status,
	cpu_time as [CPU Time (in milisec)],
	total_scheduled_time as [Total Scheduled Time (in milisec)],
	total_elapsed_time as    [Elapsed Time (in milisec)],
	(memory_usage * 8)      as [Memory Usage (in KB)],
	(user_objects_alloc_page_count * 8) as [Space Allocated for User Objects (in KB)],
	(user_objects_dealloc_page_count * 8) as [Space Deallocated for User Objects (in KB)],
	(internal_objects_alloc_page_count * 8) as [Space Allocated for Internal Objects (in KB)],
	(internal_objects_dealloc_page_count * 8) as [Space Deallocated for Internal Objects (in KB)],
	case is_user_process
		when 1      then 'user session'
		when 0      then 'system session'
	end         as [Session Type], row_count as [Row Count]
from sys.dm_db_session_space_usage
		inner join
	sys.dm_exec_sessions
		on sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

SELECT top 10
	transaction_id as [Transacton ID],
	transaction_sequence_num as [Transation Sequence Number],
	elapsed_time_seconds as [Elapsed Time (in sec)]
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC

select
	transaction_id as [Transacton ID],
	[name]      as [Transaction Name],
	transaction_begin_time as [Transaction Begin Time],
	datediff(mi, transaction_begin_time, getdate()) as [Elapsed Time (in Min)],
	case transaction_type
		when 1 then 'Read/write'
      	when 2 then 'Read-only'
      	when 3 then 'System'
      	when 4 then 'Distributed'
	end as [Transaction Type],
	case transaction_state
		when 0 then 'The transaction has not been completely initialized yet.'
		when 1 then 'The transaction has been initialized but has not started.'
		when 2 then 'The transaction is active.'
		when 3 then 'The transaction has ended. This is used for read-only transactions.'
		when 4 then 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
		when 5 then 'The transaction is in a prepared state and waiting resolution.'
		when 6 then 'The transaction has been committed.'
		when 7 then 'The transaction is being rolled back.'
		when 8 then 'The transaction has been rolled back.'
	end as [Transaction Description]
from sys.dm_tran_active_transactions

select
	host_name 			as [System Name],
	program_name 			as [Application Name],
	DB_NAME(database_id)    	as [Database Name],
	User_Name(user_id)            	as [User Name],
	connection_id 			as [Connection ID],
	sys.dm_exec_requests.session_id as [Current Session ID],
	blocking_session_id 		as [Blocking Session ID],
	start_time 			as [Request Start Time],
	sys.dm_exec_requests.status 	as [Status],
	command                         as [Command Type],
	(select text from sys.dm_exec_sql_text(sql_handle)) AS [Query Text],
	wait_type 			as [Waiting Type],
	wait_time 			as [Waiting Duration],
	wait_resource 			as [Waiting for Resource],
	sys.dm_exec_requests.transaction_id as [Transaction ID],
	percent_complete 		as [Percent Completed],
	estimated_completion_time 	as [Estimated Completion Time (in mili sec)],
	sys.dm_exec_requests.cpu_time 	as [CPU time used (in mili sec)],
	(memory_usage * 8)            	as [Memory Usage (in KB)],
	sys.dm_exec_requests.total_elapsed_time as [Elapsed Time (in mili sec)]
from sys.dm_exec_requests
		inner join
	sys.dm_exec_sessions
		on sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
where DB_NAME(database_id) = 'tempdb'

DBCC OPENTRAN
[ ( [ 'database_name' | database_id| 0 ] ) ]
    { [ WITH TABLERESULTS ]
      [ , [ NO_INFOMSGS ] ]
    }
]

   Create table #temptable (EmpName varchar(30), empID int)
   GO
   Begin Tran
	  Insert into #temptable values ('arun', 101)
	  drop table #temptable DBCC OPENTRAN('tempdb')

Kill [SPID]