Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.