How to delete the log file space of SQL Server 2000?

Back up the database
Back up the file, back up the log file, and rename it.
Execute sp_attach_single_file_db in the query analyzer to generate a new log file.
I have never done anything about it and asked experienced people to answer it ., I will collect the relevant information.

METHODS Extracted from the Internet without having to try
Use the BCP command to export all records in the database and save them to another machine, and then use truncate table tablename
To clear all records, and then execute dump transaction dbname with no_log. It is found that the log file has been significantly reduced and then imported with the BCP command. After the import, the log file increases, however, using dump transaction dbname with no_log not only reduces the space occupied by logs, but also reduces the size of log files.

A few days ago, I also encountered the problem that the log file is too large. the actual size of the database is 600 mb, and the actual size of the log file is 33 MB, but the occupied space of the log file is 2.8 GB !!!
I tried a variety of methods, such as shirnk database and truncate log file. In any case, this should be a bug of SQL Server.

Then find the following code to narrow down the log file to the desired size. Copy the code to the query analyzer and modify the three parameters (Database Name, log file name, and target log file size ), run the command (I have used it many times)
-----
Set nocount on
Declare @ logicalfilename sysname,
@ Maxminutes int,
@ Newsize int

Use Marias -- Name of the database to be operated
Select @ logicalfilename = 'marias _ log', -- Log File Name
@ Maxminutes = 10, -- limit on time allowed to wrap log.
@ Newsize = 100 -- the size of the log file you want to set (m)

-- Setup/initialize
Declare @ originalsize int
Select @ originalsize = size
From sysfiles
Where name = @ logicalfilename
Select 'original size of '+ db_name () + 'Log is' +
Convert (varchar (30), @ originalsize) + '8 K pages or '+
Convert (varchar (30), (@ originalsize * 8/1024) + 'mb'
From sysfiles
Where name = @ logicalfilename
Create Table dummytrans
(Dummycolumn char (8000) not null)

Declare @ counter int,
@ Starttime datetime,
@ Trunclog varchar (255)
Select @ starttime = getdate (),
@ Trunclog = 'backup log' + db_name () + 'with truncate_only'

DBCC shrinkfile (@ logicalfilename, @ newsize)
Exec (@ trunclog)
-- Wrap the log if necessary.
While @ maxminutes> datediff (MI, @ starttime, getdate () -- time has not expired
And @ originalsize = (select size from sysfiles where name = @ logicalfilename)
And (@ originalsize * 8/1024)> @ newsize
Begin -- outer loop.
Select @ counter = 0
While (@ counter <@ originalsize/16) and (@ counter< 50000 ))
Begin -- Update
Insert dummytrans values ('fill log ')
Delete dummytrans
Select @ counter = @ counter + 1
End
Exec (@ trunclog)
End
Select 'final size of '+ db_name () + 'Log is' +
Convert (varchar (30), size) + '8 K pages or '+
Convert (varchar (30), (size * 8/1024) + 'mb'
From sysfiles
Where name = @ logicalfilename
Drop table dummytrans
Set nocount off

Understanding log truncation options
Different backup log truncation options are often ignored by new DBAs. New DBAs generally do not know the existence or role of options. The following sections detail how to use or when to use each option and when to use it.
Truncate_only
The truncate_only option removes the inactive part of the transaction log, instead of backing up (copying) the log to the backup device. Because the logs are not copied, you do not need to specify the backup device when using truncate_only. For example, the syntax for using the truncate_only option to back up the transaction logs of the primary database is as follows:
Backup log master
With truncate_only
Use truncate_only in the following cases:
If you are not using transaction logs for recovery purposes and rely on full database backup (complete or differential ). If you do not execute database backup, use the truncate_only option. You will not be able to restore the completed transactions in the idle part of the transaction log when the backup log command with truncate_only is executed.
No_log
When the backup log command with the no_log option is executed, SQL Server truncates the non-active part of the transaction log without recording the backup log command.
The no_log option is used only when the transaction log is fully filled. When the log is fully filled, you cannot run a common backup log command to truncate the transaction log. This is because SQL Server tries to record the BACKUP command, but there is no space left in the transaction log. Like the truncate_only option, the no_log option does not need to back up the device because the log is not copied to the device.
No_truncate
Use the no_truncate option when the database you are trying to access is damaged and you intend to recover the database. To use no_truncate, the following conditions must be met:
Transaction logs must be on different devices than databases.
The master database must not be damaged.
No_truncate records all transaction log items from the last transaction log backup to the database destruction point. The transaction log backup is then restored as the latest backup, which can be accurate to milliseconds during the recovery process.

Most of the time, we are getting stuck with the increasing size of database log files, although the command dump transaction database_name with no_log can be used to shorten the percentage of space occupied by logs in the actual physical log file, the Database Log File occupies no disk space, affects the space required by other programs. For this reason, I have conducted many experiments to explore ways to directly reduce the size of log files. Please advise!

According to the related options in db_option, after the dump transaction database_name with no_log operation is performed in different settings, it is found that the total space of the log file is not changed, but the used space is reduced, the free space increases accordingly. This change means that there is still space for writing your logs in the future, because the space is reserved. However, when the log file is too large and affects the space used by other programs, this result is not what I need.

Later, I performed this operation and used the BCP command to export the records in the database to the hard disk of another machine. Then, all the records are cleared by truncate table table_name, and then dump transaction database_name with no_log is executed. It is found that the log file has been significantly reduced, use the BCP command to import the previously exported data to the database. After the import is complete, the log file is increased. However, when you use the dump transaction database_name with no_log command, the effect is not only to reduce the space occupied by logs, but also to significantly reduce the size of log files.

If you are interested in the above phenomenon, please verify it in the same way. As to whether this operation has an impact on the object and integrity of the database, please kindly advise!