Hi,
I am using SQL Server 2005 and .NET applications on mys erver which has been running fine for 3 years. Suddenly yesterday I got notification low disk on my primary C drive. It turned out that my tempdb.mdf grew very large up to 10 GB. We did nothing within these few days (meaning we didn't make any changes to the settings, servers, or applciations)
To solve this tempdb problem, this is what I have done:
1. Restarted the SQL Server (MSSQLSERVER) from Services. After doing so, the tempdb is back to normal which is 200MB. But within 1 hour it grew back to 10 GB!
2. I set the Maximum File Size for the tempdb to 3000 MB, then restarted the SQL Server. Well, this time the tempdb.mdf still grew very fast from 200MB to 3000MB and stop. But another problem arises: it causes error which is then logged into ERRORLOG. Thus, this ERRORLOG file grew up to 8 GB within a few hours.
Because those 2 attemps failed, I tried to find out what causes the tempdb to behave this way. This is what I did:
- I stopped all my .NET windows applications
- I stopped all FTP Sites, Application Pools, Web Sites and Web Service Extensions from the Internet Information Services (IIS) Manager
- Then I restarted the SQL Server from Services
But the thing is, the tempdb.mdf still grows very fast from 200MB to 10 GB!
I run a trace in SQL Server Profiler, and it shows that there is no activity at all!
So I can conclude that the this tempdb problem is not caused by any of the applications.
I tried to run this T-SQL:
SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC
(Sorry, I could not find a way to paste table here, so I just summarized the important information)
And from the result returned only 3 of them that has values in task_alloc:
session_id 12 has task_alloc 24744
session_id 14 has task_alloc 360
session_id 20 has task_alloc 112
Then I ran this command:
select * from Sys.dm_exec_requests
Then this is what I got for those 3 session ids:
session_id: 12
status: background
command: BRKR EVENT HNDLR
sql_handle: NULL
statement_start_offset: NULL
statement_end_offset: NULL
plan_handle: NULL
database_id: 1
user_id: 1
connection_id: NULL
blocking_session_id: 0
wait_type: BROKER_EVENTHANDLER
wait_time: 1046
last_wait_type: BROKER_EVENTHANDLER
wait_resource:
open_transaction_count: 0
open_resultset_count: 1
transaction_id: 0
context_info: NULL
percent_complete: 0
estimated_completion_time: 0
cpu_time: 60718
total_elapsed_time: 0
scheduler_id: 0
task_address: 0x006D87A8
reads: 32
writes: 30424
logical_reads: 8433857
text_size: 4096
language: us_english
date_format: mdy
date_first: 7
quoted_identifier: 1
arithabort: 0
ansi_null_dflt_on: 1
ansi_defaults: 0
ansi_warnings: 1
ansi_padding: 1
ansi_nulls: 1
concat_null_yields_null: 1
transaction_isolation_level: 2
lock_timeout: -1
deadlock_priority: 0
row_count: 0
prev_error: 0
nest_level: 1
granted_query_memory: 0
executing_managed_code: 0
session_id: 14
status: background
command: BRKR TASK
sql_handle: NULL
statement_start_offset: NULL
statement_end_offset: NULL
plan_handle: NULL
database_id: 1
user_id: 1
connection_id: NULL
blocking_session_id: 0
wait_type: NULL
wait_time: 0
last_wait_type: LATCH_EX
wait_resource: SERVICE_BROKER_TRANSMISSION_WORKTABLE (00000000)
open_transaction_count: 0
open_resultset_count: 1
transaction_id: 0
context_info: NULL
percent_complete: 0
estimated_completion_time: 0
cpu_time: 2532
total_elapsed_time: 0
scheduler_id: 1
task_address: 0x008CC988
reads: 0
writes: 0
logical_reads: 52584
text_size: 4096
language: us_english
date_format: mdy
date_first: 7
quoted_identifier: 1
arithabort: 0
ansi_null_dflt_on: 1
ansi_defaults: 0
ansi_warnings: 1
ansi_padding: 1
ansi_nulls: 1
concat_null_yields_null: 1
transaction_isolation_level: 2
lock_timeout: -1
deadlock_priority: 0
row_count: 0
prev_error: 0
nest_level: 1
granted_query_memory: 0
executing_managed_code: 0
session_id: 20
status: background
command: BRKR TASK
sql_handle: NULL
statement_start_offset: NULL
statement_end_offset: NULL
plan_handle: NULL
database_id: 5
user_id: 1
connection_id: NULL
blocking_session_id: 14
wait_type: LATCH_SH
wait_time: 0
last_wait_type: LATCH_SH
wait_resource: SERVICE_BROKER_TRANSMISSION_WORKTABLE (02A06750)
open_transaction_count: 0
open_resultset_count: 1
transaction_id: 0
context_info: NULL
percent_complete: 0
estimated_completion_time: 0
cpu_time: 266625
total_elapsed_time: 0
scheduler_id: 0
task_address: 0x006D8898
reads: 108523
writes: 132
logical_reads: 10875333
text_size: 4096
language: us_english
date_format: mdy
date_first: 7
quoted_identifier: 1
arithabort: 0
ansi_null_dflt_on: 1
ansi_defaults: 0
ansi_warnings: 1
ansi_padding: 1
ansi_nulls: 1
concat_null_yields_null: 1
transaction_isolation_level: 2
lock_timeout: -1
deadlock_priority: 0
row_count: 0
prev_error: 0
nest_level: 1
granted_query_memory: 0
executing_managed_code: 0
I noticed that the cpu time, reads, writes and logical reads are high for those 3 session_ids.
But I don't have any ideas what are those 3 transactions. Are those the ones causing my tempdb.mdf to grow large out of control?
Have any of you experiencing the same problems? Any suggestions or ideas?
Any help is appreciated.
Thank you!
Adrian
Tempdb Problem
Posted - 05/17/2012 : 01:08:32
