Display the list of indexes and columns for a given table

You might find yourself in a situation where you have to analyze the indexes for a given table and want to know which columns are part of which index. Such a view can help you quickly analyze which indexes are reduntant and can probably be dropped.

Use the below code to accomplish that. This code gives you the result set as mentioned below:

1. Rows as the list of indexes. 

2. Columns as the list of columns in that table 

3. Result set in each row displays 1 for each column that is included in that index, else displays 0

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- Step 1: Construct the list of columns for the dbo.Associate table using FOR XML PATH

SELECT @Columns = STUFF((

    SELECT ', MAX(CASE WHEN c.name = ''' + c.name + ''' THEN 1 ELSE 0 END) AS ' + QUOTENAME(c.name)

    FROM sys.columns c

    JOIN sys.tables t ON c.object_id = t.object_id

    WHERE t.name = 'TABLENAME' AND t.schema_id = SCHEMA_ID('dbo') --Replace TABLENAME here

    FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- Step 2: Construct the dynamic SQL query

SET @SQL = '

SELECT

    i.name AS IndexName, ' + @Columns + '

FROM

    sys.indexes i

    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

    JOIN sys.tables t ON t.object_id = i.object_id

WHERE

    t.name = ''Associate'' AND t.schema_id = SCHEMA_ID(''dbo'')

GROUP BY

    i.name

ORDER BY

    i.name';

-- Step 3: Execute the dynamic SQL query

EXEC sp_executesql @SQL;

Failed to obtain the resource handle for cluster resource with name or ID 'xxxxxxxxxxxxxxx' error on Availability Group databases

We recently had failures for our Backup Maintenance Plan (performing T-Log backups) and it would fail with the below error message:

Message

Error: Maintenance plan 'Backups'.Failed to obtain the resource handle for cluster resource with name or ID '7751d61b-6354-4b73-9f9c-7b57b60a3a64'. Cluster service may not be running or may not be accessible in its current state, or the specified cluster resource name or ID is invalid.  Otherwise, contact your primary support provider

If you faced the above error message, it most likely that the backup job is unable to figure out where the backup needs to be performed for a db that is part of Availability Groups. The function sys.fn_hadr_backup_is_preferred_replica is the one that is used to used to determine if the current replica is the preferred backup replica. One more dbs could be erroring out when this function is run against them, and that is the reason the maintenance plan fails.

To check which db is causing the error, run this query

select sys.fn_hadr_backup_is_preferred_replica('DBNAME')

If you have multiple DBs, and would like to run this above query against all of them at once, use the below code. The result is not pretty looking, but you will get to know which db is erroring out for this query.

EXEC master..sp_MSForeachdb

'USE [?]

IF DB_ID(''?'') > 4

select DB_NAME()

select sys.fn_hadr_backup_is_preferred_replica(''?'')'

Once you figured out which db is erroring out, remove it from the Availability Group and re-run your maintenance plan. It should run successfully now!

Error when perofrming SQL Install - 'the volume that contains sql server data directory does not belong to the cluster group'

​When performing SQL Install on a Failover cluster, there might be an error :

'the volume that contains sql server data directory does not belong to the cluster group'. Watch out for another error in the screen preceding this error in the Install wizard, there might be another error:

The mount point disk volume '\\?\Volume{fdba841d-9c06-11df-b213-0017a4770490}\' mounted at 'I:\$RECYCLE.BIN\S-1-5-21-1214440339-854245398-1801674531-11362\$RXP8E68\' is not a clustered resource.To continue, add the resource to the cluster, or remove the mount point to cluster the shared disk.

If you faced both these errors, read the below

​The reason for this error is that the Recycle Bin on the root drive is not empty. There could be leftover folders in the Recycle Bin from any previous deletions or cleanup. To resolve the issue, empty the recycle bin on that root letter, and attempt the install again. If that doesn't work, format the root drive and attempt the install again.

Change the recovery model of all the databases

If you ever find yourself in a situation where you have to edit the recovery model for multiple databases, here is a script to do that easily. The below script detects all the dbs that are in ‘simple’ recovery model and alters that setting to ‘full’ recovery model. You can modify the script to ‘bulk logged’ recovery model or alter the order of recovery models if required.

declare @name sysname,

         @recovery_model_desc nvarchar(120),

         @script nvarchar(500),

         @full_backup_count int

 

 select @full_backup_count = count(*) from sys.databases where recovery_model_desc = 'simple'

 print 'There are ' + cast(@full_backup_count as varchar) + ' with FULL backup set'

 

 declare db cursor

     for select name, recovery_model_desc from sys.databases where recovery_model_desc = 'simple'and  database_id>4

     for read only

 

 open db

 fetch next from db into @name, @recovery_model_desc

 while @@fetch_status = 0

 begin

     set @script = 'alter database [' + @name + '] set recovery FULL'

     exec sp_executesql @script

     print 'Done with ' + @name

     fetch next from db into @name, @recovery_model_desc

 end

 

 close db

 deallocate db

Get the last run information for SQL jobs

Did you ever have to find out the ‘last run’ information for all the SQL jobs or a particular job on a SQL Instance? Here is a script that will help you. For one specific job, enter the name in the commented part of the code below:

SELECT

    j.name,

    h.run_status,

    durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),

        ' ','0'),4,0,':'),7,0,':'),

    [start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '

        + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),

        ' ','0'),3,0,':'),6,0,':'))

FROM

    msdb.dbo.sysjobs AS j

INNER JOIN

    (

        SELECT job_id, instance_id = MAX(instance_id)

            FROM msdb.dbo.sysjobhistory

            GROUP BY job_id

    ) AS l

    ON j.job_id = l.job_id

INNER JOIN

    msdb.dbo.sysjobhistory AS h

    ON h.job_id = l.job_id

    AND h.instance_id = l.instance_id

--     where j.name like '%YOUR JOB NAME%'

ORDER BY

    CONVERT(INT, h.run_duration) DESC,

    [start_date] DESC;

Get the job schedule information for all the jobs or a particular one

Did you ever have to find out the job schedule information for all the SQL jobs or a particular job on a SQL Instance, and wanted to look at them in just one screen rather than going into properties for each and every job? Here is a script that will help you. For one specific job, enter the name in the last line of the code below that is commented out

SELECT [JobName] = [jobs].[name]

             ,[Category] = [categories].[name]

             ,[Owner] = SUSER_SNAME([jobs].[owner_sid])

             ,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END

             ,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END

             ,[Description] = [jobs].[description]

             ,[Occurs] =

                           CASE [schedule].[freq_type]

                                 WHEN   1 THEN 'Once'

                                 WHEN   4 THEN 'Daily'

                                 WHEN   8 THEN 'Weekly'

                                 WHEN  16 THEN 'Monthly'

                                 WHEN  32 THEN 'Monthly relative'

                                 WHEN  64 THEN 'When SQL Server Agent starts'

                                 WHEN 128 THEN 'Start whenever the CPU(s) become idle'

                                 ELSE ''

                           END

             ,[Occurs_detail] =

                           CASE [schedule].[freq_type]

                                 WHEN   1 THEN 'O'

                                 WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'

                                 WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +

                                        LEFT(

                                               CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END +

                                               CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END +

                                               CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END +

                                               CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END +

                                               CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END +

                                               CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END +

                                               CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END ,

                                               LEN(

                                                     CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END +

                                                     CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END +

                                                     CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END +

                                                     CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END +

                                                     CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END +

                                                     CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END +

                                                     CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END

                                               ) - 1

                                        )

                                 WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'

                                 WHEN  32 THEN 'The ' +

                                               CASE [schedule].[freq_relative_interval]

                                                     WHEN  1 THEN 'First'

                                                     WHEN  2 THEN 'Second'

                                                     WHEN  4 THEN 'Third'

                                                     WHEN  8 THEN 'Fourth'

                                                     WHEN 16 THEN 'Last'

                                               END +

                                               CASE [schedule].[freq_interval]

                                                     WHEN  1 THEN ' Sunday'

                                                     WHEN  2 THEN ' Monday'

                                                     WHEN  3 THEN ' Tuesday'

                                                     WHEN  4 THEN ' Wednesday'

                                                     WHEN  5 THEN ' Thursday'

                                                     WHEN  6 THEN ' Friday'

                                                     WHEN  7 THEN ' Saturday'

                                                     WHEN  8 THEN ' Day'

                                                     WHEN  9 THEN ' Weekday'

                                                     WHEN 10 THEN ' Weekend Day'

                                               END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'

                                 ELSE ''

                           END

             ,[Frequency] =

                           CASE [schedule].[freq_subday_type]

                                 WHEN 1 THEN 'Occurs once at ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')

                                 WHEN 2 THEN 'Occurs every ' +

                                                     CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')

                                 WHEN 4 THEN 'Occurs every ' +

                                                     CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')

                                 WHEN 8 THEN 'Occurs every ' +

                                                     CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +

                                                     STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')

                                 ELSE ''

                           END

             ,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])

             ,[Next_Run_Date] =

                           CASE [jobschedule].[next_run_date]

                                 WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')

                                 ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +

                                         STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))

                           END

FROM   [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)

              LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)

                            ON [jobs].[job_id] = [jobschedule].[job_id]

              LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)

                            ON [jobschedule].[schedule_id] = [schedule].[schedule_id]

              INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)

                            ON [jobs].[category_id] = [categories].[category_id]

              LEFT OUTER JOIN

                                 (      SELECT [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +

                                                                                                           (([run_duration] % 10000) / 100 * 60) +

                                                                                                            ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])

                                        FROM   [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)

                                        WHERE  [step_id] = 0

                                        GROUP BY [job_id]

                                  ) AS [jobhistory]

                            ON [jobhistory].[job_id] = [jobs].[job_id]

--                         where jobs.name='YOUR JOB NAME';

GO