Trace 3604 and 3605

Some of the DBCC commands wont show the total results when we execute. like DBCC PAGE.

If we execute DBCC PAGE, SQL Server show the result as DBCC execution completed.

So If you want to results to Display in result pane, need to enable trace flag 3604.

DBCC TRACEON(3604)

If you want to results to Display in error Log, need to enable trace flag 3605.

DBCC TRACEON(3605)

DBCC FREEZE_IO(DBNAME) AND DBCC THAW_IO(DBNAME)

These are undocumented DBCC commands and not supported by Microsft. So please be careful while using.

If we want to suspend write operations on a DB normally we will put that DB in ReadOnly Mode.

We have one more option using undocumented commands:

DBCC FREEZE_IO(DBNAME)   ---  Suspends all the write operations on the specified database.

DBCC THAW_IO(DBNAME)   --- Resumes the database for write operations.

To check Authentication level of SQL Server

SELECT SERVERPROPERTY ('IsIntegratedSecurityOnly')

0--- Is Mixed mode
1--- is WIndows Authentication