Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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.

Find more posts tagged with

4_gb_limit

sql_express

Accepted answers

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

All comments

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

rlandeo

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.

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.

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?

rlandeo

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.

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.

rlandeo

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.

dgsmith80

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

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

dgsmith80

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)?

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