Surveiller les fichiers temporaires Sql_temp via Powershell et Requête SQL

Suivre l’activité de votre serveur SQL est nécessaire et en particulier les fichiers temporaires qui peuvent en cas de surcharges ralentir le traitement des requêtes. Les fichiers « tempdb.mdf » ainsi que « templog.ldf » peuvent atteindre une taille de plusieurs Giga octets. Si les espaces de stockage dédiés au SQL_Temp sont restreints, le risque de dysfonctionnement de Reporting Services sera très fort voire inaccessible. Si vos serveurs sont monitorés par un outil de supervision de type Nagios, vous pouvez quand même utiliser ces solutions par confort et sécurité.

Il est donc important de suivre l’évolution de ces 2 fichiers. Nous proposons 2 solutions :

Surveillez les fichiers temporaires via une requête SQL à exécuter dans l’application « Microsoft SQL Management Studio » :

SELECT file_id, file_guid, type, type_desc, data_space_id, name, physical_name, state, state_desc, size, max_size, growth, is_media_read_only, is_read_only, is_sparse, is_percent_growth,
is_name_reserved, create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid, differential_base_time, redo_start_lsn, redo_start_fork_guid, redo_target_lsn,
redo_target_fork_guid, backup_lsn
FROM tempdb.sys.database_files

Cette requête vous permet de visualiser l’état de votre SQL_Temp :

Exécutez un script Powershell avec une tâche planifiée : ce script permet :
- De journaliser les espaces disques dans un fichier CSV
- Suppression du log après 10 jours.
- Horodatage du fichier « log »
- Spécifier la liste des serveurs a vérifier dans un fichier txt
- D’envoyer un mail uniquement lorsque l’espace disque disponible est inférieur à 10%

Détail du script :

# ———————————————————————————————–#
# Fonction script : Suivi espace disque du templog pour SQL et envoi un mail si < 10% d’espace #
# #
# Auteur : Yvan MARGUERITE #
# Date: 28/02/2018 #
# #
# ———————————————————————————————- #

### Début du script ###

#Suppression du log après 10 jours
$OldReports = (Get-Date).AddDays(-10)

#Répertoire du log et suppression (remplacez %serveur_de_partage% par votre lien serveur où sera stocké le script)
Get-ChildItem \\%serveur_de_partage%\_CheckSpaceDisk\*.* | `
Where-Object { $_.LastWriteTime -le $OldReports} | `
Remove-Item -Recurse -Force -ErrorAction SilentlyContinue

#Création de la variable pour la date du log
$LogDate = get-date -f yyyyMMddhhmm

#Fichier contenant la liste des serveurs a vérifier (remplacez %serveur_de_partage% par votre lien serveur où sera stocké le fichier txt contenant la liste des serveurs)
$File = Get-Content -Path \\%serveur_de_partage%\_CheckSpaceDisk\Servers.txt

#Variable des serveurs dans le fichier txt
$DiskReport = ForEach ($Servernames in ($File))

{Get-WmiObject win32_logicaldisk `
-ComputerName $Servernames -Filter « Drivetype=3 » `
-ErrorAction SilentlyContinue |
Where-Object { ($_.freespace/$_.size) -le ‘0.1’}
}

#Création du log (remplacez %serveur_de_partage% par votre lien serveur où sera stocké le log CSV)
$DiskReport |

Select-Object @{Label = « Server Name »;Expression = {$_.SystemName}},
@{Label = « Drive Letter »;Expression = {$_.DeviceID}},
@{Label = « Total Capacity (GB) »;Expression = {« {0:N1} » -f( $_.Size / 1gb)}},
@{Label = « Free Space (GB) »;Expression = {« {0:N1} » -f( $_.Freespace / 1gb ) }},
@{Label = ‘Free Space (%)’; Expression = {« {0:P0} » -f ($_.freespace/$_.size)}} |
#Export du log en CSV
Export-Csv -path « %serveur_de_partage%\CheckSpaceDisk\logDiskSQL_$logDate.csv » -NoTypeInformation

#Envoi du log par mail (remplacez %@Mail% par les adresses Emails correspondantes)
$messageParameters = @{
Subject = « Suivi espace disque SQL »
Body = « Ce message indique le suivi de l’espace disque du serveur »
From = « %@mail% »
To = « %@Mail% »
Attachments = (Get-ChildItem \\%serveur_de_partage%\sccm$\_CheckSpaceDisk\*.* | sort LastWriteTime | select -last 1)
SmtpServer = « %serveurSMTP% »
}
Send-MailMessage @messageParameters -BodyAsHtml

### Fin du script ###

Ensuite, lorsque vous avez sauvegardé le script, vous pouvez créér une tâche planifiée qui exécutera suivant votre infrastructure ( Quotidien, hebdomadaire, Mensuel ) :

Pour créer votre tâche planifiée, connectez-vous sur le serveur où sera exécuté le script,

Puis dans le Dashboard de « Server Manager« , cliquez sur « Tools » et « Task Scheduler » :