May 6, 2010 at 3:35 pm
rambilla4 (5/6/2010)
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
Did you stop and restart spotlight?
Also, are you still seeing the massive tempdb 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 7, 2010 at 10:43 am
Yes, Still the tempdb log file size increased by 500 MB After stop and starting the Spot light. Is there any process that empty (write the log to data file) and reduce the used space from tempDB log file that I need to make sure its running normally?
Like checkpoint. How can I verify whether CHECK POINT is happening normally? Is there any script to verify?
Thanks
May 7, 2010 at 10:53 am
If it is still growing, I am going to re-recommend performing that routine outlined in the article I suggested. There is another query that is causing 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 7, 2010 at 12:18 pm
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB and causing its log file size to increase.
Is there any way to verify that if checkpoint is on/off,
working/not working?
One more thing:
I did not peform Index reorganize/rebuild from last 1 month as we have NO fragmentation >10 % in indexes having page count>1000. Is that might be the reason to grow tempdb logfile growth? Is Index reorganize/rebuild related in any way to tempbdb log file growth?
and I run the command on tempdb DBCC LOGINFO (tempdb).
Results: From the results, is there anything abnormal? what should I look for?
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 656867328 8192 106 2 64 0
2 656867328 656875520 107 2 64 0
2 656867328 1313742848 108 2 64 0
2 656867328 1970610176 109 2 64 0
2 656867328 2627477504 110 2 64 0
2 656867328 3284344832 111 2 64 0
2 656867328 3941212160 112 2 64 0
2 656867328 4598079488 113 2 64 0
2 656867328 5254946816 114 2 64 0
2 656867328 5911814144 115 2 64 0
2 656867328 6568681472 116 2 64 0
2 656867328 7225548800 117 2 64 0
2 656867328 7882416128 118 2 64 0
2 656867328 8539283456 119 2 64 0
2 656867328 9196150784 120 2 64 0
2 657907712 9853018112 121 2 64 0
2 655360000 10510925824 122 2 64 113000109795300025
2 655360000 11166285824 123 2 64 113000109795300025
2 655360000 11821645824 0 0 0 113000109795300025
2 655360000 12477005824 0 0 0 113000109795300025
2 655360000 13132365824 0 0 0 113000109795300025
2 655360000 13787725824 0 0 0 113000109795300025
2 655360000 14443085824 0 0 0 113000109795300025
2 655360000 15098445824 0 0 0 113000109795300025
2 655360000 15753805824 0 0 0 113000109795300025
2 655360000 16409165824 0 0 0 113000109795300025
2 655360000 17064525824 0 0 0 113000109795300025
2 655360000 17719885824 0 0 0 113000109795300025
2 655360000 18375245824 0 0 0 113000109795300025
2 655360000 19030605824 0 0 0 113000109795300025
2 655360000 19685965824 0 0 0 113000109795300025
2 655360000 20341325824 0 0 0 113000109795300025
(32 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
and from the link http://msdn.microsoft.com/en-us/library/ms191154(v=SQL.90).aspx
SQL Server typically issues a checkpoint in a database when the number of data modifications made in the database after the last checkpoint reaches the number SQL Server estimates it can roll forward in the recovery time interval. Sometimes SQL Server will issue the checkpoint when the log becomes 70 percent full, if that is less than the estimated number
.
Here the temdb log file size is set to 20 GB and now the used space is 10 GB i.e 50%. SO when the used space reaches to 70%, then only the check point occurs and then the used log file size frees up the space?
Is that happening in our case and is that normal?
Thanks so much
May 7, 2010 at 3:42 pm
Based on the last query you for which you provided results showing the allocation pages, it appears that checkpointing is occurring just fine. The pages are deallocated and freed up for continued use.
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 7, 2010 at 4:08 pm
Thanks Jason,
Based on the last query you for which you provided results showing the allocation pages, it appears that checkpointing is occurring just fine. The pages are deallocated and freed up for continued use.
from the above query results, is the FileSize you are referring to allocation pages? COuld you please tell me based on what coulmn results you are saying that the Checkpoint is occured.
Thanks
May 7, 2010 at 4:27 pm
Hi,
I went through the link http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic5015.aspx
where the user Carolyn Godfrey stating the below:
Since tempdb does not require recovery, are there no automatic checkpoints
for tempdb except those when the log gets to be 70% full?
Note that the tempdb log truncations were not occurring for hours
Is that correct? Please clarify me..
Thanks
May 7, 2010 at 4:38 pm
rambilla4 (5/7/2010)
Thanks Jason,Based on the last query you for which you provided results showing the allocation pages, it appears that checkpointing is occurring just fine. The pages are deallocated and freed up for continued use.
from the above query results, is the FileSize you are referring to allocation pages? COuld you please tell me based on what coulmn results you are saying that the Checkpoint is occured.
Thanks
It's not a single column but the combination of columns. You have pages that were allocated and then deallocated. That means they were freed up and the space is free to use within the file again - that is what a checkpoint does. The checkpoint frees the pages for re-use in the log file.
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 7, 2010 at 4:41 pm
rambilla4 (5/7/2010)
Hi,I went through the link http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic5015.aspx
where the user Carolyn Godfrey stating the below:
Since tempdb does not require recovery, are there no automatic checkpoints
for tempdb except those when the log gets to be 70% full?
Note that the tempdb log truncations were not occurring for hours
Is that correct? Please clarify me..
Thanks
I believe that to be inaccurate.
I really think you are still barking up the wrong tree on this. You have a process that caused your tempdb to grow to a certain size. Once that process finished, the pages became free. Once a file grows, it will not shrink automatically unless you have autoshrink turned on (bad idea).
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 7, 2010 at 5:46 pm
You have a process that caused your tempdb to grow to a certain size. Once that process finished, the pages became free. Once a file grows, it will not shrink automatically unless you have autoshrink turned on (bad idea).
Thanks,
1. It's not a single process that caused the tempdb log file to grow as it is increasing 500 MB a day.
2. Currently, the ldf file size of tempdb is 20 GB and used space is 10 GB. So we cannot shrink the log file lessthan 10
GB right? The disk space where TempDB exist is 50 GB. So can I wait untill it fills up to 40 Gb and then restart the SQL service to release all the space right?. Here, the tempDB data file not at all increasing though! it's still <20 MB..
3. The apllication is share point, so do we need to check anything from share point side?
May 7, 2010 at 6:21 pm
rambilla4 (5/7/2010)
You have a process that caused your tempdb to grow to a certain size. Once that process finished, the pages became free. Once a file grows, it will not shrink automatically unless you have autoshrink turned on (bad idea).
Thanks,
1. It's not a single process that caused the tempdb log file to grow as it is increasing 500 MB a day.
It may be more than one process. But for tempdb to grow that much in a day is substantial and indicative of at least one process causing your growth. It may be a single process that runs each day and causes 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 10, 2010 at 7:42 am
another tempdb growth issue, this is just on the log file where as mine is on the datafile. Do you know +- what time the issue is happening? I intend on running a trace when my tempdb mutates (early hours of the morning) to try and get more info ...
check the suggstions on this thread from the guys above http://qa.sqlservercentral.com/Forums/Topic910208-146-1.aspx
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 11, 2010 at 6:07 pm
another tempdb growth issue, this is just on the log file where as mine is on the datafile. Do you know +- what time the issue is happening? I intend on running a trace when my tempdb mutates (early hours of the morning) to try and get more info ...
check the suggstions on this thread from the guys above http://qa.sqlservercentral.com/Forums/Topic910208-146-1.aspx
In my case, the log file is increasing 500 MB a day. I'm running a job every 30 mins to find the used space of tempdb's ldf. Please see the attchment. It's a continuous growth and NOT like sudden growth at one point in time.
Now I want to setup the trace using SQL Profiler to capture the T-sql queries. Could you please advice me what exactly I need to select while creating a new trace from Profiler.
1. Which template I need to select (like standard (default), tsql, tsql_duration)
2.What are the events & columns to select
3. What are the filters to apply (like select database as tempdb)
If see the attachment, the log file is growing continuously NOT at one point in time.
I ran the job 4 days to capture the log file size growth from 2010-05-07 14:00:00 to 2010-05-11 14:00:00.
Thanks
May 12, 2010 at 10:20 am
any advice after seeing the tempdb log file growth patteren from the attachment(':hehe:');
thanks
May 12, 2010 at 12:46 pm
Finally I ran the SQL Profile for an hour. Before running the profiler, I stopped the Spot light service.
1. I have selected the standard template with the filter database name as tempdb and stored the results in a table called TempDBGrowth. Please see the attachment
2. Ran the below query
select * from dbo.TempdbGrowth
where databasename='tempdb'
3. Please see the attachment 'Profile_output' for the results.
4. Please see the attachment 'tempdbgrowth' for temppdb log file growth for the duration of that one hour (while the profiler is running.)
In Summary, I did not find any queries running against tempdb during this one hour except the sql agent job that I scheduled to run every 30 mins to get the tempdb size.But still I can see the continuous growth of ldf file (from the attachment, tempdbgrowth )
Please advice if you find anything that causing the tempdb log file growth.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply