April 27, 2017 at 2:20 am
Running SQL 2012 SP3 CU6 AlwaysOn....
we had an issue two nights ago with the tempdb log filling the log disk. explosive growth overnight and it hung the server. could not even log on to SSMS to run a query to check. to resolve the issue quickly we failed over which reset tempdb usage. after which i closely monitored tempdb during the day and the automatic checkpoints at 70% cleared down the logs (several times during the day).
similar issue last night but not as explosive growth. the log file was 99% full and had autogrown 4 times over night (1GB each time from 4 to 8GB). i ran a manual checkpoint on tempdb and *viola* the usage went down to 8.7%.
a) why isn't the automatic checkpoint kicking in and clearing down the tempdb log?
b) would it be acceptable to have my tempdb log alert fire off a job to checkpoint the tempdb? workaround idea if no solution to a)
c) what is the best way to detect what is causing this tempdb log growth? i've searched and tried many scripts but none have helped me pinpoint the culprit.
thanks!
April 27, 2017 at 6:39 am
Next time, run the code to see what is holding up the log space reuse.
Maybe this points you in the direction of the issue.
select name, log_reuse_wait_desc from sys.databases
where name = 'tempdb'
Also, have a look at the provided script posted here some years ago: http://www.sqlservercentral.com/scripts/tempdb/72007/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 27, 2017 at 8:15 am
As a starter
SELECT TE.name AS [EventName] ,
T.DatabaseName ,
t.DatabaseID ,
t.NTDomainName ,
t.ApplicationName ,
t.LoginName ,
t.SPID ,
t.Duration ,
t.StartTime ,
t.EndTime,
planhandle,
sqlhandle
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE databasename = 'tempdb' and te.name = 'Data File Auto Grow'
OR te.name = 'Data File Auto Shrink'
ORDER BY t.StartTime ;
I suppose you could change this script to possibly se The 'Log File Auto Grow' or something similar.
This will only work if the physical file grows. I would then setup a trace over night to capture what is going on put the script can help to pin point the APP etc.
April 27, 2017 at 10:28 am
Is your Always On stuff behind?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 28, 2017 at 4:18 am
TheSQLGuru - Thursday, April 27, 2017 10:28 AMIs your Always On stuff behind?
you mean mirroring delays? no, not that i noticed. we have 3 replicas, 2 for HA synchronous, 1 for DR async at a remote site.
how would that affect tempdb log?
fyi, we had no issues again since my second reply (no explosive growth, tempdb log > 70%). waiting for the next occurance.
April 28, 2017 at 4:19 am
i'm still quite interested in any opinions of these:
a) why isn't the automatic checkpoint kicking in and clearing down the tempdb log?
b) would it be acceptable to have my tempdb log alert fire off a job to checkpoint the tempdb? workaround idea if no solution to a)
May 10, 2017 at 3:43 am
select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0
Get this to table, you can see what has been using TEMPdb and the amount of pages allocated and deallocated.
May 10, 2017 at 3:47 am
Just to add it will tell you the SPID that is causing the issue.
You might want to log SYS.SYSPROCESSES to a table too, if you don't have a monitoring tool.
May 10, 2017 at 8:04 am
Talib123 - Wednesday, May 10, 2017 3:43 AMselect * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0Get this to table, you can see what has been using TEMPdb and the amount of pages allocated and deallocated.
i'm not having the problem on the same server (it hasn't reoccurred) but this week i have migrated a SQL 2008 R2 database to a new SQL 2014 VM and dedicated SQL instance and TEMPDB LOG is slowly creeping upwards. the TEMPDB DATA is defined as 6GB and the LOG is now 13GB and currently 62% full. TEMPDB DATA is 99.68% free. the largest TEMPDB LOG fro the old server prior to migration is only 1.6GB. running the script, it points to two processes: BRKR EVENT HANDLER and BRKR TASK, so normal system processes. any idea why these processes would be eating so much of my TEMPDB LOG?
May 11, 2017 at 2:58 am
Can you send the output of the script.
May 11, 2017 at 2:59 am
May 11, 2017 at 3:02 am
You need to run that script at the time the log is growing, or the process that is growing the log is running.
Hence logging it to a table, if it is an over night process, along with logging sys.sysprocesses.
May 11, 2017 at 8:50 am
Talib123 - Thursday, May 11, 2017 3:02 AMYou need to run that script at the time the log is growing, or the process that is growing the log is running.
Hence logging it to a table, if it is an over night process, along with logging sys.sysprocesses.
thanks for the help! the script always reports the same information which is very little tempdb usage from 2 processes (the same mentioned above). we are not using broker services. looks like it's getting released at some point because at the moment we're back down to 43% used in a 13GB log file.
task_address is_remote_task session_id request_id exec_context_id database_id user_objects_alloc_page_count user_objects_dealloc_page_count internal_objects_alloc_page_count internal_objects_dealloc_page_count
------------------ -------------- ---------- ----------- --------------- ----------- ----------------------------- ------------------------------- --------------------------------- -----------------------------------
0x0000000362BAC4E8 0 26 0 0 2 8 0 16 0
0x000000036D017C28 0 29 0 0 2 0 0 8 0
(2 row(s) affected)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply