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)
(inner DML statements)
(inner error capture statements)
(outer DDL statements)
(outer DML statements)
while @@trancount > 0 commit transaction
(outer error capture statements)
while @@trancount > 0 rollback transaction
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 …
sp_configure 'remote admin connections', 1
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.
Here is a handy command to run in a Windows command prompt. I use it for housekeeping space issues.
DIR /O-S /A-D /S >> DirBySize.txt
Which lists all the files on a drive – with the biggest first.
To setup a local copy of SQL Server 2012 Books-On-Line (BOL) on Windows 2012 I downloaded it from here … http://www.microsoft.com/en-us/download/details.aspx?id=347 … 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.
The Text color (Teal) and Comment color (Dark Green) are rather similar to my eyes. I much prefer to change the former to black … Tools / Options / Environment / Fonts and Colors / Identifier / Black / Ok.
UPDATE: I changed ‘SQL System Table’ to black also.
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 🙂