MSSQL varie

ricalcolo statistiche per database

use MioDB
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

spostamento datafiles

metodo detach attach

Metti offline il database

ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:

CREATE DATABASE MioDB
ON
(
   FILENAME='/nuovo_storage/MioDB.mdf'
),
(
   FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

metodo rename file

• elencare files e percorsi:

SELECT name,physical_name,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');

• Mettere offline il database:

ALTER DATABASE MioDb SET OFFLINE;
GO

Se serve forzare transazioni in corso:

ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

• Spostare o copiare i datafile

mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/

• rinominare i files nel DB

ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
 ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO

• rimettere online il DB

ALTER DATABASE MioDB  SET ONLINE;
GO

metodo online

• aggiungi un nuovo file

ALTER DATABASE NomeDatabase 
ADD FILE ( 
   NAME = 'NomeNuovoFile', 
   FILENAME = 'D:\NuovoPercorso\NomeNuovoFile.ndf', 
   SIZE = 100MB, 
   FILEGROWTH = 100MB 
);

• sposta i dati tutti nel nuovo file

DBCC SHRINKFILE ('NomeVecchioFile', EMPTYFILE);

• rimuovi il vecchio file

ALTER DATABASE NomeDatabase REMOVE FILE NomeVecchioFile;

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;

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    r.start_time,
    r.total_elapsed_time,
    r.wait_type,
    r.blocking_session_id,
    st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;

SELECT 
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;

sessione che usa il tempdb

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    (su.user_objects_alloc_page_count
     + su.internal_objects_alloc_page_count) * 8 / 1024 AS TempDB_MB,
    st.text
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s
    ON su.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.session_id > 50
ORDER BY TempDB_MB DESC;