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

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

Tempdb 信息

tempdb 是一个临时工作区。除其他用途外，SQL Server 还将 tempdb 用于：

•

显式创建的临时表的存储。

•

保存在查询处理和排序过程中创建的中间结果的工作表。

•

具体化的静态光标。

一、用户数据库搬家

*** 作方法有以下两种

方法1：在企业管理器中进行

（1）在企业管理器中选定要搬家的数据库名，在其上单击鼠标右键，出现如图1所示对话框。

（2）选中“所有任务”，在其子菜单中单击“分离数据库”。

（3）将该数据库的数据文件和日志文件从旧的位置（如D:\MSSQL\Data）拷贝到新的位置（如E:\SQLDataNew）。

（4）再在企业管理器中选中“数据库”文件夹，在其上单击鼠标右键，在出现的对话框中选中“所有任务”，在其子菜单中单击“附加数据库”。

（5）在出现的“附加数据库”对话框中，选择数据库的两个文件的新位置。

方法2：用SQL系统存储过程sp_detach_db/sp_attach_db实现

（1）使用下面语句分离数据库：

USE master

GO

EXEC sp_detach_db 'mydb'

GO

（2）将该数据库的数据文件和日志文件从旧的位置拷贝到新的位置。

（3）使用下面语句重新附加数据库：

USE master

GO

EXEC sp_attach_db

'mydb', 'E:\SQLDataNew\mydbdatamdf', 'E:\SQLDataNew\mydblogldf''

GO

二、系统数据库搬家

1．master数据库搬家

（1）由于SQL Server每次启动的时候都需要读取master数据库，因此必须在企业管理器中改变SQL Server启动参数中master数据文件和日志文件的路径。

在 SQL Server属性的“常规”选项卡下面有一个叫做“启动参数”的按钮，单击后可以看到所示的界面。

其中，-d用来指定master数据库文件的路径，而–l用来指定master数据库日志文件的路径。

删除旧的master数据库文件和日志文件的路径，添加新的master数据文件和日志文件所在的路径，如：

-dE:\ SQLDataNew \mastermdf

-lE:\ SQLDataNew \mastlogldf

（2）停止SQL Server服务，将mastermdf文件和mastlogldf文件拷贝到新的位置，如E:\ SQLDataNew文件夹中。

（3）重新启动服务即可。

小技巧：如果在“启动参数”添加的master数据库文件和日志文件的路径或文件名有误，就会造成SQL Server启动不起来。而此时想改正错误又因SQL Server启动不了而重设不了“启动参数”。其实这两个文件路径和文件名是放置在注册表HKEY_LOCAL_MACHINE\SOFWARE\Microsoft\Miccrosoft SQL Server\你安装SQL服务器的名\MSSQLServer\Parrmeters下的参数SQLArg1和SQLArg2中，修改这两个参数的值即可。

2 msdb数据库和model数据库搬家

（1）对于msdb数据库和model数据库，我们不能在企业管理器中实现分离和附加，只能用sp_detach_db和sp_attach_db存储过程来分离和附加这两个数据库。

其中，对于msdb数据库，还应确保SQL Server代理当前没有运行，否则sp_detach_db存储过程将运行失败，并返回如下消息：

“无法除去数据库 'msdb' ，因为它当前正在使用。”

（2）调出如图4所示的“启动参数”对话框，添加新参数-T3608。这样，启动SQL Server，它就不会恢复除master之外的任何数据库。

（3）重新启动SQL Server，再用下面命令分离msdb或者model数据库：

EXEC sp_detach_db 'msdb'

GO

（4）将数据文件和日志文件移到新的位置，然后再用下面命令重新附加msdb数据库或者model数据库：

USE master

GO

EXEC sp_attach_db

'msdb', ‘E:\SQLDataNew\msdbdatamdf', ' E:\SQLDataNew\msdblogldf '

GO

（5）在企业管理器中，从“启动参数”框中删除-T3608跟踪标记，再停止并重新启动SQL Server。

3 tempdb数据库搬家

对于tempdb数据库，则可以直接使用ALTER DATABASE语句来为数据文件和日志文件指定新的存放位置，如下所示：

USE master

GO

ALTER DATABASE tempdb MODIF

Y FILE (NAME=tempdev, FILENAME ='E:\SQLDataNew \tempdbmdf')

GO

ALTER DATABASE tempdb MODIF

Y FILE(NAME=templog, FILENAME=

' E:\SQLDataNew \templogldf')

GO

至此，SQL Server 中数据库全部搬家完成。

收缩 Tempdb 的方法

此方法要求您重新启动 SQL Server。

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

sqlservr -c -f

-c 和 -f 参数使 SQL Server 以最小配置模式启动，让数据文件的 tempdb 大小为 1 MB，日志文件的 tempdb 为 05 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 作为服务重新启动，然后验证 Tempdbmdf 和 Templogldf 文件的大小。

此方法的局限是它只能对默认的 tempdb 逻辑文件 tempdev 和 templog 进行 *** 作。如果将其他文件添加到了 tempdb，您可以在将 SQL Server 作为服务重新启动后收缩它们。在启动过程中将重新创建所有 tempdb 文件；因此，它们是空的并可删除。要删除 tempdb 中的其他文件，请使用带有 REMOVE FILE 选项的 ALTER DATABASE 命令。

肯定有影响，先说说tempdb数据库的作用吧：

Tempdb 数据库保存系统运行过程中产生的临时表和存储过程。当然，它还满足其他的临时存储要求，比如保存SQL Server生成的存储表等。Tempdb 数据库是一个全局咨询，任何连接到系统的用户都可以在该数据库中产生临时表和存储过程。Tempdb 数据库在每次SQL Server启动的时候，都会清空该数据库中的内容，所以每次启动SQL Server后，该表都是干净的。临时表和存储过程在连接断开后会自动除去，而且当系统关闭后不会有任何活动连接，因此，tempdb 数据库中没有任何内容会从SQL Server的一个会话保存到另外一个会话中。

默认情况下，在 SQL Server 在运行时 tempdb 数据库会根据需要自动增长。不过，与其它数据库不同，每次启动数据库引擎时，它会重置为其初始大小。如果为 tempdb 数据库定义的大小较小，则每次重新启动 SQL Server时，将tempdb 数据库的大小自动增加到支持工作负荷所需的大小这一工作可能会成为系统处理负荷的一部分。为避免这种开销，可以使用 ALTER DATABASE 增加 tempdb 数据库的大小。

其实总体来说TempDB数据库是很重要的，也是你提升SQL运行速度的一个手段，所以，TempDB的日志已满，说明的数据库文件初始化设定值太小，建议你增大到500MB，我一直都这样用，当然你可以依据实际情况设定大小

1 首先在 SQL Server 服务器级别，创建登陆帐户（create login）

--创建登陆帐户（create login）

create login dba with password='abcd1234@', default_database=mydb

登陆帐户名为：“dba”，登陆密码：abcd1234@”，默认连接到的数据库：“mydb”。 这时候，dba 帐户就可以连接到 SQL Server 服务器上了。但是此时还不能 访问数据库中的对象（严格的说，此时 dba 帐户默认是 guest 数据库用户身份， 可以访问 guest 能够访问的数据库对象）。

要使 dba 帐户能够在 mydb 数据库中访问自己需要的对象， 需要在数据库 mydb 中建立一个“数据库用户”，赋予这个“数据库用户” 某些访问权限，并且把登陆帐户“dba” 和这个“数据库用户” 映射起来。 习惯上，“数据库用户” 的名字和 “登陆帐户”的名字相同，即：“dba”。 创建“数据库用户”和建立映射关系只需要一步即可完成：

2 创建数据库用户（create user）：

--为登陆账户创建数据库用户（create user）,在mydb数据库中的security中的user下可以找到新创建的dba

create user dba for login dba with default_schema=dbo

并指定数据库用户“dba” 的默认 schema 是“dbo”。这意味着 用户“dba” 在执行“select from t”，实际上执行的是 “select from dbot”。

3 通过加入数据库角色，赋予数据库用户“dba”权限：

--通过加入数据库角色，赋予数据库用户“db_owner”权限

exec sp_addrolemember 'db_owner', 'dba'

此时，dba 就可以全权管理数据库 mydb 中的对象了。

农业银行总行 年以来正式推广了新版网络版综合业务统计信息系统 该系统是基于WindowsNT 平台 采用客户／服务器模式 以Microsoft SQL Server为基础建立起来的大型数据库应用程序 系统界面友好 *** 作简便 计算 分析 检索功能非常强大 为保证农业银行系统及时进行纵向和横向业务数据采集 按照不同要求生成统计报表 进行全面业务活动分析提供了强有力的保障 但在这套程序的推广 维护中笔者发现系统有时运行速度较慢 特别是在Win 客户端 *** 作时尤为严重 经过排除网线连接等硬件可能带来的影响后上述问题仍然存在 笔者经过仔细摸索 发现系统对硬 软件的要求较高 为充分发挥设计效能 达到最佳运作效果 需要对计算机硬 软件系统进行较为完备的性能测试与最佳配置 特别是内存配置的好坏对系统的运行速度具有决定性的作用 下面 笔者就如何优化SQLServer数据库服务器的内存配置提出一些认识和看法 一 有关内存的基本概念 物理内存与虚拟内存WindowsNT使用两类内存 物理内存与虚拟内存 物理内存 作为RAM芯片安装在计算机内部的存储器 虚拟内存 用于模拟RAM芯片功能的磁盘(硬盘)空间 其实质是通过将内存中当前没有使用的部分内容临时存储到磁盘上 使系统可以使用到比机器物理内存更多的内存 分页和分页文件WindowsNT系统通过使用磁盘空间使得对内存的需求得到部分缓解 从而使用到比物理内存更多内存的技术就称为 交换 或分页 也就是通常所说的虚拟内存技术 通常Windows NT 系统安装时将在引导驱动器上设置一个大小为 MB的交换(分页)文件(pagefile sys) 二 优化Windows NT 系统内存配置在大多数情况下 为了充分发挥Windows NT 系统效能 内存的作用比起处理器的处理能力更具有影响力 特别是在客户／服务器模式环境下更是如此 因为通常在这种环境下并不十分强调处理器的能力 相反却十分注重是否采用足够的内存来满足各个客户的应用需要 此外 为了获得容错功能和保护应用程序 保证应用程序高速运行 充分发挥设计功能都需要有足够多的内存 特别是工业绘图设计和各种工程应用程序更需要占用大量的内存来进行复杂的计算 物理内存(RAM)方便快速的优点显而易见 但由于其价格昂贵 也就不可能做到多多益善了 因此通过合理优化内存配置 扩充虚拟内存提高计算机运算速度也就成了一项很重要的应用技术手段 保证Windows NT系统基本内存需求Windows NT 系统至小应配置 MB内存 MB内存基本够用 正常情况下保证NT系统有 MB内存就可以了 因为并不是所有的 MB基本内存在任何时候都被同时使用 如果添加一些服务和应用程序 则对内存的需求就会急剧增大 如 ( )添加网络服务需要 MB内存空间 ( )容错功能和系统保护功能需要 MB内存(如磁盘镜像和分条功能) ( )进行图形图象处理需要增加 MB内存空间 ( )安装VC VB开发系统需要增加 MB内存空间 另外 如在Windows NT上构建大型数据库如SYBASE Microsoft SQL Server等 对内存的需求就更多了 优化内存性能为了使WindowsNT不至于过分占用较多的内存或者浪费处理器的时间用于换页 可以采用以下方法优化内存性能 ( )减少显示颜色的数量 ( )降低显示分辨率 ( )尽可能不使用或使用位宽度较小的墙纸 ( )关闭不需要的服务程序或驱动程序 尽量不要在服务器上使用其它应用程序 停用服务或驱动程序的 *** 作步骤如下 ①确定需要停用的服务或驱动程序的名称 ②从 控制面板 中双击 服务 或 设备 图标 ③在列表中选择想要停用的服务或设备驱动程序的名称 单击 停止 按钮 这时出现确认 *** 作对话框 ④选择 是 确认 *** 作 然后关闭对话框完成设置 优化虚拟内存在对Windows NT虚拟内存进行设置时需要合理确定各个驱动器分页文件的 起始大小 和 最大值 两个参数 它们用于指定分页文件的起始空间和最大空间 下面对这两个参数作一些解释 起始大小 指初始创建该分页文件时的文件大小 单位为MB 根据缺省设置 这个值被设置为系统中的物理内存的大小 最大值 指出该分页文件的最大尺寸 单位为MB ( )分页文件的设置原则 ①分页文件起始大小应保留缺省设置 一般情况下请不要改动 ②分页文件理想的最大尺寸为系统物理内存尺寸的 倍至 倍 需要说明的是 如果系统工作时不需要大量内存 请选择靠近下限的值 即用系统物理内存的 倍作为这个尺寸的起始值 如果系统工作时需要大量内存 请选择靠近上限的值 ( )Windows NT虚拟内存设置步骤 ①从 控制面板 中双击 系统 图标 ②在 系统特性 对话框中单击 性能 标签 ③在虚拟内存对话框中单击 更改 按钮 这时出现 虚拟内存 对话框 上端的驱动器框逐一列出了 Windows NT所有页面文件的大小 ④在驱动器列表中 选择需要设置分页文件的驱动器盘符 在 驱动器页面文件大小 对话框中列出了 起始大小 和 最大值 两个参数栏 填入按照上面的原则确定的数值 ⑤单击 设置 确认以上 *** 作 然后依次单击 确定 按钮退出各个对话框 完成设置 ( )Win ／ 虚拟内存设置 Win ／ 虚拟内存设置方法 步骤和原则与Windows NT 的设置大致相同 请参照上面Windows NT的设置 注意事项( )合理确定分页文件的最大值 根据系统需求随时进行调整 使用过多虚拟内存将导致整个系统处理性能的下降 设置虚拟内存最大值的目的是使用户不必在WindowsNT的交换文件上消耗过多的磁盘空间 通常情况下如果超过了系统需要的最佳值后 生成交换文件的磁盘空间就被浪费了 ( )尽可能设立专用硬盘配置内存交换区 或将交换空间放到主硬盘的另一个分区 同时应将主硬盘的交换文件大小降至 MB 这样主硬盘(分区)仅用来放置 *** 作系统和应用程序 就可以减少交换次数 防止频繁交换耗费大量 CPU时间 ( )虚拟内存技术的确改善了Windows NT系统的性能 但也受到机器硬盘空间大小 硬盘速度 处理器 (CPU)速度的影响 从理想角度出发 要提高计算机的性能就必须减少交换 *** 作的次数 但是没有一个WindowsNT计算机不发生交换 这就要求计算机要有足够的物理内存 以保持最少的交换 *** 作 三 优化Microsoft SQL Server数据库内存配置内存是影响Microsoft SQL Server系统性能的一个重要因素 SQL Server数据库安装时将为具有 MB物理内存的机器缺省配置 MB可用内存 MB物理内存的机器缺省配置 MB可用内存 应在Microsoft SQL Server数据库安装后进行内存选项(Memory)设置 最大配置值为 GB 为了确定SQL Server系统最适宜的内存需求 可以从总的物理内存中减去Windows NT 需要的内存以及其它一些内存需求后综合确定 理想的情况是给SQL Server分配尽可能多的内存 而不产生页面调度 根据物理内存合理规划SQL Server可用内存在大多数的生产环境中 服务器配备的物理内存是 MB～ MB 偶尔也有 MB的 只要配置恰当是完全可以满足SQL Server的内存需求的 下表是笔者关于SQL Server内存分配的建议规划 供参考 物理内存 分配给SQL Server 设置值(单位 KB) MB MB MB MB MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ MB ～ 以下是SQL Server内存选项(Memory)设置方法( )从Microsoft SQL Server程序集中启动SQL Enterprise Manager ( )从Server Manager窗口中选择 Server 菜单选项 ( )在 Server 菜单中选择 Configurations 选项 ( )在 Server Configuration 对话框中选择 Configuration 标签 Configuration窗口显示配置选项列表 ( )选中 Memory 项目 在 Current 栏填入新值 ( )停止并重新启动SQLServer服务 使设置生效 合理扩充虚拟内存 增大SQL Server可用内存当SQL Server系统确实需要扩大可用内存时 应在磁盘空间充足的情况下扩充供虚拟内存 并相应增大 SQL Server可用内存 具体做法是 系统管理员首先扩充服务器的虚拟内存 然后再参考上表增大SQL Server可用内存 关键是要根据系统的负载情况综合决定是否扩充内存 优化配置 使用tempinRAMSQL Server使用tempdb临时数据库作为一些查询连接 *** 作时排序或创建临时表的工作空间 将tempdb创建在RAM中可以使系统 *** 作性能有较大提高 而且因为tempdb在每次重启动服务器时都重建 这样即使有非正常的关闭也是较为安全的 例如停电故障 要将tempdb创建在RAM中 可以使用sp_configure进行设置 具体用法请参阅有关资料 由于tempdbinRAM使用的内存是由系统从内存体单独分配的 与SQL Server的内存选项设置的可用内存池是分开的 使用tempdbin RAM将减少整个系统的可用内存 应根据SQL Server和服务器运行情况进行配置 否则就可能适得其反 影响系统性能 另外 适当增加tempdb数据库空间 即使不使用temp lishixinzhi/Article/program/SQLServer/201311/22052

以上就是关于如何收缩 SQL Server 中的 Tempdb 数据库全部的内容，包括:如何收缩 SQL Server 中的 Tempdb 数据库、数据库空间不足怎么解决、如何清除sqlserver tempdb数据等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！