sql 2005 收缩日志

方案一:

 

  

SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)
DECLARE @name VARCHAR(25)
DECLARE @SQL VARCHAR(1000)
DECLARE @logid INT
   
DECLARE sysdatabase_name CURSOR FOR SELECT name FROM master.dbo.sysdatabases
   
OPEN sysdatabase_name
FETCH NEXT FROM sysdatabase_name INTO @name
   
WHILE @@FETCH_STATUS = 0
BEGIN
  
   IF ( @name NOT IN ('xxx')) --不需要进行日志收缩的数据库名

   BEGIN
     SET @SQL =' DECLARE @logid INT
                 USE ' + @name+'
                 SELECT @logid = fileid FROM sysfiles WHERE RIGHT(rtrim(filename),3) = ''ldf''
   
                 BACKUP LOG ' + @name+' WITH NO_LOG
                 DBCC SHRINKFILE (@logid) '
   
     EXEC(@SQL)
   
   END
   
   FETCH NEXT FROM sysdatabase_name INTO @name
   
END
   
CLOSE sysdatabase_name
DEALLOCATE sysdatabase_name

方案二：

　企业管理器－－点目标数据库－－属性－－设置为simple--收缩--设置回full