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