In questo articolo, discuteremo la proprietà del database SQL Server Auto Shrink, che permette a SQL Server di ridurre automaticamente i file del database quando il valore è configurato come True nell’opzione del database. L’operazione di restringimento automatico è fatta dalla funzione di restringimento automatico del database del server, che è il corso principale di questo articolo.
Per evitare l’attività di restringimento dei file di dati/log ogni volta che la dimensione del file supera lo spazio libero nel file di dati, il DBA (Amministratore del database) deve eseguire il backup dei file di log ad un intervallo regolare. Fare il backup dell’intero database non è una buona idea; si deve costruire o impostare anche il log delle transazioni. I log delle transazioni aumenteranno di numero fino ad occupare tutto lo spazio disponibile sul disco se non si fa il backup di essi. Se si fa il backup del database, si libera lo spazio libero per essere riutilizzato di nuovo. L’amministratore del database deve programmare un backup del log delle transazioni per ridurre la dimensione dei file di log a una dimensione equa.
La funzione di riduzione automatica di SQL Server è disabilitata per impostazione predefinita sui database di istanza di SQL Server. Nello scenario in cui avete un certo numero di database relativamente piccoli che crescono ad una dimensione maggiore sia per l’inserimento di nuove tuple o per un gran numero di spazi vuoti creati dalla cancellazione di diverse tuple, SQL Server Auto Shrink diventa piuttosto utile in questa situazione. Inoltre, non dovete preoccuparvi della frammentazione delle dimensioni dei file di database.
Mentre si restringe il database, bisogna considerare le operazioni di restringimento sia sui database critici che su quelli relativamente più grandi come parte della manutenzione. Inoltre, questo dovrebbe evitare di eseguire la funzione di restringimento manuale; le conseguenze di fare questo sono che non si sarà mai a conoscenza dei problemi riguardanti la richiesta nuova o esistente. Tuttavia, la contrazione dei file di transazione è meglio della contrazione dei file di dati.
Come attivare e disattivare il restringimento automatico di SQL Server per il database?
Gli utenti possono attivare e disattivare l’opzione di restringimento automatico del database usando SSMS e T-SQL in entrambi i modi.
Attivare il restringimento automatico del database usando SSMS:
Gli utenti possono abilitare o disabilitare questa opzione dalle proprietà del database, con un tag di Auto Shrink. Qui, True nel drop-down abiliterà questa opzione per il vostro database.
Enable database auto shrink using T-SQL:
Gli utenti possono eseguire le seguenti istruzioni T-SQL per abilitare o disabilitare il database shrink. Abbiamo usato il database AdventureWorks per l’istruzione T-SQL di cui sopra.
|
1
2
3
4
5
6
|
–Attiva la riduzione automatica per il database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
–Disabilita il restringimento automatico per il database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Effetti del restringimento del database sulla performance della query
Possono esserci effetti negativi sulla performance della query se si attiva l’opzione di restringimento automatico del database e le impostazioni di crescita automatica insieme al database. Possiamo mantenere un po’ di spazio per la crescita dei file di dati e anche evitare alcuni frequenti eventi di auto-crescita impostando la dimensione del database a un valore ottimale o per lo più ogni database ha alcuni parametri con funzioni di auto-crescita abilitate.
Per tali database, le funzioni di auto-restringimento devono essere attivate da noi quando il database è più piccolo, uno e nessuna operazione CRUD, così può rendere possibile il restringimento dei file di dati e recuperare lo spazio libero che abbiamo fornito di proposito per tenere lontano i nostri database dagli eventi di auto-crescita. Lo spazio libero sarà rilasciato automaticamente nei file di dati e nei file di log periodicamente in priorità sequenziale round-robin dalla funzione Auto shrink se più database hanno attivato questa funzione.
Per i database di grandi dimensioni, la crescita automatica, e in seguito, la contrazione automatica sarà eseguita che porterà a problemi di prestazioni a causa della frammentazione a livello di sistema. Per riassumere tutto questo, per qualsiasi database, la contrazione automatica non dovrebbe essere attivata per i seguenti motivi:
- Eseguire gli algoritmi di auto-restringimento di SQL Server senza alcuno scopo sarà sicuramente uno spreco di risorse in quantità massiccia
- Sia che si esegua il restringimento automatico di SQL Server o quello manuale, ovviamente causerà la frammentazione dell’indice e questo alla fine esegue anche il restringimento dei file di dati
- Se il server sta anche forzando i limiti del sottosistema IO, eseguire il restringimento può spingerlo oltre, con il risultato di lunghe code su disco e possibilmente timeout IO, questo consumerà le risorse IO e CPU del server in quantità massiccia
- Le prestazioni del sistema saranno ritardate dalla frammentazione del file system a livello di disco, che è di nuovo l’effetto diretto di fare spesso la contrazione e la crescita dei file di dati
Per capire meglio gli eventi di crescita e contrazione in SQL Server, leggi questo articolo, Ottieni dettagli sugli eventi di crescita e contrazione del database SQL Server.
Per il database critico, l’operazione di restringimento manuale potrebbe essere eseguita a livello di file di database dall’esperto. L’attività manuale di restringimento del file può essere fatta quando l’operazione di cancellazione viene eseguita, e dopo, lo spazio è stato recuperato. Dobbiamo ricostruire gli indici frammentati dopo l’esecuzione dell’operazione di restringimento perché l’operazione di restringimento può portare alla frammentazione dell’indice. La percentuale di frammentazione dell’indice potrebbe essere controllata dall’utente con l’uso di dichiarazioni T-SQL di DMV. Tuttavia, il restringimento del file di log dovrebbe essere fatto manualmente come e quando richiesto e non dovrebbe essere parte dell’attività di manutenzione regolare.
Per monitorare l’utilizzo del disco su base regolare, il rapporto di utilizzo del disco potrebbe essere analizzato dall’utente prima di eseguire l’operazione di restringimento manuale utilizzando SSMS, che ti dà una visione dei dati e delle informazioni di spazio di log per un database nel display. Se l’utente vuole ottenere un cruscotto per il calcolo dello spazio riservato e dello spazio libero per i file del database, allora Disk Report diventa molto utile. Tuttavia, il rapporto disco abita le informazioni utilizzando i DMV di SQL Server. Il rapporto sul disco è disponibile nella seguente directory usando SSMS.
Database >> Reports >> Standard Reports >> Disk Usage
Qui, abbiamo un Disk Report per il database. Le statistiche in tempo reale per i file di dati e i file di log possono essere viste dall’utente. Questo rapporto incorpora informazioni primarie di spazio totale riservato, spazio riservato per i file di dati, spazio riservato per il log delle transazioni e spazio riservato per l’OLTP in memoria.
Ridurre automaticamente il database utilizza SQL Server Jobs
Tali script potrebbero essere eseguiti programmando un lavoro da parte dell’utente per eseguire l’operazione di riduzione del database con attività di scheduler. Come primo passo nello script, trovare lo spazio libero nel file del database e poi restringere quel file se i criteri definiti per lo spazio libero vengono abbinati. Lo spazio libero dovrebbe essere calcolato con l’aiuto di SQL Server DMV’s.
Fondamentalmente, il monitoraggio del file di database viene eseguito dall’amministratore del database, che a sua volta interviene sul database per monitorare la dimensione del file di database. Se il limite predefinito viene superato da qualsiasi file, allora hanno bisogno di eseguire tali passaggi. Quindi questa attività può essere automatizzata utilizzando il lavoro di SQL Server per eseguire su base giornaliera, settimanale o mensile in un momento predefinito.
La pratica migliore è quella di pianificare questo lavoro sul file di log del database solo e monitorare lo spazio libero del file di dati manualmente. Perché può influenzare anche le prestazioni delle query. SQL Server DMV restituirà il file di dati e il file di log nello stesso set di risultati, quindi il tipo di file deve essere biforcato nella dichiarazione T-SQL di SQL Server DMV. Prima di ridurre il file, l’utente può applicare la logica della query sullo spazio totale e lo spazio libero del file di log. Se i criteri di calcolo sono abbinati alla proprietà del file, allora il file si ridurrà con la dimensione del file di destinazione.
Controlla lo spazio libero per i file di database:
Se tali criteri corrispondono ai file di database, per esempio, se lo spazio libero è maggiore di (n) MB/GB, se n(%) spazio libero confronta lo spazio totale, e molti altri. Quando lo spazio libero non è disponibile per il file di log e la dimensione del file è vicina al valore del parametro della dimensione massima del file, allora l’amministratore del database ha bisogno di esplorare il log delle transazioni.
Riduci il file del database:
|
1
|
DBCC SHRINKFILE(nome_file, 5120);
|
Qui, 5120 è una dimensione target del file in MB. Quindi, il file avrà una dimensione di 5120 MB. Impostare la query T-SQL con i criteri richiesti nella fase di lavoro di SQL Server e pianificarla in ore on-off.
L’opzione di riduzione automatica di SQL Server non può essere abilitata per tutti i database. In particolare, aiuta per i database più piccoli, che eseguono meno operazioni CRUD comparativamente.
Conclusione
In questo articolo, abbiamo discusso la proprietà Auto Shrink database in SQL Server per ridurre i file di dati e di log e rimuovere lo spazio inutilizzato. Il restringimento di un database è un’operazione costosa e dovrebbe essere usata con attenzione.
- Autore
- Postate recenti
Vedi tutti i post di Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Come automatizzare il partizionamento delle tabelle in SQL Server – 7 luglio 2020
- Configurare SQL Server Always On Availability Groups su AWS EC2 – 6 luglio 2020