— 查看当前数据库日志文件大小和使用率,size以8 KB 为单位,所以除128就是MB
SELECT name, data_space_id 文件组id, size/128 [文件大小(兆)],
FILEPROPERTY(name, 'SpaceUsed')/128 [已用空间(兆)],
size/128 - FILEPROPERTY(name, 'SpaceUsed')/128 [未用空间(兆)],
FILEPROPERTY(name, 'SpaceUsed')*100.0/size [使用率(%)],
max_size/128 [最大值(兆)],
case is_percent_growth when 0 then cast(growth/128 as nvarchar) + '兆' else cast(growth as nvarchar) + '%' end 增长值,
physical_name 物理路径
FROM sys.database_files a ORDER BY a.[name]
–数据库收缩命令
USE 数据库名;
GO
-– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE 数据库名
SET RECOVERY SIMPLE;
GO
-– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (数据库名_Log, 1);
GO
– Reset the database recovery model.
ALTER DATABASE 数据库名
SET RECOVERY FULL;
GO
正常来讲,应该是先执行一次备份事务日志,然后在执行数据库收缩命令,就能正常的收缩日志大小了。
以下代码未测试
1、定时执行备份事务日志,每天凌晨1点执行
BACKUP LOG [uiyi] TO DISK = N’F:\DB_Backup\Log\uiyi\uiyi_backup_2013_06_21_145209_5008471.trn’ WITH NOFORMAT, NOINIT, NAME = N’uiyi_backup_2013_06_21_145209_4948467′, SKIP, REWIND, NOUNLOAD, STATS = 10
2、收缩日志,每天凌晨2点执行
use uiyi
go
DBCC SHRINKFILE (uiyi1224_Log, 50);
3、备份全量数据库,每天凌晨3点执行
BACKUP DATABASE [uiyi] TO DISK = N’F:\DB_Backup\All\uiyi\uiyi_backup_2013_06_21_145557_3298781.bak’ WITH NOFORMAT, NOINIT, NAME = N’uiyi_backup_2013_06_21_145557_3268780′, SKIP, REWIND, NOUNLOAD, STATS = 10
GO
ALTER DATABASE 库名 SET RECOVERY SIMPLE
---收缩日志,日志为最小值;
DBCC SHRINKFILE (N'库名_log' , 0)
---设置数据库为完整恢复模式
ALTER DATABASE 库名 SET RECOVERY FULL
相关文章
SQL Server 2008如何压缩日志(log)文件?
SQL 2000/2005/2008 的收缩日志方法,和清理日志图解教程(含工具)
正文完