Deadlocks – Querying Extended Events

Background The days of restarting SQL Service with trace flags 1204 or 1222 (MSDN Detecting and Ending Deadlocks) or being forced to start up a profiler trace are no more. Let’s face it. There are times when someone comes up…

Poll – How do you handle security?

MS SQL Server provides a wide range of options to choose from when deciding how to regulate security in our organizations? I’m curious how you handle it? Do you go full on best practice and run Windows Authentication only, allow…

Temporary Stored Procedures

We are all familiar with table variables, local temp tables, and global temp tables but what some people over look is that stored procedures and functions can also utilize tempdb. I imagine that the reason for this oversight is because…

Optimizing the BACKUP Command

One challenge for a DBA is to setup the appropriate backup plans for the databases under our responsibility. It is easy to use SSMS’s maintenance plans to get the job done or even just write a simple BACKUP DATABASE command…

Bug: INSERTing into a MySQL Database via Linked Server

Recently I found myself involved in a project where a tab delimited text file (updated daily) was being imported into a MS SQL Server 2008 R2 database and then the single imported table was manipulated into a handful of others…

Bug: Local View + Synonym + Remote Query

Recently I came across an interesting bug when upgrading a SQL Server 2000 box to 2008 R2. With SQL Server 2005, 2008, and 2008 R2, there is a situation where you are calling a local view that is also using…

Detach and Attach User Databases

The Need I’ve found myself, more times than once, needing to change disk drives that my user databases are found on. This could come up because you are moving a server and need to switch SANs, migrate from local storage…

SQL Server 2008 R2 – Service Pack 2

Quick Announcement: I just wanted to throw a quick announcement out that SQL Server 2008 R2 SP2 is available. Service Pack 2 includes Cumulative Updates 1 through 5 from SQL Server 2008 R2 SP1 and is registered as version # 10.50.4000.0….

How to: Transfer Logins to a New Server

Often enough we encounter a situation where we need security to be migrated. Microsoft hasn’t made this task as easy as moving other things such as SQL Agent jobs since restoring the master database to another server is tricky at…