SQL Server log clearing method.

SQL Server log clearing method.

Perform the following three steps in sequence in the query Analyzer. databasename is the name of your database tutorial file.
1. Clear logs: dump transaction databasename WITH NO_LOG

2. Truncate transaction logs: backup log databasename WITH NO_LOG

3. Shrink the database: dbcc shrinkdatabase (databasename)

--/////////////////////////////////////// ///////////////////////////

SQL Server log clearing method
   
One method: clear logs.
1. Open the query analyzer and enter the command dump transaction database name WITH NO_LOG
2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- select Log File -- select to shrink to XXM in the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Method 2:

Clear logs:

------------------------------------------
Backup log Library name WITH NO_LOG

Dbcc shrinkfile ('log filename ', new size numeric type such as 1)

The log file name is as follows:

Select name from sysfiles
For example:
Mastlog

---------------------------------------------
Backup log DATABASENAME
With truncate_only
Dbcc shrinkdatabase (DATABASENAME, SIZE)
If you have whole back up every day, you can set a job,
It is cleared every three days or one week.
In this way, logs will not grow up.

-------------------------------------
1: delete LOG
1: database separation
2: delete LOG files
3: attach a database
This method generates a new LOG with a size of more than 500 KB.
Then set the database to automatically contract
2: clear logs
Dump transaction database name WITH NO_LOG

Again:
Enterprise Manager -- right-click the database you want to compress -- all tasks -- contract database -- contract file -- select Log File -- select to contract to XXM in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Method 3:
Step 1:
Backup log database_name with no_log
Or backup log database_name with truncate_only -- no_log and truncate_only are synonymous here. You can execute either of them.
Step 2:
1. compress all data and log files of a specific database and execute dbcc shrinkdatabase (database_name, [, target_percent]) -- database_name is the name of the database to be compress; target_percent indicates the percentage of available space in the database file after the database is shrunk.
2. compress data or log files in a specific database at a time, and run dbcc shrinkfile (file_id, [, target_size]) -- file_id is the ID of the file to be compress, to obtain the file ID, use the FILE_ID function or search for sysfiles in the current database. target_size indicates the size of the desired file in megabytes (expressed in integers ). If not specified, dbcc shrinkfile reduces the file size to the default file size.

Either dbcc can contain the notruncate or truncateonly parameter. For more information, see help.

Method 4:
(This method is generally successful in the sqlserver2000 environment, but not necessarily in sqlserver7 or below ):
Step 1:
Back up the entire database for emergency purposes.
Step 2:
After the backup is complete, run the following statement in Query Analyzer:
Exec sp_detach_db yourDBName, true -- unload the registration information of this DB in MSSQL
Step 3:
Delete the log file or remove the log file from the directory where the log physical file is located.
Step 4:
Execute the following statement in Query Analyzer:
Exec sp_attach_single_file_db yourDBName, 'd:/mssql7/data/yourDBName_data.mdf'
-- Register the database as a single file. If yes, MSSQL automatically generates a k log file for the database.

The preceding methods are valid in log clearing.
However, can I prevent SQL server from generating log logs? The above methods seem to be invalid.
I have a case here:
My client's SQL server generates 4-MB log every day and clears it every day, which is inconvenient. Is there a way to avoid log generation?

I analyzed the cause of the customer's log generation and tested it accordingly.
The customer clears the database every day and imports data from the total system to SQL server. I feel that sqlserver does not generate much log during insertion, and the log is generated greatly when the entire database is deleted.
For example:
SELECT * into test_2 from B _bgxx
A total of 45000 records generate dozens of M logs. If
Delete from test_2
More than 80 M logs are generated, which is obviously a problem.

Although it can be changed:
Truncate table test_2
However, I still hope to find a method that does not generate logs. Just as oracle does not generate an archive.