Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » TempDb - odd usage figures
Add to briefcase

TempDb - odd usage figures Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Stuart Davies
Posted Monday, September 21, 2009 1:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
Hi,
Running SQL2005 SP2
At the moment I don't believe that it is causing me any problems, but tempdb is showing that there is -700% free for the data file and -3000% free for the log (there is no issue with space on the drives where the data and log files are stored).

Does anyone have an idea as to what is causing these odd figures?

Thanks

Stuart

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #790982
Silverfox
Posted Monday, September 21, 2009 3:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065
have you updated the usage for this database, I have seen similiar in the past where the stats/usage returned are totally out of sync.

what does dbcc loginfo and dbcc sqlperf(logspace) return

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #791003
Stuart Davies
Posted Monday, September 21, 2009 3:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
Hi Silverfox,
I've updated the stats and this reduces the value to be nearer zero but still a lrage negative value.


dbcc sqlperf(logspace) gives :

DB size %used Status
tempdb 49.48438 18.92959 0

which seems reasonable.

I haven't come across dbcc loginfo before, what am I looking for there?

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #791005
Silverfox
Posted Monday, September 21, 2009 3:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065
Stuart Davies (9/21/2009)
Hi Silverfox,
I've updated the stats and this reduces the value to be nearer zero but still a lrage negative value.


dbcc sqlperf(logspace) gives :

DB size %used Status
tempdb 49.48438 18.92959 0

which seems reasonable.

I haven't come across dbcc loginfo before, what am I looking for there?




dbcc loginfo gives you a break down of the log file, into virtual files and shows what is in use (active transactions) status = 2 and the logical fragments of the log file.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #791006
Stuart Davies
Posted Monday, September 21, 2009 3:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
OK - so really numpty time - the results are attached. What does this tell me?

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

  Post Attachments 
dbcc loginfo.doc (24 views, 186.50 KB)
Post #791007
Stuart Davies
Posted Monday, September 21, 2009 4:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
Update:-

After a log backup and log shrink things are a bit neater now with regard to dbcc loginfo figures :-
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 38943 2 64 0
2 262144 262144 38938 0 128 0

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #791015
Silverfox
Posted Monday, September 21, 2009 4:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065
Stuart Davies (9/21/2009)
OK - so really numpty time - the results are attached. What does this tell me?


this link might shed a bit more information for you.

http://www.mssqltips.com/tip.asp?tip=1225

From my point of view, I use it to find out how much of the log file is in use and how much has already been flushed to disk. in theory you could use it on your user databases to decide on log file usage and manipulation, and when you might want to re-schedule log backups.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

This is a very good article and explains about the fact that having lots of rows returned by using dbcc loginfo can have a performance hit, and explains how to resolve it.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #791017
Silverfox
Posted Monday, September 21, 2009 4:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065
Stuart Davies (9/21/2009)
Update:-

After a log backup and log shrink things are a bit neater now with regard to dbcc loginfo figures :-
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 38943 2 64 0
2 262144 262144 38938 0 128 0




yep you have removed all the entries that have already been flushed to disk, so the only remaining virtual files are the ones that have active transactions.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #791018
Stuart Davies
Posted Monday, September 21, 2009 4:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
Thanks Silverfox for the info (I had already found Tibor's article on this which is what led me to the backup shrinkfile)

I have now cured the symptoms. I checked the physical size of tempdb (4gb) and increased the initial size of tempdb to this. I shrank the db log and data files refreshed and the negative values went.

I don't know if this was the case, but it appeared that SQL server was not calculating free space on the actual size of the DB, rather on the initial size of it (the sums were about right if this was the case).

Thanks for your help

Stuart

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #791027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.