Haunting a Database Near You

Comments: 7 Comments
Published on: October 31, 2011

Today, we have a special Halloween edition.  For me, Halloween and computer geek go quite well together.  And thinking about it, I wanted to try to better understand if there was a correlation.  As a DBA, have you wondered the same thing?

Well, I have a short list of five things that may help you to correlate your affinity for Halloween with your love for Databases.


Did you know that a tombstone is a legitimate thing in SQL Server?

Tombstones are replica related.  They are deleted items in the replica and are used to make sure the deleted item doesn’t get put back in the replica inadvertently.

You can read a lot more about tombstones from the msdn article here.

Tombstones are not unique to SQL Server.  These are commonplace in Active Directory as well.



Not all rows that are deleted move on to the afterlife quickly like they should.  Some like to hang around due to unfinished business.

The unfinished business in this case is the server running a cleanup thread.  This has to be done when the server is not too busy and has enough free resources to help these records move on to the afterlife.

You can see the evidence of these ghosts with specialized equipment.  By the use of a DMO, we can see the ghost record count on a per index basis.  The DMO is sys.dm_db_index_physical_stats.  Take a look at the ghost_record_count column in the returned record set.

With more specialized equipment, the engine takes care of the cleanup and removal of these ghosts.  Here is an in-depth foray into the world of SQL ghost hunting.  Whatever you do, don’t cross the streams.


It’s alive!!

No, I killed it!!

It can’t be…How is it still alive?

The transaction will not commit and may get rolled back.  The zombie has reared its’ ugly head.  A transaction that cannot commit but keeps going (or rolls back) due to an unrecoverable error is a zombie transaction.

From MSDN, here is a more specific definition of a zombie.

Rowsets can become zombies if the internal resource on which they depend goes away because a transaction aborts.


The spawn of SQL server is not so much like the Spawn character of the comics.  Nor is it much like the spawn shown to the right.

That is unless it is not managed very well.  SQL can spawn multiple threads if the optimizer deems it necessary for a query.  This is also known as parallelism.

Parallelism can be a good thing or it can be a bad thing.  Understanding it can help keep it on the good side.  You might want to check out some of Paul White’s articles on the topic.

Children of the Corn

Well, this one is not really something in SQL server.  That said, every time I think of orphaned users in SQL server – children of the corn comes to mind.

An orphaned user is one in which the login SID does not match for one reason or another.  This makes it so that the user can no longer log in to SQL server.

If you don’t know about these kids, they can really make for a frustrating day.  Read more here.


I have just covered five things in SQL server that correlate quite closely to Halloween.  But this by no means is an exhaustive list.  For instance, an obvious correlation is the “KILL” command.  Another good one is the monster known as the Blob (read more about that monster here and here).

With the opportunity to have Halloween every day, it’s no wonder I like being a DBA.

Happy Halloween

7 Comments - Leave a comment
  1. WayneS says:

    Now I’m beginning to understand you… and that’s as scary as your blog post!

  2. gbn says:

    What about ye olde classic

  3. Jason Brimhall says:

    FTR – the Classic Halloween problem is: http://en.wikipedia.org/wiki/Halloween_Problem

    For some reason it is not showing after approving that comment.

  4. […] year I try to have something for the Halloween Holiday such as this one or this one.  I failed to provide something in 2012, and this is getting back on the right […]

  5. […] year I try to have something for the Halloween Holiday such as thisone or this one.  I failed to provide something in 2012, and this is getting back on the right […]

  6. […] Haunting a Database Near You […]

Leave a comment

Your email address will not be published. Required fields are marked *

October 2011
« Sep   Nov »


  • @SQLSoldier: @pshore73 @AngryPets About any 3rd party VSS backup tool: handles backups great. Opinion deviates if you ever need to do restores. #sqlhelp
  • @AngryPets: @pshore73 #sqlhelp Also, the backups veeam created were... crap. (I got called in after 24+ hours of down time and gobs of lost data.)
  • @AngryPets: @pshore73 #sqlhelp Arguably, that was operator error - but any TOOL that would do VSS backups and trim log like that is a FAIL in my book.
  • @AngryPets: @pshore73 #sqlhelp NOT a fan. Had someone call me after they lost 12+ hours of data because veeam did: BACKUP LOG dbName TO DISK = 'NUL'
  • @pshore73: #SQLHelp Any thoughts on Veeam as a tool for production backup & restore of SQL, including performance impacts
  • @paschott: SSIS "Could not load file or assembly" w/ SurveyMonkey DLL. Any ideas? Doesn't seem to be GACable so not sure what next. #sqlhelp #ssishelp
  • @wendy_dance: @hus_sid432 Have you looked at the job history? You can also check in the SSISDB execution logs. #sqlhelp
  • @hus_sid432: Agent job set up as ssis package taking > 12 hrs as suppose to 1hr. How can I troubleshoot #sqlhelp
  • @wendy_dance: @willmeier #sqlhelp Sounds like you need to reinit that subscription. Something may have blocked it, or it only partially completed
  • @mexicanrooster: @whoisyarad I read the next book, highly recommended: Querying Microsoft SQL Server 2012. It covers everything you need. #sqlhelp

Welcome , today is Tuesday, December 1, 2015