• 2282 阅读
  • 2 回复

SQL2008收缩数据库

视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)
20161115 SQL2008 使用语句

sql2008.txt (您是游客您没有权限下载)


xueqinjiaju 数据库名
dbcc shrinkfile(Tourism_log,8)
Tourism_log LOG文件名  8 为收缩为8M
本帖最后由 张小鱼 于 2016-11-15 10:07 编辑
小鱼的淘宝店铺-多多支持哇
视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)

实际工作中,经常数据库日志文件非常大,但是实际使用的容量又非常小,具体可以通过下面的sql看到日志文件大小,和文件使用率:
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]
于是写了一个存储过程,可以快速回收日志文件占用的磁盘空间,具体调用就是Exec UP_ShinkLog '数据库名':
CREATE PROCEDURE [UP_ShinkLog]
   @dbName VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;

   DECLARE @sql NVARCHAR(200);
   DECLARE @logname NVARCHAR(200);
   DECLARE @dbRecovery TINYINT;

   -- 获取日志文件名
   SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +
   --'GO' + Char(13) + Char(10) +
   'select top 1 @logname=name from sys.database_files where type=1';
   --PRINT @sql
   EXECUTE sp_executesql @sql, N'@logname NVARCHAR(200) output', @logname output;
   IF @logname IS NULL
   BEGIN
       PRINT '未找到日志文件:' + @sql;
       RETURN;
   END
   
   -- 获取现有的恢复模式 1是FULL 3是SIMPLE
   SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +
   --'GO' + Char(13) + Char(10) +
   'select top 1 @dbRecovery=recovery_model FROM sys.databases WHERE name = ''' + @dbName + '''';
   --PRINT @sql
   EXECUTE sp_executesql @sql, N'@dbRecovery TINYINT output', @dbRecovery output;
 
   -- 开始收缩日志
   -- 1、设置模式为简单模式
   SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE WITH NO_WAIT';
   EXECUTE sp_executesql @sql;
   SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE';
   EXECUTE sp_executesql @sql;

   -- 2、收缩文件
   SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +
       --'GO' + Char(13) + Char(10) +
       'DBCC SHRINKFILE (N''' + @logname + ''', 1, TRUNCATEONLY)';
   EXECUTE sp_executesql @sql;

   -- 3、恢复原来的模式
   IF @dbRecovery = 1
   BEGIN
       SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL WITH NO_WAIT';
       EXECUTE sp_executesql @sql;
       SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL';
       EXECUTE sp_executesql @sql;
   END  

   PRINT '数据库:' + @dbName + ' 日志文件:' + @logname + ' 收缩完成';
END




https://blog.csdn.net/youbl/article/details/8990169
完美整合了
小鱼的淘宝店铺-多多支持哇
视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)
小鱼增加了:备份数据库


代码 复制 - 运行

-- =============================================
-- Author:   张小鱼(NoFish)
-- Create date: 2020.09.07
-- https://blog.csdn.net/youbl/article/details/8990169
-- http://fish.baobao6.com/Program/thread-1655-1.aspx
/*
备份数据库,并收缩日志
*/

CREATE PROCEDURE [UP_Shrink_Log]
   @dbName VARCHAR(100),
   @bakDiskPath nvarchar(200)    --数据库备份地址 D:\123456.bak  为空则不备
AS
BEGIN
SET NOCOUNT ON;

   DECLARE @sql NVARCHAR(200);
   DECLARE @logname NVARCHAR(200);
   DECLARE @dbRecovery TINYINT;

   -- 获取日志文件名
   SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +
   --'GO' + Char(13) + Char(10) +
   'select top 1 @logname=name from sys.database_files where type=1';
   --PRINT @sql
   EXECUTE sp_executesql @sql, N'@logname NVARCHAR(200) output', @logname output;
   IF @logname IS NULL
   BEGIN
       PRINT '未找到日志文件:' + @sql;
       RETURN;
   END 
   
   --0.备份数据库- 小鱼加
   IF @bakDiskPath IS NOT NULL
   BEGIN
    SET @sql = N'backup database [' + @dbName + N'] to disk=''' + @bakDiskPath + '''';
    EXECUTE sp_executesql @sql;
   END
   
  
   -- 开始收缩日志
   -- 1、设置模式为简单模式
   SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE WITH NO_WAIT';
   EXECUTE sp_executesql @sql;
   SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE';
   EXECUTE sp_executesql @sql;

   -- 2、收缩文件
   SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) +
       --'GO' + Char(13) + Char(10) +
       'DBCC SHRINKFILE (N''' + @logname + ''', 1, TRUNCATEONLY)';
   EXECUTE sp_executesql @sql;

   -- 3、恢复原来的模式
   IF @dbRecovery = 1
   BEGIN
       SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL WITH NO_WAIT';
       EXECUTE sp_executesql @sql;
       SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL';
       EXECUTE sp_executesql @sql;
   END  

   PRINT '数据库:' + @dbName + ' 日志文件:' + @logname + ' 收缩完成';
END





代码 复制 - 运行

USE [test]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[UP_Shrink_Log]
  @dbName = N'test',
  @BakDiskPath = N'd:\xueqinjiaju_sb.bak'

SELECT 'Return Value' = @return_value

GO

小鱼的淘宝店铺-多多支持哇