Plusieurs fois SQL Server pourrait être la raison du problème d'espace sur les disques. Dans cet article, nous verrons quelles sont les causes profondes de ce problème et comment nous pouvons le résoudre.
Votre SQL Server a besoin d'espace.
À plusieurs reprises SQL Server aura besoin d'espace disque. Cela peut être dû à la croissance des données dans votre base de données ou à des fichiers journaux non réduits ou à des fichiers de sauvegarde non supprimés ou à des fichiers de base de données indésirables non supprimés. Quelle que soit la raison, sur un SQL Server, l'espace sur le disque est très important pour les transactions de base de données.
La tâche de nettoyage ne fonctionne pas
Si vous utilisez les plans de maintenance natifs de SQL pour sauvegarder votre SQL server bases de données, il est possible que le module de nettoyage de ces plans de maintenance ne fasse pas sa tâche. Lorsque le disque manque d'espace, vous vous rendrez compte que les anciens fichiers de sauvegarde ne sont pas correctement nettoyés. Vous auriez inclus le module de nettoyage dans le plan de maintenance. Malgré tout cela, vous vous demandez pourquoi cela ne fonctionnait pas ?
Vérifiez si vous avez mentionné le bon dossier pour supprimer les fichiers de sauvegarde, vérifiez si vous avez mentionné la bonne extension de fichier pour supprimer les fichiers de sauvegarde, essayez avec le point "." et sans point dans l'extension de fichier.
Les fichiers journaux sont volumineux que les fichiers de base de données
Cause la plus fréquente de problème d'espace sur SQL Server Il s'agit des fichiers journaux non surveillés et des fichiers tempdb. Bien que tempdb retrouve sa taille initiale à chaque redémarrage du service SQL, il est recommandé de surveiller sa croissance et de la réduire lorsqu'elle est sur le point de saturer l'espace disque. De même, les fichiers journaux doivent être surveillés en permanence. Assurez-vous de disposer d'une sauvegarde des journaux afin de maintenir leur taille au minimum.
Fichiers de base de données inutilisés
De nombreux fichiers de base de données inutilisés et non attachés peuvent se trouver sur votre disque et gaspiller de l'espace. Exécutez le script sur votre SQL Server instance pour identifier ces fichiers avec leur chemin. Après une analyse rapide, si vous êtes toujours sûr que ces fichiers ne sont plus nécessaires, supprimez-les et économisez de l'espace.
DECLARE @dpth NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData'
,@dpth OUTPUT
DECLARE @lpth NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultLog'
,@lpth OUTPUT
DECLARE @bk NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'BackupDirectory'
,@bk OUTPUT
DECLARE @md NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg0'
,@md OUTPUT
SELECT @md = substring(@md, 3, 255)
SELECT @md = substring(@md, 1, len(@md) - charindex('\', reverse(@md)))
DECLARE @ml NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg2'
,@ml OUTPUT
SELECT @ml = substring(@ml, 3, 255)
SELECT @ml = substring(@ml, 1, len(@ml) - charindex('\', reverse(@ml)))
SET @dpth = isnull(@dpth, @md)
SET @lpth = isnull(@lpth, @ml)
PRINT @dpth
PRINT @lpth
EXEC sp_configure 'show advanced'
,1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell'
,1
RECONFIGURE
IF object_id('tempdb.dbo.#table1') IS NOT NULL
DROP TABLE #table1
CREATE TABLE #table1 (
[filename] VARCHAR(2000)
,depth INT
,isFile INT
)
SET @dpth = 'DIR ' + @dpth + '\*.mdf /b /s'
SET @lpth = 'DIR ' + @lpth + '\*.ldf /b /s'
INSERT INTO #table1
EXEC xp_DirTree @dpth
,1
,1
INSERT INTO #table1
EXEC xp_DirTree @lpth
,1
,1
DELETE
FROM #table1
WHERE isFile <> 1
UPDATE #table1
SET filename = rtrim(filename)
CREATE TABLE t_list (
filepath VARCHAR(2000)
,sizeinmb DECIMAL(18, 2)
)
INSERT INTO t_list (filepath)
SELECT otable.filename AS orphaned_files
FROM #table1 otable
LEFT OUTER JOIN master.dbo.sysaltfiles db ON rtrim(db.filename) = otable.filename
WHERE db.dbid IS NULL
ORDER BY 1
DECLARE @sizeingb AS DECIMAL(18, 2)
DECLARE @filepath AS VARCHAR(2000)
DECLARE db_cursor CURSOR
FOR
SELECT filepath
FROM t_list
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @filepath
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE t_temp (c1 VARCHAR(2000))
DECLARE @cmd AS VARCHAR(3000)
SET @cmd = 'dir ' + @filepath
PRINT @cmd
INSERT INTO t_temp
EXEC master.dbo.xp_cmdshell @cmd
DELETE
FROM t_temp
WHERE c1 NOT LIKE '%1 File(s)%bytes'
DECLARE @size AS DECIMAL(18, 2)
SET @size = (
SELECT TOP 1 replace(replace(replace(c1, ' 1 File(s) ', ''), ',', ''), ' bytes', '')
FROM t_temp
WHERE c1 IS NOT NULL
)
SET @size = cast((@size / (1024 * 1024)) AS DECIMAL(18, 2))
DROP TABLE t_temp
UPDATE t_list
SET sizeinmb = @size
WHERE filepath = @filepath
FETCH NEXT
FROM db_cursor
INTO @filepath
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT *
FROM t_list
DROP TABLE t_list
EXEC sp_configure 'show advanced'
,1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell'
,0
RECONFIGURE
SQL Server Corruption de la base de données
Outre la surveillance et la maintenance de votre espace disque, surveillez également la santé de votre disque. Un disque défectueux peut corrompre votre SQL Server bases de données. Si cela se produit, veuillez utiliser un outil de récupération de base de données comme DataNumen SQL Recovery à réparer corrompu SQL Server.
Introduction de l'auteur:
Neil Varley est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparer la corruption des e-mails Outlook et des produits logiciels de récupération Excel. Pour plus d'informations, visitez www.datanumen.com