Critical / Severity one / P1 - calls DBA checklist
Many times we get into situation when its a P1 or Severity one call get rise when there is something critical situation get rise and we need to resolve it ASAP.
Basic check list that we need to go through as SQL DBA:
-- Ping DB server and check there is reply and check for DB windows box running fine.
-- Check with When windows box last rebooted that we can check in
task manager --> Performance --> System --> Up time will show in day:hour:mint:sec
or below command in command prompt:
systeminfo | find "System Boot Time"
-- Check with all the disk are Online and all the drives are accessible.
-- Check for windows server CPU and Memory utilization Normal.
-- Check with SQL server up and running and check last startup time. Many way we can check
1) SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
2) SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;
3) select start_time from sys.traces where is_default = 1
4) SELECT crdate FROM sysdatabases WHERE name='tempdb'
5) SELECT create_date FROM sys.databases WHERE name = 'tempdb'
-- Check all the databases are online:
SELECT name, state_desc FROM sys.databases
-- Check for primary database (if any) and Tempdb database are healthy and enough free space.
-- Check for any blocking: I use SP_WHO2 active
1) Activity Monitor
2) SQLServer:Locks Performance Object
3) DMVs
sys.dm_exec_requests
sys.dm_tran_locks
sys.dm_os_waiting_tasks
4) SQL Server Profiler Locks Event Category
5) SQL SP: SP_WHO or SP_WHO2 or SP_WHOISACTIVE
6) select * from SYS.SYSProcesses where blocked<>0
-- Check for any long running query/SP/SQL job:
1)
DBCC OPENTRAN : to get current open transaction
DBCC InputBuffer(SP_ID): to get query used with that open transaction
DBCC SQLPERF(logspace): will gives you all the databases log space information
-- Check with SQL server logs SSMS --> Object Explorer --> Management --> Logs
Basic check list that we need to go through as SQL DBA:
-- Ping DB server and check there is reply and check for DB windows box running fine.
-- Check with When windows box last rebooted that we can check in
task manager --> Performance --> System --> Up time will show in day:hour:mint:sec
or below command in command prompt:
systeminfo | find "System Boot Time"
-- Check with all the disk are Online and all the drives are accessible.
-- Check for windows server CPU and Memory utilization Normal.
-- Check with SQL server up and running and check last startup time. Many way we can check
1) SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
2) SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;
3) select start_time from sys.traces where is_default = 1
4) SELECT crdate FROM sysdatabases WHERE name='tempdb'
5) SELECT create_date FROM sys.databases WHERE name = 'tempdb'
-- Check all the databases are online:
SELECT name, state_desc FROM sys.databases
-- Check for primary database (if any) and Tempdb database are healthy and enough free space.
-- Check for any blocking: I use SP_WHO2 active
1) Activity Monitor
2) SQLServer:Locks Performance Object
3) DMVs
sys.dm_exec_requests
sys.dm_tran_locks
sys.dm_os_waiting_tasks
4) SQL Server Profiler Locks Event Category
5) SQL SP: SP_WHO or SP_WHO2 or SP_WHOISACTIVE
6) select * from SYS.SYSProcesses where blocked<>0
-- Check for any long running query/SP/SQL job:
1)
DBCC OPENTRAN : to get current open transaction
DBCC InputBuffer(SP_ID): to get query used with that open transaction
2) Long running Expensive query
SELECT
r.session_id
, r.start_time
, TotalElapsedTime_ms=r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName=DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, t.[text] AS [executing batch]
, SUBSTRING(
t.[text],r.statement_start_offset/ 2,
(CASEWHENr.statement_end_offset=-1 THENDATALENGTH(t.[text])
ELSEr.statement_end_offset
END-r.statement_start_offset)/ 2
)AS [executing statement]
p.query_plan
FROM
sys.dm_exec_requests r
CROSSAPPLY
sys.dm_exec_sql_text(r.sql_handle)AS t
CROSSAPPLY
sys.dm_exec_query_plan(r.plan_handle)AS p
ORDERBY
r.total_elapsed_timeDESC;
-- Check with Tempdb having space issue:
2)This query is use to find what is using Tempdb database
SELECT
es.host_name,
es.login_name,
tsu.session_id,
er.start_time,
er.command,
SUBSTRING(st.text,er.statement_start_offset/2 + 1,(CASEWHENer.statement_end_offset=-1 THENLEN(CONVERT(nvarchar(max),st.text))* 2 ELSEer.statement_end_offset
END-er.statement_start_offset)/2)asQuery_Text,
DB_NAME(st.dbid)asQueryExecContextDBNAME,
er.cpu_time,
es.program_name,
st.dbidasQueryExecContextDBID,
st.objectidasModuleObjectId,
tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count-tsu.user_objects_dealloc_page_count)asOutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count-tsu.internal_objects_dealloc_page_count)asOutStanding_internal_objects_page_counts,
er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.total_elapsed_time,
er.reads,er.writes,
er.logical_reads,
er.granted_query_memory
FROMsys.dm_db_task_space_usagetsuinnerjoinsys.dm_exec_requestser
ON (tsu.session_id=er.session_idandtsu.request_id=er.request_id)
innerjoinsys.dm_exec_sessionsesON (tsu.session_id=es.session_id)
CROSSAPPLYsys.dm_exec_sql_text(er.sql_handle)st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count)> 0
ORDERBY (tsu.user_objects_alloc_page_count-tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count-tsu.internal_objects_dealloc_page_count)
DESC
DBCC SQLPERF(logspace): will gives you all the databases log space information
-- Check with SQL server logs SSMS --> Object Explorer --> Management --> Logs
EXEC master.dbo.xp_readerrorlog
-- Check with Windows Application logs, System Admin logs.
No comments:
Post a Comment