sql server監控

http://www.mssqltips.com/sqlservertip/2522/sql-server-monitoring-checklist/

1.查看sql server 服務運行狀態：

http://msdn.microsoft.com/zh-cn/library/ms189089%28v=sql.105%29.aspx

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer' exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent' exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'

?

2.查看sql server agent 工作執行情況：

use msdb go select 'FAILED' as Status, cast(sj.name as varchar(100)) as "Job Name",cast(sjs.step_id as varchar(5)) as "Step ID",cast(sjs.step_name as varchar(30)) as "Step Name",cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',sjh.message as "Message" from sysjobs sj join sysjobsteps sjs on sj.job_id = sjs.job_id join sysjobhistory sjh on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id where sjh.run_status <> 1and cast(sjh.run_date as float)*1000000+sjh.run_time > cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am union select 'FAILED',cast(sj.name as varchar(100)) as "Job Name",'MAIN' as "Step ID",'MAIN' as "Step Name",cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',sjh.message as "Message" from sysjobs sj join sysjobhistory sjh on sj.job_id = sjh.job_id where sjh.run_status <> 1 and sjh.step_id=0and cast(sjh.run_date as float)*1000000+sjh.run_time >cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am

?

3.查詢數據庫備份情況：

SELECT d.name AS "Database",ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup" FROM sys.databases d LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupsetWHERE type LIKE 'D'GROUP BY database_name,type) b on d.name=b.database_name WHERE (backupdate IS NULL OR backupdate < getdate()-1) SELECT d.name AS "Database",ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup" FROM sys.databases d LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupsetWHERE type LIKE 'L'GROUP BY database_name,type) b on d.name=b.database_name WHERE recovery_model = 1AND (backupdate IS NULL OR backupdate < getdate()-1)

4.查看sql server 錯誤日志情況：

declare @Time_Start datetime; declare @Time_End datetime; set @Time_Start=getdate()-2; set @Time_End=getdate(); -- Create the temporary table CREATE TABLE #ErrorLog (logdate datetime, processinfo varchar(255), Message varchar(500)) -- Populate the temporary table INSERT #ErrorLog (logdate, processinfo, Message)EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc'; -- Filter the temporary table SELECT LogDate, Message FROM #ErrorLog WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon' ORDER BY logdate DESC -- Drop the temporary table DROP TABLE #ErrorLog

5.查看磁盤剩余空間：

--剩余空間 exec master.dbo.xp_fixeddrives --總空間和剩余空間 declare @svrName varchar(255) declare @sql varchar(400) --by default it will take the current server name, we can the set the server name as well set @svrName = @@SERVERNAME set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"' --creating a temporary table CREATE TABLE #output (line varchar(255)) --inserting disk name, total space and free space value in to temporary table insert #output EXEC xp_cmdshell @sql --script to retrieve the values in MB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)',round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)' from #output where line like '[A-Z][:]%' order by drivename --script to retrieve the values in GB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)',round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)' from #output where line like '[A-Z][:]%' order by drivename --script to drop the temporary table drop table #output

6.sql server內存使用情況：

SELECT available_physical_memory_kb/1024 as "Total Memory MB",available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free" FROM sys.dm_os_sys_memory

?

7.sql 執行消耗情況（IO vs. CPU vs. Elapsed Time）：

SELECT top 10 text as "SQL Statement",last_execution_time as "Last Execution Time",(total_logical_reads+total_physical_reads+total_logical_writes)/execution_count as [Average IO],(total_worker_time/execution_count)/1000000.0 as [Average CPU Time (sec)],(total_elapsed_time/execution_count)/1000000.0 as [Average Elapsed Time (sec)],execution_count as "Execution Count",qp.query_plan as "Query Plan" FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp order by total_elapsed_time/execution_count desc

8.數據庫連接情況：

SELECT spid, kpid, blocked, d.name, open_tran, status, hostname, cmd, login_time, loginame, net_library FROM sys.sysprocesses p INNER JOIN sys.databases d on p.dbid=d.database_id

9.正在處理的請求數：

DECLARE @BRPS BIGINT SELECT @BRPS=cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%' WAITFOR DELAY '000:00:10' SELECT (cntr_value-@BRPS)/10.0 AS "Batch Requests/sec" FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Batch Requests/sec%'

?

9.

?

?

?

?

?

?

轉載于:https://www.cnblogs.com/davidwang456/archive/2013/03/08/2950252.html

總結

以上是生活随笔為你收集整理的sql server监控的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。