Avatar of marrowyung
marrowyung
 asked on

monitor queries that use too much tempdb log

hi,

recently found a Query that eat too much tempdb log but we knew it just before it is too late.

resource governor from SQL server don't control tempdb, then what is your way to find it out if we define a tempdb log value and let MS SQL server keep detecting it and kill it if the tempdb space is growing too large because of this and then report it out by email.

what tools you all use for that if any .
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Zberteoc

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
use that ? I use idera DM for that and checking with them. but it is more on tempdb contention.

how scom do it ? you tried that before ?
Vitor Montalvão

Those are only monitoring tools. It will send you a warning when the configured threshold is reached.
marrowyung

ASKER
but it is not something like detecting what queries keep using Tempdb log and let us specify a size limit and if this query take too much , then kill it ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Vitor Montalvão

but it is not something like detecting what queries keep using Tempdb log and let us specify a size limit and if this query take too much , then kill it ?
If you need to do something like that is because you're not acting correctly.
Right thing to do is to solve the issue and not killing the query because the query will be run over and over and you'll keep killing it.
Also if your tempdb is growing too much is because is not correctly configured and you should review the size and grow policy for tempdb and reconfigure it to accomplish the extra space needed.
marrowyung

ASKER
"Right thing to do is to solve the issue and not killing the query because the query will be run over and over and you'll keep killing it."

yes, you are right, we found an issue with a developer who do open trans but not commit trans, then hold the tempdb log for a long time and it keep growthing, we found that but I need more to control it.

if kill it and report to it, we can stop the SQL serve log from growthing to much/out of control, have the evident then check TOMORROW.

this is what we do with report, handle it later as when the time it happens, we are sleeping.

"Also if your tempdb is growing too much is because is not correctly configured and you should review the size and grow policy for tempdb and reconfigure it to accomplish the extra space needed."

yes we already done that but occasionally we see sth new.

anything in your life to detect people code sth stupid, like open tran and never commit ?
Vitor Montalvão

yes, you are right, we found an issue with a developer who do open trans but not commit trans, then hold the tempdb log for a long time and it keep growthing, we found that but I need more to control it.
Why the developer don't fix his code?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
she has been caught for that yesterday and now I get complain on why I can't find this out but another senior developer accidentally found that out. so I need a one method to do it quickly..

but coding problem can never found by me as I am a production/infrastructure DBA. I have no knowledge on that.

other than whoisactive, someone use this also can't find that out, I am finding out what else i can do to control/monitor tempdb log size.
Vitor Montalvão

I would say this should be threated as an emergency case and developers should have this fixed and a new released should be immediately available in Production but I'm not the manager.
The time you're spending to try to find a solution is more than the time they should spent to fix it.
marrowyung

ASKER
"I would say this should be threated as an emergency case and developers should have this fixed and a new released should be immediately available in Production but I'm not the manager.

without the email report evident, how can we ask them to fix it.

"The time you're spending to try to find a solution is more than the time they should spent to fix it.

I don't think so as if we have the tools then make use the tools to dig it out.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vitor Montalvão

without the email report evident, how can we ask them to fix it.
Yourself answered this before:
"yes, you are right, we found an issue with a developer who do open trans but not commit trans, then hold the tempdb log for a long time and it keep growthing, we found that but I need more to control it."

I don't know how is the communication process inside your company but the places I've been we just needed to send an email to the developers stating the facts to have them starting to work on a fix.
Zberteoc

You can't have a monitoring process to detect specific development malpractice. However a very simple but extremely effective tool is Adam Mechanic's sp_whoisactive stored procedure:

http://sqlblog.com/files/folders/beta/entry42453.aspx

It is a free stored procedure that you download and just run the script on the server. It will create the procedure in the master database. After that any time there is something going on you simply open a new query window and run it:

EXEC sp_whoisactive

The result set will show you exactly what happens at the moment of execution, that is processes that are active with the exact query that is exectuted, the waiting type inormation, blocking processes, tempdb allocation, memory, CPU and other usefull info. One column that is returned is called "open_tran_count" and that normally should be 0 unless you have a long running transaction. Be aware that if >0 doesn't necessarily mean that something is wrong, some transactions simply take longer time to execute but with this it is very simple to detect your kind of situation. You can even test it:

1. Open a new query window and run this:
begin tran
select getdate() as dte

Open in new window

2. Open another query window and execute this:
EXEC sp_whoisactive

Open in new window

You will be able to see the process from step 1 with the query and with open_tran_count = 1 and it will not go away from the result of sp_whoisactive proc if you repeatedly execute it unless you add "commit tran" in the first window, select it and execute it or you kill/close that query window.
marrowyung

ASKER
Victor:

"I don't know how is the communication process inside your company but the places I've been we just needed to send an email to the developers stating the facts to have them starting to work on a fix."

yes, you are right, but how can we find it and send to them before someone complain by a more meaningful record?

Zberteoc'

"extremely effective tool is Adam Mechanic's sp_whoisactive stored procedure:

http://sqlblog.com/files/folders/beta/entry42453.aspx"

we are using this but this one point out diff things to us, one short come by this tools is, from time to time we can't see the whole statement as it cut the queries by parts.

 we do this:

EXEC dbo.sp_WhoIsActive
     @show_own_spid = 1,
     @get_plans =1 ,
     @get_additional_info =1,
       @get_transaction_info = 1
, @find_block_leaders = 1,
@get_outer_command=1
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

yes, you are right, but how can we find it and send to them before someone complain by a more meaningful record?
With a monitoring tool (I already provided 2 examples above) and DBA pro active work. There's no miracles, man.
marrowyung

ASKER
hhaaha, then I still relies on my tools then, just want to know how you handle this kind of case.
Vitor Montalvão

With some manual work. It's DBA work. Can't run from it :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Zberteoc

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
marrowyung

ASKER
tks alll
Zberteoc

yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
Give them the piece of code bellow that will return a list of all the objects in a database, like views, functions and stored procedures, that contain a certain string. You don't have to pass the whole query returned by the sp_whoisactive but at least something from it that will narrow down the search:
declare 
	@str_to_search nvarchar(4000)=''

select distinct
	OBJECT_SCHEMA_NAME(o.[object_id]) as [Schema],
	OBJECT_NAME(o.[object_id]) as ObjectName,
	o.type_desc as ObjectType
from  
	sys.objects o
	inner join sys.syscomments c
		on c.id=o.[object_id]
where
	replace([text],'_','*') like '%'+replace(@str_to_search,'_','*')+'%'

return

-- ##### version with code return here
;with objcode as
(	
	select
		OBJECT_SCHEMA_NAME(o.[object_id]) as [Schema],
		OBJECT_NAME(o.[object_id]) as ObjectName,
		o.type_desc as ObjectType,
		replace(replace(replace(replace(replace(replace(cast(
				(
					select 
						ISNULL([text],'')+'' 
					from  
						sys.syscomments 
					where 
						id=o.[object_id] 
					order by 
						colid for xml path(''), TYPE
				) as nvarchar(max)
			),'&#x0D;',''),'&gt;','>'),'&lt;','<'),'&le;','<='),'&ge;','>='),'&amp;','&') as ObjectCode
	from  
		sys.objects o
) 
select 
	*,
	case when len(ObjectCode)>'65530' then 'Text columns are limitted by MS to 65535 characters' else '' end as Note 
from 
	objcode
where
	replace(ObjectCode,'_','*') like '%'+replace(@str_to_search,'_','*')+'%'
order by 
	ObjectType,
	[Schema],
	ObjectName

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.