dm_os_performance_counters Data File(s) Size (KB) or Log File(s) Size (KB) with negative values

I am using dm_os_performance_counters to query for Data File(s) Size (KB) and Log File(s) Size (KB) of my databases. Here is a copy of the  query I am using:

select object_name, counter_name, instance_name, cntr_value, cntr_type
from sys.dm_os_performance_counters
where (counter_name in ('Log File(s) Size (KB)', 'Data File(s) Size (KB)'))

Open in new window

This works on most of my databases, however at seemingly random times some of these cntr_value results are negative numbers. This happens on multiple servers, so I could not relegate it to a specific server having the an issue, and it happens on different databases at different times. When I look at the database properties through Management Studio the files are listed with the correct sizes, it is just from dm_os_performance_counters. The only way I have found so far to get the server to update the performance counters is to offline and then online the affected database.

If anyone by chance knows why this would be happening, or a better way to force an update on the performance counters I would be greatly appreciative.

Thanks in advance,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:

"cntr_value  bigint  Current value of the counter.

For per-second counters, this value is cumulative. The rate value must be be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time inter "

this means your file size decreased in value due to backups for instance.

You can use this instead:
--in each DB:
SELECT name ,
            size/128.0 as SizeInMB,
            CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 as UsedInMB,
            size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

--at server level:
select * into #temp_sysfiles from sys.sysfiles where 1=2

EXEC sp_MSforeachdb 'Use [?] insert into #temp_sysfiles select * from sys.sysfiles'

select * from #temp_sysfiles order by name,filename

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ynpmikoAuthor Commented:
Thanks Icohan, I didn't realize it was a per second counter. It does strike me as odd that the counter can get out of sync with the actual value, but I can work your solution into my statistics logging model with a little modification.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.