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] |
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 :
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.
DB Cloud TECH 