How to clear SQL server logs

Source: Internet
Author: User
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.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.