Powered by Blogger.

Cách thu hẹp cơ sở dữ liệu tempdb trong SQL Server

Bài viết này thảo luận ba phương pháp mà bạn có thể sử dụng để thu hẹp cơ sở dữ liệu tempdb đến kích thước nhỏ hơn kích thước được cấu hình sau cùng. Phương pháp thứ nhất cho phép bạn kiểm soát hoàn toàn kích thước của các tệp tempdb nhưng yêu cầu bạn phải khởi động lại SQL Server. Phương pháp thứ hai thu hẹp toàn bộ tempdb, có một số hạn chế, có thể bao gồm việc khởi động lại SQL Server. Phương pháp thứ ba cho phép bạn thu hẹp các tệp rieng lẻ trong tempdb. Hai phương pháp sau cùng yêu cầu rằng không được có hoạt động nào xảy ra trong cơ sở dữ liệu tempdb trong quá trình hoạt động thu hẹp.

Chú ý Nếu bạn đang sử dụng SQL Server 2005, bạn cũng có thể áp dụng những phương pháp này. Tuy nhiên, bạn nên sử dụng SQL Server Management Studio thay vì Enterprise Manager và Query Analyzer để tiến hành những hoạt động này. Chú ý thêm rằng SQL Server management studio trong phiên bản 2005 không cho biết kích thước chính xác của các tệp tempdb sau hoạt động thu hẹp. Giá trị 'Không gian được phân bổ hiện tại’ luôn được lấy từ sys.master_files DMV và giá trị này không được cập nhật sau khi xảy ra hoạt động thu hẹp cho cơ sở dữ liệu tempdb. Để tìm kích cỡ chính xác của các tệp tempdb sau hoạt động thu hẹp, hãy thực thi lệnh sau trong management studio:
use tempdb
chọn (size*8) làm FileSizeKB từ sys.database_files


Chú ý SQL Server 2008 không bị ảnh hưởng bởi sự cố này (GUI hiển thị kích thước không chính xác.)

Quay lại đầu trang

Thông tin Tempdb


Tempdb là không gian làm việc tạm thời. Cùng với các ứng dụng khác, SQL Server sử dụng tempdb cho:

  • Lưu trữ các bảng tạm thời được tạo rõ ràng.

  • Các bảng làm việc lưu gữ kết quả trung gian được tạo ra trong quá trình xử lý và sắp xếp truy vấn.

  • Con trỏ tĩnh được cụ thể hoá.


SQL Server ghi lại đủ thông tin trong nhật ký giao dịch tempdb để quay lại một giao dịch nhưng không thực hiện lại giao dịch trong quá trình truy vấn cơ sở dữ liệu. Tính năng này làm tăng hiệu suất của câu lệnh INSERT trong tempdb. Ngoài ra, không cần ghi nhật ký thông tin để thực hiện lại bất kỳ giao dịch nào vì tempdb được tạo lại mỗi lần bạn khởi động lại SQL Server; do đó, không có bất kỳ giao dịch nào để chuyển đến hoặc quay lại. Khi SQL Server khởi động, tempdb được tạo lại bằng cách sử dụng bản sao của cơ sở dữ liệu mẫu và được thiết lập lại về kích thước được cấu hình sau cùng.

Theo mặc định, cơ sở dữ liệu tempdb được cấu hình để tự phát triển khi cần; do đó, cơ sở dữ liệu này có thể phát triển theo thời gian đến kích thước lớn hơn mong muốn. Việc khởi động đơn giản SQL Server thiết lập lại kích thước của tempdb về kích thước được cấu hình sau cùng. Kích thước được cấu hình là kích thước rõ ràng sau cùng được thiết lập bằng hoạt động thay đổi kích thước tệp như ALTER DATABASE với tuỳ chọn MODIFY FILE hoặc câu lệnh DBCC SHRINKFILE. Bài viết này trình bày ba phương pháp mà bạn có thể sử dụng để thu hẹp tempdb đến kích thước nhỏ hơn kích thước được cấu hình.


Quay lại đầu trang

Phương pháp 1 để Thu hẹp Tempdb


Phương pháp này yêu cầu bạn khởi động lại SQL Server.

  1. Dừng SQL Server. Mở dấu nhắc lệnh, rồi khởi động SQL Server bằng cách nhập lệnh sau:

    sqlservr -c -f

    Tham số -c và -f khiến SQL Server khởi động trong chế độ cấu hình tối thiểu với kích thước tempdb bằng 1 MB cho tệp dữ liệu và 0,5 MB cho tệp nhật ký.

    CHÚ Ý: Nếu bạn sử dụng SQL Server có tên như ví dụ, bạn phải thay đổi thành thư mục phù hợp (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) và sử dụng khoá chuyển đổi -s (-s%instance_name%).

  2. Kết nối đến SQL Server bằng Query Analyzer, rồi chạy lệnh Transact-SQL sau:
     ALTER DATABASE tempdb MODIFY FILE
    (NAME = 'tempdev', SIZE = target_size_in_MB)
    --Kích thước mục tiêu mong muốn cho tệp dữ liệu

    ALTER DATABASE tempdb MODIFY FILE
    (NAME = 'templog', SIZE = target_size_in_MB)
    --Kích thước mục tiêu mong muốn cho tệp nhật ký


  3. Dừng SQL Server bằng cách nhấn Ctrl-C tại cửa sổ dấu nhắc lệnh, khởi động lại SQL Server như một dịch vụ, rồi xác thực kích thước của các tệp Tempdb.mdf và Templog.ldf.


Một hạn chế của phương pháp này là chỉ hoạt động trên các tệp logic tempdb mặc định, tempdev và templog. Nếu các tệp bổ sung được thêm vào tempdb, bạn có thể thu hẹp chúng sau khi khởi động lại SQL Server như một dịch vụ. Tất cả các tệp tempdb đều được tạo lại trong khi khởi động; do đó, các tệp đều trống và có thể xoá được. Để xoá các tệp bổ sung trong tempdb, hãy sử dụng lệnh ALTER DATABASE với tuỳ chọn REMOVE FILE.

Quay lại đầu trang

Phương pháp 2 để Thu hẹp Tempdb


Sử dụng lệnh DBCC SHRINKDATABASE để thu hẹp toàn bộ cơ sở dữ liệu tempdb. DBCC SHRINKDATABASE nhận được tham số target_percent, là phần trăm không gian trống mong muốn còn lại trong tệp cơ sở dữ liệu sau khi cơ sở dữ liệu được thu hẹp. Nếu bạn sử dụng DBCC SHRINKDATABASE, bạn có thể phải khởi động lại SQL Server.

QUAN TRỌNG: Nếu bạn chạy DBCC SHRINKDATABASE, không có hoạt động nào khác có thể xảy ra với cơ sở dữ liệu tempdb. Để đảm bảo rằng các quá trình khác không thể sử dụng tempdb trong khi chạy DBCC SHRINKDATABASE, bạn phải khởi động SQL Server trong chế độ một người dùng. Để biết thêm thông tin, hãy tham khảo phần Ảnh hưởng của việc Thực thi DBCC SHRINKDATABASE hoặc DBCCSHRINKFILE Trong khi Tempdb Đang được Sử dụng trong bài viết này.

  1. Xác định không gian đang được sử dụng trong tempdb bằng cách sử dụng quy trình được lưu trữ sp_spaceused. Sau đó, tính toán phần trăm không gian trống còn lại để sử dụng làm tham số cho DBCC SHRINKDATABASE; việc tính toán này dựa trên kích thước cơ sở dữ liệu mong muốn.

    Chú ý Trong một số trường hợp, bạn có thể phải thực thi sp_spaceused @updateusage=true để tính toán lại không gian được sử dụng và để có được báo cáo cập nhật. Tham khảo Sách Trực tuyến về SQL Server để biết thêm thông tin về quy trình được lưu trữ sp_spaceused.

    Xem xét ví dụ này:

    Giả sử rằng tempdb có hai tệp, tệp dữ liệu chính (Tempdb.mdf), có kích thước 100 MB và tệp nhật ký (Tempdb.ldf), có kích thước 30 MB. Giả sử rằng sp_spaceused báo cáo rằng tệp dữ liệu chính chứa 60 MB dữ liệu. Đồng thời, giả sử rằng bạn muốn thu hẹp tệp dữ liệu chính xuống còn 80 MB. Tính toán phần trăm không gian trống mong muốn còn lại sau khi thu hẹp, 80 MB - 60 MB = 20 MB. Bây giờ, chia 20 MB cho 80 MB = 25% và đây là target_percent của bạn. Tệp nhật ký giao dịch được thu hẹp tương ứng, để lại 25% hoặc 20 MB không gian trống sau khi cơ sở dữ liệu được thu hẹp.

  2. Kết nối đến SQL Server bằng Query Analyzer, rồi chạy lệnh Transact-SQL sau:
     dbcc shrinkdatabase (tempdb, 'target percent') 
    -- Lệnh này thu hẹp toàn bộ cơ sở dữ liệu tempdb



Có các hạn chế đối với việc sử dụng lệnh DBCC SHRINKDATABASE trên cơ sở dữ liệu tempdb. Kích thước mục tiêu cho tệp dữ liệu và tệp nhật ký không thể nhỏ hơn kích thước được chỉ định khi cơ sở dữ liệu được tạo ra hoặc kích thước sau cùng được thiết lập rõ ràng bằng hoạt động thay đổi kích thước tệp, chẳng hạn như ALTER DATABASE với tuỳ chọn MODIFY FILE hoặc lệnh DBCC SHRINKFILE. Một hạn chế khác của DBCC SHRINKDATABASE là việc tính toán tham số target_percentage và sự phụ thuộc vào không gian hiện tại được sử dụng.



Quay lại đầu trang

Phương pháp 3 để Thu hẹp Tempdb


Sử dụng lệnh DBCC SHRINKFILE để thu hẹp các tệp tempdb riêng lẻ. DBCC SHRINKFILE cung cấp tính linh hoạt cao hơn DBCC SHRINKDATABASE vì bạn có thể sử dụng trên một tệp cơ sở dữ liệu duy nhất mà không ảnh hưởng đến các tệp khác thuộc cùng cơ sở dữ liệu đó. DBCC SHRINKFILE nhận được tham số kích thước mục tiêu, là kích thước cuối cùng mong muốn cho tệp cơ sở dữ liệu.

QUAN TRỌNG: Bạn phải chạy lệnh DBCC SHRINKFILE trong khi không có hoạt động nào xảy ra trong cơ sở dữ liệu tempdb. Để đảm bảo rằng các quá trình khác không thể sử dụng tempdb trong khi DBCC SHRINKFILE thực thi, bạn phải khởi động lại SQL Server trong chế độ một người dùng. Để biết thêm thông tin về DBCC SHRINKFILE, hãy xem phần Ảnh hưởng của việc Thực thi DBCC SHRINKDATABASE hoặc DBCCSHRINKFILE Trong khi Tempdb Đang được Sử dụng trong bài viết này.

  1. Xác định kích thước mong muốn cho tệp dữ liệu chính (tempdb.mdf), tệp nhật ký (templog.ldf), và/hoặc các tệp bổ sung được thêm vào tempdb. Đảm bảo rằng không gian được sử dụng trong các tệp nhỏ hơn hoặc bằng kích thước mục tiêu mong muốn.

  2. Kết nối với SQL Server bằng Query Analyzer, rồi chạy lệnh Transact-SQL sau cho các tệp cơ sở dữ liệu cụ thể mà bạn cần thu hẹp:
     use tempdb
    go

    dbcc shrinkfile (tempdev, 'target size in MB')
    go
    -- lệnh này thu hẹp tệp dữ liệu cơ bản

    dbcc shrinkfile (templog, 'target size in MB')
    go
    -- lệnh này thu hẹp tệp nhật ký, xem đoạn cuối cùng.



Một lợi thế của DBCC SHRINKFILE là nó có thể giảm kích thước của tệp đến kích thước nhỏ hơn kích thước ban đầu. Bạn có thể đưa ra DBCC SHRINKFILE trên bất kỳ tệp dữ liệu hoặc nhật ký nào. Một hạn chế của DBCC SHRINKFILE là bạn không thể làm cho cơ sở dữ liệu nhỏ hơn kích thước của cơ sở dữ liệu mẫu.

Trong SQL Server 7.0, thu hẹp nhật ký giao dịch là một hoạt động bị trì hoãn và bạn phải đưa ra việc cắt xén và sao lưu nhật ký để trợ giúp hoạt động thu hẹp trong cơ sở dữ liệu. Tuy nhiên, theo mặc định, tempdb có tuỳ chọn trunc log on chkpt được đặt là BẬT; do đó, bạn không cần đưa ra việc cắt xén nhật ký cho cơ sở dữ liệu đó. Để biết thêm thông tin về cách thu hẹp nhật ký giao dịch cơ sở dữ liệu trong SQL Server 7.0, hãy bấm vào số bài viết bên dưới để xem bài viết trong Cơ sở Kiến thức Microsoft:

256650  INF: Cách Thu hẹp Nhật ký Giao dịch SQL Server 7.0

 

Quay lại đầu trang

Ảnh hưởng của việc Thực thi DBCC SHRINKDATABASE hoặc DBCCSHRINKFILE Trong khi Tempdb Đang được Sử dụng


Nếu tempdb đang được sử dụng và bạn cố gắng thu hẹp bằng cách sử dụng lệnh DBCC SHRINKDATABASE hoặc DBCC SHRINKFILE, bạn có thể nhận được nhiều lỗi không nhất quán tương tự với loại sau và hoạt động thu hẹp có thể thất bại:

Máy chủ: Thông báo 2501, Mức 16, Trạng thái 1, Dòng 1 Không thể tìm thấy bảng có tên '1525580473'. Kiểm tra sysobjects.

-hoặc-

Máy chủ: Thông báo 8909, Mức 16, Trạng thái 1, Dòng 0 Bảng bị hỏng: ID đối tượng 1, ID chỉ mục 0, ID trang %S_PGID. PageId trong tiêu đề trang = %S_PGID.

Mặc dù lỗi 2501 có thể không phải là dấu hiệu của bất kỳ hỏng hóc nào trong tempdb, nó khiến hoạt động thu hẹp thất bại. Mặt khác, lỗi 8909 không thể biểu thị hỏng hóc trong cơ sở dữ liệu tempdb. Khởi động lại SQL Server để tạo lại tempdb và xoá các lỗi không nhất quán. Tuy nhiên, hãy nhớ rằng có thể có các lý do khác cho các lỗi hỏng hóc dữ liệu vật lý như lỗi 8909 và những lỗi bao gồm sự cố hệ thống đầu vào/đầu ra phụ.
    Blogger Comment
    Facebook Comment