Cách khắc phục sự cố không gian do SQL Server

Vài lần SQL Server có thể là lý do cho vấn đề không gian trên đĩa. Trong bài viết này, chúng ta sẽ xem nguyên nhân gốc rễ của vấn đề này là gì và cách chúng ta có thể khắc phục nó.

trên màn hình SQL Server cần không gian.

Nhiều lần SQL Server sẽ cần không gian đĩa. Điều này có thể là do dữ liệu ngày càng tăng bên trong cơ sở dữ liệu của bạn hoặc các tệp nhật ký chưa được thu nhỏ hoặc các tệp sao lưu chưa được xóa hoặc các tệp cơ sở dữ liệu không mong muốn chưa được xóa. Dù lý do là gì, trên một SQL Server, không gian trên đĩa rất quan trọng đối với các giao dịch cơ sở dữ liệu.

Tác vụ dọn dẹp không hoạt động

Nếu bạn đang sử dụng các gói bảo trì riêng của SQL để sao lưu SQL server cơ sở dữ liệu, có thể có khả năng mô-đun dọn dẹp trong các kế hoạch bảo trì đó không thực hiện nhiệm vụ của chúng. Khi đĩa hết dung lượng, bạn sẽ nhận ra rằng các tệp sao lưu cũ không được dọn dẹp đúng cách. Bạn sẽ đưa mô-đun dọn dẹp vào kế hoạch bảo trì. Bất chấp tất cả những điều này, bạn có thắc mắc tại sao nó không hoạt động không?

Kiểm tra xem bạn đã đề cập đúng thư mục để xóa tệp sao lưu chưa, kiểm tra xem bạn đã đề cập đúng phần mở rộng tệp để xóa tệp sao lưu chưa, hãy thử với dấu chấm “.” và không có dấu chấm trong phần mở rộng tệp.

Tệp nhật ký lớn hơn tệp cơ sở dữ liệu

Nguyên nhân phổ biến nhất gây ra vấn đề về không gian trên SQL Server Đó là các tệp nhật ký không được giám sát và các tệp tempdb. Mặc dù tempdb sẽ được thu nhỏ về kích thước ban đầu mỗi khi Dịch vụ SQL khởi động lại, nhưng việc theo dõi sự tăng trưởng của tempdb và thu nhỏ nó khi sắp chiếm hết dung lượng ổ đĩa là một việc làm tốt. Tương tự như tempdb, các tệp nhật ký cũng cần được kiểm tra thường xuyên. Hãy đảm bảo bạn đã sao lưu nhật ký để luôn giữ kích thước tệp nhật ký ở mức tối thiểu.

Tệp cơ sở dữ liệu không sử dụng

Có thể có nhiều tệp cơ sở dữ liệu chưa được sử dụng và chưa được đính kèm trên đĩa của bạn và gây lãng phí dung lượng. Thực thi tập lệnh trên của bạn SQL Server dụ để xác định các tệp như vậy cùng với đường dẫn của chúng. Sau khi phân tích nhanh, nếu bạn vẫn chắc chắn rằng những tệp đó không cần thiết nữa, hãy xóa chúng và tiết kiệm dung lượng.

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 cơ sở dữ liệu tham nhũng

Ngoài việc giám sát và duy trì dung lượng ổ đĩa của bạn, hãy theo dõi tình trạng ổ đĩa của bạn. Ổ đĩa không lành mạnh có thể làm hỏng ổ cứng của bạn SQL Server cơ sở dữ liệu. Nếu điều này xảy ra, vui lòng sử dụng công cụ khôi phục cơ sở dữ liệu như DataNumen SQL Recovery đến sửa chữa bị hỏng SQL Server.

Giới thiệu tác giả:

Neil Varley là một chuyên gia phục hồi dữ liệu trong DataNumen, Inc., công ty hàng đầu thế giới về công nghệ khôi phục dữ liệu, bao gồm sửa lỗi email Outlook và các sản phẩm phần mềm phục hồi excel. Để biết thêm thông tin, hãy truy cập www.datanumennăm