InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 12:24:39 GMT20http://www.sqlservercentral.com/Forums/Topic790982-146-1.aspxThanks 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 helpStuartMon, 21 Sep 2009 04:47:50 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspx[quote][b]Stuart Davies (9/21/2009)[/b][hr]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 02 262144 262144 38938 0 128 0[/font][/quote]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.Mon, 21 Sep 2009 04:23:59 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspx[quote][b]Stuart Davies (9/21/2009)[/b][hr]OK - so really numpty time - the results are attached. What does this tell me?[/quote]this link might shed a bit more information for you.http://www.mssqltips.com/tip.asp?tip=1225From 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.aspThis 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.Mon, 21 Sep 2009 04:21:10 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspxUpdate:-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 02 262144 262144 38938 0 128 0[/font]Mon, 21 Sep 2009 04:18:10 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspxOK - so really numpty time - the results are attached. What does this tell me?Mon, 21 Sep 2009 03:39:23 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspx[quote][b]Stuart Davies (9/21/2009)[/b][hr]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 Statustempdb 49.48438 18.92959 0which seems reasonable.I haven't come across dbcc loginfo before, what am I looking for there?[/quote]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.Mon, 21 Sep 2009 03:24:35 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspxHi 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 Statustempdb 49.48438 18.92959 0which seems reasonable.I haven't come across dbcc loginfo before, what am I looking for there?Mon, 21 Sep 2009 03:19:01 GMTStuart Davieshttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspxhave 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) returnMon, 21 Sep 2009 03:06:09 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic790982-146-1.aspxHi,Running SQL2005 SP2At 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?ThanksStuartMon, 21 Sep 2009 01:57:39 GMTStuart Davies