January 25, 2011 at 9:39 am
For the past couple of weeks we have been seeing the temDB.mdf grow unexpectedly; it grows for 2 GB to 25 or 30 GB; I would hate to run a profiler to see what is causing this growth; is there any other way I can find out what is going on?
Thanks,
Carlos
January 25, 2011 at 10:06 am
I would think you could narrow it down without a lot of trouble with just a little sleuthing..
Does it happen overnight or just during the day?
Are there scheduled jobs that run only during those times?
These questions alone should help narrow it down. Worst case is you might have to run a trace on auto-growth of the tempdb database to get a handle on the user and potentially the app and hostname.
CEWII
January 26, 2011 at 3:53 am
[font="Tahoma"]
Run this query to know the growth patterns of the DBs that are making use of the autogrowth grow option.
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT StartTime,
DB_NAME(databaseid)as DatabaseName,
Filename,
SUM ((IntegerData*8)/1024) AS [Growth in MB],
(Duration/1000)as [Duration in seconds]
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY StartTime,Databaseid, Filename, IntegerData, Duration
order by StartTime
You dont have to meddle with setting up a trace and tracking it...if you are suspicious abt jobs that can be triggering this growth then enable verbose logging to those jobs so that can investigate further...
Advanced Job properties--> enable output to file and append it..
[/font]
January 27, 2011 at 3:12 pm
I had a similar problem a few years ago, and added a SQL Agent job to run every 10 minutes or so and compare the size of tempdb to the prior size. If it grew more than some amount, the job sent me an email message.
It didn't take too long to get a message and immediately start Activity Monitor to see what was going on. It turned out to be users running ad-hoc reports through a vendor application and picking all the columns possible.
January 27, 2011 at 3:19 pm
Thanks a lot. That is a great solution !! can you walk me thru how to setup that agent job to do the comparison?
January 27, 2011 at 3:43 pm
On of the event recorded in the default trace is the Data File auto grow event (trace_event_id = 92). So without all the complex sluthing you can look here and see exactly what time the file grew.
This query will probably give you what you want, you will just need to fix the trace file path for your server.
SELECT loginname, hostname, applicationname, servername, databasename, ISNULL(objectName,'') as 'objectName',
ISNULL(TargetUsername,'') as 'TargetUsername', ISNULL(RoleName,'') as 'RoleName',
ISNULL(TargetLoginName,'') as 'TargetLoginName', e.category_id,cat.name as 'CategoryName',
ISNULL(textdata,'') as 'textdata', starttime, eventclass, eventsubclass, e.name as 'EventName'
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL9.SQL2005\MSSQL\Log\log_42.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories cat
ON e.category_id = cat.category_id
WHERE ((objectname IS not NULL and objectname not like '%WA_Sys%') or TargetLoginName is not null)
AND applicationname not like 'SQLAgent%'
and e.trace_event_id = 92
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 28, 2011 at 2:59 am
I had the same issue.
After some investigation I found that some temp tables (CREATE TABLE #...) are not dropped in some of my procedures.
After adding precisely everywhere DROP TABLE #... tempdb stopped to grow.
I hope that helps.
p.s. DBCC OPENTRAN('tempdb') should also help you to find out what is going on. Active transactions prevent tempdb to shrink automatically.
January 28, 2011 at 5:12 am
arty 15255 (1/28/2011)
I had the same issue.After some investigation I found that some temp tables (CREATE TABLE #...) are not dropped in some of my procedures.
After adding precisely everywhere DROP TABLE #... tempdb stopped to grow.
I hope that helps.
p.s. DBCC OPENTRAN('tempdb') should also help you to find out what is going on. Active transactions prevent tempdb to shrink automatically.
That shouldn't happen, what version and level are you on?
January 28, 2011 at 5:21 am
I am using SQL Server 2005. I am not sure which service pack i had when i had this issue.
It might be that I am wrong.
It was long time ago and i remembe clearly that we changed all procedure to add missing DROP TABLE #...; Also I used the DBCC OPENTRAN('...') to find open transactions on tempdb which had temp tables created and not dropped.
January 28, 2011 at 5:37 am
I'd search the KB because I'm 99% sure that this bug has been fixed. I just can't tell you when it was.
January 28, 2011 at 5:39 am
carloarango@hotmail.com (1/25/2011)
For the past couple of weeks we have been seeing the temDB.mdf grow unexpectedly; it grows for 2 GB to 25 or 30 GB; I would hate to run a profiler to see what is causing this growth; is there any other way I can find out what is going on?Thanks,
Carlos
Just fyi I had the same bug with sql 7 oem with a simple query with a derived table and a view. The server just couldn't figure out an optimal way to process the data and tempdb took a massive hit in the process.
tempdb would ballonuntill we ran out of space on the drive. Then shrink and everything was back to normal.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply