|
| | | Supreme Being
Group: Forum Members
Last Login: Yesterday @ 5:26:14 PM
Posts: 75, Visits: 636 |
| | On my database which is only 5GB in size the purging of expired rows took well over an hour. This was after I applied the 12.0.2 patch. Your db being twice the size of mine I would figure that the process would take several hours. Did you let the process run overnight? Of course, the point is moot now since you've already resolved your issue, but it could have just been a matter of not giving the process enough time. |
| |
| | | Junior Member
Group: Forum Members
Last Login: 7/11/2008 10:40:01 AM
Posts: 11, Visits: 17 |
| | No way would i let a query run that long. I am almost thinking that my database go to a point where it was not purging data because of the sheer time involved. My whole thing is why did my database in size double AFTER applying the patch? |
| |
| | | 
Forum Guru
Group: Forum Members
Last Login: 2 days ago @ 10:16:06 AM
Posts: 51, Visits: 77 |
| | It's the statisticalInterface table ( 13,389,388 rows! ) and verified against the log messaging that someone else posted (double checked it to be sure I wasn't trying to lump something into the issue which didn't fit 100% as described) I have the report data settings set pretty tight as we had a space limitation in the v11 software due to the 2GB dB limit imposed by the MSDE2K: Rollup raw data after: 12 hours Rollup hourly data after: 30 days Rollup daily data after: 90 Days This kep our v11 dB at roughly 1.3GB in size after purging expired rows, optimizing the dB tables, and validation/compaction of the dB and removal of the empty space vacated by the purged rows. After import into the v12 dB, it expanded within a few days to over 2Gb in size, and I was able to keep it down to 3.2-3.6Gb of data by purging and compacting the dB and then removing the empty space, but now we're at 4GB in size (98-99% full). I applied the v12.0.2 patch last night after checking back in to see what had been released information wise and to look up the command someone used to accomplish a kludge fix for their situation, when I saw the v12.0.2 patch had been released specifically to address this issue. I have to perform some maintenance now that it's expired a bunch more data than it had last night prior to applying the patch, so I'll report back after I've performed the maintenace on our WUGv12.0.2 installation to let you know if it's helped or not. Update- it's purging the expired rows I believe, but the sript I have been using to remove the empty rows and compat the dB does not have any effect on the dB size as reporte by WUG, nor does the Validate/Compact function work... Am I doing something wrong? I received the following error message from the dynamic report I built using the query you suggested: There was an error while attempting to select items from the database: Error=8004E14 ErrorMessage-IDispatch error #3092 Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'nDeviceID'. Christian Lawson (6/23/2008)
HarrellB (6/23/2008)
We have no interfaces greater the 1GB, so you may want to double-check yourtheory that it has to do with >2GB interfaces or larger, as itmay not be accurate.The first question I would ask is which table is consuming that space in the database. If it's something other than StatisticalInterface, this is a different problem (perhaps one we haven't seen yet). The attached SQL script will output the row count and space used for each WhatsUp table in your database. The next question would be to verify that you don't have an interface in the system with a speed larger than 2.1Gbps. This doesn't have to be a physical interface. "SELECT MAX([nIfSpeedIn]) AS nIfSpeedIn ,MAX([nIfSpeedOut]) AS nIfSpeedOut FROM [WhatsUp].[dbo].[StatisticalInterface]" should give you the highest reported value for any interface for which we've got data.
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | |
Forum Guru
Group: Forum Members
Last Login: 2 days ago @ 10:16:06 AM
Posts: 51, Visits: 77 |
| Ok, question- since I'm not familiar enough with SQL to start with- How do I use both of these queries (I tried to build a dynamic group in the report builder using the lines as given, but didn't get any results)??Christian Lawson (6/23/2008)
HarrellB (6/23/2008)
We have no interfaces greater the 1GB, so you may want to double-check yourtheory that it has to do with >2GB interfaces or larger, as itmay not be accurate.[ ... ] The attached SQL script will output the row count and space used for each WhatsUp table in your database. The next question would be to verify that you don't have an interface in the system with a speed larger than 2.1Gbps. This doesn't have to be a physical interface. "SELECT MAX([nIfSpeedIn]) AS nIfSpeedIn ,MAX([nIfSpeedOut]) AS nIfSpeedOut FROM [WhatsUp].[dbo].[StatisticalInterface]" should give you the highest reported value for any interface for which we've got data.
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | | 
Time Traveler
Group: Ipswitch Employees
Last Login: 11/21/2008 3:36:39 PM
Posts: 220, Visits: 373 |
| HarrellB (7/3/2008)
There was an error while attempting to select items from the database: Error=8004E14
ErrorMessage-IDispatch error #3092
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'nDeviceID'.
I'm not sure exactly what you meant by this, but I assume you were trying to create a dynamic group using the SELECT statement designed to return interface speeds. This query won't work as a dynamic group filter as it doesn't return devices, it returns values for interface speeds. You'll need to run this query directly against your database using any of the SQL tools. Search the Microsoft website for information on sqlcmd or Management Studio (whichever tool you have) if you need more information about how to do this.
Have a good one,
Christian Lawson
QA Engineer - Network Monitoring
Ipswitch, Inc. |
| |
| | |
Forum Guru
Group: Forum Members
Last Login: 2 days ago @ 10:16:06 AM
Posts: 51, Visits: 77 |
| Please delete this post- Thanks!
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | |
Forum Guru
Group: Forum Members
Last Login: 2 days ago @ 10:16:06 AM
Posts: 51, Visits: 77 |
| | Ok, never mind...I fell back on doing some good 'ole-fashioned logical t-shooting steps to get what you asked for, using the script file I created from v11 for monthly maintenace purposes, and following that format to execute the command you gave, but it should be noted, that according to MS;'s WEB site, OSQL will supposedly be removed from SQLExpress2k5 at some point in the future: C:\WINDOWS>osql -E -D whatsup -Q "SELECT MAX([nIfSpeedIn]) AS nIfSpeedIn ,MAX([
nIfSpeedOut]) AS nIfSpeedOut FROM [WhatsUp].[dbo].[StatisticalInterface]"
nIfSpeedIn nIfSpeedOut
-------------------- --------------------
4294967295 4294967295 (1 row affected) So you were right, evidently, I do have some interfaces rated above 2.1Gb- is WUG recording backplane speeds (we really don't have any physical network interfaces above GIG-E...)? HarrellB (7/3/2008)
Yes, I was trying to use the report Builder when I got the error message. So let's try this again- I understand you are trying to not get into the habit of supporting SQLExpress2k5, but since I'm not trying to do something that's not a default supported function, let make the assumption I do not know what I am doing to extract information from the WUG dB (which I don't - I'm a network engineer type and not an SQL admin), please provide a step-by-step proceedure to extract the information you are asking for, otherwise, we might as well forget even trying to get the information you are asking for. Thanks! Christian Lawson (7/3/2008)
HarrellB (7/3/2008)
There was an error while attempting to select items from the database: Error=8004E14
ErrorMessage-IDispatch error #3092
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'nDeviceID'.I'm not sure exactly what you meant by this, but I assume you were trying to create a dynamic group using the SELECT statement designed to return interface speeds. This query won't work as a dynamic group filter as it doesn't return devices, it returns values for interface speeds. You'll need to run this query directly against your database using any of the SQL tools. Search the Microsoft website for information on sqlcmd or Management Studio (whichever tool you have) if you need more information about how to do this.
------------------------------------------------------------
Bryan Harrell
Network Infrastructure - Tallahassee
Fla. Dept of Revenue
------------------------------------------------------------ |
| |
| | | Junior Member
Group: Forum Members
Last Login: 7/11/2008 10:40:01 AM
Posts: 11, Visits: 17 |
| | Quick tip for everyone using the new SQL2005 database. Download "Microsoft SQL Server Management Studio Express". It gives you much better management/troubleshooting of the database. Compacting the database did not work for me in the whatup console, but does work using the Management Studio. |
| |
| | |
|