SQL Server 2000/2005/2008 Shrink log method and cleanup log method _mssql

1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as Forum Database Forum)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save
2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine
3. After the database is finished, it is recommended to reset your database properties to standard mode, with the 1th, because the log is often the important basis for restoring the database in some unusual cases.

The simplest direct details are as follows:

First, download: Log cleanup shrink tool http://www.jb51.net/softs/57621.html

SQL Log Cleanup Shrinkage method explanation:
1, Nature is the download log cleaning software.
2, run Trunc.exe on the server, open the software.

Here the username must be the sa password you must remember the sa password, this is very important, otherwise you will not be able to query the corresponding database. Enter the password and then point to the database. Your server database is present.

Choose whichever you want to contract. You should understand! Easy to use and convenient oh.

second, the SQL2008 shrink logBecause SQL2008 is optimized for file and log management, the following statements can be run in SQL2005 but have been canceled in SQL2008: (SQL2005) Backup Log dnname with no_log go DUMP transaction Dnname with no_log go use Dnname DBCC shrinkfile (2)
Go--------------------------------------------------------------(SQL200: Clearing logs in SQL2008 must be done in simple mode, Wait until the cleanup action is complete and then back to full mode. Use [master] to alter DATABASE Dnname set RECOVERY simple and no_wait go Alter DATABASE dnname SET RECOVERY Simplicity--Easy mode Go to Dnname go DBCC shrinkfile (N ' Dnname_log ', one, truncateonly) go with [master] go ALTER DATABASE dnname SET RECOVERY Full and no_wait go ALTER DATABASE dnname SET RECOVERY fully-restore to complete mode go benefits: This purge log runs for a short period of time, 90GB of log is cleared in minutes, and complete after completion Can be completed in minutes. Disadvantage: This action is best not to be used frequently, because its operation will bring system fragmentation. Log and diff backups can be truncated in normal state. The appropriate environment used by this statement: use when the system's log files are abnormally large or if the backup log time is too long to be affected by production.

2005 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:\mssql7\data\yourDBName_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.
Third, Server 2005: Empty the database log
When the log file is full and the SQL database cannot write to the file, there are two methods available:
Method One: Empty the log.
1. Open Query Analyzer, enter command
DUMP TRANSACTION database name with NO_LOG
2. Then open Enterprise Manager-right you want to compress the database-all tasks-shrink the database-shrink the file-Select the log file-in the shrink option to shrink to XXM, this gives you a minimum m number that you want to shrink to, just enter the number and make sure.
Another approach has a certain risk, because SQL Server log files are not immediately written to the database master file, such as improper handling, can result in loss of data.
Method Two:
1: Delete Log
Detach Database Enterprise Manager-> Server-> Database-> Right key-> Detach database
2: Delete log file
Additional database Enterprise Manager-> Server-> Database-> Right key-> attached database
This method generates a new log with a size of more than 500 K.
Note: The first method is recommended.
If you want to not let it grow
Enterprise Manager--server--right key database--attribute--transaction log--Limit file growth to XM (x is the maximum data file size you allow)
How the--sql statement is set:
ALTER DATABASE name modify file (name= logical filename, maxsize=20)