Method 1:
First step:
BACKUP LOG database_name with NO_LOG
or the BACKUP log database_name with TRUNCATE_ONLY--no_log and truncate_only is synonymous here, and any sentence can be executed.
Step Two:
1. Shrink all data and log files for a particular database, execute DBCC SHRINKDATABASE (Database_name,[,target_percent])--database_name is the name of the database to shrink; target_ Percent is the percentage of remaining free space that is required in the database file after shrinking
2. Shrink data or log files in a particular database at once, execute DBCC SHRINKFILE (File_id,[,target_size])--file_id is the identification (ID) number of the file to shrink, and to obtain the file ID, use the file_id function or searches the current database for sysfiles;target_size is the desired file size in megabytes (represented by integers). If not specified, DBCC SHRINKFILE reduces the file size to the default file size
Two DBCC can be taken with parameters Notruncate or truncateonly, specifically to see Help.
Method 2 (This method is generally successful in sqlserver2000 environments, not necessarily in Sqlserver7 and the following versions):
First step:
Back up the entire database in case of a rainy start
Step Two:
After the backup is complete, execute the following statement in Query Analyzer:
EXEC sp_detach_db Yourdbname,true--Dismount the DB's registration information in MSSQL
Step Three:
Delete the log file in the same directory as the physical file of the log or move the log file out of the directory
Fourth Step:
In Query Analyzer, execute the following statement:
EXEC sp_attach_single_file_db yourdbname, ' d:mssql7datayourdbname_data.mdf '
--Register the DB as a single file, and if successful, MSSQL will automatically generate a 500K log file for this db.
The above methods are valid for clearing log logs.
But is it possible for SQL Server to not generate log logs? All of the above methods seem to be invalid.
I have a case right here:
My client's SQL Server generates 4,500m log logs every day, and it's very inconvenient to clean them every day. Is there a way to achieve this without creating log logs?
I analyzed the reason why the customer generated log log, and did the corresponding test.
The customer empties the database every day, importing data from the Master System into SQL Server. I have a feeling that SQL Server generates log small when inserting, and generates log maxima when you delete the entire library.
Like what:
SELECT * into test_2 from b_bgxx
A total of 45,000 records, resulting in more than 10 m log, if
Delete from test_2
Produces more than 80 m log, which is obviously problematic.
Although it can be replaced by:
TRUNCATE TABLE Test_2
But I still want to find a way of not creating a log. Just as Oracle does not produce an archive.
