Sample code for monitor temp db usage for MSSQL
This article will explain in detail the sample code about monitor temp db usage for MSSQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
Create a SP to record the temp file usage, and record the active session with SQL with requested paged from temp db
USE [DBCenter]
GO
CREATE PROCEDURE [dba]. [sys_tmp_monitor_85]
AS
Begin
Truncate table dbcenter..tmpdbmonitor_SQL
Insert into dbcenter..tmpdbmonitor_85
Select sum (convert (float,size) * (8192 1.0/sum 1024) / 1024 USED_PER,GETDATE 1024) TMPFILE_GB, sum (convert (float,maxsize) * (8192 Universe 1024) / 1024 Universe 1024) MAX_TMPFILE_GB, (sum (size) * 1.0/sum (maxsize)) * 100 USED_PER,GETDATE () [DATE_TIME]
From tempdb.dbo.sysfiles where name like 'tempdev%'
Insert into dbcenter..tmpdbmonitor_SQL
Select replace (a.hostname as program_name',') as hostname,' + replace (hostname',') + as program_name
, loginame,db_name (a.dbid) AS DBname, j.badge () date_time
From (
SELECT t1.session_id, t1.internal_objects_alloc_page_count*8.0 internal_objects_alloc_KB
T1.user_objects_alloc_page_count*8.0 user_objects_alloc_KB
T1.internal_objects_dealloc_page_count*8.0 internal_objects_dealloc_KB
T1.user_objects_dealloc_page_count*8.0 user_objects_dealloc_KB
St.text
From tempdb.sys.dm_db_session_space_usage as t1
Tempdb.sys.dm_exec_requests as t4
CROSS APPLY tempdb.sys.dm_exec_sql_text (t4.sql_handle) AS st
Where t1.session_id = t4.session_id
And t1.session_id > 50
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0) as j
Left join
Tempdb.sys.sysprocesses as a with (nolock) on a.spid=j.session_id
Where isnull (a. Loginame1)')'
Insert into dbcenter..tmpdbmonitor_SQL_his
Select replace (a.hostname as program_name',') as hostname,' + replace (hostname',') + as program_name
, loginame,db_name (a.dbid) AS DBname, j.badge () date_time
From (
SELECT t1.session_id, t1.internal_objects_alloc_page_count*8.0 internal_objects_alloc_KB
T1.user_objects_alloc_page_count*8.0 user_objects_alloc_KB
T1.internal_objects_dealloc_page_count*8.0 internal_objects_dealloc_KB
T1.user_objects_dealloc_page_count*8.0 user_objects_dealloc_KB
St.text
From tempdb.sys.dm_db_session_space_usage as t1
Tempdb.sys.dm_exec_requests as t4
CROSS APPLY tempdb.sys.dm_exec_sql_text (t4.sql_handle) AS st
Where t1.session_id = t4.session_id
And t1.session_id > 50
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0) as j
Left join
Tempdb.sys.sysprocesses as a with (nolock) on a.spid=j.session_id
Where isnull (a. Loginame1)')'
End
2. Create a job which will get the tmpdb usage info
If usage percent > = 85 sent out mail
Declare
@ used_per int
@ sql varchar (8000)
@ sbj varchar (1000)
Begin
Select @ used_per= (sum (size) * 1.0/sum (maxsize)) * 100 from tempdb.dbo.sysfiles where name like 'tempdev%'
Print @ used_per
If @ used_per > = 85
Begin
Exec [DBCenter] .[dba] .[sys _ tmp_monitor_85]
Set @ sql='SELECT [hostname]
, [program_name]
, [loginame]
, [DBname]
, [session_id]
, [text]
, [date_time]
FROM [DBCenter]. [dbo]. [tmpdbmonitor _ SQL]'
Select @ sbj=@@SERVERNAME
Set @ sbj=@sbj+'tmpdb usage over 85%'
-- print @ sql
EXEC msdb.dbo.sp_send_dbmail
@ profile_name = 'monitor'
@ recipients = '1234567qq.com'
@ query = @ sql
@ subject = @ sbj
@ attach_query_result_as_file = 1
End
End
This is the end of this article on "sample code for monitor temp db usage for MSSQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
