压缩数据库日志SQL语句

 数据库时常因为日志过大而导致硬盘空间不够，或者备份出来的文件太大无法通过邮件传送。

--【SQL2005】在MASTER数据库中执行以下脚本（使用查询分析器）
declare @dbname varchar(50)
declare temp_cur cursor scroll for select name from sysdatabases
open temp_cur
fetch first from temp_cur into @dbname
while @@fetch_status =0
begin
  exec ('backup log '+@dbname+' with no_log')
  exec ('dbcc shrinkdatabase('+@dbname+')')
  exec ('dbcc checkcatalog ('+@dbname+')')
  exec ('dump transaction '+@dbname+' with no_log')
  fetch next from temp_cur into @dbname
end
close temp_cur
deallocate temp_cur

--【SQL2008】具体操作如下

--收缩数据库
DBCC SHRINKDATABASE(dbName)
GO

--压缩数据文件,只有压缩文件后，数据库文件才能真正缩小
USE [master]
GO
ALTER DATABASE dbName SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE dbName SET RECOVERY SIMPLE --简单模式
GO
USE dbName --批量压缩数据库文件
GO
DECLARE @sysFileName VARCHAR(50)
DECLARE @sysfiles TABLE(sysFileName VARCHAR(50))
INSERT @sysfiles(sysFileName)
SELECT name FROM dbName.dbo.sysfiles
WHILE((SELECT COUNT(0) FROM @sysfiles) > 0)
BEGIN
SELECT TOP 1 @sysFileName = sysFileName FROM @sysfiles
        DBCC SHRINKFILE (@sysFileName , 11, TRUNCATEONLY)
        DELETE FROM @sysfiles WHERE sysFileName = @sysFileName
END
GO
USE [master]
GO
ALTER DATABASE dbName SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE dbName SET RECOVERY FULL --还原为完全模式

GO

--【批量刷新表自增键值】

DECLARE @table TABLE(tableName VARCHAR(50),columnName VARCHAR(50))

INSERT @table(tableName,columnName)
SELECT t.name AS tableName,c.name AS columnName
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE is_identity = 1

DECLARE @tableName VARCHAR(50),@columnName VARCHAR(50),@sqlString NVARCHAR(200)
WHILE((SELECT COUNT(0) FROM @table) > 0)
BEGIN
SELECT TOP 1 @tableName = tableName,@columnName = columnName FROM @table

SET @sqlString = 'DECLARE @maxValue INT'
+' SELECT @maxValue = MAX('+@columnName+') FROM '+@tableName
+' DBCC CHECKIDENT('+@tableName+',RESEED,@maxValue)'

EXEC sp_executesql @sqlString

DELETE FROM @table WHERE tableName = @tableName
END
GO