Problem:
How to know what isolation
level is set and other set options are set on a database?
Solution: sys.databases (Transact-SQL) – Displays all the
set options that a database has and shows 1 or 0 to indicate whether the SET
options are set to ON or OFF.
DBCC USEROPTIONS (Transact-SQL)
- Returns the SET options active
(set) for the current connection.
you can obtain the same information from SQL Server management by going through the Database properties from the object explorer.
The below navigation screen
will help you in where to go for this information.
Examples:
In this
example, I would like to show we can option the information from a sql query to
get the options that are set on a database.
--using
system views
SELECT CASE
WHEN is_ansi_null_default_on =
1
THEN 'ON'
ELSE 'OFF'
END as [SET
is_ansi_null_default_on],
CASE
WHEN is_read_committed_snapshot_on = 1
THEN 'ON'
ELSE 'OFF'
END as [SET
is_read_committed_snapshot_on],
CASE
WHEN is_read_only = 1
THEN 'ON'
ELSE 'OFF'
END as [SET
is_read_only],name as
DatabaseName,snapshot_isolation_state,snapshot_isolation_state_desc,
compatibility_level
FROM sys.databases
WHERE NAME = 'DB'
OUTPUT:
|
SET
is_ansi_null_default_on
|
SET
is_read_committed_snapshot_on
|
SET
is_read_only
|
DatabaseName
|
snapshot_isolation_state
|
snapshot_isolation_state_desc
|
compatibility_level
|
|
OFF
|
OFF
|
OFF
|
DB
|
0
|
OFF
|
120
|
--
using DBCC options
DBCC USEROPTIONS
OUTPUT:
|
Set Option
|
Value
|
|
textsize
|
2147483647
|
|
language
|
us_english
|
|
dateformat
|
mdy
|
|
datefirst
|
7
|
|
lock_timeout
|
-1
|
|
quoted_identifier
|
SET
|
|
arithabort
|
SET
|
|
ansi_null_dflt_on
|
SET
|
|
xact_abort
|
SET
|
|
ansi_warnings
|
SET
|
|
ansi_padding
|
SET
|
|
ansi_nulls
|
SET
|
|
concat_null_yields_null
|
SET
|
|
isolation level
|
read committed
|
0 comments:
Post a Comment