非常全面的SQL Server巡检脚本来自sqlskills团队的Glenn Berry 大牛

Glenn Berry 大牛会对这个脚本持续更新

-- SQL Server 2012 Diagnostic Information Queries-- Glenn Berry -- APRil 2015-- Last Modified: April 27, 2015-- http://sqlserverperformance.Wordpress.com/-- http://sqlskills.com/blogs/glenn/-- Twitter: GlennAlanBerry-- Please listen to my Pluralsight courses-- http://www.pluralsight.com/author/glenn-berry-- Many of these queries will not work if you have databases in 80 compatibility mode-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server--******************************************************************************--*   Copyright (C) 2015 Glenn Berry, SQLskills.com--*   All rights reserved. --*--*   For more scripts and sample code, check out --*      http://sqlskills.com/blogs/glenn--*--*   You may alter this code for your own *non-commercial* purposes. You may--*   republish altered code as long as you include this copyright and give due credit. --*--*--*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF --*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED --*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A--*   PARTICULAR PURPOSE. --*--******************************************************************************-- Check the major product version to see if it is SQL Server 2012IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '11%')    BEGIN        DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));        RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);    END    ELSE        PRINT N'You have the correct major version of SQL Server for this diagnostic information script';-- Instance level queries *******************************-- SQL and OS Version information for current instance  (Query 1) (Version Info)SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];-- SQL Server 2012 RTM Branch Builds                        SQL Server 2012 SP1 Branch Builds                    SQL Server 2012 SP2 Branch Builds-- Build            Description            Release Date        Build            Description        Release Date        Build            Description            Release Date-- 11.0.2100        RTM                      3/6/2012-- 11.0.2316        RTM CU1                 4/12/2012-- 11.0.2325        RTM CU2                 6/18/2012 -->        11.0.3000        SP1 RTM            11/7/2012-- 11.0.2332        RTM CU3                 8/31/2012-- 11.0.2376        RTM CU3 + QFE         10/9/2012-- 11.0.2383        RTM CU4                10/15/2012 -->        11.0.3321        SP1 CU1            11/20/2012-- 11.0.2395        RTM CU5                12/17/2012 -->      11.0.3339        SP1 CU2            1/21/2013-- 11.0.2401        RTM CU6              2/18/2013 -->      11.0.3349       SP1 CU3            3/18/2013-- 11.0.2405        RTM CU7              4/15/2013 -->      11.0 3368       SP1 CU4         5/30/2013-- 11.0.2410        RTM CU8              6/17/2013 -->      11.0.3373       SP1 CU5         7/15/2013-- 11.0.2419        RTM CU9              8/20/2013 -->      11.0.3381        SP1 CU6            9/16/2013-- 11.0.2420        RTM CU10            10/21/2013 -->        11.0.3393       SP1 CU7         11/18/2013-- 11.0.2424        RTM CU11            12/16/2003 -->      11.0.3401       SP1 CU8         1/20/2014--                                                          11.0.3412       SP1 CU9         3/17/2014 -->        11.0.5058        SP2 RTM                6/10/2014--                                                          11.0.3431       SP1 CU10        5/19/2014--                                                          11.0.3449       SP1 CU11        7/21/2014 -->        11.0.5532        SP2 CU1                7/23/2014--                                                          11.0.3470       SP1 CU12        9/15/2014 -->       11.0.5548       SP2 CU2             9/15/2014--                                                          11.0.3482        SP1 CU13        11/17/2014-->       11.0.5556        SP2 CU3            11/17/2014--                                                          11.0.3486       SP1 CU14        1/19/2015 -->       11.0.5569       SP2 CU4             1/19/2015--                                                                                                              11.0.5571       SP2 CU4 + COD HF     2/4/2015  (this includes the AlwaysOn AG hotfix that is in SP2 CU5)--                                                          11.0.3487        SP1 CU15        3/16/2015           11.0.5582       SP2 CU5             3/16/2015-- The SQL Server 2012 builds that were released after SQL Server 2012 was released-- http://support.microsoft.com/kb/2692828-- The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released-- http://support.microsoft.com/kb/2772858-- SQL Server 2012 SP2 build versions (new format for the build list KB article)-- http://support.microsoft.com/kb/2983249-- Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads-- http://support.microsoft.com/kb/2964518/EN-US-- Performance and Stability Related Fixes in Post-SQL Server 2012 SP2 Builds-- http://www.sqlskills.com/blogs/glenn/performance-and-stability-related-fixes-in-post-sql-server-2012-sp2-builds/-- When was SQL Server installed  (Query 2) (SQL Server Install Date)  SELECT @@SERVERNAME AS [Server Name], create_date AS [SQL Server Install Date] FROM sys.server_principals WITH (NOLOCK)WHERE name = N'NT AUTHORITY/SYSTEM'OR name = N'NT AUTHORITY/NETWORK SERVICE' OPTION (RECOMPILE);-- Tells you the date and time that SQL Server was installed-- It is a good idea to know how old your instance is-- Get selected server properties (SQL Server 2012)  (Query 3) (Server Properties)SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],  SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus];-- This gives you a lot of useful information about your instance of SQL Server,-- such as the ProcessID for SQL Server and your collation-- The last two columns are new for SQL Server 2012-- Get SQL Server Agent jobs and Category information (Query 4) (SQL Server Agent Jobs)SELECT sj.name AS [JobName], sj.[description] AS [JobDescription], SUSER_SNAME(sj.owner_sid) AS [JobOwner],sj.date_created, sj.[enabled], sj.notify_email_Operator_id, sj.notify_level_email, sc.name AS [CategoryName],js.next_run_date, js.next_run_timeFROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)ON sj.category_id = sc.category_idLEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)ON sj.job_id = js.job_idORDER BY sj.name OPTION (RECOMPILE);-- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured-- Look for Agent jobs that are not owned by sa-- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)---- MSDN sysjobs documentation-- http://msdn.microsoft.com/en-us/library/ms189817.aspx-- Get SQL Server Agent Alert Information (Query 5) (SQL Serv