Home / server / Questions / 301744
In Process
user44650
Asked: 2011-08-17 10:15:53 +0800 CST

shrinking a syslog sql database

  • 772

I am trying to shrink a SQL database used for our syslog monitoring. the DB is about 43GB right now, and i'm sure hasn't been cleaned up in years. I recently inherited this DB when someone quit, and i'm not sure how to even access it or clean it up. I've tried to do the standard SHRINK on either the database or the files, and it seems to drop it down to about 40GB, but within minutes when a new entry gets logged to the DB, it immediately goes right back to the original 43GB size. Is there a way to remove data older than a certain number of years or months? We are currently without a DB Admin and i'm just trying to do what i can to free up some space on DB that potentially has years worth of old data.

edit: MS Sql Server 2005

sql database syslog

1 Answers

  1. Be careful here since this will delete data permanently from your database!

    If you truly want to remove old data from the database and your dates are in a date type column you can do that using a SQL DELETE command. Something like this:

    DELETE FROM <tablename> WHERE <datecolumn> < '01/01/2008';

    This will delete all rows from the table with a date earlier than 2008. You will have to substitute the table name from the schema for <tablename> and the column in that table that contains the date of entry for <datecolumn>.

    If the date column isn't an actual DATE type then you're going to have to do some conversion to get it into a date.

    • 2