cancel
Showing results for 
Search instead for 
Did you mean: 

sysstatistics row size

Former Member
0 Kudos

Hi

I'm trying to set up regular update statics. Due to some problems we had in the past, I have to first make a backup of the statistics, prior to the update, and I also want to do update statistics on backup server and transfer them to the primary server (we have a warm-standby enviroment).

Now, I know I can use optdiag to do the backup and transfering between servers, but that requires sso_role, which I don't have.

So, I wanted to make a table copy of sysstatistics, (named sysstatistics_backup) and system procedure, that would delete sysstatistics and insert there data from sysstatistics_backup, so I could use it either for loading old statistics or to transfer them to primary server (I can get our sso to turn on allow updates to system tables when i create the proc, but I can't make him to transfer the stats via optdiag every week).

But the first hurdle, when i do

select * into sysstatistics_backup from sysstatistics

i get

Warning: Row size (20706 bytes) could exceed row size limit, which is 1964 bytes.

and when i compare the data in the two tables, they are not the same.

My question : how come the rows in sysstatistics are wider than server page and can I somehow achieve the same thing for my backup table?

Or, am I doing the whole thing wrong and there is some other way to do, what I want to do?

Adaptive Server Enterprise/15.0.3/EBF 17770 ESD#4/P/x86_64/Enterprise Linux/ase1503/2768/64-bit/FBO/Thu Aug 26 09:54:27 2010

2k page size

Thanks

Karel

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor
0 Kudos

You don't have to worry about the warning.  The table does not actually contain any rows that are wider than allowed.  The warning is just saying that the table definition contains some variable-length columns that can't all be filled to their full length with data.  As a very simplified example, if the page size is 2k, you can create a table with 5 varchar(1024) columns, but you are still limited to 2k of data distributed across the 5 columns - you could have 2 at 1024 bytes, of 4 at 512, or 1 with 1024 and 4 with 205 bytes each.   (real values are somewhat smaller due to overhead on the page, but that is the general idea.).   If you try to insert more data than the row can really hold, you will get an actual error and the insert will fail and roll back.

-bret

Former Member
0 Kudos

Ha, you are correct, me = stupid, was comparing the data between the sysstatistics and sysstatistics_backup using my eyes, and since the data in the backup table didn't have trailing zeroes, I thought the data was somehow trucated. Good to know that is not the case.

Thanks

Karel

Answers (0)