從網上整理的Sql 2005減少日誌文件的方法
方法一：粗暴型：
1.进入企业管理器，选中数据库，
2、所有任务->分离数据库
3、到数据库文件的存放目录，将***_log.LDF文件删除，你怕S的话可以拷出去
4、企业管理器->附加数据库，选mdf，这个时候你会看见日志文件这项是一个叉，不要紧继续，此时数据库就会提示你该数据库无日志是否创建一个新的，确定就是了。
5、记得数据库重新附加后用户要重新设置一下
方法二：一般型
建立作业，每天运行一次以下代码，缩小日志文件
backup log xxx with NO_LOG
backup log xxx with TRUNCATE_ONLY
DBCC SHRINKDATABASE(xxx)
方法三：持久型
修改数据库属性，恢复模式选为“简单”
修改日志文件属性，自动增长设为1MB，不设最大大小
执行收缩数据库
--SQL语句的设置方式:
alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)
方法四:如果我们以前没有注意,现在日志已经很大, 但是日志对我们又没有什么用的话,此时可以使用两种方法来减少日志文件所占有的空间
1.直接删除日志文件
exec sp_helpdb 'pubs'
Name filename
pubs d:"Program Files"Microsoft SQL Server"MSSQL"data"pubs.mdf
pubs_log d:"Program Files"Microsoft SQL Server"MSSQL"data"pubs_log.ldf
EXEC SP_DETACH_DB 'PUBS'
EXEC MASTER.DBO.XP_CMDSHELL 'DEL "d:"Program Files"Microsoft SQL Server"MSSQL"data"pubs_log.ldf"'
EXEC MASTER.DBO.XP_CMDSHELL 'DIR "d:"Program Files"Microsoft SQL Server"MSSQL"data"PUBS*.LDF"'
THE RESULT IS:
File Not Found
EXEC SP_ATTACH_SINGLE_FILE_DB N'PUBS',
@PHYSNAME=N'd:"Program Files"Microsoft SQL Server"MSSQL"data"pubs.mdf'
SQL SERVER PRINT THE FOLLOW MESSAGE:
Device activation error. The physical file name 'd:"Program Files"Microsoft SQL Server"MSSQL"data"pubs_log.ldf' may be incorrect.
New log file 'd:"Program Files"Microsoft SQL Server"MSSQL"data"PUBS_log.LDF' was created.
NOW YOU CAN USE PUBS NORMALLY.
2.截断日志,然后收缩日志文件.可以把这引步骤放在一个存储过程中,请先执行下面的存储过程创建语句:
CREATE PROC Up_shrinkfile
@LogicalFileName sysname,---- Use sp_helpfile to identify the logical filename that you want to shrink.
@MaxMinutes INT,--Limit on time allowed to wrap log.
@NewSize INT -- in MB
as
Begin
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT '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 = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
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) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
WAITFOR DELAY '00:00:30'
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
End
然后执行此存储过程,如:
exec sp_helpdb 'pubs'
EXEC Up_shrinkfile 'pubs_log',10,1
