[OT] Compattare DB Microsoft Sql Server

[bonmario]

Ciao a tutti,
mi hanno passato in DB in oggetto.
Ho fatto il ripristino, ed il DB occupava circa 1.7 Giga Bytes, il file di Log circa 300
Il DB conteneva circa 18.000 tabelle, quasi tutte contenenti dati. Di queste tabelle, ne ho cancellate circa 9.000

Arriviamo quindi alla domanda: come faccio a compattare il DB per recuperare lo spazio che prima occupavano le 9.000 tabelle che ho cancellato?
Ho visto parecchie guide, che consigliano questo:
- Aprire “Microsoft SQL Server Management Studio 18”
- Fare tasto destro sul DB, e poi Attività ==> Compatta ==> Database
 

Ci ho provato, ma la dimensione del file è diminuita di poco meno di 100 mega, quella del log niente

Grazie, Mario

[nomorelogic]

quando compatti devi compattare sia i dati (database e file) che il log

io uso questi script

Codice: [Seleziona]

USE [DATABASENAME]
GO
DBCC SHRINKFILE (N'DATABASENAME_log' , 0, TRUNCATEONLY)
GO

USE [DATABASENAME]
GO
DBCC SHRINKDATABASE(N'DATABASENAME' )
GO

USE [DATABASENAME]
GO
DBCC SHRINKFILE (N'DATABASENAME_file' , 0, TRUNCATEONLY)
GO

[bonmario]

Scusa, mi sono accorto adesso che in quello che ho scritto prima, mi sono dimenticato un pezzo ...

In effetti, avevo visto anche i comandi da lanciare tramite script, che poi si rifanno a quello che puoi impostare in Microsoft SQL Server Management Studio, ma c'è una cosa che non capisco, e di cui non ho trovato una spiegazione esaustiva: quel numero, dove tu hai messo 0, è spiegato qui:
https://learn.microsoft.com/it-it/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16#target_size

Quello che non capisco è questo: io al momento ho un DB di circa un giga e 600 mega.
Se lancio i comandi che hai postato, quindi con valore 0, credo che il file venga ridotto fino al massimo alla dimensione prevista da chi lo aveva creato, che credo sia un giga e mezzo. Ho capito bene?
Volendo potrei mettere quel parametro ad esempio a 100, ma non ho capito in quel caso cosa succederebbe ai miei dati.

Provo a spiegarmi:
- supponiamo che la definizione delle tabelle, ed i dati che ci sono dentro occupino in tutto 500 mega
- lancio i comandi, mettendo come numero 100. Cosa succede? Rischio di perdere dei dati perché il valore che ho messo è troppo piccolo?

Grazie, Mario

[bonmario]

Ok, ho scritto una cavolata: nel link che ho postato prima, c'è la frase che probabilmente spiega il mio dubbio, e che prima non avevo notato:

DBCC SHRINKFILE non compatta un file oltre le dimensioni dei dati archiviate necessarie. Ad esempio, se viene usato un file di dati da 10 MB a 7 MB, un'istruzione DBCC SHRINKFILE con un target_size di 6 riduce il file a solo 7 MB, non a 6 MB.

Ciao, Mario

[bonmario]

Niente da fare, al massimo lo riduce fino ad una dimensione di "1.776.222.208" bytes.
Oltre a quello non va ...

Vado a memoria: una decina di anni fa ho usato Firebird, e lì bastava fare un backup, creare il DB vuoto e poi ripristinare il backup sul DB vuoto per ottenere il ridimensionamento.
Funziona così anche col DB di Microsoft?

Grazie, Mario

[nomorelogic]

si con firebird risolvi con backup/restore

prova a guardare nelle proprietà dei file (vedi allegato)
se clicchi sul bottone con i 3 puntini, si apre il dettaglio
probabilmente la dimensione è troppo grande, puoi cambiarla manualmente

se la modifica viene accettata, rifai la compattazione

Edit:
con MsSql il backup/restore è un po' diverso
non solo non compatta, ma si porta dietro anche i log

[bonmario]

Niente da fare: mi fa fare la modifica, clicco su Ok e non da errori, ma se rientro, mi ritrovo 1694.
La cosa funziona invece sul file di log

A questo punto, credo che il problema sia quello che ho letto da qualche parte: chi ha creato il DB gli ha dato quella come dimensione minima, e sotto quel valore non ti fa scendere.

Grazie, Mario

[bonmario]

Credo di averci capito qualcosa ...

Ecco cosa ho fatto:
- Ho attivato il profiler dul mio DB
- Entrato in “Microsoft SQL Server Management Studio 18”
- Selezionato il mio DB
- Tasto destro
- Report ==> Report standard ==> Utilizzo disco per tabella
- Quando ha finito, sono tornato nel profiler ed ho reperito la query

Ho lanciato la query, ed ho sommato la colonna "reserved"


A questo punto, ho preso la tabella col valore massimo in quella colonna e l'ho droppata

Rifacendo tutto, ho poi provato a ridurre le dimensioni del DB come mi aveva detto prima nomorelogic, e me lo ha fatto fare.
Credo, ma questa è solo una mia ipotesi che comunque la dimensione del DB sia sempre circa il doppio rispetto alla somma della colonna "reserved"

Ciao, Mario

[nomorelogic]

si è possibile che l DB allochi più spazio fisico di quello che serve per motivi di prestazioni
ma questa cosa del reserved non la sapevo

nelle proprietà di cui parlavamo prima c'è anche come allocare nuovo spazio alla bisogna (in % o in MB)
forse anche quel parametro influisce, ma solo quando è il momento di ottenere nuovo spazio

[bonmario]

Mi sono appena accorto di avere il problema opposto su 2 DB che uso per fare prove: 8 mega di dati, e 120 di log 
Oggi vedo se riesco a sistemare !

Ciao, Mario

[nomorelogic]

se i log non ti servono (come quasi sempre ) puoi fare così:
1) compatta al massimo
2) vai su proprietà del DB e poi seleziona "opzioni"
sulla voce "Modello di recupero" facilmente trovi impostato "Con registrazione completa"
sostituisci con "con registrazione minima"
3) vai su proprietà / file e - per il log - togli l'espansione del file e metti le dimensioni massime

in questo modo dovresti bloccare i log ad una certa dimensione

Edit / NB:
se il DB è di tipo datawarehouse e normalmente lo vuoti completamente per ripopolarlo completamente ogni volta, non puoi evitare l'espansione automatica del log
questo perché quel tipo di transazioni sono veramente enormi e, se non c'è spazio per il log, fallisce tutto...

[bonmario]

Fatto, grazie !!!
Per tutti e 2, sono passato a 24 mega, di meno non mi fa fare, ma in ogni caso, è già un valore accettabile.

P.S. Sempre nelle opzioni, ho notato un "Compattazione automatica". L'ho impostato a True, e vediamo se fa il suo lavoro !!

Ciao, Mario