Script to get Long Running queries, current running queries in SQL Server

Run following query to find longest running query using T-SQL.

SELECT DISTINCT TOP 10 t.TEXT QueryName,s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,ISNULL (s.total_elapsed_time / s.execution_count, 0)AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,ISNULL (s.execution_count /DATEDIFF(s, s.creation_time,GETDATE()), 0)AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle) t
ORDER BY s.max_elapsed_time DESC
GO

How to identify modified objects by using SQL Script

When working on quality assurance, staging and production environments, it is very important to have control over the changes made to the database definition – objects and design.This is a simple script to help you get a list of objects in your database with their creation and modification dates.

USE RajendraDB
GO
SELECT  so.name AS ObjectName,
OBJECT_NAME(so.parent_object_id) AS ParentObjectName,
SCHEMA_NAME(so.schema_id) AS SchemaName,
so.type_desc AS ObjectType,
so.create_date AS ObjectCreationDateTime,
so.modify_date AS ObjectModificationDateTime,
so.is_published AS IsPublished
FROM    sys.objects AS so
WHERE   so.is_ms_shipped = 0
ORDER BY CASE WHEN so.parent_object_id = 0 THEN so.object_id
ELSE so.parent_object_id
END,
so.schema_id,
so.type DESC,
so.modify_date DESC,
so.create_date DESC ;
GO

Script to check db size available space

USE TempDB
       SELECT
              a.FILEID,
              CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
              CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
              CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
              a.name as [DATABASENAME],
              a.FILENAME as [FILENAME]

       FROM      dbo.sysfiles a

Scriptto kill the existing connection

DECLARE @DBNAME VARCHAR(25)
       SET @DBNAME = DB_NAME()
       USE [master]
       DECLARE @KILLSPID VARCHAR(10)
       DECLARE @SPID INT
       DECLARE SPID_Cr CURSOR FOR SELECT SPID FROM Master..sysprocesses WHERE DBID = DB_ID(@DBNAME)
       OPEN SPID_Cr
       FETCH NEXT FROM SPID_Cr INTO @SPID
       WHILE @@FETCH_STATUS = 0
       BEGIN
        SET @KILLSPID = 'KILL '+ CAST(@SPID AS VARCHAR(10))
        Exec (@KILLSPID)
       FETCH NEXT FROM SPID_Cr INTO @SPID
       END
       CLOSE SPID_Cr
       DEALLOCATE SPID_Cr

SQL Script to find out Missing Index Script

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

SQL Script to find out currently executing query

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

SQL Script to find the list of all the jobs failed yesterday

One of the important task of any DBA is to find out all the jobs which are failed yesterday.

SELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name  AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs  T2
ON T1.job_id = T2.job_id
WHERE  T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)