SQLServer 维护脚本分享（08）临时数据库（tempdb）

dbcc sqlperf(logspace)	--各数据库日志大小及使用百分比

dbcc loginfo	--查看当前数据库的虚拟日志文件


--临时表'Tempdb'最近使用情况
SELECT t1.session_id,t1.internal_objects_alloc_page_count*8.0/1024 as internal_objects_alloc_MB,t1.internal_objects_dealloc_page_count *8.0/1024 as internal_objects_dealloc_MB,t1.user_objects_alloc_page_count*8.0/1024 as user_objects_alloc_MB,t1.user_objects_dealloc_page_count*8.0/1024 as user_objects_dealloc_MB,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 internal_objects_alloc_page_count DESC


--'Tempdb'存储对象使用情况
Select 'Tempdb' as DB,getdate() as Time,SUM (user_object_reserved_page_count)*8 as user_objects_kb,SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,SUM (version_store_reserved_page_count)*8  as version_store_kb,SUM (unallocated_extent_page_count)*8 as freespace_kb                
From sys.dm_db_file_space_usage                                          
Where database_id = 2  


--使用计数器(SQLServer:Transactions )监视 tempdb 中行版本存储区的大小和增长速率  
SELECT * FROM sys.dm_os_performance_counters  
where counter_name='Free Space in tempdb (KB)'  
or counter_name='Version Store Size (KB)'  
  
  
--tempdb大小和增长速率  
SELECT   
    name AS FileName,size*1.0/128 AS FileSizeinMB,CASE max_size   
        WHEN 0 THEN 'Autogrowth is off.'  
        WHEN -1 THEN 'Autogrowth is on.'  
        ELSE 'Log file will grow to a maximum size of 2 TB.'  
    END,growth AS 'GrowthValue','GrowthIncrement' =   
        CASE  
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'  
            WHEN growth > 0 AND is_percent_growth = 0   
                THEN 'Growth value is in 8-KB pages.'  
            ELSE 'Growth value is a percentage.'  
        END  
FROM tempdb.sys.database_files;  
GO  

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!