5 Replies Latest reply on Feb 28, 2012 9:03 AM by tonybarrett

    SQL Express 4GB limit reached

    tonybarrett

      We're currently running NPM 10.2 on a Windows 2008 R2 server, but with a local SQL Express 2008 (not R2). I seem to have hit the 4GB database size limit in this version, so some tables are not updating anymore, specifically graphs, which have stopped. Am I ok to upgrade the SQL install to 2008 R2 Express, and would I automatically gain the max 10GB database size increase in the process?

      When SW talk 'SQL Server 2008', do they automatically imply R2 as well?

      I'm a bit stuck at the moment, so any advice would be welcome. I've tried compacting the database, but I continuously get errors that it can't rebuild the index because a page cannot be allocated.

        • Re: SQL Express 4GB limit reached
          Nohyls

          I don't know for sure but i wouldn't count on it.
          Take a look at this document http://www.solarwinds.com/documentation/Orion/docs/MovingYourOrionNPMDatabase.pdf
          Take a backup of your database and install R2, if you gain the 10GB, you're fine, if you don't remove it and create a new one using the backup (don't forget to update NPM).
          You can also reduce the amount of data stored in your polling settings

          Good luck
          Greets
          Nohyls

            • Re: SQL Express 4GB limit reached
              Malik Haider

              In order to claim your space back from the DB . follow the steps. 

              The below steps will truncate the TrapVarbinds table. If need to keep the data, simply compact the database without truncating the table.

              Open Database Manager

              Start > All Programs > Solarwinds Orion > Advanced Features > Database Manager

              If this is the first time you use the tool, add your database server by clicking on the Add server button.

              Expand your database and right-click on the TrapVarbinds table.

              Select Query Table.

              Type the below SQL query: (** Note this will Delete all Trap and Syslog Info from your Database**)

               TRUNCATE TABLE TrapVarbinds

               TRUNCATE TABLE Traps

               TRUNCATE TABLE Syslog

              and click on Refresh to execute the query.

              This may take a while depending how many entries are recorded.

              Once done, right-click on the database and select Compact Database.

              Compacting a database shrinks it, reindexes it, and removes whitespace.

              So you do not have the same issue in the future can you have a look at the following Filtering plans so you can filter out unwanted Syslog and Trap messages.

              Traps Filter Plan:

              The best way to maintain the size of your Traps tables it to change the retention settings for your traps. This can be set in the Trap Viewer, in Settings. By Default, we keep traps for 180 days - this can be reduced to keep the size of the database smaller. 

               

              Also, I would suggest checking the Trap Viewer for the types of traps being received. If you are receiving a lot of info/debug severity messages from a device, the device itself can be set up to only send higher severity messages. Your vendor should be able to provide configuration commands for sending Traps on the device.

              Also, look for traps being received by the Trap Viewer that you are not interested in keeping - you can create a new Rules based on these Traps by right-clicking and choosing Add Rule, to discard those messages. It should automatically will out all tabs of the new rule to match that trap exactly. Use wildcards ( * ) as appropriate to expand what the rule will match to. Add the actions to "Discard the Trap Message" and to "Stop processing Trap rules". 

               

              Trap rules are checked in order top-to-bottom - placing these discard rules at the top of the list will ensure that these messages are discarded first, and that no other rules are checked against those messages. 

               

              Syslog Filter Plan:

               

              Option 1 Easy Solution is to stop Orion’s Syslog Service, This Stops Syslog Table from Growing again. 

               

              Option 2 Edit Your Syslog Retention Settings to keep Syslogs for x Days. I would suggest you tune the Severity levels for the Syslog output on your devices to Warning or above. Launch the Syslog view on the server and go to Server Settings. On the first tab you have a keep data for an amount of days option. Reduce this. 

               

              Option 3 On your Device - Tell them to stop sending some or all Syslog messages

               

              Option 4 Syslog Message comes to Orion, Syslog Service. Using Rules from Syslog Viewer to Determine whether you want to Store the Syslog message in the Database or whether to discard message. 

               

              If you have a definite need for level 5 (notice) or above, you will have to look at the data retention settings in the Syslog application within Orion. Alternatively you could use filter Rules so that the ones which filter and discard messages, are at the top of the list. This will ensure that they are processed first. 

               

              I recommend to make sure that all rules which are set up to “Discard messages” also contain the line "Stop processing syslog rules". 

               

              The syslog and traps filter/rules work very differently to the Orion alerting engine. Each time a syslog message or trap is received it will work through every rule, from the top, until it either gets to the end, or hit a rule that specifically tells it to "stop processing rules". 

               

              "Discard Syslog Message" 

              Start -> Program Files -> Solarwinds -> Orion -> Syslog Viewer 

              From this tool, Goto File -> Syslog Server settings -> Alert/Filter Rules Tab 

               

              In here you can filter using various methods, By IP address, by Message Type Patterns, Syslog Message Patterns, Severity, etc… 

               

              And then Add the following Alert Actions to your Rule: "Discard Syslog Message" "Stop processing syslog rules" 

               

              "Stop processing syslog rules" Rearrange the syslog rules so that the ones which filter and discard messages, are at the top of the list. This will ensure that they are processed first. 

               

              I recommend to make sure that all rules which are set up to “Discard messages” also contain the line "Stop processing syslog rules".

               

              Database Maintenance Plan:

              The Database manager tool allows you to schedule both backups and compact/repairs of your database on a nightly basis. Right click on your database and choose "Database Backup Schedule". Set the schedule as required, and on the next screen of the wizard, ensure that the "Compact and Shrink database" box has been checked. 

              Make sure that the Database Backup does not occur at the same time as your nightly maintenance schedule (by default this is 2:15AM, and can run from a few minutes to a half hour depending on DB size). The nightly maintenance schedule can be changed by opening the System manager, going to File -> Orion NPM Settings and click on the database tab.

               

                • Re: SQL Express 4GB limit reached
                  tonybarrett

                  Awesome tip. Thanks a lot. I'll look into this. I've currently changed the retention period for all data held for 365 days to 180 days, and I'm re-running the database maintenance tool, which should give me some time to sort things out.

                  Our Trap Retention time is currently 30 days, and syslog 7 days, so I wouldn't really want to decrease them further. I think the thing that's pushed us over the edge with DB size is adding UDT to the mix. It's certainly caused the DB to grow quite quickly.

                  I still need to know though, whether SW support SQL 2008 R2 Express, and whether if I upgrade from 2008 Express, I'll automatically gain to max 10GB DB size.

                    • Re: SQL Express 4GB limit reached
                      Bedrich.Michalek

                      You are right that UDT can cause quite a big DB growth. However it depends on how many nodes you poll from UDT and your environment. Few ideas:

                      - check the size of UDT_PortToEndpoint table (this one is usually the biggest)

                      - adjust your History Retention setting (Settings->UDT Settings->Data Retention) as this will reduce size of UDT_PortToEndpoint table

                      - check what is your polling interval for UDT nodes. If it's too low, it may lead to an extreme growth in UDT_PortToEndpoint table.

                        • Re: SQL Express 4GB limit reached
                          tonybarrett

                          Thanks. What I've done so far is, after consultation with SW, upgraded the local SQL intance to SQL 2008 R2 Express which has increased the default DB size to 10GB, so I'm running again now. The event log on the NPM server was filled with SQL Application errors complaining about DB size being exceeded. NPM maintenance tasks were failing, and I was getting gaps in graphs.

                          UDT was the only extra thing we added in the last month, and before this the database was a fairly consistent 2GB in size. UDT pretty much doubled this in a matter of a few weeks, so that's something to be aware of.

                          I've checked the data retention settings in UDT, and they are all still at the default;

                          History Retention: 90 days
                          Detailed Statistics Retention: 7 days
                          Hourly Statistics Retention: 30 days
                          Daily Statistics Retention: 365 days

                          The only setting I would consider changing (without losing too much history) is the Daily Stats, which I'd likely set to 180 days. Just got to see if that keeps me within the 10GB DB size, or I'll be having to budget for a full SQL Standard install!