DB太大?一键帮你收缩所有DB文件大小(Shrink Fi

作者:分分快三全天计划网站

 

use DB_NAME
sp_dboption DB_NAME, "trunc. log on chkpt.", true
checkpoint
sp_dboption DB_NAME, "autoshrink", true 

 

实践以上语句能够飞快减掉日志文件到1M。

分分快三全天计划网站 1

有未有更加快的主意吗?

给大家带给的不改变深表歉意。

无效。

 

 

 

第二行提醒“One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options. ”

剧本如下:

DBCC shrinkfile (N'DBTEST_Log' , 0, TRUNCATEONLY) --裁减日志文件

  • 意气风发旦不放心使用,可提早备份相关数据库;
  • 应用前请稳重阅读脚本援助成效和连锁逻辑,如与和谐要求不符,请不要选择该脚本,只怕请依据自己须求自动改良脚本;
  • 剧本为简单脚本,仅用于测量检验学习,大概有BUG,不可临盆情形使用,如有错误,请留言。

DBCC shrinkfile ('DBTEST_Log',1)--裁减日志文件 

Note:

sql Server 二零零六验证中分明:包罗 DUMP 语句是为了向后相当。而 后续版本的 Microsoft SQL Server 将去除该功效。请制止在新的开垦专业中央银行使该效能,并开头纠正当前还在接纳该意义的应用程序。 使用 BACKUP。

正文介绍叁个简短的SQL脚本,达成裁减整个Microsoft SQL Server实例全体非系统DB文件大小的效用。

分分快三全天计划网站 2代码

实践完效果如下:

  1. Detach数据库。
    2.删除log文件。
  2. 叠合数据库,选移除log文件,那时候SQL Server 会自动重新创立二个512K 的Log 文件。

进而后生可畏旦想在SQL Server 二零一二以下版本中应用的话须求把CONCAT函数改成string 方法,具体如下:

其三行能够实施。但日志log文件并未有别的变动。

 1 -- Created by Bob from http://www.cnblogs.com/lavender000/
 2 use master
 3 DECLARE dbCursor CURSOR for select name from [master].[sys].[databases] where state = 0 and is_in_standby = 0;
 4 DECLARE @dbname NVARCHAR(255)
 5 DECLARE @recoveryModel NVARCHAR(255)
 6 DECLARE @tempTSQL NVARCHAR(255)
 7 DECLARE @dbFilesCursor CURSOR
 8 DECLARE @dbFile NVARCHAR(255)
 9 DECLARE @flag BIT
10 
11 OPEN dbCursor
12 FETCH NEXT FROM dbCursor INTO @dbname
13 
14 WHILE @@FETCH_STATUS = 0
15 BEGIN
16     if((@dbname <> 'master') and (@dbname <> 'model') and (@dbname <> 'msdb') and (@dbname <> 'tempdb') and (@dbname <> 'Resource'))
17     begin
18         print('')
19         print('Database ['   @dbname   '] will be shrinked log...')
20         SET @flag = 1
21         SET @recoveryModel = (SELECT recovery_model_desc FROM sys.databases WHERE name = @dbname)
22         if((@recoveryModel = 'FULL') or (@recoveryModel = 'BULK_LOGGED'))
23         begin
24             SET @tempTSQL = (select CONCAT('ALTER DATABASE [', @dbname, '] SET RECOVERY SIMPLE with no_wait'))
25             EXEC sp_executesql @tempTSQL
26             if (@@ERROR = 0)
27             begin
28                 print('    Database ['   @dbname   '] recovery model has been changed to ''SIMPLE''.')
29                 SET @flag = 1
30             end
31             else
32             begin
33                 print('Database ['   @dbname   '] recovery model failed to be changed to ''SIMPLE''.')
34                 SET @flag = 0
35             end
36         end
37 
38         if(@flag = 1)
39         begin
40             SET @tempTSQL = (select CONCAT('use [', @dbname, ']'))
41             EXEC sp_executesql @tempTSQL            
42             SET @dbFilesCursor = CURSOR for select sys.master_files.name from sys.master_files, [master].[sys].[databases] where databases.name = @dbname and databases.database_id = sys.master_files.database_id
43             open @dbFilesCursor
44             FETCH NEXT FROM @dbFilesCursor INTO @dbFile
45             WHILE @@FETCH_STATUS = 0
46             BEGIN
47                 SET @tempTSQL = (select CONCAT('use [', @dbname, '] DBCC SHRINKFILE (N''', @dbFile, ''') with NO_INFOMSGS'))
48                 EXEC sp_executesql @tempTSQL
49                 if(@@ERROR = 0)    print('        Database file ['   @dbFile   '] has been shrinked log successfully.')
50                 FETCH NEXT FROM @dbFilesCursor INTO @dbFile
51             END
52             CLOSE @dbFilesCursor
53             DEALLOCATE @dbFilesCursor
54 
55             if(@recoveryModel <> 'SIMPLE')
56             begin
57                 -- Finally changed back
58                 SET @tempTSQL = (select CONCAT('ALTER DATABASE [', @dbname, '] SET RECOVERY ', @recoveryModel, ' with no_wait'))
59                 EXEC sp_executesql @tempTSQL
60                 if (@@ERROR = 0)
61                 begin
62                     print('    Database ['   @dbname   '] recovery model has been changed back to '''   @recoveryModel   '''')
63                 end
64                 else 
65                 begin
66                     print('    Database ['   @dbname   '] recovery model failed to be changed back to '''   @recoveryModel   '''')
67                 end
68             end
69         end
70     end
71 FETCH NEXT FROM dbCursor INTO @dbname
72 END
73 
74 CLOSE dbCursor
75 DEALLOCATE dbCursor

然而上述语句中前两行在SQL Server 2010下不或然实践 ,

'CONCAT' is not a recognized built-in function name.

品味方法三:  

此处介绍一个本子,扶植大器晚成键收缩整个SQL Server实例上有所非系统DB文件大小。

 

分分快三全天计划网站 3

backup log DBTEST with no_log  --截断事务日志

更新:该脚本在SQL Server 2010揽胜2上实行也许会报如下错误:

----Logical Files :
--CMS1.5_Data
--CMS1.5_Log
DBCC SHRINKFILE (N'CMS1.5_Log' , 1)
GO

分分快三全天计划网站 4

---DBTEST 为数据库名,顺序最佳别乱。注意要先截断再清空,最终减弱!

[原站点:,来自世代薰薰]

每豆蔻梢头行指令请单独施行。个中的DB_NAME是指Database Name,在施行完语法后的数钟头至数十钟头,该LOG档会慢慢释放空间,最后差不离都会维持在数1024KB左右。

用作二个与SQL每二二十八日打交道的技师,通常会遇上DB文件太大,把空间占满的事态:

 

而对于开辟测量检验人士来讲,假若DB数据不是特意主要的话,不会专程扩张磁盘空间,而是径直利用SQL的Shrink File功能减弱DB文件大小,详见:。

附图:

 1 -- Created by Bob from http://www.cnblogs.com/lavender000/
 2 use master
 3 DECLARE dbCursor CURSOR for select name from [master].[sys].[databases] where state = 0 and is_in_standby = 0;
 4 DECLARE @dbname NVARCHAR(255)
 5 DECLARE @recoveryModel NVARCHAR(255)
 6 DECLARE @tempTSQL NVARCHAR(255)
 7 DECLARE @dbFilesCursor CURSOR
 8 DECLARE @dbFile NVARCHAR(255)
 9 DECLARE @flag BIT
10 
11 OPEN dbCursor
12 FETCH NEXT FROM dbCursor INTO @dbname
13 
14 WHILE @@FETCH_STATUS = 0
15 BEGIN
16     if((@dbname <> 'master') and (@dbname <> 'model') and (@dbname <> 'msdb') and (@dbname <> 'tempdb') and (@dbname <> 'Resource'))
17     begin
18         print('')
19         print('Database ['   @dbname   '] will be shrinked log...')
20         SET @flag = 1
21         SET @recoveryModel = (SELECT recovery_model_desc FROM sys.databases WHERE name = @dbname)
22         if((@recoveryModel = 'FULL') or (@recoveryModel = 'BULK_LOGGED'))
23         begin
24             SET @tempTSQL = ('ALTER DATABASE ['   @dbname   '] SET RECOVERY SIMPLE with no_wait')
25             EXEC sp_executesql @tempTSQL
26             if (@@ERROR = 0)
27             begin
28                 print('    Database ['   @dbname   '] recovery model has been changed to ''SIMPLE''.')
29                 SET @flag = 1
30             end
31             else
32             begin
33                 print('Database ['   @dbname   '] recovery model failed to be changed to ''SIMPLE''.')
34                 SET @flag = 0
35             end
36         end
37 
38         if(@flag = 1)
39         begin
40             SET @tempTSQL = ('use ['   @dbname   ']')
41             EXEC sp_executesql @tempTSQL            
42             SET @dbFilesCursor = CURSOR for select sys.master_files.name from sys.master_files, [master].[sys].[databases] where databases.name = @dbname and databases.database_id = sys.master_files.database_id
43             open @dbFilesCursor
44             FETCH NEXT FROM @dbFilesCursor INTO @dbFile
45             WHILE @@FETCH_STATUS = 0
46             BEGIN
47                 SET @tempTSQL = ('use ['   @dbname   '] DBCC SHRINKFILE (N'''   @dbFile   ''') with NO_INFOMSGS')
48                 EXEC sp_executesql @tempTSQL
49                 if(@@ERROR = 0)    print('        Database file ['   @dbFile   '] has been shrinked log successfully.')
50                 FETCH NEXT FROM @dbFilesCursor INTO @dbFile
51             END
52             CLOSE @dbFilesCursor
53             DEALLOCATE @dbFilesCursor
54 
55             if(@recoveryModel <> 'SIMPLE')
56             begin
57                 -- Finally changed back
58                 SET @tempTSQL = ('ALTER DATABASE ['   @dbname   '] SET RECOVERY '   @recoveryModel   ' with no_wait')
59                 EXEC sp_executesql @tempTSQL
60                 if (@@ERROR = 0)
61                 begin
62                     print('    Database ['   @dbname   '] recovery model has been changed back to '''   @recoveryModel   '''')
63                 end
64                 else 
65                 begin
66                     print('    Database ['   @dbname   '] recovery model failed to be changed back to '''   @recoveryModel   '''')
67                 end
68             end
69         end
70     end
71 FETCH NEXT FROM dbCursor INTO @dbname
72 END
73 
74 CLOSE dbCursor
75 DEALLOCATE dbCursor

尝试方法四:(请提早备份文件!!卡塔 尔(阿拉伯语:قطر‎

  • 三个种类DB(master、model、msdb、tempdb、Resource卡塔 尔(阿拉伯语:قطر‎不会进行Shrink File操作;
  • 如果DB的Recovery Model是FULL或者BULK_LOGGED,会活动改成SIMPLE,Shrink File操作后再改回原本的;
  • 只会对DB状态为ONLINE的DB进行Shrink File操作;
  • DB的兼具文件,满含数据文件和日志文件都会被施行减少操作(Shrink Database Data Files & Shrink Database Log Files卡塔 尔(英语:State of Qatar);
  • 试行SQL脚本的顾客要求有sysadmin可能连带数据库的DBO权限。

DBCC shrinkdatabasse ('DBTEST',TRUNCATEONLY) 裁减数据库和日志库

[原创随笔,转发请声明出处,仅供就学斟酌之用,如有错误请留言,多谢扶持]

SQL 收缩数据库日志的两种方法 (二零零五与二〇〇八 略有区别卡塔 尔(英语:State of Qatar)

 

办法五(未有试过,请提前备份文件!!卡塔 尔(英语:State of Qatar):

 1. 停止 SQL Server 的服务
 2. 应用删除 Log文件
 3. 重新起动SQL Server 服务,这个时候SQL Server 会自动重新树立一个1MB 的Log 文件。
方法六: (尘尘提供)

先安装复苏方式为“轻易苏醒”情势,再收缩:

 

USE BigData ;
GO
ALTER DATABASE BigData
SET RECOVE福特ExplorerY SIMPLE;--设置轻松复苏情势
GO
DBCC SHRINKFILE (BigData_Log, 1);
GO
ALTER DATABASE BigData
SET RECOVE中华VY FULL;--恢复为原情势
GO 

  方法七: (尘尘提供)

 USE BigData;

GO
BACKUP LOG DATABASENAME TO DISK='d:test.bak'
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Bigdata_Log, 1);
GO

 

随笔来源

本子帮衬功效和血脉近似逻辑如下:

第意气风发行提醒“Incorrect syntax near the keyword 'TRANSACTION'.”

 

品尝方法二:  

是因为脚本中运用了CONCAT函数,该函数是TSQL在SQL Server 二〇一一中新引入的函数,具体详尽:。

 

SQL Server 2008说明:BACKUP LOG WITH NO_LOG 和 WITH TRUNCATE_ONLY 选项已废止。使用完整复苏情势或大体量日志苏醒形式时,要是必需删除数据库中的日志备份链,请切换至轻巧恢复生机格局。有关详细消息,请参阅有关从总体复苏情势或大体量日志苏醒形式切换的注意事项。

方法一:

 另有只减少日志文件,语句如下 

 

在SQL Server 二〇〇四/2007中得以赶快回退日志log文件,通过SQL,

dump transaction DBTEST with no_log  --清空日志

本来SQL Server 二〇〇八 已经不复扶持 DUMP TRANSACTIONBACKUP LOG WITH NO_LOG,详细的情况请看
  
   

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 2-SQL 数据库