超まとめ
Instant File Initializationは、トレースフラグ1806をOnにすると無効にできます。
DBCC TRACEON (1806, -1)
Instant File Initializationを有効化した環境をわざわざ無効化するシチュエーションがいまいち思い浮かびませんが…。
実際やってみた
- トレースフラグ1806: Off
日付 , ソース , 重大度 , メッセージ
03/03/2017 16:49:18 , spid71 , 不明 , RestoreDatabase: Finished
03/03/2017 16:49:18 , spid71 , 不明 , Restore: Done with MSDB maintenance
03/03/2017 16:49:18 , spid71 , 不明 , Writing backup history records
03/03/2017 16:49:18 , spid71 , 不明 , Restore: Writing history records
03/03/2017 16:49:18 , spid71 , 不明 , Resuming any halted fulltext crawls
03/03/2017 16:49:18 , spid71 , 不明 , Restore: Database is restarted
03/03/2017 16:49:18 , spid71 , 不明 , CHECKDB for database ‘test_db_d’ finished without errors on 2017-02-19 10:21:45.520 (local time). This is an informational message only; no user action is required.
03/03/2017 16:49:18 , spid71 , 不明 , PostRestoreReplicationFixup for test_db_d complete
03/03/2017 16:49:17 , spid71 , 不明 , PostRestoreReplicationFixup for test_db_d starts
03/03/2017 16:49:17 , spid71 , 不明 , PostRestoreContainerFixups: fixups complete
03/03/2017 16:49:17 , spid71 , 不明 , PostRestoreContainerFixups: running fixups on test_db_d
03/03/2017 16:49:17 , spid71 , 不明 , Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2C\MSSQL\DATA\test_db_d_1.ldf
03/03/2017 16:49:17 , spid71 , 不明 , Zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2C\MSSQL\DATA\test_db_d_1.ldf from page 1407451 to 1407931 (0x2af3b6000 to 0x2af776000)
03/03/2017 16:49:17 , spid71 , 不明 , FixupLogTail(progress) zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2C\MSSQL\DATA\test_db_d_1.ldf from 0x2af3b5000 to 0x2af3b6000.
03/03/2017 16:49:17 , spid71 , 不明 , Database test_db_d has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
03/03/2017 16:49:07 , spid71 , 不明 , Starting up database ‘test_db_d’.
03/03/2017 16:49:07 , spid71 , 不明 , Restore: Restarting database for ONLINE
03/03/2017 16:49:07 , spid71 , 不明 , Restore: Transitioning database to ONLINE
03/03/2017 16:49:07 , spid71 , 不明 , Restore: Done with fixups
03/03/2017 16:49:07 , spid71 , 不明 , Rollforward complete on database test_db_d
03/03/2017 16:49:07 , spid71 , 不明 , Database test_db_d has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
03/03/2017 16:48:35 , spid71 , 不明 , Restore-Redo begins on database test_db_d
03/03/2017 16:48:35 , spid71 , 不明 , The database ‘test_db_d’ is marked RESTORING and is in a state that does not allow recovery to be run.
03/03/2017 16:48:35 , spid71 , 不明 , Starting up database ‘test_db_d’.
03/03/2017 16:48:35 , spid71 , 不明 , Restore: Backup set restored
03/03/2017 16:48:35 , spid71 , 不明 , Restore: Data transfer complete on test_db_d
03/03/2017 16:48:35 , spid71 , 不明 , FileHandleCache: 0 files opened. CacheSize: 12
03/03/2017 16:48:35 , spid71 , 不明 , Restore: LogZero complete
03/03/2017 16:48:35 , spid71 , 不明 , Restore: Waiting for log zero on test_db_d
03/03/2017 16:39:50 , spid71 , 不明 , Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2C\MSSQL\DATA\test_db_d_1.ldf
03/03/2017 16:34:13 , spid71 , 不明 , Restore: Transferring data to test_db_d
03/03/2017 16:34:13 , spid71 , 不明 , Restore: Restoring backup set
03/03/2017 16:34:12 , spid71 , 不明 , Restore: Containers are ready
03/03/2017 16:34:12 , spid71 , 不明 , Zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2C\MSSQL\DATA\test_db_d_1.ldf from page 1 to 2984064 (0x2000 to 0x5b1100000)
03/03/2017 16:34:12 , spid71 , 不明 , Restore: PreparingContainers
03/03/2017 16:34:12 , spid71 , 不明 , Restore: Attached database test_db_d as DBID=20
03/03/2017 16:34:12 , spid71 , 不明 , Restore: BeginRestore (offline) on test_db_d
03/03/2017 16:34:12 , spid71 , 不明 , Restore: Planning complete
03/03/2017 16:34:12 , spid71 , 不明 , Restore: Planning begins
03/03/2017 16:34:12 , spid71 , 不明 , Restore: Backup set is open
03/03/2017 16:34:12 , spid71 , 不明 , Restore: Configuration section loaded
03/03/2017 16:34:12 , spid71 , 不明 , Opening backup set
03/03/2017 16:34:12 , spid71 , 不明 , RestoreDatabase: Database test_db_d - トレースフラグ1806: On
日付 , ソース , 重大度 , メッセージ
03/03/2017 16:25:14 , spid71 , 不明 , RestoreDatabase: Finished
03/03/2017 16:25:14 , spid71 , 不明 , Restore: Done with MSDB maintenance
03/03/2017 16:25:14 , spid71 , 不明 , Writing backup history records
03/03/2017 16:25:14 , spid71 , 不明 , Restore: Writing history records
03/03/2017 16:25:14 , spid71 , 不明 , Resuming any halted fulltext crawls
03/03/2017 16:25:13 , spid71 , 不明 , Restore: Database is restarted
03/03/2017 16:25:13 , spid71 , 不明 , CHECKDB for database ‘test_db_c’ finished without errors on 2017-02-19 10:21:45.520 (local time). This is an informational message only; no user action is required.
03/03/2017 16:25:13 , spid71 , 不明 , PostRestoreReplicationFixup for test_db_c complete
03/03/2017 16:25:13 , spid71 , 不明 , PostRestoreReplicationFixup for test_db_c starts
03/03/2017 16:25:13 , spid71 , 不明 , PostRestoreContainerFixups: fixups complete
03/03/2017 16:25:13 , spid71 , 不明 , PostRestoreContainerFixups: running fixups on test_db_c
03/03/2017 16:25:13 , spid71 , 不明 , Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c_1.ldf
03/03/2017 16:25:13 , spid71 , 不明 , Zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c_1.ldf from page 1407451 to 1407931 (0x2af3b6000 to 0x2af776000)
03/03/2017 16:25:13 , spid71 , 不明 , FixupLogTail(progress) zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c_1.ldf from 0x2af3b5000 to 0x2af3b6000.
03/03/2017 16:25:13 , spid71 , 不明 , Database test_db_c has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
03/03/2017 16:25:04 , spid71 , 不明 , Starting up database ‘test_db_c’.
03/03/2017 16:25:04 , spid71 , 不明 , Restore: Restarting database for ONLINE
03/03/2017 16:25:04 , spid71 , 不明 , Restore: Transitioning database to ONLINE
03/03/2017 16:25:04 , spid71 , 不明 , Restore: Done with fixups
03/03/2017 16:25:04 , spid71 , 不明 , Rollforward complete on database test_db_c
03/03/2017 16:25:04 , spid71 , 不明 , Database test_db_c has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
03/03/2017 16:24:37 , spid71 , 不明 , Restore-Redo begins on database test_db_c
03/03/2017 16:24:37 , spid71 , 不明 , The database ‘test_db_c’ is marked RESTORING and is in a state that does not allow recovery to be run.
03/03/2017 16:24:37 , spid71 , 不明 , Starting up database ‘test_db_c’.
03/03/2017 16:24:37 , spid71 , 不明 , Restore: Backup set restored
03/03/2017 16:24:37 , spid71 , 不明 , Restore: Data transfer complete on test_db_c
03/03/2017 16:24:37 , spid71 , 不明 , FileHandleCache: 0 files opened. CacheSize: 12
03/03/2017 16:24:37 , spid71 , 不明 , Restore: LogZero complete
03/03/2017 16:24:37 , spid71 , 不明 , Restore: Waiting for log zero on test_db_c
03/03/2017 16:16:22 , spid71 , 不明 , Restore: Transferring data to test_db_c
03/03/2017 16:16:22 , spid71 , 不明 , Restore: Restoring backup set
03/03/2017 16:16:22 , spid71 , 不明 , Restore: Containers are ready
03/03/2017 16:16:22 , spid71 , 不明 , Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c.mdf
03/03/2017 16:11:55 , spid71 , 不明 , Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c_1.ldf
03/03/2017 16:01:52 , spid71 , 不明 , Zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c_1.ldf from page 1 to 2984064 (0x2000 to 0x5b1100000)
03/03/2017 16:01:52 , spid71 , 不明 , Zeroing D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\test_db_c.mdf from page 1 to 5935352 (0x2000 to 0xb521f0000)
03/03/2017 16:01:51 , spid71 , 不明 , Restore: PreparingContainers
03/03/2017 16:01:51 , spid71 , 不明 , Restore: Attached database test_db_c as DBID=19
03/03/2017 16:01:51 , spid71 , 不明 , Restore: BeginRestore (offline) on test_db_c
03/03/2017 16:01:51 , spid71 , 不明 , Restore: Planning complete
03/03/2017 16:01:51 , spid71 , 不明 , Restore: Planning begins
03/03/2017 16:01:51 , spid71 , 不明 , Restore: Backup set is open
03/03/2017 16:01:51 , spid71 , 不明 , Restore: Configuration section loaded
03/03/2017 16:01:51 , spid71 , 不明 , Opening backup set
03/03/2017 16:01:51 , spid71 , 不明 , RestoreDatabase: Database test_db_c
トレースフラグ1806をOnにすると、mdfファイルのゼロ埋め初期化が実行されている=Instant File Initializationが無効になっている ことがわかります。