SQL Reference

By Frederic JOFES and Co.

Étiquette : Space used

Mesurer l’espace utilisé par la tempdb

Pour la session courante :

SELECT * FROM sys.dm_db_session_space_usage WHERE SESSION_ID = @@spid
GO
--<create or rebuild the index>
GO
select * from sys.dm_db_session_space_usage where session_id = @@spid
GO

Pour chaque taches :

sys.dm_db_task_space_usage

Analyse sur les répercutions des actions de réindexation sur la tempdb :
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name

Space used by datafiles for a database

Script de visualisation des espaces occupés et alloués

Lire la suite

Download Treesize

>>> Download Treesize !

Check space used on each database of an instance

Affichage des espaces utilisés pour chaque base

Lire la suite

Check physical space allocated & space used on each files of an instance

Affichage des espaces physiques et alloués pour chaques fichiers

Lire la suite

Fièrement propulsé par Fred & Co.