Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Location
    Budapest - Hungary
    Posts
    12

    Unhappy Unanswered: Drop large transaction log file and create new

    Team,
    Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

    It's very urgent because we need to clean up space on NT server tonight.

    Thanks,
    Attila

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: Drop large transaction log file and create new

    Originally posted by horvata
    Team,
    Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

    It's very urgent because we need to clean up space on NT server tonight.

    Thanks,
    Attila
    See: http://dbforums.com/t546372.html

  3. #3
    Join Date
    May 2002
    Location
    Budapest - Hungary
    Posts
    12

    Re: Drop large transaction log file and create new

    Thanks a lot for your help.

  4. #4
    Join Date
    Apr 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    4

    Re: Drop large transaction log file and create new

    Originally posted by DBA
    See: http://dbforums.com/t546372.html
    Whou much time you spend to make you database full backup ??
    Wich type of backup do you do ?

    If you log is no longer used (because you data is static) you can use the command bellow

    sp_detach_db <dbname>

    GO

    CREATE DATABASE <dbname>
    ON PRIMARY (FILENAME = '<path>.dbname.extension')
    FOR ATTACH
    GO


    Ps: Make 2 full backups of you database before do this. On filename choose the path of you datafile <only>, forget you logfile.

    Jorge

  5. #5
    Join Date
    May 2002
    Location
    Budapest - Hungary
    Posts
    12
    Just for your information...
    I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

    Thanks,
    Attila

  6. #6
    Join Date
    Apr 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    4
    Originally posted by horvata
    Just for your information...
    I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

    Thanks,
    Attila
    Hi Attila, I discover another way to do this.

    Ps:Allways execute a full backup before.

    First execute
    EXEC sp_detach_db 'database_name', 'true'

    Rename your physical log file on Operation system
    After this execute the following command.

    EXEC sp_attach_single_file_db @dbname = 'database_name',
    @physname = 'path\database_name.extension'

    This command works. I haver already done this.

    Jorge Demattos
    Bank of America.
    Last edited by demattos; 12-20-02 at 11:27.

Posting Permissions

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