How to clear SQL server logs Method 1:
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 2
(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.