Thursday, February 14, 2019

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

at 1 comment:

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;

at 1 comment:

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
at No comments:
Subscribe to: Posts (Atom)

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 ...