Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Marshfield WI
    Posts
    2

    Question Unanswered: SQL2000 Automatic Transaction Log Truncation Fails

    Greetings,

    I have a SQL2000 transaction log problem that is not described in the forum and I hope someone can shed some light on how to fix it. On SQL 6.5 we backed up our database daily and the transaction log hourly. The first transaction log job of the day initialized the log thereby truncating it; this job ran after a full backup of the database associated with the log. This worked fine.

    After migrating to SQL2000 (running in 6.5 compatibility mode), the transaction log is NOT tuncated following a full database backup, nor is it truncated by manually running the first job of the day which looks like this:

    DUMP TRANSACTION databasename
    TO databasename_trans_log_dump_device
    WITH STATS = 10, INIT

    where databasename is a place holder for the real name.

    Any ideas?

    Thanks in advance.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question Re: SQL2000 Automatic Transaction Log Truncation Fails

    I'm not sure I correctly understand what you are asking, however, you may wish to check your retention and expire date settings (either can negate INIT from behaving "as expected")?

    (Incidentially, you may also wish to consider switching to the BackUp DataBase 'DBName' and BackUp Log 'DBName' syntax.)

  3. #3
    Join Date
    Dec 2002
    Location
    Marshfield WI
    Posts
    2
    It appears that the commands you suggest are not recognized when the SQL2000 Server is running in SQL6.5 compatibility mode. However, the commands I described are recognized by the SQL parser; I'm not convinced, though, that they are doing what they should be doing.

    The problem is this: the transaction log is not truncated following a full database dump. It should be truncated, and it was under SQL6.5 with the commands I mentioned previously. SQL2000 documentation claims that the log is truncated automatically following a full database dump or backup using the newer vernacular. Because the log is not being tuncated, it is growing to rediculous sizes. A point was reached when the size of the log plus it's backup consumed nearly all of a hard drive. Manually truncating the log did not work. Thanks for your reply...

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    RE: the transaction log is not truncated following a full database dump. It should be truncated, and it was under SQL6.5 with the commands I mentioned previously. SQL2000 documentation claims that the log is truncated automatically following a full database dump or backup using the newer vernacular. Because the log is not being tuncated, it is growing to rediculous sizes. A point was reached when the size of the log plus it's backup consumed nearly all of a hard drive. Manually truncating the log did not work. Thanks for your reply...
    In that case I'd look for long running open transactions, held locks, etc., and double verify your DB recovery option (should be full not simple) using both EM and tsql. Also, has the log ever been freed up as expected? What if you reset the server and immediatly do a full dump, is log space freed up then?...

Posting Permissions

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