Tempdb Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Tempdb Problem

Author

Topic

adrian01
Starting Member

5 Posts

Posted - 2012-05-17 : 01:08:32

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

Sachin.Nand

2937 Posts

Posted - 2012-05-17 : 01:52:54

Looks like someone tried to play with Service Broker on your server.Check whether Service Broker is currently running on it.

After Monday and Tuesday even the calendar says W T F ....

adrian01
Starting Member

5 Posts

Posted - 2012-05-17 : 02:25:10

Yes, actually we are using Service Broker for our application.
Is it the one causing the problem? We have been using Service Broker for years but never experiencing this problem.

I tried running
SELECT TOP 10 * FROM sys.transmission_queue

but it returns empty.

Sachin.Nand

2937 Posts

Posted - 2012-05-17 : 03:47:58

I had heard/read sometime back that not closing the dialogs properly causes the tempdb to fill.

After Monday and Tuesday even the calendar says W T F ....

adrian01
Starting Member

5 Posts

Posted - 2012-05-17 : 11:24:09

Is there any way to check where is the trouble in the Service Broker?

prett
Posting Yak Master

212 Posts

Posted - 2012-05-18 : 04:24:34

Hello Adrian,

Restarted the SQL Server is the short term solution of the issue. I found an excellent article on causes of tempdb to behave this way. Here are some suggestions http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html
Hope the article will help you!!

adrian01
Starting Member

5 Posts

Posted - 2012-05-18 : 12:22:35

Yes, it is indeed the service broker.
select * from sys.conversation_endpoints returns 5,698,396 rows of records! And it took 14 minutes to run the query.
I just need to do END CONVERSATION.

Thanks all of you who have helped.

granuharmot
Starting Member

31 Posts

Posted - 2014-09-23 : 05:12:56

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources