SQL Server Agent Job Information

USE msdb
GO
CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
@freq_interval INT ,
@freq_subday_type INT ,
@freq_subday_interval INT ,
@freq_relative_interval INT ,
@freq_recurrence_factor INT ,
@active_start_date INT ,
@active_end_date INT,
@active_start_time INT ,
@active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT

IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7) BEGIN IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1)) SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', ' SELECT @loop = @loop + 1 END IF (RIGHT(@schedule_description, 2) = N', ') SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' ' END IF (@freq_type = 0x10) -- Monthly BEGIN SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month ' END IF (@freq_type = 0x20) -- Monthly Relative BEGIN SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the ' SELECT @schedule_description = @schedule_description + CASE @freq_relative_interval WHEN 0x01 THEN N'first ' WHEN 0x02 THEN N'second ' WHEN 0x04 THEN N'third ' WHEN 0x08 THEN N'fourth ' WHEN 0x10 THEN N'last ' END + CASE WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description
END

GO

/**** Once created the above function, we can get the output of the job using below query ********/

SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time,
dbo.udf_schedule_description(dbo.sysschedules.freq_type,
dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type,
dbo.sysschedules.freq_subday_interval,
dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor,
dbo.sysschedules.active_start_date,
dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time,
dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

SQL Server Jobs Step Query Findings

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%KEYWORD_SEARCH%'

Resource BottleNeck - Investigation Queries

select * from sys.dm_os_wait_stats where wait_type = 'threadpool';
go
select top 10 *
from sys.dm_os_wait_stats
where wait_type not in --remove common waits to identify worst bottlenecks
(
'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'
)
order by wait_time_ms desc




SELECT
record.value('(Record/@id)[1]', 'int') as id,
record.value('(Record/@type)[1]', 'varchar(50)') as type,
record.value('(Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(50)') as RecordType,
record.value('(Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(50)') as RecordSource,
record.value('(Record/ConnectivityTraceRecord/Spid)[1]', 'int') as Spid,
record.value('(Record/ConnectivityTraceRecord/SniConnectionId)[1]', 'uniqueidentifier') as SniConnectionId,
record.value('(Record/ConnectivityTraceRecord/SniProvider)[1]', 'int') as SniProvider,
record.value('(Record/ConnectivityTraceRecord/OSError)[1]', 'int') as OSError,
record.value('(Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') as SniConsumerError,
record.value('(Record/ConnectivityTraceRecord/State)[1]', 'int') as State,
record.value('(Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(50)') as RemoteHost,
record.value('(Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(50)') as RemotePort,
record.value('(Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(50)') as LocalHost,
record.value('(Record/ConnectivityTraceRecord/LocalPort)[1]', 'varchar(50)') as LocalPort,
record.value('(Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') as RecordTime,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/TotalLoginTimeInMilliseconds)[1]', 'bigint') as TotalLoginTimeInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTaskEnqueuedInMilliseconds)[1]', 'bigint') as LoginTaskEnqueuedInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkWritesInMilliseconds)[1]', 'bigint') as NetworkWritesInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkReadsInMilliseconds)[1]', 'bigint') as NetworkReadsInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/SslProcessingInMilliseconds)[1]', 'bigint') as SslProcessingInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/SspiProcessingInMilliseconds)[1]', 'bigint') as SspiProcessingInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTriggerAndResourceGovernorProcessingInMilliseconds)[1]', 'bigint') as LoginTriggerAndResourceGovernorProcessingInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') as TdsInputBufferError,
record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') as TdsOutputBufferError,
record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') as TdsInputBufferBytes,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'int') as PhysicalConnectionIsKilled,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') as DisconnectDueToReadError,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'int') as NetworkErrorFoundInInputStream,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int') as ErrorFoundBeforeLogin,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') as SessionIsKilled,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') as NormalDisconnect,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]', 'int') as NormalLogout
FROM
( SELECT CAST(record as xml) as record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY') as tab
order by RecordTime desc


By seeing tons of "loginTimers" and seems like have network issue from network card to switch.
Any network issue between SQL Server and Domain controller shouldn't be ruled out.
• http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

Dependency Findings for Objects

SELECT ReferencingObjectType = o1.type, ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name, ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name, ReferencedObjectType = o2.type FROM sys.sql_expression_dependencies ed INNER JOIN sys.objects o1 ON ed.referencing_id = o1.object_id INNER JOIN sys.objects o2 ON ed.referenced_id = o2.object_id WHERE o1.type in ('P','TR','V', 'TF') ORDER BY ReferencingObjectType, ReferencingObject -- Method 1: Using sp_depends sp_depends 'dbo.First' GO -- Method 2: Using information_schema.routines SELECT * FROM information_schema.routines ISR WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0 GO -- Method 3: Using DMV sys.dm_sql_referencing_entities SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT'); GO

Fragmentation Percentage Check

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC

Locking on Table

SELECT req_spid AS 'spid',

DB_NAME(rsc_dbid) AS 'Database',

OBJECT_NAME(rsc_objid) AS 'Name',

rsc_indid AS 'Index',

rsc_text AS 'Description',

ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'

WHEN rsc_type = 2 THEN 'Database'

WHEN rsc_type = 3 THEN 'File'

WHEN rsc_type = 4 THEN 'Index'

WHEN rsc_type = 5 THEN 'Table'

WHEN rsc_type = 6 THEN 'Page'

WHEN rsc_type = 7 THEN 'Key'

WHEN rsc_type = 8 THEN 'Extent'

WHEN rsc_type = 9 THEN 'RID (Row ID)'

WHEN rsc_type = 10 THEN 'Application'

ELSE 'Unknown'

END,

Status = CASE WHEN req_status = 1 THEN 'Granted'

WHEN req_status = 2 THEN 'Converting'

WHEN req_status = 3 THEN 'Waiting'

ELSE 'Unknown'

END,

OwnerType =

CASE WHEN req_ownertype = 1 THEN 'Transaction'

WHEN req_ownertype = 2 THEN 'Cursor'

WHEN req_ownertype = 3 THEN 'Session'

WHEN req_ownertype = 4 THEN 'ExSession'

ELSE 'Unknown'

END,

LockRequestMode =

CASE WHEN req_mode = 0 THEN 'No access '

WHEN req_mode = 1 THEN 'Sch-S (Schema stability)'

WHEN req_mode = 2 THEN 'Sch-M (Schema modification)'

WHEN req_mode = 3 THEN 'S (Shared)'

WHEN req_mode = 4 THEN 'U (Update)'

WHEN req_mode = 5 THEN 'X (Exclusive)'

WHEN req_mode = 6 THEN 'IS (Intent Shared)'

WHEN req_mode = 7 THEN 'IU (Intent Update)'

WHEN req_mode = 8 THEN 'IX (Intent Exclusive)'

WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)'

WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)'

WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)'

WHEN req_mode = 12 THEN 'BU. (Bulk operations)'

WHEN req_mode = 13 THEN 'RangeS_S'

WHEN req_mode = 14 THEN 'RangeS_U'

WHEN req_mode = 15 THEN 'RangeI_N'

WHEN req_mode = 16 THEN 'RangeI_S'

WHEN req_mode = 17 THEN 'RangeI_U'

WHEN req_mode = 18 THEN 'RangeI_X'

WHEN req_mode = 19 THEN 'RangeX_S'

WHEN req_mode = 20 THEN 'RangeX_U'

WHEN req_mode = 21 THEN 'RangeX_X'

ELSE 'Unknown'

END

FROM master.dbo.syslockinfo

GO

SQL Server Check

SELECT

      SERVERPROPERTY('servername') as ServerName,

      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerName,

      SERVERPROPERTY('edition') as Edition,

      SERVERPROPERTY('productlevel') as [Prod.Level],

      SERVERPROPERTY('productversion') as ProductVersion,

      SERVERPROPERTY('IsClustered') as IsClustered,

      SERVERPROPERTY('SqlCharSet') as SqlCharSet,

      SERVERPROPERTY('SqlCharSetName') as SqlCharSetName,

      SERVERPROPERTY('SqlSortOrder') as SqlSortOrder,

      SERVERPROPERTY('SqlSortOrderName') as SqlSortOrderName

Host Gator Support URLS

http://support.hostgator.com/articles/plesk/plesk-10/plesk-10-client-side/how-to-manage-your-domains-dns-zones-plesk-10

Changing SQL Server's Default Snapshot Folder for Replication

USE [DISTRIBUTION]

GO

DECLARE @result nvarchar(255)

exec sp_helpdistributor @directory = @result OUTPUT

PRINT @result

GO

SELECT value FROM ::fn_listextendedproperty('SnapshotFolder', 'user',

'dbo', 'table', 'UIProperties', null, null)

go

SELECT * FROM msdb.dbo.MSdistpublishers

GO

/*Changing SQL Server's Default Snapshot Folder for Replication*/

USE distribution   

exec sp_changedistpublisher

    @publisher = 'FL-WS-CON-CC03',

    @property = 'working_directory',

    @value = '\\FL-WS-CON-CC03\Share2'

Reference:http://www.mssqltips.com/sqlservertip/2785/changing-sql-servers-default-snapshot-folder-for-replication/

TEMP DB File Creation based on CPU

USE [master]

GO
DECLARE @cpu_count int,@file_count int,@logical_name sysname,@file_name nvarchar(520),@physical_name nvarchar(520),@size int,@max_size int,@growth int,@alter_command nvarchar(max)SELECT @physical_name = physical_name,@size = size / 128, @max_size
= max_size / 128,@growth = growth / 128FROM tempdb.sys.database_filesWHERE name = 'temp'select * from tempdb.sys.database_filesSELECT @file_count = COUNT(*)FROM tempdb.sys.database_filesWHERE type_desc = 'ROWS'SELECT @cpu_count = cpu_countFROM sys.dm_os_sys_infoWHILE @file_count < @cpu_count -- Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.BEGINSELECT @logical_name = 'tempdb' + CAST(@file_count AS nvarchar)SELECT @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' + @file_name + ''', SIZE = ' + CAST(@size AS nvarchar) + 'MB, MAXSIZE = ' + CAST(@max_size AS nvarchar) + 'MB, FILEGROWTH = ' + CAST(@growth AS nvarchar) + 'MB )'PRINT @alter_command
--EXEC sp_executesql @alter_commandSELECT @file_count = @file_count + 1
END


Exec xp_msver 'processorcount' - Number of processor to verify

Memory Grants Pending

SELECT cntr_value AS [Memory Grants Outstanding]

FROM sys.dm_os_performance_countersWHERE OBJECT_NAME = 'MSSQL$ISQL01:Memory Manager'AND counter_name = 'Memory Grants Outstanding'SELECT OBJECT_NAME,cntr_value AS [Memory Grants Pending]FROM sys.dm_os_performance_countersWHERE OBJECT_NAME = 'MSSQL$ISQL01:Memory Manager'AND counter_name = 'Memory Grants Pending'

How can I reduce the SQL Wait Type - RESOURCE SEMAPHORE?
SQL Server Books online  defines the RESOURCE SEMAPHORE SQL wait stat type as “Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts”
RESOURCE SEMAPHORE usually correlates with poor response time to all the users ,  so a high percentage of RESOURCE SEMAPHORE wait time is a critical indicator.
The Resource Semaphore manager reserves memory or throttles the queries if the memory requested by the queries exceeds the memory capacity.
Memory intensive queries  - using sorting and hashing are the main underlying reasons.  The GROUP BY clause and ORDER BY clause   use sorting and hashing.
A built in feature of SQL server as it doesn’t want to overcommit queries beyond the memory capacity. SQL Server checks to see if memory grant is necessary. If not , the query executes immediately. If there isn’t enough memory , the query queues.
How To Troubleshoot : RESOURCE SEMAPHORE
Step 1)  Capture and fine tune slow running queries  using SQL Server query tuning techniques
Step 2)  If step 1 doesn’t help, look to distribute queries across a wider time frame.
Step 3)  Use the DMV - sys.dm_exec_query_resource_semaphores
Step 4)  Use the DMV sys.dm_exec_query_memory_grants - especially if experiencing query timeouts. Read more on How to troubleshoot query timeouts
Displays queries with memory grant – or waiting for a memory grant. When RESOURCE_SEMAPHORE wait stat is reported – I usually check this DMV as an immediate check.
Step 5)  Cross check the wait type RESOURCE SEMAPHORE with the SQL Server perfmon counters
Step 5.1)          Memory Grants Pending  - If greater than 0 , there is a problem
Step 5.2)          Memory Grants Outstanding – look for a higher than normal value
Use these queries to access the current Memory Grants Pending  values and Memory Grants Outstanding.

Reference: http://www.sqlserver-dba.com/2011/06/sql-wait-type-resource-semaphore-and-how-to-reduce-it.html

TEMPDB Usage

To find the usage of tempdb:

select
* from sys.dm_db_session_space_usage spujoin sys.dm_exec_sessions s on s.session_id = spu.session_idjoin sys.dm_exec_requests r on s.session_id = r.session_idcross apply sys.dm_exec_sql_text(sql_handle) t order by internal_objects_alloc_page_count desc-----------------------------------


SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;
--The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] FROM sys.dm_db_file_space_usage;
SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed

Tempdb
session File usage sys.dm_db_session_space_usage : Returns the number of pages allocated and deallocated by each session for the database.sys.dm_exec_sessions: Gives details about the sessions.SELECTsys.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_usageINNER joinsys.dm_exec_sessionsON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

GO--A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.SELECTtransaction_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
GO--Long running Queries--sys.dm_exec_requests : Returns information regarding the requests made to the database server.SELECTHOST_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_requestsINNER joinsys.dm_exec_sessionsON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_idWHERE DB_NAME(database_id) = 'tempdb'GO
select * from sys.dm_db_session_space_usage spujoin sys.dm_exec_sessions s on s.session_id = spu.session_idjoin sys.dm_exec_requests r on s.session_id = r.session_idcross apply sys.dm_exec_sql_text(sql_handle) t order by internal_objects_alloc_page_count desc--------------------------------------------------------------------------------