如何收缩 SQL Server 中的 Tempdb 数据库


于数据库日志拉长棉被服装置为“无界定”,所以时间一长日志文件必然会十分的大,四个400G的数据库居然有600G的LOG文件,严重占用了磁盘空间。由于关键
是做OLAP,所以数据库本人不会有大退换,所以日志也就从未多少效果与利益了,由此想办法把数据库日志文件降至非常小照旧去除。

 

互连网寻找相关施工方案后,取得的答案多姿多彩,可是真的实用的方案并相当少,这里享受二个csdn上找到的措施。那些主意叙述了SQL
Server 二〇〇六和SQL Server
2009在减少数据库日志的区别之处,颇具帮扶。同时,该办法的频率异常高,缩短600G的日志到10M只花了不到30秒。

图片 1Tempdb
信息

末段附上代码:

图片 2收缩
Tempdb 的方法 1

适用于SQL Server 2000的方法

图片 3收缩
Tempdb 的方法 2

DUMP TRANSACTION [jb51] WITH NO_LOGBACKUP LOG [jb51] WITH NO_LOGDBCC SHRINKDATABASE([jb51])

图片 4收缩
Tempdb 的方法 3

中间jb51为多少库名

图片 5在使用
Tempdb 时执行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 的结果

适用于SQL Server 2005的方法

图片 6参考

Backup Log [jb51] WITH no_logGODUMP TRANSACTION [jb51] WITH no_logGOUSE jb51 DBCC SHRINKFILE (2)GO

 

注解:由于SQL Server
二〇一〇对文件和日志管理举办了优化,所以上述语句在SQL2005中能够运维但在SQL二零一零中早已被吊销。

概要

正文研商将 tempdb
数据库收缩为小于其上次安排的尺寸的二种办法。第一种艺术让你能够完全调节
tempdb 文件的轻重缓急,但它须要你再次开动 SQL Server。第二种格局将
tempdb 作为完全来减弱,但它具有有些限定,只怕满含重新启航 SQL
Server。第三种方法允许你减弱 tempdb
中的单个文件。最后三种方法必要在减少操作进程中在 tempdb
数据库中不发生任何活动。

注意:假如您使用的是 SQL Server
2007,那一个办法仍适用。可是,您应该使用 SQL Server Management Studio
并不是信用合作社管理器和询问深入分析器来试行这一个操作。

USE[master]GOALTER DATABASE jb51 SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE jb51 SET RECOVERY SIMPLE --简单模式GOUSE jb51GODBCC SHRINKFILE (N'DNName_Log', 11, TRUNCATEONLY)GOUSE[master]GOALTER DATABASE jb51 SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE jb51 SET RECOVERY FULL --还原为完全模式GO

Tempdb 信息

tempdb 是多个一时半刻事业区。除其余用处外,SQL Server 还将 tempdb
用于:

显式创建的临时表的存储。
保存在查询处理和排序过程中创建的中间结果的工作表。
具体化的静态光标。

SQL Server 在 tempdb
事务日志中著录的消息只丰盛用于回滚事务,而不足以用于在数据库故障复苏进度中重新推行专业。这一本性提升了
tempdb 中 INSERT 语句的质量。其余,由于每回重复开动 SQL Server
时都会再也创建tempdb,没有须求记下用于重新实行别的交事务情的消息。由此,未有其他要前滚或回滚的事体。当
SQL Server 运营时,通过应用 model 数据库的别本重新成立tempdb,并将其重新初始化为上次布署的轻重。

暗中认可情形下,tempdb
数据库配置为基于须要活动拉长;因而,此数据库也许最终升高到过量所需的朗朗上口。轻巧地重新启航
SQL Server 会将 tempdb
的大大小小重新初始化为上次布置的大大小小。配置的分寸是用文件大小改正操作(如带有
MODIFY FILE 选项的 ALTEPRADO DATABASE 或许 DBCC SH奥迪Q5INKFILE
语句)设置的上次显式大小。本文注解你能够用来将 tempdb
收缩到小于其结构的轻重的二种方法。

中间jb51为多少库名,DNName_Log为日志名,须求找一下,具体的表明能够参照那篇作品,也可能有图像和文字方法

收缩 Tempdb 的方法 1

此方式供给你再一次启航 SQL Server。

1. 停止 SQL Server。打开命令提示符,然后键入以下命令启动 SQL Server:

sqlservr -c -f

-c-f 参数使 SQL Server 以最小配置模式启动,让数据文件的 tempdb 大小为 1 MB,日志文件的 tempdb 为 0.5 MB。

注意:如果使用 SQL Server 命名实例,必须切换到适当的文件夹 (Program Files/Microsoft SQL Server/MSSQL$instance name/Binn),并使用 -s 开关 (-s%instance_name%)。

2. 用查询分析器连接到 SQL Server,然后运行下列 Transact-SQL 命令:

   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'tempdev', SIZE = target_size_in_MB) 
   --Desired target size for the data file

   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'templog', SIZE = target_size_in_MB)
   --Desired target size for the log file
                    

3. 通过在命令提示符窗口中按 Ctrl-C 停止 SQL Server,将 SQL Server 作为服务重新启动,然后验证 Tempdb.mdf 和 Templog.ldf 文件的大小。

此措施的受制是它必须要对默认的 tempdb 逻辑文件 tempdev
templog 实行操作。假如将别的文件加多到了 tempdb,您能够在将 SQL
Server 作为服务重新起动后减弱它们。在运维进程中校重新创制全体 tempdb
文件;由此,它们是空的并可去除。要去除 tempdb
中的其余文件,请使用含有 REMOVE FILE 选项的 ALTEPAJERO DATABASE 命令。

这篇小说就介绍到那了,须要的心上人能够参谋一下,希望我们今后多多关照脚本之家。

收缩 Tempdb 的方法 2

使用 DBCC SHRINKDATABASE 命令将 tempdb 数据库作为全体减弱。DBCC
SHTiggoINKDATABASE 选用参数
target_percent,该参数是数据库减弱后数据库文件中剩下可用空间的所需百分比。借使采纳DBCC SH牧马人INKDATABASE,大概必需再度开动 SQL Server。

重在表明:即使运维 DBCC SH哈弗INKDATABASE,则 tempdb
数据库不能够正在产生其余运动。要确认保障在运作 DBCC SH讴歌ZDXINKDATABASE
时其余进度相当的小概采纳 tempdb,必需以单顾客方式运营 SQL
Server。有关越来越多新闻,请参见本文的在使用 Tempdb 时施行 DBCC
SHCRUISERINKDATABASE 或 DBCCSH揽胜INKFILE 的结果 一节。

1. 通过使用 sp_spaceused 存储过程确定 tempdb 中当前使用的空间。然后,计算剩余可用空间的百分比,它将用作 DBCC SHRINKDATABASE 的参数;该计算是基于所需数据库大小进行的。

注意:在某些情况下,您可能必须执行 sp_spaceused @updateusage=true 来重新计算使用的空间和获得更新的报告。有关 sp_spaceused 存储过程的更多信息,请参考 SQL Server 联机丛书。

请考虑以下示例:

假定 tempdb 有两个文件:主数据文件 (Tempdb.mdf) 和日志文件 (Tempdb.ldf),其大小分别为 100 MB 和 30 MB。假定 sp_spaceused 报告主数据文件包含 60 MB 的数据。还假定您要将主数据文件收缩到 80 MB。计算收缩后剩余可用空间的所需百分比,即 80 MB – 60 MB = 20 MB。现在,用 20 MB 除以 80 MB = 25%,这就是您的 target_percent。事务日志文件将据此进行收缩,从而在数据库收缩后剩下 25% 即 20 MB 的可用空间。
2. 用查询分析器连接到 SQL Server,然后运行下列 Transact-SQL 命令:

   dbcc shrinkdatabase (tempdb, 'target percent') 
   -- This command shrinks the tempdb database as a whole
                    

tempdb 数据库使用 DBCC SHMuranoINKDATABASE
命令具备局限性。数据文件和日志文件的对象大小无法小于创立数据库时钦点的分寸,也不可能小于用文件大小校正操作(如含有
MODIFY FILE 选项的 ALTETucson DATABASE 命令或 DBCC SHMuranoINKFILE
命令)显式设置的上次大小。DBCC SH牧马人INKDATABASE 的另二个限量是
target_percentage 参数的计量和它对近期选择的长空的重视性。

收缩 Tempdb 的方法 3

利用命令 DBCC SHPAJEROINKFILE 减弱单个 tempdb 文件。DBCC SH揽胜INKFILE 比
DBCC SH中华VINKDATABASE
提供越来越多的八面见光,因为您能够对单个数据库文件使用它而不必影响归属同一数据库的别样文件。DBCC
SHCRUISERINKFILE 选用 target size 参数,那是所需的数据库文件的最后大小。

珍视表明:必须在 tempdb 数据库不产生别的活动时运营 DBCC
SHGL450INKFILE 命令。要担保在试行 DBCC SHMuranoINKFILE 时其它进程不能够利用
tempdb,必得以单客户格局再次开动 SQL Server。有关 DBCC SH奥迪Q7INKFILE
的更加多音信,请参见本文中在应用 Tempdb 时举行 DBCC SHCR-VINKDATABASE 或
DBCCSHWranglerINKFILE 的结果 一节。

1. 确定主数据文件 (tempdb.mdf)、日志文件 (templog.ldf) 和/或添加到 tempdb 的其他文件的所需大小。确保在这些文件中使用的空间小于或等于所需的目标大小。
2. 用查询分析器连接到 SQL Server,然后为需要收缩的特定数据库运行下列 Transact-SQL 命令:

   use tempdb
   go

   dbcc shrinkfile (tempdev, 'target size in MB')
   go
   -- this command shrinks the primary data file

   dbcc shrinkfile (templog, 'target size in MB')
   go
   -- this command shrinks the log file, look at the last paragraph.
                        

DBCC SHLX570INKFILE
的叁个独特之处是它能够将文件大小减小到低于其本来大小。您能够对此外数据文件或日志文件举行DBCC SH哈弗INKFILE。DBCC SH奥迪Q5INKFILE 的一个受制是你不能够使数据库小于 model
数据库的轻重。

在 SQL Server 7.0
中,事务日志减弱是三个延迟操作,您必须推行日志截断和备份,以协办数据库中的裁减操作。不过,暗许境况下,tempdb
trunc log on chkpt
选项设置为“展开”(ON卡塔尔;那样,您就无需为该数据库推行日志截断。有关怎么样在
SQL Server 7.0
中减少数据库事务日志的其他新闻,请单击上面包车型大巴文章编号,以查看 Microsoft
知识库中相应的篇章:

256650
(卡塔尔国 INF:如何减少 SQL
Server 7.0 事务日志

在使用 Tempdb 时执行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 的结果

当正在利用 tempdb 时,要是您尝试通过行使 DBCC SH福睿斯INKDATABASE 或 DBCC
SHEscortINKFILE
命令缩短它,恐怕会收下与以下体系平常的两个一致性错误,而且收缩操作恐怕破产:

Server:Msg 2501, Level 16, State 1, Line 1 Could not find table named
‘1525580473’.Check sysobjects.

– 或 –

Server:Msg 8909, Level 16, State 1, Line 0 Table Corrupt:Object ID 1,
index ID 0, page ID %S_PGID.The PageId in the page header = %S_PGID.

即使错误 2501 只怕不意味着 tempdb
中的任何破坏,但它会变成收缩操作战败。与其不相同,错误 8909 恐怕代表
tempdb 数据库中的损坏。应重新开动 SQL Server 来重新创立 tempdb
并扫除一致性错误。可是,请记住像错误 8909
那样的概况数据损坏也许有其余原因,那富含输入/输出子系统难点。

参考

SQL Server 联机丛书;核心:“DBCC SHPAJEROINKFILE”;“DBCC SHGL450INKDATABASE”


那篇随笔中的信息适用于:
Microsoft SQL Server 2000 标准版
Microsoft SQL Server 7.0 标准版
Microsoft SQL Server 2000 64-bit Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL 2005 Server Enterprise
Microsoft SQL 2005 Server Workgroup

发表评论

电子邮件地址不会被公开。 必填项已用*标注