Finding Rowcount problem

Question

Finding Rowcount problem

RichB

SSCrazy Eights

Points: 9651
More actions
March 28, 2007 at 5:13 am

#173029

Guys/Gals
I have a handy little proc that nips round the production dbs and stores the rowcounts/datasizes for each table in a table. This has been fine for some time, but 1 table has now got a slight problem.
I use the sysindexes.rows column for the task.
However, both this value and sp_spaceused (which iirc uses si.rows) have had the rather tragic answer of 2,147,483,647 for a couple of weeks now. EM agrees. Now I know that we are adding just shy of 30m records a day.

My question is how do I get a rowcount for this table now?

Cheers

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Use DBCC UPDATEUSAGE... see Books Online for the details... you can do the whole database, a single table, or a single index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RichB SSCrazy Eights Points: 9651 More actions · Answer 2

Ahaha ok, it seems there is a rowcnt column as well as a rows column, rowcnt is bigint rows is only int.

I shall mod my procs to use rowcnt where rows is not ok.

Cheers all.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Um... I think you really need to use the DBCC proc I told you about as part of normal maintenance, anyway.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

Its not viable. Pretty much anything (including count(*)) on this table provokes instability, and it takes 30m inserts a day, mission critical. The pure and simple problem is that the rows column in sysindexes iirc is where the sp_spaceused gets its data is only an Int, and I now have more rows than that handles. Rowcnt is a bigint and works fine. Ta for the help tho.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Ah... sorry. Didn't read the whole thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)