最近100次的備份行為

SELECT TOP 100 mb.database_name AS [DatabaseName],
mb.recovery_model AS [Recovery],
mbm.physical_device_name AS [PhysicalName],
mb.backup_start_date AS [StartDate],
CASE mb.type WHEN 'D' THEN 'DataFile'
WHEN 'I' THEN 'DiffFile'
WHEN 'L' THEN 'LogFile' END [BackupType],
mb.first_lsn AS [LSN],
mb.user_name AS [UserName]
FROM msdb..backupset mb
INNER JOIN msdb..backupmediafamily mbm
ON mb.media_set_id = mbm.media_set_id
ORDER BY mb.backup_finish_date DESC

查詢SQL Server ERROR LOG

/*查詢SQL Server ERROR LOG,18456 (帳號密碼錯誤), 18487(密碼過期),錯3次以上,
可用StartTime籂選區間條件,可替換ERROR查詢不同錯誤,可塞入Table自動發信*/

DECLARE @path varchar(max)
DECLARE @s1 varchar(max)
SELECT @path = path FROM sys.traces
SELECT HostName , LoginName, ApplicationName, StartTime, ServerName, Error, TextData
FROM fn_trace_gettable(@path, default)
WHERE Error IN ( 18456, 18487 )
AND LoginName IN (
SELECT LoginName
FROM fn_trace_gettable(@path, default)
WHERE Error IN ( 18456, 18487 )
GROUP BY LoginName
HAVING COUNT(*) > 2 )

/*未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。請改用擴充事件。*/

查詢鎖定及被鎖定Query

WITH SessionBlock AS
(
SELECT er.session_id, er.Blocking_session_id, 
er.sql_handle AS [si_sql_handle], 
er.plan_handle AS [si_plan_handle], 
er.statement_start_offset AS [si_statement_start_offset], 
er.statement_end_offset AS [si_statement_end_offset], 
ec.most_recent_sql_handle AS [bsi_sql_handle], 
es.last_request_start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es 
    ON er.Blocking_session_id = es.session_id
INNER JOIN sys.dm_exec_connections ec 
    ON er.Blocking_session_id = ec.session_id
WHERE er.blocking_session_id > 50
)
SELECT session_id, Blocking_session_id, 
SUBSTRING (sist.TEXT, (si_statement_start_offset/2)+1,
((CASE si_statement_end_offset WHEN -1 THEN datalength(sist.TEXT)  
 ELSE si_statement_end_offset END - si_statement_start_offset)/2) + 1 ) AS [si_query],
bsist.TEXT AS [bsi_query],
sb.last_request_start_time
FROM SessionBlock sb
CROSS APPLY sys.dm_exec_sql_text(sb.si_sql_handle) AS sist
CROSS APPLY sys.dm_exec_sql_text(sb.bsi_sql_handle) AS bsist

Drop Login and User

-- 殺掉DB裏所有的相關帳號.殺之前要先解除該帳號相關連的作業,如AGENT...

-- Drop Login and User
DECLARE @account sysname = 'dbaccount'
DECLARE @dbname sysname
DECLARE @s1 varchar(max)
BEGIN
SET @s1 = 'USE master; DROP LOGIN ['+@account+']; '
EXEC (@s1)
END

DECLARE dropacc CURSOR FOR SELECT name FROM sys.databases
OPEN dropacc FETCH NEXT FROM dropacc INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 =
'USE '+@dbname+'; DROP USER ['+@account+']; '
EXEC (@s1)
FETCH NEXT FROM dropacc INTO @dbname
END
CLOSE dropacc
DEALLOCATE dropacc

-- 參考:我自己

List DB account Permission

-- 已授與予權限的資料
SELECT l.name as [Grantee_Name],
p.state_desc AS [State_Desc],
p.permission_name AS [Permission_Name]
FROM sys.server_permissions AS p 
JOIN sys.server_principals AS l 
ON p.grantee_principal_id = l.principal_id 
WHERE l.name NOT LIKE '##%' AND l.principal_id > 10
ORDER BY 1
GO

-- 角色及其成員資料
SELECT srm.role_principal_id AS [Role_Principal_ID],
sp.name AS [RoleName],
srm.member_principal_id AS [Member_Principal_ID],
member.name AS [MemberName]
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS sp
ON srm.role_principal_id = sp.principal_id
JOIN sys.server_principals AS member
ON srm.member_principal_id = member.principal_id
ORDER BY 1

-- 所有資料庫角色資料
DECLARE @DBName varchar(20)
DECLARE @s1 varchar(max)
CREATE TABLE #DBRole( 
DBName varchar(20), RoleName varchar(50), MemberName varchar(50) )

DECLARE dbrole CURSOR FOR SELECT name FROM [sys].[databases]
OPEN dbrole FETCH NEXT FROM dbrole INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 = 
'INSERT #DBRole '+
'SELECT '''+@DBName+''' AS [DBName], 
role.name AS [RoleName], member.name AS [MemberName] 
FROM '+@DBName+'.sys.database_role_members AS drm 
JOIN '+@DBName+'.sys.database_principals AS role 
ON drm.role_principal_id = role.principal_id 
JOIN '+@DBName+'.sys.database_principals AS member 
ON drm.member_principal_id = member.principal_id 
WHERE member.name NOT IN ( ''dbo'' )'
EXEC (@s1)
FETCH NEXT FROM dbrole INTO @DBName
END
SELECT * FROM #DBRole --WHERE RoleName = 'db_owner' 
DROP TABLE #DBRole
CLOSE dbrole
DEALLOCATE dbrole

-- 參考
-- M$ MSDN

Check Datafile Space

--查詢DB datafile 資料

DECLARE @DBName varchar(50)
DECLARE @s1 varchar(max)
CREATE TABLE #DBFileList ( [DBName] varchar(50), [FileID] tinyint,
[FileType] char(8), [CurrentSize(MB)] int, [FreeSize(MB)] int, 
[FileName] varchar(50), [path] varchar(200), [Status] char(7)) 
DECLARE dbfile CURSOR FOR 
SELECT name FROM sys.databases
OPEN dbfile FETCH NEXT FROM dbfile INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 =
'USE '+@DBName+'; '+
'INSERT INTO #DBFileList '+
'SELECT '''+@DBName+''' AS [DBName], 
file_id AS [FileID], 
CASE type WHEN 0 THEN ''DataFile'' 
WHEN 1 THEN ''LogFile'' 
ELSE ''Other'' END AS [FileType], 
size/128 AS [CurrentSize(MB)], 
size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128 AS [FreeSize(MB)], 
name AS [FileName], physical_name AS [Path], state_desc AS [Status] 
FROM sys.database_files; '
EXEC (@s1)
FETCH NEXT FROM dbfile INTO @DBName
END
CLOSE dbfile
DEALLOCATE dbfile
SELECT * FROM #DBFileList ORDER BY 3, 4 DESC
DROP TABLE #DBFileList
-- 參考 我自己

check data and log files grow and shrink log

DECLARE @logpath varchar(200)
SELECT @logpath =
SUBSTRING( [path], 1, LEN([path]) - PATINDEX('%\%', REVERSE([path]) ) )+'/log.trc' FROM sys.traces WHERE is_default = 1

SELECT TOP 100 HostName,
DatabaseID,
DatabaseName,
FileName,
CASE WHEN EventClass = 92 THEN 'Data File Auto Grow'
WHEN EventClass = 93 THEN 'Log File Auto Grow'
WHEN EventClass = 94 THEN 'Data File Auto Shrink'
WHEN EventClass = 95 THEN 'Log File Auto Shrink'
END AS EventClass,
StartTime,
EndTime
FROM fn_trace_gettable( @logpath, default )
WHERE EventClass Between 92 AND 95
ORDER BY StartTime DESC

-- 參考
-- ~楓花雪岳~ 檢查 SQL Server 是否觸發自動成長或自動壓縮