RSS

How to monitor your SQL Server instances..(1/10)?

17 Mar

Any DBA or DB Analyst need to know two important point

How to administrate DBs in a single click..?

How to monitor your SQL Server instances..?

this from my opinion first point  How to administrate DBs in a single click..? we covered the most important subject in this point in this Series posted by SHEHAP EL-NAGAR :

How to administrate your databases in a single click..?! (1/8)

How to administrate your databases in a single click..?! (2/8)

How to administrate your databases in a single click..?! (3/8)

How to administrate your databases in a single click..?! (4/8)

How to administrate your databases in a single click..?! (5/8)

How to administrate your databases in a single click ..?! (6/8)

Follow up  SHEHAP EL-NAGAR to complete this wonderful and amazing series thanks for AMRO SELIM for his Constructive and fruitful collaboration in this Series .

but this not the end because we will start very Wonderful and Impressive Series to complete our plan to help any DBA or DB Analyst in his/here job now you can mange your database  and administrate it but How to monitor your SQL Server instances..?

1- How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?

2- How to Administrate and Monitor SQL Server Agent by T-SQL..?

3- How to Administrate and Monitor SQL Server Backup and Restore by T-SQL..?

4- How to Audit your SQL Server instance and database ..?

5-How To Monitor your instance and Database by Using ( DMV , Alerts , UCP Moitor , Third party tools..Etc)..?

and most of Question and topic i will cover it in this series to be repository for any DBA to Administrate and monitor his/here SQL Sever instance and Databases it will be DBA portfolio. Let’s Start

How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?

At the first the goal from this post is making repository for scripts can help any DBA or DB Analyst to do Diagnoses on any MSSQL instance or Database by fast and easy way I Collected it after more Search and from the past Experience so if anyone has good script or idea can share it with us and we will share it by his Name

First thing instance level:

SQL and OS Version information :

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [MSSQL OS info];

Installation Data for MSSQL:

SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date]
FROM sys.syslogins
WHERE [sid] = 0x010100000000000512000000;

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];

SQL Server Services information :

SELECT servicename, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename
FROM sys.dm_server_services OPTION (RECOMPILE);

Hardware information for SQL Server Instance:

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)],
 committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count],
affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time],
virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Where the SQL Server Error log is located

SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION(RECOMPILE);

Information about OS Cluster :

</pre>
SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,
 SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);

Instance Configuration Value:

SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);

I am not the owner for this Scripts only i do wide Search on the internet to Collect it Follow me in the Next post to know more Scripts About how we can Diagnose MSSQL Instance|Database.

 
4 Comments

Posted by on March 17, 2014 in SQl server Administration

 

4 responses to “How to monitor your SQL Server instances..(1/10)?

  1. Bob Genov

    April 8, 2016 at 10:26 PM

    Hi,
    The link is not working:

    How to administrate your databases in a single click..?! (5/8)

    How to administrate your databases in a single click ..?! (6/8)

    Could you please provide workable ones, please?

    Thanks a lot.

     
    • Mustafa EL-Masry

      April 9, 2016 at 11:53 PM

      Yes it is related to old website I will try to find the source for it

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s