0

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

user44650
  • 137
  • 4
  • 11
  • 2
    What kind of database? (SQL is the Structured Query Language. You're talking about DB maintenance, which has nothing to do with queries :) – voretaq7 Aug 16 '11 at 18:29
  • You should specify the database, if it's mysql the table storage also makes a difference (innodb or myisam) – Yavor Shahpasov Aug 16 '11 at 18:53

1 Answers1

2

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.

Dave Kincaid
  • 121
  • 3
  • 2
    This often does not reduce the on-disk size of the tables (some DB engines may just mark the rows as deleted/reusable - so your DB won't grow anymore when you insert new rows, but you get no space back) – voretaq7 Aug 16 '11 at 18:38
  • 1
    That is true. He would want to run his SHRINK command after doing the delete. (I'm assuming he's using MS SQLServer since he referred to the SHRINK command in the question) – Dave Kincaid Aug 16 '11 at 18:42
  • you are correct, it is MS Sql Server 2005. thanks for the thoughts/input! – user44650 Aug 16 '11 at 19:15
  • Then a good advise from me. Run it in smaller batches. Do it for the years 2005, 2006, 2007, ... first. Smaller batches are sometimes faster than one single command. – mailq Aug 16 '11 at 22:14