Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry Unanswered: Select and Delete

    I have a script that delete records daily and shrinks the log file. I tested it with the SQL Analyzer and I should get the same results? I put this in a job that runs at night and when checking today when I got in the jobs ran successfully but the datafile continued to grow along with the transaction log. Here is the script:
    Use wslogdb62
    go
    delete * FROM dbo.INCOMING_temp

    WHERE DATE_TIME <=dateadd(day, -180, getdate())
    DBCC SHRINKFILE(wslogdb62_log, 1)
    as you know I get a error from the analyzer.

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    try putting the commands in two seperate tasks in the same job.

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Unhappy Select and Delete Reply to Thread

    It's running now in the analyzer and the transaction log continue's to grow and the data file is the same.

    P.S. How could you'll let the lakers off the hook. They
    Last edited by garrydawkins; 05-17-04 at 12:11.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    those lakers
    probably the best team to ever hit the boards
    the spurs just suck, they held duncan and parker to nothing

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    dont we need a log backup before the shrink ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Question Select and Delete

    Yes,
    I get this message again when I run this script:

    BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(wslogdb62_log, 1)

    This is the error message

    Server: Msg 8985, Level 16, State 1, Line 2
    Could not locate file 'wslogdb62_log' in sysfiles.
    DBCC execution completed. If DBCC printed error messages,
    contact your system administrator.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    try this
    Code:
    use wslogdb62 
    BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(wslogdb62_log, 1)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Exclamation Select and Delete

    I still get this error message:

    Server: Msg 8985, Level 16, State 1, Line 3
    Could not locate file 'wslogdb62_log' in sysfiles.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Even with the code you gave me.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    run this and post the results
    use wslogdb62
    select name from sysfiles
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink Per Your Request

    use wslogdb62
    select name from sysfiles

    Results:

    wslogdb62
    wslogdb62_Log.ldf

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Nowe try this :-
    Code:
    use wslogdb62 
    BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE('wslogdb62_log.ldf', 1)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Talking Select and Delete

    You are the true Apostle.

    Results:
    7 2 128 128 128 128

    Checking the Log file it has shrunk.

    Thanks

Posting Permissions

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