보미네

SQL Server supports numerous commands, most of which are extremely well documented with detailed examples provided in “Books on Line.” However, there are quite a few that were left out of the official documentation and remain unsupported by Microsoft.

You’ll find references regarding these in blogs across the Internet and some are more useful than others. Obviously, these are unsupported commands and should only be used with great care.

TRACE FLAGS

There are quite a few undocumented trace flags in SQL Server. However, I’m only going to touch on four today. They are trace flags 3004, 3014 3604 and 3605.

These can be enabled for the current session with the following commands:

– Enable the trace flags

DBCC TRACEON(3004);

DBCC TRACEON(3014);

DBCC TRACEON(3604);

DBCC TRACEON(3605);

GO

These trace flags are going to be used in conjunction with a database restore so we can get an idea about what SQL Server is doing during the process. We’ll be looking at output in the message output screen and output sent to the SQL Server error log.

Before we go much further, I want to briefly explain the purpose of these flags and why it’s important to use them together to get the information we’re looking for.

Trace Flag 3004

Most Database Administrators are aware of instant file initialization. In a nutshell, when instant file initialization is enabled the data files do not need to be zeroed out during creation. This can save an incredible amount of time during the restoration of VLDBs. As you can imagine, the zeroing out of a 1 TB data file can take a very long time.

Trace flag 3004 turns on information regarding instant file initialization. Enabling this trace flag will not make this information available to view. You will still need to turn on trace flag 3605 to send this information to the error log.

Trace Flag 3014

Trace flag 3014 provides detailed information regarding the steps performed during the backup and restore process. Normally, SQL Server only provides a limited amount of information in the error log regarding these processes. By enabling this trace flag you’ll be able to see some very detailed and interesting information.

Trace Flag 3604

Trace flag 3604 can be used under a variety of circumstances. If you’ve ever used DBCC IND or DBCC PAGE then you’ve probably already used trace flag 3604. It simply informs SQL Server to send some DBCC output information to the screen instead of the error log. In many cases, you have to use this trace flag to see any output at all.

Trace Flag 3605

Trace flag 3605 will send some DBCC output to the error log. This trace flag needs to be enabled to see the instant file initialization information made available by trace flag 3004.

Restore without Trace Flags

I created a full backup of the AdventureWorks database and then restored it with the following command:

– Restore the database

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:\TEMP\ADVENTUREWORKS.BAK’

GO

Without these trace flags the following information is returned to the message output screen:

You can also see very similar information in the error log. I returned the contents of the error log with the following command:

– Read the error log

EXEC xp_readerrorlog;

GO

Restore with Trace Flags

I’m going to perform the restore again. This time my four trace flags have been enabled and I’m hoping to see some additional information in both the message output window and the error log.

In order to make sure the error log is easy to read, I’ve cycled it with the following command:

– Cycle the error log

EXEC sp_cycle_errorlog;

GO

The next step is to execute the restore again.

– Restore the database

RESTORE DATABASE AdventureWorks

FROM DISK = ‘C:\TEMP\ADVENTUREWORKS.BAK’

GO

After the restore is finished, and with the log cleared and the trace flags enabled, we get the following information in the message output window:

It’s easy to see that this output is far more detailed when compared to what we received during our last restore. You notice that there are quite a few additional steps that were not reported when the trace flags were not enabled.

But wait, we also need to check the error log to see what additional information is available there too.

– Read the error log

EXEC xp_readerrorlog;

GO

The most interesting information in the error log is in regard to instant file initialization. You can see that SQL Server is zeroing out the data file during the container prepare process. This means we’re not benefiting from instant file initialization.

Conclusion

It’s important to understand that SQL Server purposely hides this level of detail from us on a daily basis. Arguably, this information isn’t really that necessary. However, if you feel as though you need to have a greater understanding about what’s going on inside the database engine then this is a good starting point on your journey.