Cum să remediați problemele de spațiu cauzate de SQL Server

De cateva ori SQL Server ar putea fi motivul problemei de spațiu pe discuri. În acest articol, vom vedea care sunt cauzele principale ale acestei probleme și cum o putem remedia.

Ta SQL Server are nevoie de spatiu.

Probleme de spațiu cauzate de SQLMulte ori SQL Server va avea nevoie de spațiu pe disc. Acest lucru se poate datora creșterii datelor în baza de date sau a fișierelor jurnal necomprimate sau a fișierelor de rezervă neșterse sau a fișierelor de bază de date neșterse nedorite. Oricare ar fi motivul, pe a SQL Server, spațiul de pe disc este foarte important pentru tranzacțiile cu bazele de date.

Sarcina de curățare nu funcționează

Dacă utilizați planurile de întreținere native ale SQL pentru a face backup SQL server baze de date, ar putea exista șanse ca modulul de curățare din acele planuri de întreținere să nu-și îndeplinească sarcina. Când discul rămâne fără spațiu, veți realiza că fișierele vechi de rezervă nu sunt curățate corespunzător. Ați fi inclus modulul de curățare în planul de întreținere. În ciuda tuturor acestor lucruri, vă întrebați de ce nu a funcționat?

Verificați dacă ați menționat folderul corect pentru a șterge fișierele de rezervă, verificați dacă ați menționat extensia de fișier corectă pentru a șterge fișierele de rezervă, încercați cu punctul „.” și fără un punct în extensia fișierului.

Fișierele jurnal sunt uriașe decât fișierele de bază de date

SQL Server Baza de dateMost cauza comună pentru probleme de spațiu pe SQL Server sunt fișierele jurnal nesupravegheate și fișierele tempdb. Deși tempdb se va micșora la dimensiunea originală ori de câte ori serviciul SQL restarEste o practică bună să monitorizați creșterea tempdb și să o micșorați atunci când este pe cale să consume întreg spațiu pe disc. Similar cu tempdb, fișierele jurnal ar trebui să fie întotdeauna verificate. Asigurați-vă că aveți un backup pentru jurnal pentru a păstra întotdeauna fișierele jurnal la dimensiunea minimă.

Fișiere de baze de date neutilizate

S-ar putea să existe multe fișiere de bază de date neutilizate și neatașate pe disc și să piardă spațiu. Executați scriptul pe dvs SQL Server instanță pentru a identifica astfel de fișiere împreună cu calea lor. După o analiză rapidă, dacă încă ești sigur că acele fișiere nu mai sunt necesare, șterge-le și economisește spațiu.

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 Corupția bazei de date

Pe lângă monitorizarea și menținerea spațiului pe disc, monitorizați și starea discului. Discul nesănătos vă poate deteriora SQL Server baze de date. Dacă se întâmplă acest lucru, vă rugăm să utilizați instrumentul de recuperare a bazei de date, cum ar fi DataNumen SQL Recovery la repara corupt SQL Server.

Introducerea autorului:

Neil Varley este un expert în recuperarea datelor DataNumen, Inc., care este lider mondial în tehnologiile de recuperare a datelor, inclusiv reparați corupția e-mailului Outlook și produse software de recuperare Excel. Pentru mai multe informații vizitați www.datanumen.com

Lasă un comentariu

Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate *