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 |
|
hooperm
Starting Member
5 Posts |
 Posted - 2010-08-20 : 12:46:33
|
| Issue: We have an application that monitors all our servers. It also monitors databases and aspects of SQL server. The application uses MySQL for its databse but the issue is with a database on a SQL 2000 server that can't be upgraded at this time. On this server there is a database that displays the space available as (unknown) in the properties. When the plloing function of the application tries to get the available space from this database it fails and then kills the process for the remaining databases. I believe that if we can get this databse to properly display the space available it will fix the problem. Below is the log file from the poler service of the application showing the error. 20 Aug 2010 09:09:08 INFO [Thread-24] [Logger.logAlways] - Built Varbinding Text: host = 10.17.210.166<br>databaseName = ManagementServer<br>usedPercentage = ?% NotifierEvent20 Aug 2010 09:09:08 INFO [Thread-24] [Logger.logInfo] - Successfully updated appropriate database tables to record the received trap TrapEventsDBManager20 Aug 2010 09:09:08 INFO [Thread-24] [Logger.logAlways] - UPDATE devices SET LastTrapTime=1282320548301 WHERE DeviceId=360 DeviceTableDBManager20 Aug 2010 09:09:08 INFO [Thread-24] [Logger.logInfo] - Obtained subscriber list for this device TrapEventNotifer20 Aug 2010 09:09:08 INFO [Thread-24] [ServerSetupManager.getSSP] - Fetching SSP by name: stEmailServer20 Aug 2010 09:09:08 INFO [Thread-24] [ServerSetupManager.getSSP] - Fetching SSP by name: stEmailPort20 Aug 2010 09:09:08 INFO [Thread-24] [ServerSetupManager.getSSP] - Fetching SSP by name: emailServerUsername20 Aug 2010 09:09:08 INFO [Thread-24] [ServerSetupManager.getSSP] - Fetching SSP by name: emailServerPassword20 Aug 2010 09:09:08 INFO [Thread-24] [ServerSetupManager.getSSP] - Fetching SSP by name: emailServerUseSmtps20 Aug 2010 09:09:08 INFO [Thread-24] [MailSender.init] - SMTP Server config: Server- inbound.saccounty.net Port- 2520 Aug 2010 09:09:08 INFO [Thread-24] [Logger.logInfo] - Notifying subscribers TrapEventNotifer20 Aug 2010 09:09:08 INFO [Thread-24] [Logger.logAlways] - StatementCallback; bad SQL grammar [INSERT INTO db_usage(deviceId,time,dbName,totalMbUsed,unallocatedSpace,reservedSize,dataSize,indexSize,unusedSize, totalMb, freeMb,pctused) VALUES (360,1282320546511,'ManagementServer',1245.7578125,0.0,0.0,0.0,0.0,0.0,0.0,1067.3671875,Infinity)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Infinity' in 'field list' RunSqlServerPoller.run20 Aug 2010 09:09:08 ERROR [Thread-24] [Logger.logError] - StatementCallback; bad SQL grammar [INSERT INTO db_usage(deviceId,time,dbName,totalMbUsed,unallocatedSpace,reservedSize,dataSize,indexSize,unusedSize, totalMb, freeMb,pctused) VALUES (360,1282320546511,'ManagementServer',1245.7578125,0.0,0.0,0.0,0.0,0.0,0.0,1067.3671875,Infinity)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Infinity' in 'field list'org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [INSERT INTO db_usage(deviceId,time,dbName,totalMbUsed,unallocatedSpace,reservedSize,dataSize,indexSize,unusedSize, totalMb, freeMb,pctused) VALUES (360,1282320546511,'ManagementServer',1245.7578125,0.0,0.0,0.0,0.0,0.0,0.0,1067.3671875,Infinity)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Infinity' in 'field list' |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hooperm
Starting Member
5 Posts |
Posted - 2010-08-20 : 15:04:03
|
| This app queries over 250 databases on eight different servers. The only database with an issue is the one that reports the space available as unknown. If we could resolve that issue then and still had the problem then I would go back to the devloper to look at the code. Until the space unknown issue is resolved they aren't going to consider it a code problem. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-20 : 15:35:48
|
| You've got several errors in your log and specifically with MySql:INSERT INTO db_usage(deviceId,time,dbName,totalMbUsed,unallocatedSpace,reservedSize,dataSize,indexSize,unusedSize, totalMb, freeMb,pctused) VALUES (360,1282320546511,'ManagementServer',1245.7578125,0.0,0.0,0.0,0.0,0.0,0.0,1067.3671875,Infinity)As for the unknown issue, well we don't have enough information to be able to assist. The SQL code isn't in your log and since you didn't post it, we can't even tell what it is running to be able to help. All we can do is look at what you've posted and point out things that seem "off" to us.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2010-08-21 : 16:37:24
|
| At a guess, whatever code is constructing the SQL insert statements is rendering an infinity value in a numeric (probably floating point) data type as "Infinity" when converting it into a string.Java is one example of a programming language that does this, and moreover, it produces infinity when dividing (positive) floating point values by zero (rather than generating a run-time exception). |
|
|
|
hooperm
Starting Member
5 Posts |
Posted - 2010-08-23 : 10:04:49
|
| Thanks for the replies. Let me try and clarify the issue. I'm trying to solve a problem with one database that reports the space available as unknown. When the MySQL process hits this db it kicks out the errors as shown in the log file above. We have tried to run DBCC UPDATEUSAGE on the database and it give back an error. Arithmetic overflow error converting numeric to data type numeric.The MySQL process runs error free until it gets to this one database. This is the only database that has size avaialbe as unknown. Thanks again for the help. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-23 : 10:30:10
|
it is a bug in the app. is this a homegrown app? find the line that is calling it "infinity" and make sure it calls it NULL.as to the "unknown" (which means null, not infinity),what is result if youexec sp_spaceused on the db in question? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hooperm
Starting Member
5 Posts |
Posted - 2010-08-24 : 17:45:16
|
| tkizer, No the database is 23Gb in size. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-08-24 : 22:38:56
|
quote:
Originally posted by hoopermThe MySQL process runs error free until it gets to this one database. This is the only database that has size avaialbe as unknown.
Have you considered running the same "process" outside of mySQL?OH, and BTW, what exactly is the "process"?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
hooperm
Starting Member
5 Posts |
Posted - 2010-08-25 : 18:03:28
|
| Runnig the process outside mySQL is not an option. We purchased an application for monitoroing our servers from Tek-Tools (now SolarWinds). I have no ability to port anything over to SQL and would likely void warranty if I did. SolarWinds has looked at the logs and determined the issue lies with this one database. So I'm left with trying to solve the problem with the database. It appears fine except for the space avaialble and the error when we try to run UPDATEUSAGE. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Infinity