September 21, 2009 at 1:57 am
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 [/url]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
September 21, 2009 at 3:06 am
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
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 3:19 am
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
tempdb49.48438 18.929590
which seems reasonable.
I haven't come across dbcc loginfo before, what am I looking for there?
-------------------------------Posting Data Etiquette - Jeff Moden [/url]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
September 21, 2009 at 3:24 am
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
tempdb49.48438 18.929590
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 3:39 am
OK - so really numpty time - the results are attached. What does this tell me?
-------------------------------Posting Data Etiquette - Jeff Moden [/url]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
September 21, 2009 at 4:18 am
Update:-
After a log backup and log shrink things are a bit neater now with regard to dbcc loginfo figures :-
[font="Courier New"]FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 38943 2 64 0
2 262144 262144 38938 0 128 0[/font]
-------------------------------Posting Data Etiquette - Jeff Moden [/url]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
September 21, 2009 at 4:21 am
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 4:24 am
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 :-
[font="Courier New"]FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 38943 2 64 0
2 262144 262144 38938 0 128 0[/font]
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.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 4:47 am
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 [/url]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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply