Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TempDb - odd usage figures Expand / Collapse
Author
Message
Posted Monday, September 21, 2009 1:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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
Posted Monday, September 21, 2009 3:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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
Posted Monday, September 21, 2009 3:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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
Posted Monday, September 21, 2009 3:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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
Posted Monday, September 21, 2009 3:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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 (25 views, 186.50 KB)
Post #791007
Posted Monday, September 21, 2009 4:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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
Posted Monday, September 21, 2009 4:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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
Posted Monday, September 21, 2009 4:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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
Posted Monday, September 21, 2009 4:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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