SQL Server: Placing Alert for Compatibility Level Change in SQL 2005

Microsoft SQL Server allows its users to keep behavior of a database compatible to its older versions. Like, if someone is using “*=” type of left outer joins in some quires/Stored Procedures as she created it for SQL Server 2000. Though such join are not allowed in SQL Server 2005 and subsequent versions but one still can keep database behavior as SQL Server 2000 by keeping its compatibility level to 80.

Recently, a client reported that someone (DBA or Application) is changing his database compatibility, which should remain compatible to SQL Server 2000 (compatibility level 80). He wants to know at what time this change is being made.

SQL Server 2008 and subsequent versions keep record of this compatibility change to its log, but SQL Server 2005 has no such facility. It means, in SQL Server 2005, you never know when someone has changed compatibility level.

In SQL Server 2008 and subsequent versions one can change compatibility level of a database by following simple TSql statement.

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 90;

But in SQL Server 2005, only method to change this compatability level is its system stored procedure i.e. sys.sp_dbcmptlevel. SQL Profiler is the only place where you can trace when this stored proecdure was executed. But what if, we need to place an alert for this change and generate a mail for this change. Or what if, we need to stop users/applications to change a database compatability level.

Only way to achieve this functionality is,  to update system stored procedure  sp_dbcmptlevel.

Lets perform this task, step by step.

Step 1:  Stop SQL Server 2005 services

Step 2:  Login using DAC (Dadicated Administrative Connection). For this right click on SQL Server 2005 service, on Advanced tab, change startup parameters by adding -m; at existing values.

Step 3: Start SQL Server 2005 services

Step 4: Open SQL Server Management Studio and open Database Engine Query

Step 5: Login as valid sysadmin user or ADMIN:InstanceName

Step 6: Change mssqlsystemresource database to read_write mode

Step 7: It’s the time to update our system stored procedure i.e. sp_dbcmptlevel. If you need to keep only comptability level to 80 or 90 then change following lines of stored procedures with same values i.e.80 or 90 or as per your choice.

select  @cmptlvl60 = 60, 
@cmptlvl60 = 65,
@cmptlvl60 = 70,
@cmptlvl60 = 80,
@cmptlvl60 = 90, 

And if you also need to add a mail alert for this change then add following code in error control portion of stored procedure.

DECLARE @bodyText VARCHAR(200)

SET @bodyText='User '
+ CONVERT(VARCHAR,SYSTEM_USER)  
+' trying to change Compatibility Level of Database '                            + CONVERT(VARCHAR,@dbname)
+ ' at '
 + CAST(GETDATE() AS VARCHAR(50)) 
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly 
@subject = 'Compatibility Level Change Alter', 
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly 
@body = @bodyText, 
@body_format = 'TEXT' ;

Here is complete updated script of stored procedure. (This script is only applicable to SQL Server 2005, for SQL Server 2008 and subsequent version, its totally different, which you can get by sp_helptext)

Step 8: Change mssqlsystemresource database to read_only mode

Step 9: Close SSMS session, stop SQL Server services and change its startup parameters back to normal.

Step 10: Start SQL Server Services and you are done.

SQL Server: Automatic Query Execution at Every Instance Startup

Though production database servers are design to stay up for 24x7, but still when ever these production database servers go down and restart, sometime we need to execute some queries automatically on every start-up, like clean some setup tables or capture some sort of necessary data which is only available at instance start-up.

For such queries which need to be executed automatically at every start-up, we have to create a store procedure to encapsulate all these queries. Then automatic execution of this stored procedure is achieved by using the sp_procoption system stored procedure.

(Note: Best place to store such stored procedure is MASTER database)

Let’s create a stored procedure to store instance start-up time in a log table.

USE MASTER

GO

--Create table to hold startup time

CREATE TABLE dbo.InstanceLog

(StartupTime DATETIME)

GO

--Create stored procedure to execute on startup automatically

CREATE PROCEDURE dbo.Proc_InsertStartupTime

AS

INSERT dbo.InstanceLog

SELECT GETDATE()

GO

Now we will use SP_PROCOPTION to tell SQL Server that we want to execute our stored procedure at every instance start-up. Syntax will be as follow:

EXEC SP_PROCOPTION

@ProcName = 'Proc_InsertStartupTime',

@OptionName = 'STARTUP',

@OptionValue = 'TRUE'

After executing above statement, when ever SQL Server instance will restart, stored procedure will be executed automatically and a new row in our log table dbo.InstanceLog will be inserted.

To revert this option and to stop stored procedure from automatic execution, we will use following syntax.

EXEC sp_procoption

@ProcName = 'Proc_InsertStartupTime',

@OptionName = 'STARTUP',

@OptionValue = 'OFF'

(Applicable for SQL Server 2005 and above versions)

A Quick Intorduction to tempdb

TempDB is one of system databases to manage the SQL Server instance. Following is a quick introduction to tempdb:

• SQL Server uses tempdb to store internal objects such as the intermediate results of a query
• tempdb does not persist after SQL Server shuts down
• Each time SQL Server restarts, tempdb is copied from the model database
• Only one file group in tempdb is allowed for data and one file group for logs
• Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts
• . In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute
• Auto shrink is not allowed for tempdb
• The database CHECKSUM option cannot be enabled.
• A database snapshot cannot be created on tempdb.
• DBCC CHECKALLOC and DBCC CHECKCATALOG are not supported.
• Only offline checking for DBCC CHECKTABLE is performed. This means that a TAB-S lock is needed. There are internal consistency checks that occur when tempdb is in use. If these checks fail, the user connection is broken and the tempdb space used by the connection is freed.

Which objects occupy tempdb space

The following types of objects can occupy tempdb space:

• Internal objects
• Version stores
• User objects

Internal objects are used:

• To store intermediate runs for sort.
• To store intermediate results for hash joins and hash aggregates.
• To store XML variables or other large object (LOB) data type variables. The LOB data type includes all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.
• By queries that need a spool to store intermediate results.
• By keyset cursors to store the keys.
• By static cursors to store a query result.
• By Service Broker to store messages in transit.
• By INSTEAD OF triggers to store data for internal processing.

Must remember that:

• Updates to internal objects do not generate log records
• Each internal object occupies at least nine pages (one IAM page and eight data pages) in tempdb

Version store

• Version stores are used to store row versions generated by transactions for features such as snapshot isolation, triggers, MARS (multiple active result sets), and online index build
• Inserts into the version store do not generate log records
• INSTEAD OF triggers do not generate versions

User objects

• Operations on user objects in tempdb are mostly logged.
• Bulk copy program (BCP), bulk insert, SELECT INTO, and index rebuild operations are bulk logged. This is exactly the same as in other databases with the recovery model set to simple

tempdb Space Requirements

It is difficult to estimate the tempdb space requirement for an application.

But it is recommend that you always have a safety factor of about 20% more space