• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • Last Modified:

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 .
0
marrowyung
Asked:
marrowyung
  • 9
  • 8
  • 3
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
There are corporate monitoring tools as SCOM or BMC Patrol.
0
 
marrowyungAuthor 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 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Those are only monitoring tools. It will send you a warning when the configured threshold is reached.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
marrowyungAuthor 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 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor 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 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor 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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor 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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ZberteocCommented:
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
 
marrowyungAuthor 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
hhaaha, then I still relies on my tools then, just want to know how you handle this kind of case.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
With some manual work. It's DBA work. Can't run from it :)
0
 
ZberteocCommented:
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
 
marrowyungAuthor Commented:
yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
0
 
marrowyungAuthor Commented:
tks alll
0
 
ZberteocCommented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 9
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now