Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Unanswered: Delete old records from database

    Hello guys!

    I have a problem again. My mdf database have grown
    so big that I would like to delete old records from there.
    How can I do that. For exaple delete records older than
    one year.

    Thank you in advance,
    Martin

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You would have to post DDL to get exact script but something like this

    delete from table where datediff(yy,datecolumn,getdate()) >= 1

    Test first though

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Sep 2003
    Posts
    17
    Actally I'm kinda dumb in sql area. Where should I enter this command?
    And can it be done somehow from dos command line?

    Jessica

  4. #4
    Join Date
    Sep 2003
    Posts
    69
    You can use Enterprise Manager or possible query analyzer. Not too sure about the latter. I think it can be done from the CLI, but I've never
    used it, although I would like to.

    Also, I might change

    delete from table where datediff(yy,datecolumn,getdate()) >= 1

    to

    delete from table where datediff(yy,datecolumn,getdate()) > 0

    So that it is performing the same check but short circuiting the logic.
    Good Luck!

  5. #5
    Join Date
    Sep 2003
    Posts
    17
    can you please be more specific and tell me were will I use this command and how would the example look like if i have
    data.mdf file and table called event. And I would like to delete records older than 1year.


    Pleaaasse help me, I'm desperate
    Jessica

  6. #6
    Join Date
    Sep 2003
    Posts
    17
    Can anyone pleease help, I would be so grateful

    Little Jessica

  7. #7
    Join Date
    Sep 2003
    Posts
    69
    Heh...


    Click Start > Programs > Microsoft SQL Server > Enterprise Manager

    Or... alternatively you could go into the partition it is installed to and find
    EM and run it from there. Next click on the database (if its not there you'll have to add one). Next enter user/pass if need be... expand the server expand the databases. Select one that you want open a table and then click the SQL button at the top and then type in your SQL.

    OR

    You could do

    Click Start > Programs > Microsoft SQL Server > Query Analyzer

    that should run your queries too.

  8. #8
    Join Date
    Sep 2003
    Posts
    17
    I tried running it on query analyser but did'nt succeed
    Can you give me an exact exaple on how to delete
    records older than 3 months from database named
    events.mdf and table called events.

    Jessica

  9. #9
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by Jessica7
    I tried running it on query analyser but did'nt succeed
    Can you give me an exact exaple on how to delete
    records older than 3 months from database named
    events.mdf and table called events.

    Jessica
    ok here is the query:
    use events
    delete from events where datediff(m,datecolumn,getdate()) >= 3



    in the above query just replace the 'datecolumn' with the actual column in the table events which saves the date.

  10. #10
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Finally, when you query for deleting will work - just create job on server and run this query every month or 2 weeks, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •