Try/Catch and Transactions

Try/Catch-blocks and Transaction-Blocks really don’t play nice together. Within nests for example an outer @@ROLLBACK TRANSACTION will rollback both inner and outer transaction-blocks regardless of already executed COMMIT TRANSACTION statements.

This is the most robust template I could come up with for nested Try/Catch-blocks that contain Transactions.

(inner DDL statements)
begin transaction
(inner DML statements)
commit transaction

(inner error capture statements)
rollback transaction
(outer DDL statements)
begin transaction
(outer DML statements)
while @@trancount > 0 commit transaction

(outer error capture statements)
while @@trancount > 0 rollback transaction

Configuring DAC

There is another definition of DAC in SQL Server – but this one is about the emergency DBA connection method, used when the server it too busy to get onto any other way.

DAC can be a life saver but has to be configured in advance. Luckily its a two minute job and I do it on all Production servers. and here’s how …

– Right click on server-name (in SSMS Object explorer), and choose facets.
– when the Facets box pops up select Surface Area Configuration in the top box.
– in the lower box set RemoteDacEnabled to True, and save with OK.

** TO USE IT **

– startup SSMS and ensure nothing is connected.
– click on “New Query”
– in the “Connect to Server” box prefix the Server Name with “admin: ”
– complete Authentication and click “Connect”

You should now be connected within a minimal safe-mode type environment. That is, with a blank Object Explorer pane.

** UPDATE 05 Mar 2015 **

To enable DAC on multiple servers (2005 and above) programmatically (via Registered Servers) use this …

Use master
sp_configure 'remote admin connections', 1

Check a whole server for database corruption

To check every database on a sql-2005 server for corruption (before an upgrade) I created a job with one step containing this code …

exec sp_msforeachdb 'DBCC CHECKDB(''?'')  WITH NO_INFOMSGS'

… then in the advanced tab I created an output file d:\checkdb.log.

A few hours after I’d manually started the job I was happy to see the output file was empty.

SQL Server 2012 Books-On-Line

To setup a local copy of SQL Server 2012 Books-On-Line (BOL) on Windows 2012 I downloaded it from here … … unpacked it to the default location and noted it. Then in SSMS I chose Help / Manage Help Settings / Choose online or local help / I want to use local help / Install contents from disk, and browsed within the unpack location for the HelpContentSetup.msha file. I clicked the three ADD links within the pop-up box. And found BOL by clicking Help / View help within SSMS.

Using OUTPUT to simplify the auditing of data changes.

Here’s an example of using OUTPUT to simplify the recording of updates etc …

-- create the audit log
create table audit_table (fname varchar(50), colour varchar(50), 
old_tasty int, changed smalldatetime, new_tasty int)

-- create and populate the fruit table
create table fruit (fname varchar(50),colour varchar(50),tasty int)
insert into fruit values ('banana','yellow',6),('kiwi','green',3),('gooseberry','green',2)

-- change score to 'yuk' for any green fruits
update fruit set tasty = 1
output deleted.*, getdate(), inserted.tasty into audit_table
where colour = 'green'

-- check audit log
select * from audit_table

… and another nail in the coffin of Triggers 🙂