This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Express 4G limit

My Solarwinds Database is at 3.67 Gig and it has top saving historical Data, the database manager will not let me create a backup of the database. How can I clear some of my historical Data so I Can recover my database.

  • Hi Josequiroz,


    If you are reaching the 4GB limit, you may need to look into moving your database to a SQL Server running Standard or Enterprise edition, as they will not limit your database size.


    To reduce down the current size of your database, you can try the following:
    Open the Orion System Manager, and go to File -> Orion NPM Settings. Under the database tab, you can choose how long to retain your statistics for - reduce these settings down to reduce the amount of polling and statistics data being retained.

    On the same tab, you can start off a "Nightly maintenance" - this will go through the data in your tables and 'groom out' the data you no longer want to retain. Once this has completed, open the Database Manager tool (under database Utilities). Connect to your database server, right click on your database, and choose "Compact Database". This should reduce down the size of your database for you.


    ~ Caroline

  • Hi,


    I have the same issue.  I did try aging off data and compacting the database using the Database Manager, but I kept getting the following error:


    [Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate a new page for database 'NetPerfMon' because of insufficient disk space in filegroup 'PRIMARY'.  Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


    I see that in Properties, data is taking 2.3 GB and the indexes are taking up 1.7, so that's how I'm hitting the 4 GB limit.  Is there anything else I can try?  Are there any objects in the filegroup I can drop and if so, how is that done?  Thanks.

  • You could try using a free tool such as Microsoft's SQL Server Management Studio Express to run a Shrink on the database - this should regain any 'white space' in the database.



    Failing that, if you've got a version of SQL 2005 Standard or Enterprise onsite somewhere, you could try stopping your Orion services, then backing up your database and restoring it to the Standard/Enterprise version, then running the Shrink on it there where you're not affected by size limitations.

    Back it up again from the Standard/Enterprise version, and restore it back to your Express version.


    You can change your retention settings in System Manager under File-> Orion Network Performance Monitor Settings on the Database tab.
  •  Raul-

     In the Orion DB Manager -> select the DB and Right Click -> Database Details. Now sort the Tables by size. What are the largest tables?
     

  • Hi,


     The largest tables I have are SysLog 2.1 GB, ResponseTime_Detail 59 MB, InterfaceTraffic_Detail 16 MB, ResponseTime_Hourly 8 MB, and InterfaceTraffic_Hourly 7 MB.

  • You will need to truncate that table and then do a DB compact. To prevent this from happening again try adding some rules/filters to syslog, such as "Discard Syslog Message"

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

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

    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".
  •  Steps to Truncate Syslog:

     

    Truncate the table if you do not need to keep historical syslog messages

    Open Database Manager

    Start > All Programs > Solarwinds Orion > Database Utilities > 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 SysLog table.

    Select Query Table.

    Type the below SQL query:

    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.

  • I am in awe of your god-like SQL power.  It worked perfectly.  I have family in Peru.  Perhaps one of them is in law enforcement and can change your status from outlaw to bandit so you can visit there again.  Thanks Mark.


  • Hey guys,


    Whilst I am not yet having any problems with my database, it is slowly creeping upto that size where I am starting to get worried, especially as we still need to grow our system to add more devices.


    So my question is this, as I currently have a dedicated Win 2k3 Server running the NPM with SQL Express. Can I backup my DB stop all my services and install the Standard Edition of SQL and continue to use the same server to run the SQL DB and NPM? I only ask this as I would ideally like to keep the DB on the same server as the NPM Software itself, and also I don't really have any budget for a new server at the moment.


    Any advise will be greatly appreciated.



    Thanks
    Dave

  • Hi Dave,



    There should be no problem with that plan for the time being. As your system grows larger and larger you might need to look at moving the SQL server off to another machine (depending on the performance of your server), but without any details I can't really comment emoticons_happy.png



    Stop all of your Orion services, and back up your database. Once your installation of SQL 2005 Standard is complete, restore the backup to it, then run the Orion Configuration Wizard again. Choose the Database option, and use this to connect Orion to your new database server.



    HTH