Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajantonyv
Starting Member
12 Posts |
 Posted - 2002-03-24 : 17:24:00
|
| Hi,While running sp_monitor stored procedure last week, I received an error called arithmetic conversion overflow on it. Now, I have never had this problem before just starting last week, I was getting it. Has anyone else encountered the same problem???Also, if possible can I drop the existing sp_monitor stored procedure and get the sp_monitor stored procedure from another server, compile it on this server. Sort of like deleting and recreating with another server's stored procedure. I am assuming the stored procedures should be the same and nothing is hard coded. I am also hoping I am not going to corrupt anything. Thanks and looking for any advice. Raj Antony V |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-25 : 00:08:33
|
| for one thing NEVER PLAY WITH SYSTEM OBJECTS -------------------------------------------------------------- |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-03-25 : 11:26:55
|
| Yeah, I've seen this problem with sp_monitor on SQL7. It works fine for a period of time after you restart the server, but eventually it begins to error out on an idle time calculation. I believe the overflow exception is thrown when the value of (@idle * @mspertick) gets too big for datatype int. I am pretty sure you aren't gonna mess anything up if you make the mods directly in the sp_monitor, but as Nazim pointed out, generally it is not recommended. Instead, you can copy the code from the sp_monitor and use it to create a procedure called sp_monitor2. In this new stored proc, find the SELECT statement which calculates cpu_busy, io_busy, and idle values. In the calculation of idle value use CAST function around @idle variable to transform it to a decimal (18,0). I think that'll fix it. This problem was fixed in SQL2k. But don't try to copy the stored proc from SQL2k server to SQL7 -- SQL2k version is using bigint datatype, which wasn't available in SQL7.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
|
|
|
|
|
|
|
|
sp_monitor