SQLERVER的tempdb库突然暴增（已经暴增了）,怎么排查暴增的原因？
发布网友
发布时间：2022-04-08 21:23
共2个回答
懂视网
时间：2022-04-09 01:44
今天收到预警消息,提示磁盘空间已经满了,感觉很奇怪.刚装的新机器怎么可能会磁盘空间不足.登陆看了看
可以看的到tempdb已经65G的了,而且显示是百分百可用.这个就很奇怪了,为什么会出现这种情况呢.考虑到磁盘会爆满,立即重启了sqlserver,修改tempdb的初始值大小为10G,然后回收调可用的空间给OS,看样子是告一段落了.事实上并不是这样,我的tempdb第二天暴增了.这是怎么回事?我这台机器现在跑的程序基本没有,只是作为复制的订阅端.怎么会出现这种情况呢.然后就登陆看了看数据库统计信息:
SELECT top 10 t1.session_id,
t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count,
t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,
t3.login_name,t3.status,t3.total_elapsed_time
from sys.dm_db_session_space_usage t1
inner join sys.dm_exec_sessions as t3
on t1.session_id = t3.session_id
where (t1.internal_objects_alloc_page_count>0
or t1.user_objects_alloc_page_count >0
or t1.internal_objects_dealloc_page_count>0
or t1.user_objects_dealloc_page_count>0)
order by t1.internal_objects_alloc_page_count desc
查看数据库的会话占用的tempdb的空间大小,然后根据SPID查看详细信息,然后查处会话的具体信息
根据信息我查到有一个会话占用特别多的临时空间,仔细查看了一下会话的详细信息.select 语句如下:
select 1
很奇怪对不对,有两个问题考虑一下:
1:为什么要一直不停执行这个语句
2:这个语句是用来做什么的
根据以上统计信息我们可以查出来登陆名和程序所在的程序名,初步确认了是那个程序造成的,然后根据上面两个疑问去查询
发现有以下代码:
红线内是原来没有的,一个数据库链接链接以后没有释放.所以才会一致链接数据库不释放整个会话和会话资源.然后加上以后重新启动程序.继续监控,发现问题已经解决了.
tempdb过大事故记录-sqlserver
标签：
热心网友
时间：2022-04-08 22:52
tempdb 是一个临时工作区。除其他用途外，SQL Server 还将 tempdb 用于：
• 显式创建的临时表的存储。
• 保存在查询处理和排序过程中创建的中间结果的工作表。
• 具体化的静态光标。
SQL Server 在 tempdb 事务日志中记录的信息只足够用于回滚事务，而不足以用于在数据库故障恢复过程中重新执行事务。这一特点提高了 tempdb 中 INSERT 语句的性能。另外，由于每次重新启动 SQL Server 时都会重新创建 tempdb，无需记录用于重新执行任何事务的信息。因此，没有任何要前滚或回滚的事务。当 SQL Server 启动时，通过使用 model 数据库的副本重新创建 tempdb，并将其重置为上次配置的大小。
默认情况下，tempdb 数据库配置为根据需要自动增长；因此，此数据库可能最终增长到大于所需的大小。简单地重新启动 SQL Server 会将 tempdb 的大小重置为上次配置的大小。配置的大小是用文件大小更改操作（如带有 MODIFY FILE 选项的 ALTER DATABASE 或者 DBCC SHRINKFILE 语句）设置的上次显式大小。本文说明您可以用来将 tempdb 收缩到小于其配置的大小的三种方法。
收缩 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 选项的 ALTER DATABASE 命令。
收缩 Tempdb 的方法 2
使用 DBCC SHRINKDATABASE 命令将 tempdb 数据库作为整体收缩。DBCC SHRINKDATABASE 接收参数 target_percent，该参数是数据库收缩后数据库文件中剩余可用空间的所需百分比。如果使用 DBCC SHRINKDATABASE，可能必须重新启动 SQL Server。
重要说明：如果运行 DBCC SHRINKDATABASE，则 tempdb 数据库不能正在发生其他活动。要确保在运行 DBCC SHRINKDATABASE 时其他进程无法使用tempdb，必须以单用户模式启动 SQL Server。有关更多信息，请参考本文的在使用 Tempdb 时执行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 的结果 一节。
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 SHRINKDATABASE 命令具有局限性。数据文件和日志文件的目标大小不能小于创建数据库时指定的大小，也不能小于用文件大小更改操作（如带有 MODIFY FILE 选项的 ALTER DATABASE 命令或 DBCC SHRINKFILE 命令）显式设置的上次大小。DBCC SHRINKDATABASE 的另一个*是target_percentage 参数的计算和它对当前使用的空间的依赖。追问首先，非常感谢你的回答，但是我是想要怎么快速找出暴增的原因
