Wednesday, 21 December 2011

My Trip to Delhi for MS Certification Training

I was made redundant/laid off in November. So decided to make something of it rather than wallow in sorrow (as it's the second time in as many years that this has happened). I had found a training centre in India almost a year ago, Koenig Solutions, and was angling to get my employer to split the cost of sending me out for training. Obviously, that was not to be. So when I got the sad news I decided to book a course for myself.

I was a little worried about the standards even though I had read good things about the company. You never know whether testimonials are genuine or not. But the price was good, even with the flights, and they provided everything: accommodation; full board; etc.

Next thing I know I've paid the deposit on the SQL Server 2008 DBA MCITP course, gotten a visa to India and booked my flights! No turning back. In the end everything was pretty much as promised. And the proof is in the pudding: I passed my certification and am that much more prepared for the dreaded job hunt in the new year.

Below are a few (understatement) pictures from my trip.

Sunday, 11 December 2011

Logon Triggers

I just learned of a nice feature, Logon Triggers. A nice example of what they can do is limit a specific user to a certain number, 3 for example, concurrent logons. A nice little example of how to do this is as follows: Let's say we have a login name of login_test.

The interesting feature of this type of trigger is that the server authenticates the login but BEFORE the user is connected to the server or database the trigger is fired and the appropriate action is taken.

See the full explanation of logon triggers at

There's also a good explanation of how these triggers relate to trace events and event notifications.

See my other post detailing a good use for such a trigger.

Friday, 23 September 2011

Drop Table Loop

I often, for various reasons, need to import data from external sources. Most of the time I import into tables on a staging database before further manipulating the data.

What I then tend to find after a few months is a very large and cluttered database. So below is a simple little query that creates a temp table of objects that then get dropped via a while loop. Nothing fancy, just a small time saver.

Tuesday, 8 March 2011

Quick Cheat to Shrink Database Log Files

This is a quick way to reduce the size of a database log file. As a caveat to beginners, this should not be used on mission critical db systems. It is intended as a quick and dirty fix for non-essential systems.

For those not familiar with the Attach and Detach commands:
--You need to make sure the database is not being used by anyone else/any other process. That is why there is a set single_user statement.
--If your database has multiple files/filegroups, you need only specify the primary file in the create statement (if you haven't made any file modifications after detaching).
-- You may need to enable xp_cmdshell therefore I've included the sp_configure statements.