May 5, 2010 at 12:40 pm
Hi,
We have SQL Server 2005 EE 64 bit with service pack 3. The tempdb log file size growth is 600 MB a day. I did not understand why this is happening.
I want to see what queries are running currently on Tempdb from SSMS. Please advice me..
Application using this SQL instance are Share point (MOSS 2007) and Project Server 2007. I would like to know whether Share point causing the tempdb log to grow or Project Server causing the tempdb log to grow.
many thanks
May 5, 2010 at 1:25 pm
rambilla4 (5/5/2010)
Hi,We have SQL Server 2005 EE 64 bit with service pack 3. The tempdb log file size growth is 600 MB a day. I did not understand why this is happening.
I want to see what queries are running currently on Tempdb from SSMS. Please advice me..
Application using this SQL instance are Share point (MOSS 2007) and Project Server 2007. I would like to know whether Share point causing the tempdb log to grow or Project Server causing the tempdb log to grow.
many thanks
Run a server side trace and find the query.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 5, 2010 at 1:31 pm
Sys.dm_exec_requests DMVs to find the currently active requests, their associated TSQL statement, and the corresponding query plan that is allocating most space resources in tempdb.
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
Its using old form of joins so you might need to rewrite it.
Also, follow-->http://qa.sqlservercentral.com/Forums/Topic913145-146-1.aspx
HTH!
May 5, 2010 at 1:41 pm
Now, for a simple answer without running any queries: Both applications are using tempdb and contributing to its growth.
You need to monitor the size of the tempdb, both mdf and ldf. You should size tempdb so that it does not need to constantly grow, and it should reside on its own disk or set of disks.
May 5, 2010 at 3:04 pm
Now, for a simple answer without running any queries: Both applications are using tempdb and contributing to its growth.
You need to monitor the size of the tempdb, both mdf and ldf. You should size tempdb so that it does not need to constantly grow, and it should reside on its own disk or set of disks
Thanks,
Initially I have set the tempdb settings as below:
TempDB is on it's own dedicated Disk.
Initial size of mdf file is set to 10 GB & autogrowth is enabled. Currently, it used only 50 MB out of 10 GB
Initial size of ldf file is set to 20 GB & autogrowth is enabled. Cirrently, it used 10 GB out of 20 GB.
From my observation, tempdb log fize is increasing 600 MB a day. So I want to find what causing this huge growth (I'm assuming 600 MB growth a day as huge. Correct me if I'm wrong.)
May 5, 2010 at 3:06 pm
Run a server side trace and find the query.
Is there any other way to find what causing the tempdb log growth other than running a server side trace?
May 5, 2010 at 5:37 pm
I have tried to troubleshoot to find why the tempdb log file size is increasing.But I didnot get any clue so far. Please see the attachment.
Thank you
May 5, 2010 at 5:51 pm
rambilla4 (5/5/2010)
Run a server side trace and find the query.
Is there any other way to find what causing the tempdb log growth other than running a server side trace?
Yes, I demonstrated a nice method to do this with an article I wrote.
You can find it here: http://qa.sqlservercentral.com/articles/Log+growth/69476/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 1:16 pm
From the analysis I done on Tempdb usage (please see the attchment), do you think is there anything unusal tempdb log file growth is happening? In last 24 hours, the log file size of TempDB is increased by 500 MB. So the log file size is increasing 500 MB a day and there is NO Share point crawl job is running (it runs weelklty once), NO DBCC CHECKDB, NO Index rebuild/reorganize has run But still the log file is increasing 500 MB a day.
Please advice
May 6, 2010 at 1:22 pm
I think you may have an Issue with Spotlight. Those page counts for the Spotlight spid are quite high. It has caused the system to allocate 4.7Gb of pages in the system, though it releases it - that is a lot of activity that seems to be causing your growth.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 1:41 pm
I agree with Jason. I noticed it when you posted five queries with their output earlier.
Try to run following code when it grows to find the offending query, if there is anything except from spotlight.
-- This DMV query shows currently executing tasks and
-- tempdb space usage
-- Once you have isolated the task(s) that are generating lots
-- of internal object allocations,
-- you can even find out which TSQL statement and its query plan
-- for detailed analysis
select top 10
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
(SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan
from (Select session_id, request_id,
sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_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) and
t1.session_id > 50
order by t1.task_alloc DESC
May 6, 2010 at 2:32 pm
It has caused the system to allocate 4.7Gb of pages in the system, though it releases it - that is a lot of activity that seems to be causing your growth
From which query's results, you are seeing that the spot light caused the system to allocate 4.7 GB?
and I also monitoring other SQL Server instances using Spot light but there is no impact on the tempdb and the Tempdb size is less than 1 GB. So I belive Spot light may not be the culprit.
Thanks
May 6, 2010 at 2:38 pm
Query 2 output for spotlight process(Session_id 118 is Spot light monitoring tool user connected to QuestWorkDatabase) as per internal_objects_alloac_page_count is 606664 pages i.e. 4.63gb.
May 6, 2010 at 3:01 pm
I ran the same query 'query2' again. But I did not get any high values for internal_objects_alloc_page_count from spot light session.
SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC
Results:
session_id database_id user_objects_alloc_page_count user_objects_dealloc_page_count internal_objects_alloc_page_count internal_objects_dealloc_page_count
---------- ----------- ----------------------------- ------------------------------- --------------------------------- -----------------------------------
70 2 82818 82818 0 0
94 2 82270 82270 0 0
121 2 8127 8115 7968 7736
78 2 0 0 960 960
51 2 0 0 512 0
(5 row(s) affected)
Here:
SPID 70 is ProjectServer user connecting to Project_Published database
SPID 94 is ProjectServer user connecting to Project_Draft database
SPID 121 is Spotlight user connecting to QuestWorkdatabase
SPID 78 is share point user connecting to Content database
SPID 51 is Share point user connecting to Search database.
for the first time, to have high values for user_objects_dealloc_page_count from Spotlight Session was I'm checking the fragmentation level of indexes from Spot light, whcih
needs to load all indexes to show up. So that time it might took large amount of Memory.
Thanks
May 6, 2010 at 3:34 pm
MANU-J. (5/6/2010)
Query 2 output for spotlight process(Session_id 118 is Spot light monitoring tool user connected to QuestWorkDatabase) as per internal_objects_alloac_page_count is 606664 pages i.e. 4.63gb.
That is the one I was referring to.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply