SQL DB Help!!!!

pax · 28 Feb 2006 at 13:31

Hello Oracles(pun intended)....

Have got myself a nice new job, with lots of problems for me to solve.

My situation: Websense server with SQL as the backend. It ran out of HDD Space (yes DB, swap and system were on the same single non-redundant disk). I am slowly clearing space, but due to the space filling up in 2.5 months, I am basically going to keep 6 weeks worth of data and can the rest.

I have NO SQL SERVER EXPERIENCE, just to make that clear. I understand SQL select statements and how to handle replication and session management, but that's it.

My problem: Write a stored procedure which I can schedule to run. It needs to look at a table called INCOMING (dbo.INCOMING) and read data from column DATE_TIME (datetime, Not Null) and to delete all rows in that DB, where the value of DATE_TIME is a certain date/time period older than the current date.

I also need to then compress the DB after this is done. This is easy enough done as I can schedule it through the taskpad.

Can you help? If not, where should I point my bottom and get a wiggle on over to?

Pax

garyh · 28 Feb 2006 at 14:57

A trigger will do this for you.

Google "SQL Trigger" and you should find the results you're wanting. Always best to learn yourself rather than somebody code it for you - then you actually know what's going on

pax · 28 Feb 2006 at 16:03

garyh said:
A trigger will do this for you.

Google "SQL Trigger" and you should find the results you're wanting. Always best to learn yourself rather than somebody code it for you - then you actually know what's going on

Thanks for the heads up. Mind if I come back if I've got specific coding queries?

Pax

pax · 28 Feb 2006 at 16:27

I've had a quick look.

As I have read it, a trigger will happen after an activity (ie insert, update, or delete). I was looking more for having a script which I can schedule to run as a job, as opposed to after every insert it then triggers for a delete.

I am sure you can appreciate that anything which logs web requests, for consultants working at a travel company is going to be busy enough inserting each webpage request, let alone having to then trigger a delete event.

I must say that this system also has no hardware redundancy, so the disk read/write load will get quite high,

Pax

edit: I had nothing todo with the ordering of hardware or system setup prior to 2 weeks ago, as that is when I joined the company.

Seraphim · 28 Feb 2006 at 16:36

So you want to delete all rows in INCOMING that are a certain number of days old? This should do what you want - I'm not sure if it's standard SQL as I've been ruined by MS SQL, but the basics are there. This'll delete all rows that are over 3 days old:

Code:

DELETE FROM dbo.INCOMING
WHERE DATEDIFF(DAY, DATE_TIME, GETDATE()) > 3

Nice and simple!

pax · 28 Feb 2006 at 18:39

Seraphim said:
So you want to delete all rows in INCOMING that are a certain number of days old? This should do what you want - I'm not sure if it's standard SQL as I've been ruined by MS SQL, but the basics are there. This'll delete all rows that are over 3 days old:

Code:

DELETE FROM dbo.INCOMING WHERE DATEDIFF(DAY, DATE_TIME, GETDATE()) > 3

Nice and simple!

Hey Seraphim, yeah this is MSSQL, so should work fine.

Thanks for the code. I'll work it into my code, as I'll have to find the variable for the current date from MS SQL.

Pax

edit: I can use CURRENT DATE variable, excellent. Thanks heaps.