23 Replies Latest reply on Dec 11, 2009 3:25 PM by jwilling

    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.

        • Re: SQL Express 4G limit
          cdoyle

          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

            • Re: SQL Express 4G limit

              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.

                • Re: SQL Express 4G limit
                  cdoyle
                  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.
                  • Re: SQL Express 4G limit
                    mark wiggans

                     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?
                     

                      • Re: SQL Express 4G limit

                        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.

                          • Re: SQL Express 4G limit
                            mark wiggans

                            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".

                              • Re: SQL Express 4G limit
                                mark wiggans

                                 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.

                                  • Re: SQL Express 4G limit

                                    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.

                                      • Re: SQL Express 4G limit
                                        David Smith


                                        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

                                          • Re: SQL Express 4G limit
                                            cdoyle
                                            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 



                                            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
                                              • Re: SQL Express 4G limit
                                                David Smith

                                                Thanks for your response Caroline, and I apologise for re-posting a dead thread, but I have a further question please.


                                                I am now at the stage where I have authorisation to purchase the SQL Server upgrade, but I am not to familiar with the CAL's terms etc.


                                                I was offered: SQL Svr Std Ed 2008 Single or Single 1 Proc?


                                                A colleague advised the 1 Proc means unlimited CAL's and the other is probably 1-5. How many CAL's do I need to simply run SQL for Solarwinds NPM and Solarwinds Cirrius (NCM)?

                                                  • Re: SQL Express 4G limit
                                                    cdoyle
                                                    Hi Dave,



                                                    I'm not familiar with Microsoft's licensing terms, so unfortunately I can't help you there. Maybe other customers could comment about what licenses they use on their SQL server.



                                                    What I can say is, you'll need to get SQL Server 2005 instead of 2008 - we don't support SQL Server 2008 currently.

                                                    You can find the system requirements here: http://www.solarwinds.com/products/orion/system_requirements.aspx


                                                    HTH!


                                                    • Re: SQL Express 4G limit

                                                      If you have NPM and Cirrius running on the same server, you only need 1 CAL.  CALs are distributed to each machine accessing the services of the SQL server.  Here's a link to Microsoft's licensing overview:


                                                      http://www.microsoft.com/sqlserver/2008/en/us/licensing.aspx

                                                        • Re: SQL Express 4G limit
                                                          cereal83

                                                           Thanks, I will look into this as I was having a similar issue

                                                            • Re: SQL Express 4G limit

                                                              If you want to keep SQL 2005 Express, then the advice I've seen so far is good. 


                                                              Just make sure you adjust the data retention periods for things like syslog, and if necessary clear the syslog tables and shrink the db to reclaim the space.


                                                              I also found that the Traps and TrapVarbinds tables could get large, so I used a truncate on those to tidy things up. Again, make sure you shrink the DB afterwards to reclaim the space.


                                                              Judicious use of rules for the traps and syslog can also help, especially if you're using the syslog to log firewall messages, which can come in at a fast rate, especially informational level messages.


                                                              I have recently upgraded SQL on 2 of our SolarWinds servers from Express to Standard without too many problems.


                                                              However I did an in place upgrade retaining the SQLExpress instance name and here are a few points to make a note of if you do the same:


                                                              1. When upgrading I had to specify the SKU upgrade switch with the installer to allow me to change versions from Express to Standard. Google it for more details.


                                                              2. If you have SQL 2005 Management Studio Express, remove it before you install the full version, and ensure you install the full version of SQL 2005 Management Studio to give you full access to the SQL 2005 tools.


                                                              3. I know this is obvious, but back up your databases first. We also have Cirrus on the same server so this was imperative.


                                                              4. I had to re-do the SQL connection security afterwards using the SQL surface area tool as everything was set back to default after the install.


                                                              Hope this is of some help to others in this situation.


                                                            • Re: SQL Express 4G limit
                                                              bberry

                                                              I do have one question. We are in the process of moving off SQL Express and our SQL server gods are asking if we need a SQL CAL or a client CAL? What does NPM require? The server we are moving to is currently supporting another SQL application. I just want to make sure there is nothign else from a licensing standpoint I will not need to acquire.

                                                              Brent

                                                                • Re: SQL Express 4G limit
                                                                  David Smith

                                                                  Thanks for the posts guys, much appreciated.

                                                                  bberry - The answer depends on if your Database is stored on a seperate SQL Server. If like me your SQL DB is on the same server as your Orion software then you simply need 1 CAL for the server itself. If you have a SQL Server running the databases and 1 or more servers running Orion software which access that Database then you will need 1 Client CAL for each server that access the SQL Server DB. I am still getting my head around this stuff myself, but I hope this helps.

                                                                  I now have my SQL 2005 Std Edition, and am going to be doing the upgrade tonight so wish me luck. I am still not sure if I will do a in-place upgrade or simply un-install all the SQL (after a DB backup of course :P) and then do a fresh instance of 2005 Standard. I will let you know how it goes.

                                                                    • Re: SQL Express 4G limit
                                                                      bberry

                                                                      My SQL is on a standalone server both now under SQL Express and will be when I move to an existing SQL 2005 Std. The SQL 2005 is currently supporting other application so figured adding another database would not be a problem looking at utilization, drive space, etc. on the server.

                                                                      Now their concerns and questions regard just how big the database could get. I told them I am pusing the 4G limit now with all the devices in NPM and about 1/2 the devices in Netwflow and am not keeping that much historical data. I am also not using the syslog feature. I have about 120 devices in NPM and will have about 35 under NetFlow. I guess the target for database size will depend on how much and how long I keep data. They tell me size is not an isue but we shall see.

                                                                      dgsmith - I have read and beel told it is best to make sure the SQL Express is completely gone and install the full SQL 2005 so that the studio stuff would work well. I am planning to simply do a backup of the databse off SQL Express and restore to SQL 2005 once I run back through the NPM wizard to point everything to the new server.

                                                                      Good luck and let us know how it goes.

                                                                      • Re: SQL Express 4G limit

                                                                        Hello All;

                                                                        I too have reached the 4GB barrier with my SolarWinds deployment. I've got both NPM and NCM running on the same box. My devices are dumping Syslogs to SolarWinds as well and this is causing my SQL database to hit the 4GB limit; concequentially, my server crashes when this happens.

                                                                        I've read through this thread and I can see where all these solutions solve the SQL database problem, but, I do not have the liberty to discard my syslogs completely. I need to keep them for historical purposes (PCI Compliance).

                                                                        My question is, is it possible, to "Rotate" the logs from the SQL database to a flat text file or CSV every 24 hours or if the database reaches a certian size? After that happens, then, the SQL database can terminate the existing table, and start over.

                                                                        Any good thoughts on how to do that?

                                                                        Thanks and Regards

                                                                        Dan

                                                          • Re: SQL Express 4G limit

                                                             Steps to Truncate Syslog:

                                                             

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

                                                             



                                                             

                                                            WAHOO! This just shaved 12 GB off of my DB! Thank you Mark.



                                                  • Re: SQL Express 4G limit
                                                    jwilling

                                                    Along this line of inquiry...

                                                    As I've noted that SQL Server 2008 is now a supported version, and we're also running into the 4GB file size limitation in the 'Express' edition after adding the NetFlow module...

                                                    What about moving up to the 'Workgroup' edition of SQL Server 2008 rather than the 'Standard' edition?  It has all of the functionality of the 'Express' edition but without the restriction on database size.  (and is a good deal less expensive)

                                                    -jim