How to Retrieve Disabled SQL Server Jobs

In SQL Server, SQL Agent Jobs play a crucial role in automating database maintenance tasks, backups, and other scheduled activities. However, sometimes jobs may be disabled due to administrative decisions, troubleshooting needs, or unexpected issues. As a DBA, it’s essential to track and manage these disabled jobs to ensure critical processes are not inadvertently left inactive.

In this blog post, we will look at a simple yet effective SQL query to retrieve a list of all disabled jobs in SQL Server.

Query to Fetch Disabled Jobs Below is a straightforward SQL query to identify all jobs that are currently disabled:

SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name;

Notes:

• msdb.dbo.sysjobs: This system table stores metadata for all SQL Server Agent jobs.

• enabled = 0: The enabled column indicates whether a job is active (1) or disabled (0). By filtering on 0, we retrieve only disabled jobs.

Automating the Monitoring Process To proactively monitor disabled jobs, consider setting up an automated alert or report. Here’s an approach using SQL Server Agent:

Create a SQL Server Agent Job

• Schedule the above query to run at regular intervals.
• Store the results in a table or send an email alert.

Using Database Mail for Notifications

DECLARE @body NVARCHAR(MAX);
SET @body = (SELECT STRING_AGG(name, ', ') FROM msdb.dbo.sysjobs WHERE enabled = 0);

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'dba_team@example.com',
    @subject = 'Disabled SQL Server Jobs Alert',
    @body = 'The following jobs are currently disabled: ' + @body;

Monitoring SQL Server Backup and Restore Progress

Managing database backups and restores is a critical aspect of SQL Server administration. When dealing with large databases, it becomes essential to track the progress of ongoing backup and restore operations to estimate completion time and monitor performance. This blog post provides a structured SQL script to monitor these operations effectively.

Why Monitor Backup and Restore Progress?

• Ensures visibility into long-running operations

• Helps estimate completion time for better planning

• Identifies potential performance bottlenecks

• Prevents unnecessary disruptions by avoiding premature interventions

SQL Script to Monitor Backup and Restore Progress Use the following SQL script to track the percentage completion, elapsed time, and estimated completion time of backup and restore operations in SQL Server:

SELECT

    r.session_id AS [Session_Id],

    r.command AS [Command],

    CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete],

    GETDATE() AS [Current Time],

    CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time],

    CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min],

    CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min],

    CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated 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)

    )) AS [Statement Text]

FROM sys.dm_exec_requests r

WHERE r.command LIKE 'RESTORE%'

   OR r.command LIKE 'BACKUP%'

   OR r.command LIKE 'DbccFilesCompact'

   OR r.command LIKE 'DbccSpaceReclaim';

How to Verify SQL Server Backups with a Simple Query

Ensuring database backups are completed successfully is a critical task for every SQL Server DBA. In this post, I’ll share a SQL script that quickly checks the latest full backup status of all databases on a SQL Server instance.

SQL Script to Verify Backup Completion

• The following script provides:
• The server name
• The database name
• The last backup date
• The backup status (Completed/Not Taken)
• The backup file name

SET NOCOUNT ON  

SELECT 'SERVER NAME : ' + @@SERVERNAME  

SELECT  
    SUBSTRING(s.name, 1, 40) AS 'Database Name',  
    CAST(b.backup_start_date AS CHAR(25)) AS 'Last Backup Date',  
    CASE  
        WHEN b.backup_start_date > DATEADD(dd, -1, GETDATE()) THEN 'Backup Completed'  
        WHEN b.backup_start_date > DATEADD(dd, -7, GETDATE()) THEN 'Not taken'  
        ELSE 'Not taken'  
    END AS 'Status',  
    SUBSTRING(m.physical_device_name, 1, 100) AS 'Backup File Name'  
FROM master..sysdatabases s  
LEFT OUTER JOIN msdb..backupset b  
    ON s.name = b.database_name  
    AND b.backup_start_date = (  
        SELECT MAX(backup_start_date)  
        FROM msdb..backupset  
        WHERE database_name = b.database_name  
        AND type = 'D' -- Full database backups only  
    )  
LEFT OUTER JOIN msdb..backupmediafamily m  
    ON m.media_set_id = b.media_set_id  
    AND m.physical_device_name = (  
        SELECT MAX(physical_device_name)  
        FROM msdb..backupmediafamily  
        WHERE media_set_id = b.media_set_id  
    )  
WHERE s.name <> 'tempdb'  
ORDER BY s.name  


The script retrieves the most recent full backup (type = 'D') for each database.
It checks if the last backup was completed within the last 24 hours.
If no backup is found in the last 24 hours, it flags it as Not Taken.
The output includes the backup file name for easy tracking.

How to Remove Stuck SQL Server Services After a Failed Cluster Installation

Problem:
A friend encountered an issue while installing SQL Server in a clustered environment. The installation stopped midway, and he was unable to remove SQL Server using Add/Remove Programs or the SQL Server Setup. Even after manually deleting registry entries and folders, SQL Server services were still visible in Services.msc.

Solution:
If you face a similar issue, follow these steps to completely remove SQL Server services:

Step 1: Delete SQL Server Services Using SC Command

Open Command Prompt as Administrator and run the following commands:

sc delete sqlserveragent
sc delete mssqlserver

This will remove the SQL Server Agent and SQL Server (MSSQLSERVER) services. If you are using a named instance, replace mssqlserver with mssql$<InstanceName>.

Step 2: Delete SQL Server Services from Registry

Since the services might still be registered in the Windows registry, follow these steps to clean up:

1. Press Win + R, type regedit, and hit Enter.
2. Navigate to the following path:

   
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
   

3. Look for entries related to SQL Server, such as:
   • MSSQLSERVER
   • SQLSERVERAGENT
   • MSSQL$InstanceName (for named instances)
4. Right-click each SQL-related entry and Delete them.
5. Close Registry Editor and restart the system.

After performing these steps, the SQL Server services should be completely removed from the system.

Granting VIEW DEFINITION and EXECUTE Permissions on All Stored Procedures in SQL Server

Managing permissions effectively in SQL Server is crucial to maintaining security and access control. One common requirement is to grant users or groups the ability to view the definitions of stored procedures or execute them without giving broader privileges.

In this post, we'll share SQL scripts that automate granting VIEW DEFINITION and EXECUTE permissions on all stored procedures in a given database.

Granting VIEW DEFINITION Access

The VIEW DEFINITION permission allows users to see the stored procedure’s definition without modifying it. Use the following script to generate and execute GRANT VIEW DEFINITION statements for all stored procedures:

SELECT 'GRANT VIEW DEFINITION ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu]'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

Note: Replace [Domain\\Ajeyudu] with the actual user or group to whom you want to grant permissions.

After running the above query, copy the generated GRANT VIEW DEFINITION statements and execute them.

Granting EXECUTE Access

To allow users to execute all stored procedures without requiring other permissions, use the script below:

SELECT 'GRANT EXECUTE ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu]'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

Run the generated commands to apply EXECUTE permissions to the specified user or group.

Automating the Permission Granting

For larger databases, automating permission assignments via a dynamic SQL approach can be beneficial. Use the following script to execute the generated GRANT statements dynamically:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'GRANT VIEW DEFINITION ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu];' + CHAR(13)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

EXEC sp_executesql @sql

Similarly, modify the script for EXECUTE permissions:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'GRANT EXECUTE ON ' + QUOTENAME(specific_schema) + '.' + QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu];' + CHAR(13)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_type = 'PROCEDURE'

EXEC sp_executesql @sql

By using dynamic SQL, these scripts can directly apply permissions without requiring manual execution of individual statements.