T SQL query to find when was the database backed up lastly on SQL Server

Select database_name As [Database Name]
, [name] As [Backup Name]
, Case When Type = 'D' Then 'Full Backup'
When Type = 'I' Then 'Differential Backup'
When Type = 'L' Then 'Log Backup'
Else 'File or filegroup or partial'
End As [Backup Type]
, Recovery_Model
, [backup_start_date] As [Time of the SQL Backup Job]
FROM [msdb].[dbo].[backupset]

How to find when was a login deleted in SQL Server

SELECT TE.NAME AS [EventName]
 ,v.subclass_name
 ,T.DatabaseName
 ,t.DatabaseID
 ,t.NTDomainName
 ,t.ApplicationName
 ,t.LoginName
 ,t.SPID
 ,t.StartTime
 ,t.RoleName
 ,t.TargetUserName
 ,t.TargetLoginName
 ,t.SessionLoginName
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (
    SELECT TOP 1 f.[value]
    FROM sys.fn_trace_getinfo(NULL) f
    WHERE f.property = 2
    )), DEFAULT) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
INNER JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
 AND v.subclass_value = t.EventSubClass
WHERE te.NAME IN (
  'Audit Addlogin Event'
  ,'Audit Add DB User Event'
  ,'Audit Add Member to DB Role Event'
  )
 AND v.subclass_name IN (
  'add'
  ,'Grant database access'
  ,'drop'
  ,'Revoke database access'
  )

Script to find date and details when database was restored in SQL Server

USE msdb;

SELECT DBRestored = destination_database_name
 ,RestoreDate = restore_date
 ,SourceDB = b.database_name
 ,SourceFile = physical_name
 ,BackupDate = backup_start_date
FROM RestoreHistory h
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate

Query to find total number of logical reads/physical reads done, when a given stored procedure is executed

SELECT ss.sum_execution_count

,t.TEXT

,ss.sum_total_elapsed_time

,ss.sum_total_worker_time

,ss.sum_total_logical_reads

,ss.sum_total_logical_writes

FROM (SELECT s.plan_handle

,SUM(s.execution_count) sum_execution_count

,SUM(s.total_elapsed_time) sum_total_elapsed_time

,SUM(s.total_worker_time) sum_total_worker_time

,SUM(s.total_logical_reads) sum_total_logical_reads

,SUM(s.total_logical_writes) sum_total_logical_writes

FROM sys.dm_exec_query_stats s

GROUP BY s.plan_handle

) AS ss

CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t

WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'

ORDER BY sum_total_logical_reads DESC



If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.

Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.