Also talk about how to narrow the SQLServer log file
Also talk about how to narrow the SQL Server log file
1. How to narrow the MSSQL log files is already a regular problem, but this problem has many answers in the essence area, and I will not go here. Now we discuss the problem of cure, how to make the log file no longer increase? First introduce a simple method. It is set to "simple" (SQL2K) of the fault reduction model of the database. This will cut off the log when checkpoint. The specific method of operation is: 1. In Enterprise Manager, the "Properties | Options | Fault Restore" in Enterprise Manager, select "Simple", if it is SQL7, there is a "trunc. Log on" Trunc. Chkpt. ", you can check it. 2, if you don't want to use Enterprise Manager, execute Exec sp_dboption 'your_dbname', 'trunc. Log on chkpt.', 'TrUNC. Log on chkpt.', 'TrUE' is it, it is necessary to pay attention to the log. Will not increase, but it also means that once you have a misuse, you will not have an opportunity to use log recovery. (How to use the log to restore, please refer to the essence of the Essence), it is absolutely not recommended to cut off the log on the production database unless you have sufficient reasons and enough grasp, or ... is not your responsibility. Since this method is not safe, I will introduce a safe approach. Everyone knows that SQL Server will automatically truncate inactive parts in the transaction log when completing the transaction log backup. These inactive parts contain completed transactions, so they are no longer used during recovery. Instead, the event part of the transaction log contains a transaction that is still running but not completed. SQL Server will reuse these truncated non-active spaces in the transaction log, not the transaction log, continue to increase and occupy more space. So, we can back up the transaction log to make the log file no longer increase. However, log files are not a way, delete, will lose recovery. We can do it with complete backup. You can delete the transaction log before you have a complete backup. For example, a backup plan is completely backed up once a day, reserved within 7 days, and retains 2 days every 15 minutes. Use the Database Maintenance Plan Wizard to establish a backup plan, but be sure to remember how long the settings are kept, otherwise the hard disk space is made to account for a bad thing. 2. A few days ago, I also encountered the log file too much, the actual size of the database is 600m, the actual size of the log file is 33m, but the log file takes up the space of 2.8G !!! Try a variety of ways, Shirnk Database, Truncate log File, there is no way to narrow the file. Anyway, this should be a bug of SQL Server. Later, I find the following code, you can narrow the log file to the size you want.
Put the code COPY to the query analyzer, then modify the three parameters (the database name, log file name, and the size of the target log file), run (I have used it many times) ----- SET NOCOUNT ONDECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INTUSE Marias - the database name SELECT @LogicalFileName to operate = 'Marias_log', - log file name @MaxMinutes = 10, - Limit on time allowed to wrap log. @NewSize = 100 - you want to set the size of the log file (M) - Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileNameSELECT 'Original size of' db_name () 'lOG is ' CONVERT (VARCHAR (30), @ OriginalSize) ' 8K pages or ' CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024)) ' MB 'FROM sysfiles WHERE name = @LogicalFileNameCREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR (255) SELECT @StartTime = GETDATE (), @TruncLog = 'BACKUP LOG' db_name () 'WITH TRUNCATE_ONLY'DBCC SHRINKFILE (@ LogicalFileName, @newsiz e) EXEC (@TruncLog) - Wrap the log if necessary.WHILE @MaxMinutes> DATEDIFF (mi, @StartTime, GETDATE ()) - time has not expired AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName )> @Newsize begin - outer loop. SELECT @counter = 0 while (@counter <@originalsize / 16) and (@counter <50000)) Begin - Update Insert DummyTrans VALUES ('Fill Log'
