Delete Transaction Log

Hi all,

I am using MSSQL Server 2000. I have my SQL Server Database: mydb.mdf (1 GB) and mydb_log.ldf (30 GB).
Now I would like to delete this VERY BIG transaction log and let the SQL Server automatic create a new one. What is the best way to do that? System downtime is available. Here is my way when I tested on development machine:
1) Backup full database and transaction log.
2) Detach DB by using Enterprise Manager
3) Delete transaction log (mydb_log.ldf file) by using Windows Explorer.
4) Attach DB.
Then I saw SQL Server automatic created a new Transaction log (mydb_log.ldf) only 1 MB in size.

Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
I appreciate for all help.
ThanksHave you tried dbcc shrinkfile / dbcc shrinkdatabase ? Have a look at the following article:

article (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318)

If all else fails, then your solution is the quickest.

Good luck.|||On deleting Transaction logs:

Re: Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
I appreciate for all help

Q1 Before I apply on Production, I would like to ask for your idea?
A1 Why not use DBCC ShrinkFile, and / or enable autoshrink? Also, why is your Log so much larger than your Data e.g., (i Are you not frequently dumping / backing up your Log to transaction log backup *.trn files? ; ii Or, do you have long running transactions that are filling up your DB Log; iii Or, is your DB very heavily used, etc.?)?

DBCC ShrinkFile advantages:
* it is safe
* it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
* ordinary users may work in the DB while its files are being shrunk

Use MyDB
Go
DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
Go

Q2 Any warning?
A2 You may "get away with" using your method indefinitly; however it is not safe.

Q3 If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
A3 BOL = Books On Line. You may install BOL using the Sql Server installer, it covers various recommended DBMS methods for reducing / limiting transaction log file sizes e.g., (including DBCC ShrinkFile, DBCC ShrinkDatabase). Microsoft Technet is another source of documentation that may help if / when your method gets you into trouble.|||Many thanks for all your help.
First of all, I just received new role as a DBA. Then I found my DB is not monitored, dumped/backed up to *TRN for a long time.
Why I do not want to use SHRINKFile? It seems to me DBCC SHRINKFILE does not work well, the physical size not reduce much as I expected! So I would like to "delete" and create a new log file.

Regards,
John|||Bill,
Below is a reply I made to a post from a month or so ago. Think it will help you out.

A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

Hope this helps.

Brent|||JohnBill,
Below is a reply I made to a post from a month or so ago. Think it will help you out.

A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

Hope this helps.

Brent