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