Finding Rowcount problem
-
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
-
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)
Intro to Tally Tables and Functions -
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.
-
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)
Intro to Tally Tables and Functions -
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.
-
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)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply