MSSQL临时(tempdb)系统数据库

create database d
alter database d set read_committed_snapshot on
use d
--占用 tempdb 上用户自定对象的空间
create table ##t1(c1 int primary key identity(1,1),c2 nvarchar(10) default 'hello t1')
create table ##t2(c1 int primary key identity(1,1),c2 int,c3 nvarchar(10) default 'hello t2',c4 int default rand(datepart(ms,getdate()))*10000)

insert ##t1 default values

set nocount on
declare @i int
set @i=0
while @i<20
begin
insert ##t1(c2) select c2 from ##t1
set @i=@i+1
end 

--为了要让随机数乱，所以逐条增加...
while @i<1000000
begin
insert ##t2(c2) values (@i)
set @i=@i+1
end

--要占用 tempdb 上大量的内部对象
select * from ##t1 join ##t2 on ##t1.c1=##t2.c4 order by c4


--使用记录版本空间
select top 1000 * into t1 from ##t1

begin tran
update t1 set c2=c2+ ' a'

SELECT
SUM (user_object_reserved_page_count)*8 as [用户对象(kb)],
SUM (internal_object_reserved_page_count)*8 as [内部对象(kb)],
SUM (version_store_reserved_page_count)*8  as [纪录版本空间(kb)],
SUM (unallocated_extent_page_count)*8 as [可用空间(kb)],
SUM (mixed_extent_page_count)*8 as [mixedextent(kb)]
FROM sys.dm_db_file_space_usage

--或是在任务阶段或任务层级查看 tempdb 中的页面设置或取消设置活动

SELECT top 5 * 
FROM sys.dm_db_session_space_usage  
ORDER BY (user_objects_alloc_page_count +
    internal_objects_alloc_page_count) DESC

SELECT top 5 * 
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
    internal_objects_alloc_page_count) DESC

--移动Tempdb
Alter Database tempdb Modify File (Name='tempdev',FileName='C:\temp\temp.mdf',size=30 MB,FileGrowth=10 MB)
Alter Database tempdb Modify File (Name='templog',FileName='C:\temp\temp.ldf',size=10 MB,FileGrowth=10 MB)

--增加Tempdb相关文件
Alter Database tempdb Add File (Name='tempdev2',FileName='C:\temp\temp2.mdf',size=30 MB,FileGrowth=10 MB)
Alter Database tempdb Modify File (Name='templog2',FileName='C:\temp\temp2.ldf',size=30 MB,FileGrowth=10 MB)

--以SQLCMD模式停止并且重新激活服务
!!NET STOP MSSQLSERVER /Y
!!NET START MSSQLSERVER

--可以在sys.master_files系统视图查看到文件位置