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

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 AS path,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

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;