February 20, 2008 at 9:38 am
just wondering if I'm missing a dbcc option, I note that if I run dbcc dropcleanbuffers it only clears the user databases and usage by tempdb is not cleared. I tried checkpointing and dbcc updateusage on tempdb but ti doesn't seem to clear down. SQL 2005 sp2 x64 3215
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 20, 2008 at 1:40 pm
How are you determining usage?
February 21, 2008 at 1:40 am
reported by both the dmvs and diagnostic manager.
you can see the report I use on this page http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 21, 2008 at 8:20 am
I installed the buffer space rdl and looked at the query ... it is accurate for the information it is reporting but it doesn't distinguish between clean and dirty pages on sys.dm_os_buffer_descriptors - could this be the reason why you still see the tempdb pages in the data buffer?
February 21, 2008 at 8:37 am
if the page is there , clean or dirty, it's still in cache. I did look at the dirty pages but technically a checkpoint should have flushed them, add to which I find the concept of 4.5gb of dirty tempdb pages somewhat strange. Still it's something to ask microsoft I guess.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 21, 2008 at 9:17 am
When I try all the clean pages are removed, the remaining dirty pages all belong to system objects (I'm the only user on the server). If you ever find out, I'd be interested to know what the problem is.
February 22, 2008 at 8:40 am
unresolved as yet - I will blog about it this weekend and see if it gets a suggestion - I asked at a user group meeting but no-one had any real ideas, you can read a post about tempdb Tony did which covers some part of it http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 22, 2008 at 9:19 am
My first thought was that you didn't have the hotfix for the tempdb table issue - but then noticed you are on 3215 so that is out as a suggestion. I know you are sharp when it comes to SQL Server so you have obviously disconnected your connections that were using tempdb objects in the first place. BOL doesn't have anything about NOT flushing tempdb pages, as I am sure you noted. Prolly best to hit MS up for an answer.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 10, 2012 at 6:56 am
you will need to execute a manual checkpoint command in tempdb because tempdb isonly flushed when its tranlog becomes >70% full.
See http://www.sqlskills.com/BLOGS/PAUL/post/What-does-checkpoint-do-for-tempdb.aspx
April 11, 2012 at 12:45 pm
Check out this thread and my post at the end ...
http://www.sqlservercentral.com/Forums/Topic544573-146-2.aspx
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 11, 2012 at 3:49 pm
Hi - oh such a long time ago!! I did actually raise the issue with microsoft and in SQL 2005 you can't clear Tempdb buffer cache, it's not particularly by design and it was due to be fixed sometime forwards - maybe in SQL 2012, I will have to check.
I don't really use SQL 2005 any more so I guess i'm not too concerned. I had a similar sort of problem, it's quite amazing how developers can write code which does the most interesting things!!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 12, 2012 at 9:05 am
It has actually gotten even worse in one respect - in SQL 2008 R2, and probably 2012, they locked out the ability to clear the tempdb transaction log !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 26, 2012 at 6:16 am
🙂
Thanks & Best Regards
Sumit Rana
May 2, 2012 at 5:37 am
almost 4 years old post 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply