数据库时常因为日志过大而导致硬盘空间不够,或者备份出来的文件太大无法通过邮件传送。
--【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