Hướng dẫn SHRINK file log trong SQL

Thông tin Tempdb

Cơ sở dữ liệu tempdb là không gian làm việc tạm thời. SQL Server sử dụng tempdb để thực hiện nhiều tác vụ. Trong số các tác vụ đó là những tác vụ sau:

• 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 giữ 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ể hóa

• Lưu trữ các bản ghi phiên bản khi cấp độ tách biệt ảnh chụp nhanh hoặc cấp độ tách biệt ảnh chụp nhanh đọc-đã cam kết được sử dụng

SQL Server chỉ 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 khôi phục 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, bạn không phải ghi lại 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ó giao dịch nào để nâng cấp hoặc hoàn nguyên. 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. 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 sử dụng tÙy chọn MODIFY FILE hoặc câu lệnh DBCC SHRINKFILE hoặc DBCC SHRINKDATABASE.

Theo mặc định, cơ sở dữ liệu tempdb được cấu hình để tự động phát triển khi cần thiết. Do đó, cơ sở dữ liệu này có thể phát triển không mong muốn kịp thời tới kích thước lớn hơn kích thước mong muốn. Việc khởi động lại đơn giản SQL Server sẽ 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.

Trong SQL Server 2005 và phiên bản mới hơn, bạn có thể sử dụng bất kỳ phương pháp nào sau đây để thay đổi kích thước của tempdb.

Phương pháp

Yêu cầu khởi động lại?

Thông tin bổ sung

Lưu ý SQL Server Management Studio trong SQL Server 2005 không cho biết kích thước chính xác của các tệp tempdb sau thao tác 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 thao tác thu hẹp diễn ra cho cơ sở dữ liệu tempdb. Để tìm kích cỡ chính xác của các tệp tempdb sau thao tác thu hẹp, hãy thực thi lệnh sau trong SQL Server Management Studio:

Ba phương pháp đầu tiên được thảo luận ở đây.

Lưu ý Đối với cài đặt SQL Server 2000, bạn sẽ phải sử dụng Query Analyzer thay vì SQL Server Management Studio. Ngoài ra, các lệnh DBCC sẽ yêu cầu bạn đặt cơ sở dữ liệu ở chế độ một người dÙng.

Bạn có thể sử dụng ba phương pháp sau để thu hẹp tempdb về kích thước nhỏ hơn kích thước được định cấu hình.

Phương pháp 1: Sử dụng các lệnh Transact-SQL
Lưu ý Phương pháp này yêu cầu bạn khởi động lại SQL Server.

1. Dừng SQL Server.

2. Tại dấu nhắc lệnh, hãy khởi động phiên bản ở chế độ cấu hình tối thiểu. Để thực hiện việc này, hãy làm theo các bước sau:

   1. Tại dấu nhắc lệnh, hãy thay đổi thư mục sau:

   2. Nếu phiên bản là một phiên bản được đặt tên của SQL Server, hãy chạy lệnh sau:

      sqlservr.exe -s InstanceName -c -f Nếu phiên bản là một phiên bản mặc định của SQL Server, hãy chạy lệnh sau:
      

      sqlservr -c -f Lưu ý 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 đối với tệp dữ liệu và 0,5 MB đối với tệp nhật ký.

3. 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] --Desired target size for the data file ALTER DATABASE tempdb MODIFY FILE [NAME = 'templog', SIZE = target_size_in_MB] --Desired target size for the log file

4. Dừng SQL Server. Để thực hiện việc này, 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 dưới dạng một dịch vụ. Mọi tệp tempdb đều được tạo lại trong khi khởi động. Tuy nhiên, các tệp này trống và có thể bị xóa. Để xóa các tệp bổ sung trong tempdb, hãy sử dụng lệnh ALTER DATABASE bằng cách sử dụng tÙy chọn REMOVE FILE.

Phương pháp 2: Sử dụng lệnh DBCC SHRINKDATABASE
Sử dụng lệnh DBCC SHRINKDATABASE để thu hẹp cơ sở dữ liệu tempdb. DBCC SHRINKDATABASE nhận tham số target_percent. Đây 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 sử dụng DBCC SHRINKDATABASE, bạn có thể phải khởi động lại SQL Server.

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 phần trăm không gian trống còn lại để sử dụng làm tham số cho DBCC SHRINKDATABASE. Tính toán này dựa vào kích thước cơ sở dữ liệu mong muốn.
   
   Lưu ý 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 đã lưu trữ sp_spaceused.
   
   
   Xem xét ví dụ sau:

   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'] -- This command shrinks the tempdb database

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 nhỏ hơn 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 sử dụng tÙy chọn MODIFY FILE hoặc lệnh. Một hạn chế khác của BCC 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.

Phương pháp 3: Sử dụng lệnh DBCC SHRINKFILE
Sử dụng lệnh DBCC SHRINKFILE để thu hẹp 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 tham số kích thước mục tiêu. Đây là kích thước cuối cÙng mong muốn cho tệp cơ sở dữ liệu.

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à 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 muốn thu hẹp:

   use tempdb go dbcc shrinkfile [tempdev, 'target size in MB'] go -- this command shrinks the primary data file dbcc shrinkfile [templog, 'target size in MB'] go -- this command shrinks the log file, examine the last paragraph.

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.

Lỗi 2501 và 8909 khi bạn chạy thao tác thu hẹp

Nếu tempdb đang được sử dụng và nếu bạn cố thu hẹp tệp này bằng lệnh DBCC SHRINKDATABASE hoặc DBCC SHRINKFILE, bạn có thể nhận được thông báo giống như sau, tÙy vào phiên bản SQL Server mà bạn đang sử dụng:

SQL Server 2005 và các phiên bản mới hơn

SQL Server 2000

Ghi chú

• Các lỗi này không cho biết bất kỳ lỗi thực nào trong tempdb. Tuy nhiên, hãy nhớ rằng có thể có các lý do khác cho các lỗi hỏng dữ liệu vật lý như lỗi 8909 và những lý do đó bao gồm sự cố hệ thống đầu vào/đầu ra phụ. Do đó, nếu lỗi xảy ra bên ngoài thao tác thu hẹp, bạn nên tiến hành điều tra thêm.

• Trong SQL Server 2005 và các phiên bản mới hơn, mặc dÙ thông báo 8909 được trả về cho ứng dụng hoặc cho người dÙng thực hiện thao tác thu hẹp, các thao tác thu hẹp sẽ không thất bại.

• Trong SQL Server 2000 và các phiên bản trước đó, các lỗi này sẽ khiến thao tác thu hẹp không thành công. Do đó, để thu hẹp cơ sở dữ liệu tempdb, bạn sẽ phải khởi động lại SQL Server để tạo lại tempdb.