SQL Server DBA: Database in Recovery Pending state

 I have recently encountered an issue where one of the databases suddenly moved to Recovery Pending status after SQL Server fail-over.

I checked the database and it is in Recovery Pending status. Recovery Pending means that recovery cannot be started. Until the cause is fixed, recovery cannot run and the database cannot come online. I then checked the drives in the server and all drives are accessible.Then

Just i took database offline and bring back to online and  database is automatically recovered.

OR

if multiple database are in recovery pending after SQL Fail over or  SQL Server Restart..

I had to restart the SQL Server service manually and the database is automatically recovered.

SQL Server script to get a list of all jobs that access a database in SQL server?

 This will work for Agent jobs that have T-SQL job steps pointing to a database.

Databases Last Accessed

 Running the script below will show you the last access date and time of all databases since the day sql server was rebooted

-- Get Last Restart time

SELECT

crdate as 'Last Rebooted On'

FROM

sysdatabases

WHERE name = 'tempdb'

go

 

-- Get last database access time (Null - no access since last reboot)

SELECT name, last_access =(SELECT X1= max(LA.xx)

FROM ( SELECT xx =

MAX(last_user_seek)

WHERE MAX(last_user_seek)is not null

UNION ALL

SELECT xx = max(last_user_scan)

where max(last_user_scan)is not null

UNION ALL

SELECT xx = max(last_user_lookup)

WHERE MAX(last_user_lookup) is not null

UNION ALL

SELECT xx =max(last_user_update)

WHERE MAX(last_user_update) is not null) LA)

FROM master.dbo.sysdatabases sd

LEFT OUTER JOIN sys.dm_db_index_usage_stats s

on sd.dbid= s.database_id

GROUP BY sd.name

ORDER BY name

Should I Keep Autoshrink On?

 It is not a good practice to keep Autoshrink on because it will cause indexes to become fragmented. If you want to reclaim free space then a better approach is to manually shrink the files and then rebuild the indexes.

By default AutoShrink feature is turned off in SQL Server

Configure Alerts for disk I/O errors using t-sql

 Use this script to create alerts for disk I/O errors.

Before running the script replace the <OperatorName> parameter with the actual operator that you would like to alert.

/*

PARAMETERS:

<OperatorName,sysname,Alerts> - Name of the Operator/Alias to alert.

@notification_method 1 - Bitmap of notification types/options: 1 = email,

2 = pager, 4 = netsend

*/

 

USE msdb

GO

 

EXEC msdb. dbo.sp_add_alert @name = N'823 - Read/Write Failure',

    @message_id = 823,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1

GO

EXEC msdb .dbo. sp_add_notification

        @alert_name=N'823 - Read/Write Failure' ,

        @operator_name =N'<OperatorName>' ,

        @notification_method = 1;   -- 1 for email

 

EXEC msdb. dbo.sp_add_alert @name = N'824 - Page Error',

    @message_id = 824,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1

GO

EXEC msdb .dbo. sp_add_notification

        @alert_name=N'824 - Page Error' ,

        @operator_name =N'<OperatorName>' ,

        @notification_method = 1;   -- 1 for email

 

EXEC msdb. dbo.sp_add_alert @name = N'825 - Read-Retry Required',

    @message_id = 825,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1

GO

EXEC msdb .dbo. sp_add_notification

        @alert_name=N'825 - Read-Retry Required' ,

        @operator_name =N'<OperatorName>' ,

        @notification_method = 1;   -- 1 for email