This will work for Agent jobs that have T-SQL job steps pointing to a database.
This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
This will work for Agent jobs that have T-SQL job steps pointing to a database.
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
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
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