xp_fixeddrives на дает нам полной информации о состоянии дисковой подсистемы на сервере. Раньше приходилось совместно с этим использовать утилиту windows fsutil volume diskfree чтобы получить полный объем дисков. Интересное решение предлагается на sqlservercentral. возьмем на вооружение =)))
http://www.sqlservercentral.com/articles/SQLCLR/70963/
--
BR Semenenko Konstantin
вторник, 21 сентября 2010 г.
вторник, 14 сентября 2010 г.
понедельник, 13 сентября 2010 г.
четверг, 2 сентября 2010 г.
Как просмотреть текущие значения параметров сессий SQL Server.
Source http://www.mssqltips.com/tip.asp?tip=1415
ProblemWith each session that is made to SQL Server the user can configure the options that are SET for that session and therefore affect the outcome of how queries are executed and the underlying behavior of SQL Server. Some of these options can be made via the GUI, while others need to be made by using the SET command. When using the GUI it is easy to see which options are on and which options are off, but how can you get a better handle on all the options that are currently set for the current session?
.| Value | Configuration | Description |
|---|---|---|
| 1 | DISABLE_DEF_CNST_CHK | Controls interim or deferred constraint checking. |
| 2 | IMPLICIT_TRANSACTIONS | For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections. |
| 4 | CURSOR_CLOSE_ON_COMMIT | Controls behavior of cursors after a commit operation has been performed. |
| 8 | ANSI_WARNINGS | Controls truncation and NULL in aggregate warnings. |
| 16 | ANSI_PADDING | Controls padding of fixed-length variables. |
| 32 | ANSI_NULLS | Controls NULL handling when using equality operators. |
| 64 | ARITHABORT | Terminates a query when an overflow or divide-by-zero error occurs during query execution. |
| 128 | ARITHIGNORE | Returns NULL when an overflow or divide-by-zero error occurs during a query. |
| 256 | QUOTED_IDENTIFIER | Differentiates between single and double quotation marks when evaluating an expression. |
| 512 | NOCOUNT | Turns off the message returned at the end of each statement that states how many rows were affected. |
| 1024 | ANSI_NULL_DFLT_ON | Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls. |
| 2048 | ANSI_NULL_DFLT_OFF | Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls. |
| 4096 | CONCAT_NULL_YIELDS_NULL | Returns NULL when concatenating a NULL value with a string. |
| 8192 | NUMERIC_ROUNDABORT | Generates an error when a loss of precision occurs in an expression. |
| 16384 | XACT_ABORT | Rolls back a transaction if a Transact-SQL statement raises a run-time error. |
(Source: SQL Server 2005 Books Online)
In a previous tip, Reproducing Query Execution Plan Performance Problems the author talked about how SET options could impact the outcome of queries and performance issues, so this command could become useful to determine what the current options are for the session.
To get the settings for the current session you can run this simple SELECT statement:
| SELECT @@OPTIONS |
When run, this command returns an integer that represents the bit values as shown in the table above.
To help make further sense of these values you can run the following bitwise code that will show you what SET options are turned on.
| DECLARE @options INT SELECT @options = @@OPTIONS PRINT @options |
When the above code is run for my session, this is the output:
The first value 5496 displays the current @@OPTIONS value and the following lines signify which options are currently set for the session.
To illustrate how this value changes we are going to run SET NOCOUNT ON which should turn on bit value 512. If we add 5496 and 512 the new value should be 6008.
| SET NOCOUNT ON DECLARE @options INT SELECT @options = @@OPTIONS PRINT @options |
When the above code is run, this is the output with NOCOUNT now on:
Here is one more example where we have set NOCOUNT ON and QUOTED_IDENTIFIER OFF.
| SET NOCOUNT ON SET QUOTED_IDENTIFIER OFF DECLARE @options INT SELECT @options = @@OPTIONS PRINT @options |
When the above code is run, this is the output we get
Summary
This is a simple function that can give you a lot of insight into your current session settings. By using this function, you can determine what settings users have set for their session and then determine if you need to turn certain options on or off to ensure your code acts as desired.
Подписаться на:
Сообщения (Atom)