Solved

monitor queries that use too much tempdb log

Posted on 2016-10-19
20
33 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 .
0
Comment
Question by:marrowyung
  • 9
  • 8
  • 3
20 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
There are corporate monitoring tools as SCOM or BMC Patrol.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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 ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Those are only monitoring tools. It will send you a warning when the configured threshold is reached.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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 ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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 ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
hhaaha, then I still relies on my tools then, just want to know how you handle this kind of case.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
With some manual work. It's DBA work. Can't run from it :)
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
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.
How is that a shortcoming? At contrary, it is an advantage! You know exactly what piece of code cases you the problem. I would hate a tool that will only point me to the stored procedure call. And the problem you are describing should had been caught in no time with sp_whoisactive. But if you really want a graphical monitoring tool that is also free I highly recommend you the free version of the Solaris Database Performance Analyzer. I think it is the best there is, it is showing the waiting statistics, which at at the core of performance statistics:

http://www.solarwinds.com/free-tools/database-performance-analyzer-free
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
0
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
tks alll
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now