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,
AttilaOriginally 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|||Thanks a lot for your help.|||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|||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|||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.