How to shrink OperationsManagerDW database file size
當 DB & Transaction Log 檔占滿硬碟空間時，您可以使用以下步驟壓縮資料庫:
-- How to Shrink OperationManagerDW Database
--1. Check the OperationManagerDW Database Properties and DBID, for example this database is DBID 18
sp_helpdb
--2. Check the OperationManagerDW(DBID 18) Database Log File ID / name (OperationManagerDW_Log)
select * from master..sysaltfiles where dbid=18
--3. Truncate Trnsaction Log
Backup Log OperationManagerDW With Truncate_Only
--4. Shrink Whole Database
DBCC SHRINKDATABASE(OperationManagerDW)
--5. Srink Log File Again to reduce file size
USE OperationManagerDW
GO
DBCC SHRINKFILE(OperationManagerDW_Log)
您可以參考 KB 256650 來避免 SQL Server 資料庫的交易記錄檔超出預期大小:
- 像是固定執行 DB 備份
- 設定維護計劃
- 變更復原模式
- etc...
By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.
You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
Change the recovery model. If a disaster or data corruption occurs, you must recover your database so that the data consistency and the transactional integrity of the database are maintained. Based on how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up and what your exposure to the data loss is:
- Simple recovery model
- Full recovery model
- Bulk-logged recovery model
By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.
In addition, the Operations Manager databases do not need maintenance tasks as it has its own internal maintenance processes. Some table/index are dropped and created again. That is why your maintenance job is failing re-indexing an index that does not exist
REFERENCE
================
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE https://support.microsoft.com/kb/272318/en-us
INF: 如何將 SQL Server 交易記錄檔壓縮 https://support.microsoft.com/kb/256650/zh-tw
如何避免 SQL Server 資料庫的交易記錄檔超出預期大小 https://support.microsoft.com/kb/873235/zh-tw
INF: SQL Server 中的 Autogrow 及 Autoshrink 設定考量 https://support.microsoft.com/kb/315512/zh-tw
Maintenance Plans https://msdn.microsoft.com/en-us/library/ms187658.aspx
Maintenance Tasks https://msdn.microsoft.com/en-us/library/ms140255.aspx
Recovery Model Overview https://msdn.microsoft.com/en-us/library/ms189275.aspx