How to narrow the SQL Server log file
Article Source: 山 夜
Http://www.33d9.com
A few days ago, I also encountered the problem of excessive log files. 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 Int
Use Marias - Database name to be operated SELECT @LogicalFileName = 'Marias_log', - log file name @MaxMinutes = 10, - limit on time allowed to wrap log. @ Newsize = 100 - You want to set the log file you want to set Size (m)
- Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfilesWHERE name = @LogicalFileNameSELECT 'Original Size of' db_name () 'LOG is' CONVERT (VARCHAR (30), @ OriginalSize) '8K pages or' Convert (VARCHAR (30), (@ OriginalSize * 8/1024)) 'mb'From sysfileswhere 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'
