Tag Archives: t-sql

Set a SQL Server 2005/2008 database offline and disconnect users

These 2 snippets of T-SQL code will disconnect the users (application) and set the database offline. The usefulness of disconnecting the users is so that you’re actually able to set the database as offline.

Note: YOUR_DATABASE will have to be replaced by the name of the database you wish to set as offline.

-- set database offline
alter database YOUR_DATABASE set single_user
with ROLLBACK IMMEDIATE; -- all connections are disconnected
go
alter database YOUR_DATABASE set offline;
go
-- set the database online
alter database YOUR_DATABASE set online; -- database in single-user mode
go
alter database YOUR_DATABASE set multi_user; -- allow multi-user access
go

// CrashMAG

How to check for active trace flags on Microsoft SQL Server

You check for active trace flags by running the following query. They may be global or they may be session based.

Checking for active trace flags

DBCC TRACESTATUS

The output could be something like the following.

TraceFlag Status Global Session
--------- ------ ------ -------
4199      1      1      0
4616      1      1      0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If there are no active trace flags you will only see

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Enable session based trace flags

To enable trace flags only in your session use the following two commands.

DBCC TRACEON (trace#)
DBCC TRACEOFF (trace#)

trace#
Is the number of the trace flag to turn on.

Enable trace flags globally

DBCC TRACEON (trace#,-1)
DBCC TRACEOFF (trace#,-1)

-1
Switches on the specified trace flags globally.

To enable a trace flag to persist through a restart. Alter the “Startup Parameters” in the Advanced tab for the SQL Server service in SQL Server Configuration Manager.

One example would be.

Before modification

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

After modification

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T4199

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]
[-eerror_log_path] [-lmaster_log_path] [-m]
[-n] [-Ttrace#] [-v] [-x] [-gnumber] [-h]

References

DBCC TRACEON (Transact-SQL)
DBCC TRACEOFF (Transact-SQL)
DBCC TRACESTATUS (Transact-SQL)
Trace Flags (Transact-SQL)

// CrashMAG