概述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.aspxexec 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 msdbgoselect '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 sjjoin sysjobsteps sjson sj.job_id = sjs.job_idjoin sysjobhistory sjhon sj.job_id = sjh.job_id and sjs.step_id = sjh.step_idwhere 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 7amunionselect '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 sjjoin sysjobhistory sjhon sj.job_id = sjh.job_idwhere 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 dLEFT 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_nameWHERE (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 dLEFT 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_nameWHERE 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 tableCREATE TABLE #ErrorLog (logdate datetime, processinfo varchar(255), Message varchar(500))-- Populate the temporary tableINSERT #ErrorLog (logdate, processinfo, Message)EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';-- Filter the temporary tableSELECT LogDate, Message FROM #ErrorLogWHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'ORDER BY logdate DESC-- Drop the temporary tableDROP TABLE #ErrorLog5.查看磁盘剩余空间：--剩余空间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 wellset @svrName = @@SERVERNAMEset @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 tableCREATE TABLE #output(line varchar(255))--inserting disk name, total space and free space value in to temporary tableinsert #outputEXEC xp_cmdshell @sql--script to retrieve the values in MB from PS Script outputselect 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 #outputwhere line like '[A-Z][:]%'order by drivename--script to retrieve the values in GB from PS Script outputselect rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename,rou
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
master.dbo.xp_servicecontrol , master.dbo.xp_servicecontrol , master.dbo.xp_servicecontrol ,2.查看sql server agent 工作执行情况：
Status,(sj.name ()) (sJs.step_ID ()) (sJs.step_name ()) (((,(,(,sjh.run_date)),),,) (((,sjh.run_time),),,)(((,,,) ()) sj.job_ID sj.job_ID sjh.job_ID sJs.step_ID sjh.run_status (sjh.run_date )sjh.run_time (((),(),) ) , sj.job_ID sjh.run_status sjh.step_ID (sjh.run_date )sjh.run_time (((),) ) 3.查询数据库备份情况：
d.name "((,b.backupdate,),) "Last ( database_name,type,(backup_finish_date) backupdate type database_name,type) b d.name (backupdate backupdate () d.name ") "Last ( database_name,(backup_finish_date) backupdate 