We help IT Professionals succeed at work.

monitor queries that use too much tempdb log

265 Views
Last Modified: 2016-10-26
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 .
Comment
Watch Question

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Those are only monitoring tools. It will send you a warning when the configured threshold is reached.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hhaaha, then I still relies on my tools then, just want to know how you handle this kind of case.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
With some manual work. It's DBA work. Can't run from it :)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks alll
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.