May 17, 2012 at 3:40 am
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
May 17, 2012 at 3:55 am
do you have service broker enabled ? if so have a look at the queues and see what is in there .
MVDBA
May 17, 2012 at 8:42 am
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.
May 17, 2012 at 8:57 am
look in sys.transmission_queue
these should be messages that coudn't be delivered may be they are re-trying ?
MVDBA
May 17, 2012 at 8:58 am
have a look at this article on troubleshooting service broker
http://www.sqlteam.com/article/how-to-troubleshoot-service-broker-problems
MVDBA
May 17, 2012 at 9:00 am
michael vessey (5/17/2012)
look in sys.transmission_queuethese should be messages that coudn't be delivered may be they are re-trying ?
apologies - i didn't read your post correctly 🙁
how about the inbound queue of your custom made services
MVDBA
May 17, 2012 at 9:15 am
michael vessey (5/17/2012)
michael vessey (5/17/2012)
look in sys.transmission_queuethese should be messages that coudn't be delivered may be they are re-trying ?
apologies - i didn't read your post correctly 🙁
how about the inbound queue of your custom made services
Mike,
Thanks for the advise.
Could you please give me some pointers on how to check the inbound queue of my custom made services?
I'm not familiar with it.
Thanks.
May 17, 2012 at 9:54 am
What do the errors in your ERRORLOG say? They should identify what proc/batch is failing due to insufficient TempDb space.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 17, 2012 at 11:56 am
RBarryYoung (5/17/2012)
What do the errors in your ERRORLOG say? They should identify what proc/batch is failing due to insufficient TempDb space.
The ERRORLOG contains these:
2012-05-18 00:51:48.81 spid18s Error: 1105, Severity: 17, State: 2.
2012-05-18 00:51:48.81 spid18s Could not allocate space for object '<temporary system object: 491897996509184>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2012-05-18 00:51:48.81 spid18s Error: 9736, Severity: 16, State: 4.
2012-05-18 00:51:48.81 spid18s An error occurred in dialog transmission: Error: 1105, State: 2.
2012-05-18 00:51:48.81 spid18s Error: 1105, Severity: 17, State: 2.
2012-05-18 00:51:48.81 spid18s Could not allocate space for object '<temporary system object: 491897996509184>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2012-05-18 00:51:48.81 spid18s Error: 9736, Severity: 16, State: 4.
2012-05-18 00:51:48.81 spid18s An error occurred in dialog transmission: Error: 1105, State: 2.
Those 2 error messages keep repeating all the way.
I'm not sure it says much about the process which is failing.
May 17, 2012 at 12:56 pm
Hmm, well "An error occurred in dialog transmission:" is definitely Service Broker stuff. Have you checked to see what spid 18 is on your Sql server?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2012 at 2:18 am
adrian.sudirgo (5/17/2012)
michael vessey (5/17/2012)
michael vessey (5/17/2012)
look in sys.transmission_queuethese should be messages that coudn't be delivered may be they are re-trying ?
apologies - i didn't read your post correctly 🙁
how about the inbound queue of your custom made services
Mike,
Thanks for the advise.
Could you please give me some pointers on how to check the inbound queue of my custom made services?
I'm not familiar with it.
Thanks.
go to your database, expand the service broker section and look for queues - then its a case of SELECT * FROM queuename - or right click on the queue and select the option to view it.
most likely it is the receive queue, but depending on your converstaion structure and queue names i would have no way of telling you which ones to look at
MVDBA
May 18, 2012 at 4:11 am
go to your database, expand the service broker section and look for queues - then its a case of SELECT * FROM queuename - or right click on the queue and select the option to view it.
most likely it is the receive queue, but depending on your converstaion structure and queue names i would have no way of telling you which ones to look at
On my Service Broker, I only have 2 custom queues: lbs_in and lbs_out.
I tried running SELECT * FROM [dbo].[lbs_in] and SELECT * FROM [dbo].[lbs_out], but both returned empty table. Meaning the queues are empty.
Do you have any other suggestions that could help me to narrow down the cause of the problem? I'm really lost...
Thanks
May 18, 2012 at 4:45 am
sorry - its a bit difficult to diagnose without being able to actually get my hands on the system.
as was posted earlier by someone else - have a look at those spids that are firing up from the service broker and see what they are trying to do
just in case (i'm not teaching anyone to suck eggs here, but worth posting just in case)
sp_who2 should get you the list of spids
and dbcc inputbuffer(57) will get you the input string of the spid (replace 57 with desired spid)
also - is there any chance that another database on that server also has service broker queues?
MVDBA
May 18, 2012 at 4:49 am
are you using database mirroring? and if so what service pack are you on ?
MVDBA
May 18, 2012 at 4:54 am
also try looking to see how many conversations are still open
select * from sys.conversation_endpoints
maybe your application is not closing the conversations after the messages.
since you've got nothing on the queues, if there are lots of rows in conversation_enpoints then that would be the issue...(speculating here by the way)
MVDBA
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply