Database size problem

We had some problems on our network last week which caused our DB to go from 8gig to 16gig mostly because of syslog we were keeping our syslogs for 14 days but I thought to clear out all the crap I'd reduce this down to 2 days and do a compact DB, this had the oppoiste effect and now the DB is over 40gig. What I don't understand is why a compact had the opposite effect and the backup at the weekend was only 16gig. T

The syslog table has currently got 20 million entries, I presume that its these entries that are causing the size of the DB to be so big.

We currently use SQL tasks for the backup etc but I used the Solarwinds compact task for the compact DB.

Any help with reducing the size of our DB would be much appreciated.

Thanks

Jon

Find more posts tagged with

syslog

db_compact

sql_2000

Accepted answers

All comments

mark_wiggans

If the syslog table grows to a size that is unmanageable then the maintenance will fail to remove old entires. You will have to run a SQL truncate coomand on that Table to remove all data in the table.

Run the following query against your NetPerfMon database" TRUNCATE TABLE Syslog This removes all entires in the syslog table. Once that has been run, I suggest running a compact on the DB to regain all the "white space". Also consider looking into creating rules to decides which syslog messages you need to keep and which ca be discarded immediately to help keep the DB pruned.

Similar detailed Truncating steps can be found in this thread -

kclark

This bit us awhile ago. Someone at our company inadvertantly turned syslog filters on a few firewalls with 30Mbps+ of Internet bandwidth to "Informational." 600 million rows and 200GB later, we had to do the same thing. You can also try to delete several rows at a time to gradually bring the database size down without losing all your data. Try this:

Set ROWCOUNT 10
DELETE FROM SysLog WHERE (MONTH(DateTime) = 7) AND (DAY(DateTime) = 9)
SET ROWCOUNT 0

This will delete 10 rows at a time from a specific record date. You can of course increase this as long as the server continues to execute the query. We started at 10, tried 100, 500, 1000, 10000, etc...until we found a point at which the server quit executing the query reliably. Takes longer, but you get to granularly define what data to delete, and therefore what data to keep.

-Kevin