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