How to: Get All Databases Index Fragmentation Details

This SQL script will provide you the Index fragmentation details for all databases. It would be helpful while scheduling or determining maintenance task of re-indexing.

This SQL script will provides you all the databases names, object names, index id, index name, average fragmentation percent, index type & action required.

The “Action required column” on the basis of fragmentation value using sys.dm_db_index_physical_stats DMV.

If average fragmentation value is >5 & 30 index should rebuild, those value is less than 5 then no result is require.

IF EXISTS (	SELECT * 
	FROM [tempdb].[dbo].[sysobjects] 
	WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
	DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details] 
GO

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
	[DatabaseName] 					[nvarchar] (100) NULL,
	[ObjectName]					[nvarchar] (100) NULL,
	[Index_id] 						INT,
	[indexName] 					[nvarchar] (100) NULL,
	[avg_fragmentation_percent]		float NULL,
	[IndexType] 					[nvarchar] (100) NULL,
	[Action_Required] 				[nvarchar] (100) default 'NA' 
) ON [PRIMARY]

DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)

DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlQuery = '
	USE [' + @dbname + '];

	IF EXISTS
	(
		SELECT compatibility_level 
		FROM sys.databases 
		WHERE 
			name  = N'''+ @dbname +'''
			AND compatibility_level >= 90
	)
	BEGIN
		INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details] 
		(
			DatabaseName
			, ObjectName
			, Index_id
			, indexName
			, avg_fragmentation_percent
			, IndexType
		) 
		SELECT 
			db_name() as DatabaseName
			, OBJECT_NAME (a.object_id) as ObjectName
			, a.index_id, b.name as IndexName
			, avg_fragmentation_in_percent
			, index_type_desc 
		FROM 
			sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
			JOIN sys.indexes AS b
		ON 
			a.object_id = b.object_id 
			AND a.index_id = b.index_id 
		WHERE
			b.index_id <> 0 
			AND avg_fragmentation_in_percent <> 0
	END;'
	
	EXEC sp_executesql @sqlQuery
	
FETCH NEXT FROM dbcursor
INTO @dbname
END

CLOSE dbcursor
Deallocate dbcursor

-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details] 
SET Action_Required = 'Rebuild' 
WHERE avg_fragmentation_percent >30  
GO 

-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details] 
SET Action_Required = 'Reorganize' 
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5 
GO

-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details] 
ORDER BY databasename

NOTE: This SQL script will work on both SQL Server 2005, 2008 & 2008R2. It won’t work for SQL 2000 as DMV’s are involved in this script.

Original scripts adopted from : http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Index-a68ed3c5

How to: Find SQL Server Login group members (BUILTIN\Administrators)

The following query will list all administrators with sysadmin access in your SQL Server.

SELECT name, sysadmin FROM sys.syslogins 
WHERE sysadmin=1

You will able to see the BUILTIN\Administrators login in the list, if you granted it with sysadmin privilege. The above query not just display the individual logins but also the login groups have admin access. There are multiple login members associated with the login group, the following query is useful to find the members in the login group.

EXEC xp_logininfo 'BUILTIN\Administrators', 'members'

You will see a list of logins with account names and type. If you execute the above script again, replace the ‘BUILTIN\Administrators’ with any account name with group type, you will find another list of accounts under the group. The total login id having admin access.

How to: Check Databases That Have Not Had a Full Backup in x Days

Below is a nice quick sanity check showing all the dbs that have NOT been backed up for X days or have never been backed up at all.

DECLARE @num_of_days INT
SET @num_of_days = 1 

SELECT
	A.name
	, MAX(B.backup_finish_date) AS 'LastBackupDateTime'
FROM
	master.dbo.sysdatabases A WITH(NOLOCK) LEFT OUTER JOIN
	msdb.dbo.backupset B WITH(NOLOCK) ON A.name = B.database_name
WHERE (B.TYPE = 'D' OR B.TYPE IS NULL)
GROUP BY A.name
HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR MAX(B.backup_finish_date) IS NULL) 
ORDER BY A.name

Reference :
SQL DBs That Have Not Had A Full Backup in X Days

How to: Check Failed SQL Server Agent Jobs for Last 7 Days

-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 

-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_status = 0 -- Failure 
         AND h.run_date > @FinalDate 
ORDER BY h.instance_id DESC 

Scripts obtain from:
http://www.mssqltips.com/sqlservertip/1054/failed-sql-server-agent-jobs/

How to: Find The Largest Objects in SQL Server Database

How to find the largest objects in the SQL Server database?

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id) 

Of course, you can use another ordering criteria, e.g.

ORDER BY SUM(p.rows) DESC

to get the tables with the most rows, or

ORDER BY SUM(a.total_pages) DESC

to get the tables with the most pages (8K blocks) used.

What is: Detect Virtual Log Files (VLF) in LDF

Multiple small Virtual Log Files commonly known as VLFs together make an LDF file. The writing of the VLF is sequential and resulting in the writing of the LDF file is sequential as well. This leads to another talk that one does not need more than one log file in most cases.

However, in short, you can use following DBCC command to know how many Virtual Log Files or VLFs are present in your log file.

DBCC LOGINFO

After you execute the above query you can see the column which is marked as 2 which means it is active VLF and the one with 0 which is inactive VLF.

How to: Find Currently Running Queries

You can use sys.dm_exec_requests DMV to list all requests. This view also contains a hash map of the SQL text – sql_handle, which can be passed to DMF sys.dm_exec_sql_text to get the query text:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text
FROM        sys.dm_exec_requests REQUEST
CROSS APPLY
            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

In case you are trying to find the query which is causing blocking, you can find the blocking session id by using sp_who or sp_who3.

Once you locate the blocking session id, it can be used to filter above results:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text
FROM        sys.dm_exec_requests REQUEST
CROSS APPLY
            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY
WHERE REQUEST.session_id = 58

Query to Identify Current Activity

This query will shows you a significant amount of real-time data including active cursor data and query plans.

This is a current activity query used to identify what processes are currently running on the processors. Use this query to find what user is running a large report or process and consuming system resources. This is a snapshot view of current activity. You should execute the query several times to identify if a query is increasing it’s I/O or CPU time.

SELECT r.session_id,
       se.host_name,
       se.login_name,
       Db_name(r.database_id) AS dbname,
       r.status,
       r.command,
       r.cpu_time,
       r.total_elapsed_time,
       r.reads,
       r.logical_reads,
       r.writes,
       s.text AS sql_text,
       p.query_plan AS query_plan,
       SQL_CURSORSQL.text,
       SQL_CURSORPLAN.query_plan
FROM   sys.dm_exec_requests r
       INNER JOIN sys.dm_exec_sessions se
         ON r.session_id = se.session_id
       OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
       OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
       OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS
       OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL
                   LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS
                     ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle
       OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN
WHERE  r.session_id &lt;&gt; @@SPID
       AND se.is_user_process = 1