Databases Reference
In-Depth Information
LISTING 15-8:
SQL Server Error Log information
-- Shows you where the SQL Server error log is located and how it is configured
SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK)
OPTION (RECOMPILE);
-- Knowing this information is important for troubleshooting purposes
This query gives you the i le path to the SQL Server Error Log (which is simply a text i le that you
can open in Notepad in an emergency). If your SQL Server Service ever fails to start, the i rst place
you should look is in the SQL Server Error Log. Of course, if your SQL Server Service is not run-
ning, you won't be able to run this query, so you should run it ahead of time and store the results
in your server run book. Next, you will i nd out whether your database server is using Windows
Clustering, with the query shown in Listing 15-9.
LISTING 15-9:
Operating system cluster information
-- Get information about your OS cluster
--(if your database server is in a cluster)
SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,
SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);
-- You will see no results if your instance is not clustered
This query returns some coni guration information about your Windows cluster. If it returns
no information, then your operating system on the database server is not clustered (you have a
standalone instance), in which case you can skip the query shown in Listing 15-10.
Otherwise, if you are using a cluster, you can use the query shown in Listing 15-10 to get some
useful information about your cluster nodes.
LISTING 15-10:
Cluster node information
-- Get information about your cluster nodes and their status
-- (if your database server is in a cluster)
SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
-- Knowing which node owns the cluster resources is critical
-- Especially when you are installing Windows or SQL Server updates
This query returns all your cluster nodes, including their status and whether they own the cluster
resources. For example, if you have a three-node cluster, this query would return three rows and
indicate which node currently owned the SQL Server instance. This is actually important informa-
tion to know if you are getting ready to do some maintenance on the cluster, such as installing a
Search WWH ::
Custom Search