SQL SERVER DBA, Linux and Azure

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