MSSQL varie

ricalcolo statistiche per database

use miudb
go
EXEC sp_updatestats;
go

Elenco files e spazio occupato

SELECT 
   d.name AS DatabaseName,
   f.name AS LogicalFileName,
   f.physical_name AS PhysicalFilePath,
   f.type_desc AS FileType,
   cast(f.size*8.0/1024.0 as float) mb,
   f.max_size,
   f.growth
FROM 
   sys.master_files AS f
INNER JOIN 
   sys.databases AS d ON f.database_id = d.database_id
   order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

sessioni bloccate

SELECT 
   r.session_id AS [Sessione Bloccata],
   r.blocking_session_id AS [ID Bloccante],
   t.text AS [Query Bloccante],
   w.wait_type AS [Tipo di Attesa],
   w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;