demosql44.sql

--Session 1(模擬開發人員的SP邏輯)
while(1=1)
begin
create table #mytest
(	
c1 int
,c2 varchar(10)
,c3 varchar(max)
,c4 nvarchar(max)
)
insert into #mytest select 1,'rico',REPLICATE('ricoisme',100),REPLICATE('sherryis',100)
drop table #mytest
end

--session2(模擬自行收集資料庫資料邏輯)
declare @dbfilestats table
(
dbsize bigint
,logsize  bigint
,ftsize bigint
);

declare @allocateUnits table( 
        total_pages bigint
,       used_pages bigint
,       data_pages bigint
,       container_id bigint
,       type tinyint
); 
while(1=1)
begin

insert @dbfilestats 
SELECT  SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      , SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      , SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files --with(nolock);

INSERT @allocateUnits SELECT total_pages, used_pages, data_pages, container_id, type 
FROM sys.allocation_units; --with(nolock);

end