Sysperfinfo

Question

Sysperfinfo

stubbybub

Valued Member

Points: 66
More actions
December 18, 2003 at 1:41 pm

#59925

Hi,
I had an issue earlier today where I created a sql server alert to monitor when the log for a specific database reached 50% full. When this happens the log gets truncated.
The alert fired several times today and the log file was only about 2 MB's so I added space to it to make it 25 MB.
The alert keep firing for about an hour after I added the space to the log.
I noticed that
DBCC SQLPERF(logspace)
Told me my log was using only 2%. However when I selected the information from sysperfinfo it said the log was at 51%.
So my question is this:
When does sysperfinfo get updated? I thought it was a virtual table and got updated at the time of select. So should I have ran update statistics or something on sysperfinfo to update it?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

cbadiseshan Mr or Mrs. 500 Points: 566 More actions · Answer 1

quote:
Hi,
Nice to see your email. Actually, sysperfinfo is not a virtual table. it is a system table. It will get updated when you create performance counters. Please check with SQL BOL
Hi,
I had an issue earlier today where I created a sql server alert to monitor when the log for a specific database reached 50% full. When this happens the log gets truncated.
The alert fired several times today and the log file was only about 2 MB's so I added space to it to make it 25 MB.
The alert keep firing for about an hour after I added the space to the log.
I noticed that
DBCC SQLPERF(logspace)
Told me my log was using only 2%. However when I selected the information from sysperfinfo it said the log was at 51%.
So my question is this:
When does sysperfinfo get updated? I thought it was a virtual table and got updated at the time of select. So should I have ran update statistics or something on sysperfinfo to update it?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Actually it IS a fake table

Try this



SELECT 

OBJECTPROPERTY(OBJECT_ID('sysprocesses'),'TableIsFake'), 

OBJECTPROPERTY(OBJECT_ID('syslockinfo'),'TableIsFake'),

OBJECTPROPERTY(OBJECT_ID('sysperfinfo'),'TableIsFake'),

OBJECTPROPERTY(OBJECT_ID('syscolumns'),'TableIsFake')



                                                

----------- ----------- ----------- ----------- 

1           1           1           0



(1 row(s) affected)

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

stubbybub Valued Member Points: 66 More actions · Answer 3

So why is dbcc sqlperf(logspace) and sysperfinfo showing different information for the percent log used? When does sysperfinfo get updated?

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 4

Likely the information comes from two different places and is calculated differently.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net