GAPTHEGURU

Geek with special skills

Some of my favorite SQL scripts

— SQL Server 2008/2008R2/(2012) Diagnostic Information Queries — Geir Atle Paulsen

— SQL and OS Version information for current instance

SELECT @@VERSION AS [SQL Version Info];   _________________________________________________________________________________________________   — Hardware information from SQL Server 2008 — (Cannot distinguish between HT and multi-core)

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time FROM sys.dm_os_sys_info;

_________________________________________________________________________________________________

— Get sp_configure values for instance

EXEC sp_configure ‘Show Advanced Options’, 1; GO RECONFIGURE; GO EXEC sp_configure;

— Focus on — backup compression default — clr enabled — lightweight pooling (should be zero) — max degree of parallelism — max server memory (MB) — optimize for ad hoc workloads — priority boost (should be zero)

_________________________________________________________________________________________________

— File Names and Paths for all databases in instance

SELECT [dbid], fileid, [filename] FROM sys.sysaltfiles;   — Things to look at — Are data files and log files on different drives? — Is everything on C: drive? — Is TempDB on dedicated drives? — Are there multiple data files?

_________________________________________________________________________________________________

— Recovery model, log reuse wait description, and compatibility level for all databases on instance

SELECT [name], recovery_model_desc, log_reuse_wait_desc, [compatibility_level] FROM sys.databases;   — Things to look at — How many databases are on the instance? — What recovery models are they using? — What is the log reuse wait description? — What compatibility level are they on?   _________________________________________________________________________________________________

— Clear Wait Stats DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR);

 

_________________________________________________________________________________________________

— Isolate top waits for server instance since last restart or statistics clear

WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,     100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,     ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn  FROM sys.dm_os_wait_stats  WHERE wait_type NOT IN( ‘SLEEP_TASK’, ‘BROKER_TASK_STOP’,   ‘SQLTRACE_BUFFER_FLUSH’, ‘CLR_AUTO_EVENT’, ‘CLR_MANUAL_EVENT’,   ‘LAZYWRITER_SLEEP’)) — filter out additional irrelevant waits SELECT W1.wait_type,   CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,   CAST(W1.pct AS DECIMAL(12, 2)) AS pct,   CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) – W1.pct < 95; — percentage threshold

_________________________________________________________________________________________________

INFORMATION: Common Significant Wait types with BOL explanations

*** Network Related Waits *** ASYNC_NETWORK_IO        Occurs on network writes when the task is blocked behind the network   *** Locking Waits *** LCK_M_IX                Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock LCK_M_IU                Occurs when a task is waiting to acquire an Intent Update (IU) lock LCK_M_S                Occurs when a task is waiting to acquire a Shared lock

*** I/O Related Waits *** ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish IO_COMPLETION        Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits PAGEIOLATCH_SH       Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem. PAGEIOLATCH_EX       Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem. WRITELOG             Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. PAGELATCH_EX         Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode. BACKUPIO             Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

*** CPU Related Waits *** SOS_SCHEDULER_YIELD  Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed. THREADPOOL           Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are                      taking unusually long, thus reducing the number of workers available to satisfy other batches. CX_PACKET            Occurs when trying to synchronize the query processor exchange iterator You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

_________________________________________________________________________________________________

— Signal Waits for instance

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],        CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats;

— Signal Waits above 10-15% is usually a sign of CPU pressure

_________________________________________________________________________________________________

— Page Life Expectancy (PLE) value for default instance

SELECT cntr_value AS [Page Life Expectancy] FROM sys.dm_os_performance_counters WHERE object_name = ‘SQLServer:Buffer Manager’ — Modify this if you have named instances AND counter_name = ‘Page life expectancy’;   INFORMATION: — PLE is a good measurement of memory pressure — Higher PLE is better. Below 300 is generally bad. — Watch the trend, not the absolute value

_________________________________________________________________________________________________

— Buffer Pool Usage for instance

SELECT TOP(20) [type], SUM(single_pages_kb) AS [SPA Mem, Kb] FROM sys.dm_os_memory_clerks GROUP BY type  ORDER BY SUM(single_pages_kb) DESC;

INFORMATION:  CACHESTORE_SQLCP  SQL Plans – These are cached SQL statements or batches that aren’t in stored procedures, functions and triggers CACHESTORE_OBJCP  Object Plans – These are compiled plans for stored procedures, functions and triggers CACHESTORE_PHDR   Algebrizer Trees – An algebrizer tree is the parsed SQL text that resolves the table and column names

_________________________________________________________________________________________________    — Individual File Sizes and space available for current database

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB], size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS [Available Space In MB] FROM sys.database_files;

INFORMATION:  — Look at how large and how full the files are and where they are located — Make sure transaction log is not full!!

_________________________________________________________________________________________________    — Top Cached SPs By Execution Count (SQL 2008)

SELECT TOP(50) p.name AS [SP Name], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],  qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time, qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.execution_count DESC;

_________________________________________________________________________________________________      — Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time, qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.total_worker_time DESC;

 

_________________________________________________________________________________________________    — Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads], ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS [Calls/Second], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time, qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.total_logical_reads DESC;

_________________________________________________________________________________________________    — Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS [Calls/Second], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time, qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.total_physical_reads DESC;

 

_________________________________________________________________________________________________          — Top Cached SPs By Total Logical Writes (SQL 2008). Logical writes relate to both memory and disk I/O pressure

SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS [Calls/Second], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time, qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.total_logical_writes DESC;

_________________________________________________________________________________________________      — Possible Bad Indexes (writes > reads)

SELECT object_name(s.object_id) AS [Table Name], i.name AS [Index Name], i.index_id,         user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],         user_updates – (user_seeks + user_scans + user_lookups) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE objectproperty(s.object_id,’IsUserTable’) = 1 AND s.database_id = db_id() AND user_updates > (user_seeks + user_scans + user_lookups) AND i.index_id > 1 ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

_________________________________________________________________________________________________      — Missing Indexes for entire instance by Index Advantage

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement AS [Database.Schema.Table], mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle ORDER BY index_advantage DESC;

INFORMATION: — Look at last user seek time, number of user seeks to help determine source and importance — SQL Server is overly eager to add included columns, so beware

_________________________________________________________________________________________________      — Breaks down buffers used by current database by object (table, index) in the buffer cache

SELECT OBJECT_NAME(p.object_id) AS [ObjectName], p.object_id, p.index_id, COUNT(*)/128 AS [buffer size(MB)],  COUNT(*) AS [buffer_count] FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id WHERE b.database_id = db_id() AND p.object_id > 100 GROUP BY p.object_id, p.index_id ORDER BY buffer_count DESC;

INFORMATION: — Tells you what tables and indexes are using the most memory in the buffer cache

_________________________________________________________________________________________________      — Detect blocking (run multiple times)

SELECT t1.resource_type AS ‘lock type’,db_name(resource_database_id) AS ‘database’, t1.resource_associated_entity_id AS ‘blk object’,t1.request_mode AS ‘lock req’, — lock requested t1.request_session_id AS ‘waiter sid’, t2.wait_duration_ms AS ‘wait time’, — spid of waiter  (SELECT [text] FROM sys.dm_exec_requests AS r                              — get sql for waiter CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = t1.request_session_id) AS ‘waiter_batch’, (SELECT substring(qt.text,r.statement_start_offset/2,     (CASE WHEN r.statement_end_offset = -1     THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2     ELSE r.statement_end_offset END – r.statement_start_offset)/2) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id = t1.request_session_id) AS ‘waiter_stmt’,    — statement blocked t2.blocking_session_id AS ‘blocker sid’,                         — spid of blocker (SELECT [text] FROM sys.sysprocesses AS p                        — get sql for blocker CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE p.spid = t2.blocking_session_id) AS ‘blocker_stmt’ FROM sys.dm_tran_locks AS t1 INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address;

_________________________________________________________________________________________________

 

Advertisements

12/06/2012 Posted by | SQL Scripting, Sql Server, T-SQL | Leave a comment

SQL Server performance – first step

1) System Configuration

SELECT *

FROM sys.configurations

ORDER BY name OPTION (RECOMPILE);

 

2) Filename and Paths of Database

SELECT DB_NAME([database_id])AS [DBName],

name, physical_name, type_desc, state_desc,

CONVERT( bigint, size/128.0) [SizeinMB]

FROM sys.master_files

ORDER BY DB_NAME([database_id])

 

3)Capturing Wait Types and Wait Stats Information

— Create Table

CREATE TABLE [MyWaitStatTable](

[wait_type] [nvarchar](60) NOT NULL,

[waiting_tasks_count] [bigint] NOT NULL,

[wait_time_ms] [bigint] NOT NULL,

[max_wait_time_ms] [bigint] NOT NULL,

[signal_wait_time_ms] [bigint] NOT NULL,

[CurrentDateTime] DATETIME NOT NULL,

[Flag] INT

)

GO

— Populate Table at Time 1

INSERT INTO MyWaitStatTable

([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],

[CurrentDateTime],[Flag])

SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],

GETDATE(), 1

FROM sys.dm_os_wait_stats

GO

—– Desired Delay (for one hour) WAITFOR DELAY ’01:00:00′

— Populate Table at Time 2

INSERT INTO MyWaitStatTable

([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],

[CurrentDateTime],[Flag])

SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms],

GETDATE(), 2

FROM sys.dm_os_wait_stats

GO

— Check the difference between Time 1 and Time 2

SELECT T1.wait_type, T1.wait_time_ms Original_WaitTime,

T2.wait_time_ms LaterWaitTime,

(T2.wait_time_ms – T1.wait_time_ms) DiffenceWaitTime

FROM MyWaitStatTable T1

INNER JOIN MyWaitStatTable T2 ON T1.wait_type = T2.wait_type

WHERE T2.wait_time_ms > T1.wait_time_ms

AND T1.Flag = 1 AND T2.Flag = 2

ORDER BY DiffenceWaitTime DESC

GO

— Clean up

DROP TABLE MyWaitStatTable

GO

12/06/2012 Posted by | SQL Scripting, Sql Server, T-SQL | Leave a comment

Rebuild MSDB Database

If you accidentally delete the transaction log file of msdb database on a newly installed SQL Server 2008 R2 instance do the following steps

I searched Books Online and found this article about Rebuilding System Databases, which helps in rebuild the msdb database.

Steps to Follows

  1. Stop all the SQL Server services & start the command prompt with elevated administrative privilege & execute the following command:
    NET START MSSQLSERVER /T3608
  2. Once you start the SQL Server with trace flag 3608, you will be able to detach the msdb database. To do that, execute the following command in SQLCMD mode:
    SQLCMD -E -S DBS03 -dmaster -Q"EXEC sp_detach_db msdb"
  3. Rename the msdb data file, and execute the instmsdb.sql file from the install folder, as shown below:
    SQLCMD -E -S DBS03 -i"E:\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"E:\instmsdb.out"
  4. Review the instmsdb.out for any errors and re-apply the service packs.
  5. Stop the SQL Server.
  6. Start the SQL Server normally

StepstoFollow

Since I was able to connect to the instance without any error, I stopped the SQL Server instance and copy all the system databases files. Later I restarted the SQL Server Agent and the instance was online.

Hope, this may help someone, Happy Learning Smile

06/19/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , | Leave a comment

Tracking Login Password Changes in SQL Server

Problem

By default, SQL Server does not keep track of login password changes. When the question initially came up with a user, I thought that perhaps it might be in the default trace or in the system_health extended event session. No such luck. So I was in search of an alternate way to keep track of these events, if not retroactively, at least going forward.

Solution

In a short time you can be up and running with collecting password change information using three different methods: server-side trace, event notifications, and SQL Server audit. Below I will provide an example using each technology. Note that all three examples are able to track password changes using ALTER LOGIN, the system procedure sp_password (deprecated since SQL Server 2005), or the Management Studio Login properties dialog.


Server-Side Trace

Trace includes an event called “Audit Login Change Password Event” – which is much more reliable than capturing all batches and filtering on ‘%sp_password%’ and ‘%ALTER%LOGIN%PASSWORD%’. The EventID is 107, so you can set up a very simple trace with the following code (make sure to set a proper path to the desired trace file):

DECLARE @TraceID INT, @MaxFileSize BIGINT;
SET @MaxFileSize = 5;

EXEC sp_trace_create 
    @TraceID OUTPUT, 
    2, 
    N'C:\Traces\PasswordChangeTrace', -- make sure to change this!
    @MaxFileSize,
    10; 

EXEC sp_trace_setevent @TraceID,107, 1,  1;
EXEC sp_trace_setevent @TraceID,107, 11, 1;
EXEC sp_trace_setevent @TraceID,107, 8,  1;
EXEC sp_trace_setevent @TraceID,107, 12, 1;
EXEC sp_trace_setevent @TraceID,107, 14, 1;
EXEC sp_trace_setevent @TraceID,107, 40, 1;
EXEC sp_trace_setevent @TraceID,107, 42, 1;

EXEC sp_trace_setstatus @TraceID, 1;

SELECT @TraceID;

Make note of the TraceID in the output. Once this has been running, you can use that TraceID to review the events that have been captured using the following query:

DECLARE @path NVARCHAR(255);

SELECT @path = [path]
FROM  sys.traces
WHERE id = <traceID from above>;

SELECT 
  LoginName  = TargetLoginName, 
  EventCount = COUNT(*), 
  FirstEvent = MIN(StartTime), 
  LastEvent  = MAX(StartTime)
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 107 -- in case you've added other events
GROUP BY TargetLoginName;

Since the above trace definition specifies a max of 10 x 5MB files, eventually an event that happens today will no longer be available through the above query. So as an added exercise you may consider periodically taking a snapshot of this data into a permanent table, and running your queries from there.


Event Notifications

An alternative to trace is to set up a targeted Event Notification. These are lightweight, asynchronous messages sent via Service Broker that can be used to perform various actions in response to a specific event. One such event is AUDIT_LOGIN_CHANGE_PASSWORD_EVENT. In a lot of cases people use these to send an e-mail or start a job, but in this case we’re just going to log to a table. We can create the following table in msdb:

USE [msdb];
GO

CREATE TABLE dbo.PasswordChangeLog
(
    LoginName  SYSNAME,
    EventTime  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

We will then need to set up a queue and a notification to handle our events:

CREATE QUEUE PasswordChangeQueue;
GO

CREATE SERVICE PasswordChangeService ON QUEUE PasswordChangeQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

And then the following procedure can be used to log events to our table:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @message_body XML;

    WHILE (1 = 1)
    BEGIN
       WAITFOR 
       ( 
         RECEIVE TOP(1) @message_body = message_body
         FROM dbo.PasswordChangeQueue
       ), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChangeLog(LoginName) 
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
    END
END
GO

Finally, we can change the queue to call this stored procedure in response to the event:

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Now change the password for a few logins, and you should see results from the following query:

SELECT 
  LoginName, 
  EventCount = COUNT(*), 
  FirstEvent = MIN(EventTime), 
  LastEvent  = MAX(EventTime)
FROM dbo.PasswordChangeLog
GROUP BY LoginName;

Server Audit

The final option I’ll present here is creating a Server Audit Specification. You may already be using Server Audit, and if so, handling password change auditing using this technology might make more sense than using either of the above two methods. (However note that Server Audit requires Enterprise Edition of SQL Server 2008 or SQL Server 2008 R2 – in SQL Server 2012, this feature has been made available in all editions.)

One of the options for a Server Audit Specification is LOGIN_CHANGE_PASSWORD_GROUP. We can set up a file-based audit to capture these events with the following code (note that this needs to be performed in master and you should update the file path appropriately – you probably don’t want to rely on C:\ for this):

USE [master];
GO

CREATE SERVER AUDIT ChangePasswordAudit
  TO FILE (FILEPATH = 'C:\Audits\', MAXSIZE = 5MB, MAX_ROLLOVER_FILES = 10)
  WITH (ON_FAILURE = CONTINUE); -- important unless you want your server to halt on failure

ALTER SERVER AUDIT ChangePasswordAudit
  WITH (STATE = ON);

CREATE SERVER AUDIT SPECIFICATION ChangePasswordAuditSpecification
  FOR SERVER AUDIT ChangePasswordAudit
  ADD (LOGIN_CHANGE_PASSWORD_GROUP)
  WITH (STATE = ON);
GO

Once this is running, you can change a few passwords and then retrieve data from the audit using the following query:

DECLARE @folder VARCHAR(255);

SELECT @folder = log_file_path + '*' 
  FROM sys.server_file_audits 
  WHERE name = 'ChangePasswordAudit';

SELECT 
  LoginName  = target_server_principal_name, 
  EventCount = COUNT(*),
  FirstEvent = MIN(event_time), 
  LastEvent  = MAX(event_time)
FROM sys.fn_get_audit_file(@folder, DEFAULT, DEFAULT)
WHERE action_id IN ('PWR', 'PWC') -- PWR = ALTER LOGIN / SSMS, PWC = sp_password
GROUP BY target_server_principal_name;

As with the trace above, this file-based audit is limited to 10 x 5MB files. So you may want to change those options to have the audit data hang around longer, or you may consider occasionally storing the result of this query in a permanent table.

One important thing to note about Server Audit is that it records the event time in UTC, so you might notice that the timestamps are off depending on your time zone. Therefore you may need to look into adding a helper function that will convert any UTC date to your time zone. Since this can get complicated with Daylight Saving Time, I’ve often found it easier to just set up all of our servers to do everything in UTC. 🙂


Conclusion

As you can see, there are a variety of ways to set up tracking for password changes, and each method is relatively straightforward to implement. While it is still impossible to obtain this information from the past, once you have implemented one of the above solutions, you will be able to look back on this information over time.

 

06/13/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , | Leave a comment

SSMS Timeout Expired Error When Making Changes To a Table

Problem

I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn’t think it would have a timeout issue.  In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.

Solution

To simulate the timeout error, let’s create a table with 500,000 rows. We will use this for the examples.

-- Create the table for demo
IF EXISTS (SELECT * FROM sys.objects where name = 'TestingTimeOut')
DROP TABLE TestingTimeOut
GO
create table TestingTimeOut (EmpName varchar(75), Designation varchar(50), Department varchar(50))
GO

-- Populate the table with 500000 rows 
-- Multiple executions by providing number with GO is not ANSI standard 
-- Should not be used in production environment
INSERT INTO TestingTimeOut VALUES ('Atif Shehzad', 'DBA', 'Human Resource')
GO 500000

Transaction timeout for SSMS Designer operates only when SQL Server object modification is performed through the SSMS Designer. It is not a server level parameter and may be viewed or modified in the ‘Designers’ link in the Options menu of SSMS. Its value can be different for each SSMS client and is not stored as a server level configuration.

To check or change this setting, within SSMS go to Tools > Options and click on the Designers link in the left panel as shown below.

Time-out for SSMS designers

The default value is 30 seconds, but for the timeout simulation let’s change it to 1 second and save the changes.

In SSMS, find the table that we just created, right click and select Desgin.  Add an integer type column EmpID as a primary key with an identity property and try to save the modifications. Since we changed the timeout to 1 second, this should cause a timeout error as shown below. If the error is not generated you may need to increase the number of rows in the demo table and try again. (Note if you get this error “Saving changes is not permitted” then check out this tip.)

ssms designer timeout designer expired for operation

This is the T-SQL that SSMS generates and is trying to execute.  We can see here that it is just doing an ALTER TABLE with the ADD option.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestingTimeOut ADD
 EmpID int NOT NULL IDENTITY (1, 1)
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

If we try to add the EmpID as the first column, as shown below we can see that SSMS generates a pre-save warning that this will take some time. .

This is the T-SQL that SSMS generates and we can see that this is a much more intensive script that drops and recreates the table.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestingTimeOut
 (
 EmpID int NOT NULL IDENTITY (1, 1),
 EmpName varchar(75) NULL,
 Designation varchar(50) NULL,
 Department varchar(50) NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_TestingTimeOut OFF
GO
IF EXISTS(SELECT * FROM dbo.TestingTimeOut)
  EXEC('INSERT INTO dbo.Tmp_TestingTimeOut (EmpName, Designation, Department)
  SELECT EmpName, Designation, Department FROM dbo.TestingTimeOut WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestingTimeOut
GO
EXECUTE sp_rename N'dbo.Tmp_TestingTimeOut', N'TestingTimeOut', 'OBJECT' 
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

Summary

So if you face these timeout issues when using SSMS to make changes you now know the exact parameter to manipulate. The solution to this problem is to increase the timeout limit for Designer. The specified limit would be implemented for any sever connected via that SSMS connection.

05/23/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , | Leave a comment

SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem

Will SQL Server database mirroring stop working if the user used to create the endpoints is removed?  In this tip I will walk through the steps to find out.

Solution

Before I am able to provide with a definitive answer to the above problem, I created the following test environment.

  • A Windows 2008 VM (Virtual Server) named SQLTEST1 with SQL 2008 Developer installed
  • A Windows 2008 VM (Virtual Server) named SQLTEST2 with SQL 2008 Developer installed
  • Since these servers are not in a domain I created a Windows user WinUser on both VMs and used this account to setup mirroring.
  • I created a test database called mytest and mirrored it between the two SQL Servers SQLTEST1 and SQLTEST2.

Check Mirroring Status

Let’s start Database Mirroring Monitor to verify that database mirroring is working. Below we can see that the databases are synchronized.

use database mirroring monitor to check status

Drop SQL Server Login

Now, let’s try to drop SQLTEST1\WinUser login by executing the following. As expected, the SQL login cannot be dropped because it owns the Mirroring endpoint. Since this failed I did not bother to try this on SQLTEST2.

tsql code to drop sql server login

Drop Windows User

Now let’s try to drop the Windows user WinUser on both servers.  This was successful on both servers.

steps to delete a windows login

Check Mirroring Status

Now that WinUser has been deleted on both servers, let’s check if database mirroring is still working.  With a bit of surprise, database Mirroring is still working. To further verify it, I created tables and inserted data on the mytest database on the principle server SQLTEST1 and verified that such information was successfully replicated to the mirroring server SQLTEST2.

use database mirroring monitor to check status

Restart SQL Server

Now, let’s restart the SQL Server service on both servers.

restart sql server services

Check Mirroring Status

If we check the mirroring status again we can see that it has failed.

use database mirroring monitor to check status

Conclusion

In order to avoid a possible mirroring outage it is appropriate to verify that a Windows user does not own mirroring endpoints, because if the user is removed from Active Directory or a local group, mirroring will break the next time the server is restarted.  If you do use a Windows user you need to make sure that your accounts are well documented and not accidently deleted.

05/23/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , | Leave a comment

SQL Server 2012 Functions – Lead and Lag

Problem

SQL Server 2012 introduces two new analytical functions, LEAD() and LAG(). In this tip we will be exploring these functions and how to use them.

Solution

These functions access data from a subsequent row (lead) and previous row (lag) in the same result set without the use of a self-join.

The syntax for the Lead and Lag functions is:

LAG|LEAD (scalar_expression [,offset] [,default]) 
    OVER ( [ partition_by_clause ] order_by_clause )

Let me explain using this example which creates table Test_table in database TestDB and inserts some data.

CREATE DATABASE [TestDB]

--Create testable to hold some data
CREATE TABLE [dbo].[Test_table](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Department] [nchar](10) NOT NULL,
 [Code] [int] NOT NULL,
 CONSTRAINT [PK_Test_table] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Insert some test data
insert into Test_table values('A',111)
insert into Test_table values('B',29)
insert into Test_table values('C',258)
insert into Test_table values('D',333)
insert into Test_table values('E',15)
insert into Test_table values('F',449)
insert into Test_table values('G',419)
insert into Test_table values('H',555)
insert into Test_table values('I',524)
insert into Test_table values('J',698)
insert into Test_table values('K',715)
insert into Test_table values('L',799)
insert into Test_table values('M',139)
insert into Test_table values('N',219)
insert into Test_table values('O',869)

Our table data will look like this:

Create Test_table on the databse TestDB

Now the query for lead value and lag value will be:

SELECT id,department,Code,
LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,
LAG(Code,1) OVER (ORDER BY Code ) LagValue
FROM test_table

 

Now the query for leadvalue and lagvalue will be

In the above example, for the first row the Lead value is the value of the next row because the offset is set to 1. The Lag value is NULL because there were no previous rows.

Now if we change the Lead offset to 2 and Lag offset to 3 the output will be as follows:

If we change Lead offset to 2 and Lag offset to 3 the output will be:

One thing to note is that NULL values appear, because there are not values for the Lag or Lead.  To replace NULL values with zero add 0 in Lead\Lag function as shown below.

SELECT id,department,Code,
LEAD(Code,2,0) OVER (ORDER BY Code ) LeadValue,
LAG(Code,3,0) OVER (ORDER BY Code ) LagValue
FFROM test_table
replace NULL with ‘0’ add 0 in Lead\Lag function.

 

05/03/2012 Posted by | SQL Scripting, Sql Server, T-SQL | | Leave a comment

Restore Database From SQL Server 2008 to SQL Server 2005 Part 1 – 3

PART 1:

Problem

When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will see some error messages as the examples below.

Backup and Restore

You have backup a database from SQL Server 2008. If you try to restore the backup database file to SQL Server 2005, you will receive the error message:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information:
-> The media family on device ‘the backup file‘ is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Restore Database Error Message

Detach and Attach

You have detach a database from SQL Server 2008. If you try to attach the detached database file to SQL Server 2005, you will receive the error message:

Attach database failed for Server ‘SQL Server name’. (Microsoft.SqlServer.Smo)
Additional information:
-> An exception occurred while executing a Transact-SQL statement batch. (Microsoft.SqlServer.ConnectionInfo)
–> The database ‘database name’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database ‘database name’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

Attach Database Error Message

Solution

These problems occur because a backup or detach database file is not backward compatible. You cannot restore or attach a database which is created from a higher version of SQL Server to a lower version of SQL Server.

But there are some alternatives which can help you to restore a database to a lower version of SQL Server. I divide into separate parts.

  1. Part 2: Generate SQL Server Scripts Wizard. The solution creates a SQL Server script file using a wizard. Then, you simply execute the script file on SQL Server 2005 or SQL Server 2000. So you will get everything as same as the source database on the destination. But there are some disadvantages:
    • If the source database contains lots of data, you will have a large script file.
    • The generated file is a plain text. Anyone who has access to the file can read it. So you should delete the script file after the restoration.
  2. Part 3: Import and Export Wizard. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.

————————————————————————————————————————————————————————————————————————————————————————————

PART 2: Generate SQL Server Scripts Wizard

Now let’s see a first solution to solve the problems.

On this post, you see how to backup ‘Northwind’ database by generate a SQL Server script on SQL Server 2008. Then, restore the ‘Northwind’ database by execute the SQL Server script on SQL Server 2005.

Step-by-step

  1. On Microsoft SQL Server Management Studio, connects to the SQL Server 2008. Right-click on the database that you want to backup and select Tasks -> Generate Scripts.
    Generate Scripts
  2. On Welcome to the Generate SQL Server Scripts Wizard, click Next.
    Welcome to the Generate SQL Server Scripts Wizard
  3. On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
    Select Database
  4. On Choose Script Options, set Script Database Create to False and Script for Server Version to SQL Server 2005.
    Note: You can set Script Database Create to True if your source and destination for store database files are the same location.
    Script for Server Version to SQL Server 2005
  5. Continue on Choose Script Options, scroll down and set Script Data to True. Click Next
    Note: Set this option to true to include data on each table to a script.
    Script Data to True
  6. On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
    Select Script Output Destination
  7. On Script Wizard Summary, you can review your selections. Then, click Finish.
    Script Wizard Summary
  8. On Generate Script Progress, the wizard is creating a SQL Server script.
    Generate Script Progress
  9. When the script has been completed, you see the output file as similar the figure below.
    The Generated Script
  10. Connect to SQL Server 2005, create a new database. Right-click Database -> New Database.
    Note: If you have set Script Database Create to True on step 4, you don’t have to create a database manually.
    Create New Database
  11. Type ‘Northwind’ as database name. Click OK.
    Create Database Northwind
  12. Execute the SQL Server script file that you have created.
    Execute the script
  13. Now the database ‘Northwind’ is restored on SQL Server 2005.
    Northwind Database

———————————————————————————————————————————————————————————-

PART 3: Export Data Wizard

On this post, you see how to export tables on ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 using export data wizard.

Step-by-step

  1. On Microsoft SQL Server Management Studio, connects to SQL Server 2008. Right-click on the database that you want to export data -> select Tasks -> Export Data.
    Export Data
  2. On Welcome to SQL Server Import and Export Wizard, click Next.
    Welcome to SQL Server Import and Export Wizard
  3. On Choose a Data Source, select the source from which to copy data. Set Data source to SQL Server Native Client 10.0. Verify that Server name is the source of SQL Server 2008 that you want and select Database as ‘Northwind’. Click Next.
    Choose a Data Source
  4. On Choose a Destination, specify where to copy data to. Set Destination to SQL Server Native Client 10.0. Type the Server name to the destination of SQL Server 2005 that you want. You can also click Refresh to verify if you can connect to the specify server name. Currently, I don’t have ‘Northwind’ database on SQL Server 2005 so I will create a new one, click New.
    Choose a Destination
  5. On Create Database, type name as ‘Northwind’ and click OK.
    Create Database
  6. Back to Choose a Destination, I have created ‘Northwind’ database so select it as Database. Click Next.
    Choose a Destination
  7. On Specify Table Copy or Query, select Copy data from one or more tables or views and click Next.
    Copy data from one or more tables or views
  8. On Select Sources Tables and Views, select tables that you want to export. On this example, I select all tables on ‘Northwind’ database.
    Select Sources Tables and Views
  9. On Save and Run Package, click Next.
    Save and Run Package
  10. On Complete the Wizard, you can verify the choices made in the wizard. Then, click Finish.
    Complete the Wizard
  11. Wait until the wizard finishes execution.
    Export Data Successful
  12. Now I have exported tables of ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 successfully.
    The Exported Tables

04/24/2012 Posted by | Sql Server, T-SQL | , , | 2 Comments

Simple script to backup all SQL Server databases

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution
With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this.

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of “DBnameYYYDDMM.BAK”.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

04/10/2012 Posted by | SQL Scripting, Sql Server, T-SQL | | Leave a comment

Upgrading SQL Server databases and changing compatibility levels

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem
When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server.  From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed.  This tip will show you how to check the current compatibly level, how to change the compatibly level and also some of the differences between earlier versions and SQL Server 2005.

Solution
The first thing that you need to do is to check the compatibility level that your database is running under.  As mentioned above any database that is upgraded using the backup and restore or detach and attach method will not change the compatibly level automatically, so you will need to check each database and make the change.

Although SQL Server has changed its naming convention to SQL Server 2000, 2005 and soon to be released 2008 the internal version numbers still remain.  Here is a list of the compatibly levels (versions) that you will see:

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005

Identifying Compatibly Level

To check the compatibility level of your databases you can use one of these methods:

Using SQL Server Management Studio, right click on the database, select “Properties” and look at the “Options” page for each database as the following image shows:

Another option is to use sp_helpdb so you can get the information for all databases at once:

EXEC sp_helpdb

Or select directly from the sys.databases catalog to get the information for all databases.

SELECT * FROM sys.databases


Compatibly Level for New Databases

When issuing a CREATE DATABASE statement there is not a way to select which compatibility level you want to use.  The compatibility level that is used is the compatibility level of your model database.

Here is a sample CREATE DATABASE command, but there is not an option to change the compatibility level.

CREATE DATABASE [test] ON PRIMARY
( NAME = N’test’, FILENAME = N’Z:\SQLData\test.mdf’ , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’test_log’, FILENAME = N’Y:\SQLData\test3_log.ldf’ , SIZE = 3072KB , FILEGROWTH = 10%)
GO

When creating a database using SQL Server Management Studio you have the ability to change the compatibility level on the “Options” tab such as follows:

If we use the “Script” option we can see that SQL Server issues the CREATE DATABASE statement and then issues “sp_dbcmptlevel” to set the database compatibility level to 80 as shown below.

CREATE DATABASE [test] ON PRIMARY
( NAME = N’test’, FILENAME = N’Z:\SQLData\test.mdf’ , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’test_log’, FILENAME = N’Y:\SQLData\test3_log.ldf’ , SIZE = 3072KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80
GO

Changing Compatibility Level

So once you have identified the compatibility level of your database and know what you want to change it to you can use the sp_dbcmptlevel system stored procedure to make the change.  The command has the following syntax:

sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]
–to change to level 80
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80

–to change to level 90
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=90

–or
sp_dbcmptlevel ‘test’, ’80’

sp_dbcmptlevel ‘test’, ’90’


Differences

There are several differences on how compatibly levels affect your database operations.  SQL Server Books Online has a list of these differences and the following list shows you a few of these items:

Compatibility level setting of 80 or earlier Compatibility level setting of 90 Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional. With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL). High
The *= and =* operators for outer join are supported with a warning message. These operators are not supported; the OUTER JOIN keyword should be used. High
SET XACT_ABORT OFF is allowed inside a trigger. SET XACT_ABORT OFF is not allowed inside a trigger. Medium

(Source: SQL Server 2005 Books Online)  For a complete list of these items look here:

In addition, each new compatibility level offers a new list of reserved keywords.  Here is a list of the new keywords for SQL Server 2005.

Compatibility level setting Reserved keywords
90 PIVOT, UNPIVOT, REVERT, TABLESAMPLE
80 COLLATE, FUNCTION, OPENXML
70 BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP
65 AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK

(Source: SQL Server 2005 Books Online)

If one of these keywords is being used and your database is set to this compatibly level the commands will fail.  To get around this you could put the keyword in either square brackets ([ ]) or use quotation marks (” “) such as [PIVOT] or “PIVOT”.


Summary

The compatibly level setting is used by SQL Server to determine how certain new features should be handled.  This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking.  This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.

In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.

04/10/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , , | Leave a comment

%d bloggers like this: