Orphaned Locks – Idle Session – and XACT_ABORT

Sometimes you might see a situation where a session is holding locks, blocking other sessions for an extended period of time, but does not have an active request and appears to be Idle.  This can happen in a few different scenarios, but here we’ll outline one scenario and how being sure to use XACT_ABORT can…

0

Sequence Objects in SQL 2012 and SQL 2008

  Sequence Objects are new starting in SQL Server 2012.  They work similarly to an IDENTITY value, but where the IDENTITY value is scoped to a specific column in a specific table, the Sequence Object is scoped to the entire database and controlled by application code.  This can allow you to synchronize seed values across…

0

If a worker thread doesn’t yield, is it guaranteed to run?

  Put another way, can Windows preempt our worker thread and perform a context switch even though it has been “scheduled” by the SQL OS (SOS) and SQL thinks it is running?  Of course it can.  SQL Server implements a cooperative scheduling mechanism to make the most efficient use of the CPUs as it can. …

0

Do waiting or suspended tasks tie up a worker thread?

  I had a discussion the other day with someone about worker threads and their relation to tasks.  I thought a quick demo might be worthwhile.  When we have tasks that are waiting on a resource (whether it be a timer or a resource like a lock) we are tying up a worker thread.  A…

0

Finding Missing Dependencies

  While working with a customer on a SQL port, I came across a system view that was new with SQL Server 2008.  Have you ever needed to check your database code (stored procedures) to see if it references any missing objects?  As of SQL Server 2008 this is easy thanks to: sys.sql_expression_dependencies More can…

1

Loading data with foreign keys

  Working on a customer project the other day, I needed to load some data into a database that had been scripted and re-created.  The newly created database already had foreign keys in place which means for loading data (this was from SSIS), we had 2 options: 1) drop the foreign key constraints, load the…

2

Need to find the Database Version? Last Log Backup? Last DBCC execution? Ummm….

Backup software and Maintenance plans usually take care of tracking backup schedules and maintenance schedules for us… but how else do we find this information inside SQL Server?  The following query will get the last backup date and time from MSDB: select database_name, max(backup_finish_date) as ‘last backup’ from dbo.backupset group by database_name And the error…

0

Default Trace and System Health

So it’s been a little while since my last posting – actually a long while.  Since my last post some 8 months ago I’ve changed customers in my role as a PFE here at Microsoft and that has had me quite busy. I have a couple of posts I wanted to write to answer some…

1