T-SQL Tuesday #53 – Work Hard, Play Hard, Joke Hard

Comments: 2 Comments
Published on: April 8, 2014


It is April and April Fools has only just begun.  Well, or so Matt Velic (blog | twitter) would have us believe.

Matt decided that this month for TSQL Tuesday, he would pull out all stops to help us break out the inner prankster in ourselves.

You can read all about it from his invitation here.

Reading the invitation made me immediately flash to a couple of recent possibilities or things that maybe others had done.

For instance, I thought about the April Fools post I did about Backups in SQL 2014.  Mix a little truth and a splash of fun and you have a believable April Fools blog post.  You can read that post here.

Then I thought momentarily on a great post by Paul Randal for April Fools.  Paul talked about a great prank that could be pulled on some co-workers and it would really get them in a frenzy.  You could read about his Day 0 checksum issue here.

Then I flashed to something a friend tried to pull on me.  He sent me a script to the following tune.

SELECT  'principal name' = p.NAME ,
        'principal creation date' = p.create_date ,
        'password' = ca.passwordcrack ,
        'instance sid' = p.sid,
        'user type' = p.type
FROM    master.sys.server_principals p
        CROSS APPLY ( SELECT    p1.principal_id ,
                                CAST(0x596F75207468696E6B2070617373776F726473206172652074686174206561737920746F206465637279707420696E2053514C205365727665723F2E2E2E417072696C20466F6F6C277321 AS VARCHAR(1023))
                                + ISNULL(CONVERT(VARCHAR(10), NULLIF(POWER(p1.principal_id,
                                                              0), 1)), ' ')
                      FROM      sys.server_principals p1
                    ) ca ( principal_id, passwordcrack )
WHERE   p.type LIKE '[GUS]'
        AND ca.principal_id = p.principal_id

For the seasoned DBA, the joke in this one is easy to spot.  But it will still catch some people and it could provide a good laugh.

But my favorite piece of seriousness to parley in the workplace comes from this gem.


This gem from our friends at ThinkGeek®, can provide several minutes of hard laughter.  You plug this into an USB port that is not very visible and then camp out and watch for the fun to begin.  If they are typing in SSMS, you could end up with some real fun (random key strokes inserted into keywords etc).

Whatever you do, please do not attempt this with somebody who will be connecting to a Production instance.

SQLSaturday Vegas Style

Comments: No Comments
Published on: April 3, 2014


We are mere moments from the inaugural SQL Saturday (announced a few short months ago) event in fabulous Las Vegas, Nevada.  Can you feel the excitement building?

The SQLSat 295 team has been working hard to bring together what we think will be a great event.  From the volunteers, to the speakers, to the vendors, and most importantly to the attendees.


If you are in Vegas or nearby, we welcome you to come down and check out what we have for you.

This event will be held Apr 5 2014 at The InNEVation Center, 6795 Edmond St., Las Vegas, NV 89118.

Where else do you get an open invitation to learn about SQL Server for free combined with what Vegas has to offer for entertainment?

Just remember, what is learned in Vegas doesn’t have to stay in Vegas.  But what happens in Vegas is up to your discretion.


New Backup Behavior in SQL 2014

Comments: 1 Comment
Published on: April 1, 2014

As has been well publicized, today is the official Release To Manufacturing date for SQL Server 2014.  You can read more about all of that here.

Something that hasn’t received much word is a new feature that is a game changer.  I’m not referring to the advancements with the In-Memory OLTP (aka Hekaton).  The real game changer in my opinion is the way backups will be treated in 2014.

encryptionSQL Server 2014 brings the added functionality of encryption to the database backups.  This is a significant improvement to securing data at rest.  This functionality applies to databases that have been TDE enabled as well as those that are not TDE enabled.  This functionality also applies to backups that are compressed and backups that are not compressed.

The beauty of this functionality is that all backups will be encrypted now by default.  What this means is that you need not configure anything on your end to make it happen.  Despite it being enabled by default, you can change the encryption method should you choose.

Another interesting note with this new default behavior is that all of your database backups will fail out of the box.  You might ask why.  Well, there are some pre-requisites that must be met in order for the encrypted backup to succeed.

Here are those pre-reqs.

  1. Create a Database Master Key for the master database.
  2. Create a certificate or asymmetric Key to use for backup encryption.

If you have not created your DMK, your backups will fail and you will be none the wiser until you try to restore that backup.  That is really the way you want to conduct your duties as a DBA, right?  You guessed it, the backup shows that it succeeds yet it does nothing.

As you move forward with your SQL 2014 implementation, ensure you create those DMKs and ensure your backups are safe.

Oh and in case you haven’t noticed, pay attention to today’s date.


Table Space updated again

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 28, 2014

Today we have another update for an age old script on this site.  You can find the last update to the script here.

This time we have a bit of a bug update.  The details of that bug are in the notes for the script.

To Evaluate table sizes combined with index space consumption to determine higher cost tables in 
terms of storage, resources and maintenance needs.
ModifiedDate	ModifiedBy	Description
2013-11-21		JB			Tables without Indexes had a Null Value in the Output
							Fixed the output to sum properly for the NULLS in absence of an index
DECLARE @dbsize DECIMAL(19,2)
        ,@logsize DECIMAL(19,2)
**  Summary data.
        SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) / 128.0
                , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) / 128.0
                FROM sys.database_files
        WITH FirstPass AS (
                SELECT OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0,
                        PageCnt = SUM(
                                WHEN (index_id < 2)
                                        THEN (used_page_count)
                                ELSE lob_used_page_count + row_overflow_used_page_count
                        )) * 8/1024,
                        iPageCnt = SUM(
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                        )) * 8/1024,
                        RowCnt = SUM(
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id < 2
                GROUP BY OBJECT_ID
        ),IndexPass AS (
                SELECT OBJECT_ID,
                        iReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,iUsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                        ,iPageCnt = SUM(
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                        )) / 128.0
                        ,RowCnt = SUM(
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1 
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id > 1
                GROUP BY OBJECT_ID
        ),InternalTables AS (
                SELECT ps.OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                FROM sys.dm_db_partition_stats  ps
                        INNER Join sys.internal_tables it
                                ON it.OBJECT_ID = ps.OBJECT_ID
                                And it.internal_type IN (202,204,211,212,213,214,215,216)
                WHERE it.parent_id = ps.OBJECT_ID
					--And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
                GROUP BY ps.OBJECT_ID
        ),Summary AS (
                        OBJECT_NAME (F.OBJECT_ID) AS ObjName 
                        ,SCHEMA_NAME(o.schema_id) AS SchemaName
                        ,CASE WHEN OBJECTPROPERTY(F.OBJECT_ID,'IsMSShipped') = 1 
							THEN 'YES' 
							ELSE 'NO' 
							END AS IsMsShipped
                        ,NumRows = MAX(F.RowCnt)
                        ,ReservedPageMB = SUM(ISNULL(F.ReservedPage,0) + ISNULL(i.ReservedPage,0))
                        ,DataSizeMB = SUM(F.PageCnt)
                        --,IndexSizeMB = SUM(CASE WHEN (F.UsedPage + ISNULL(i.UsedPage,0)) > F.PageCnt
                        --                                THEN ((F.UsedPage + ISNULL(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
						,IndexSizeMB = SUM(ISNULL(ip.iPageCnt,0))
                        ,UnusedSpace = SUM(CASE WHEN (F.ReservedPage + ISNULL(i.ReservedPage,0)) > (F.UsedPage + ISNULL(i.UsedPage,0))
                                THEN ((F.ReservedPage + ISNULL(i.ReservedPage,0)) - (F.UsedPage + ISNULL(i.UsedPage,0))) ELSE 0 END)
                                + (SUM(ISNULL(ip.iReservedPage,0)) - SUM(ISNULL(ip.iUsedPage,0)))
                        ,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))
                        ,dbsizeMB = @dbsize
                        ,LogSizeMB = @logsize
                FROM FirstPass F
					INNER JOIN sys.objects o
					LEFT Outer Join InternalTables i
                        ON i.OBJECT_ID = F.OBJECT_ID
                    LEFT OUTER JOIN IndexPass ip
                GROUP BY F.OBJECT_ID,o.schema_id
        ),TotalUnused AS (
				SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace
						,SUM(ISNULL(reservedpageMB,0))+SUM(ISNULL(IndexReservedMB,0)) AS Usedr
					FROM Summary
        SELECT ObjName,SchemaName,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) AS DataSizeMB, ISNULL(IndexSizeMB,0) AS IndexSizeMB
						,ISNULL(S.UnusedSpace,0) AS UnusedSpace, dbsizeMB, LogSizeMB
						,TU.UnusedSpace AS TotalTableFreeSpace 
                        ,dbsizeMB - TU.Usedr AS DataFileFreeSpace  
                        /*within 1.5gb on a 1.76tb database or .000085% variance or 99.999915% accuracy */
                        ,PercentofDBPhysFile = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / @dbsize) * 100
                        ,PercentofDBUsedSpace = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / (@dbsize - TU.UnusedSpace)) * 100
        FROM Summary S
			CROSS APPLY TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC

T-SQL Tuesday #051: Bets and Results

Comments: 2 Comments
Published on: February 18, 2014


The line for this months TSQL Tuesday required wagers be made concerning the risks and bets that have either been made or not made.

At close, we saw 17 people step up and place remarkable markers.  Today, we will recap the game and let you know who the overall winner from this week of game play in Vegas just happened to be.



This is about some bets, so we needed to understand some of the hands that might have won, right?

Let’s see the hands dealt to each of our players this past week.

Andy GalbraithAndy Galbraith (b|t) shared a full house of risk this month when talking about backups.  Do you have a backup if you haven’t tested it.

“without regular test restores, your backups do not provide much of a guarantee of recoverability.  (Even successful test restores don’t 100% guarantee recoverability, but it’s much closer to 100%).”


Boris HristovBoris Hristov (b|t) thought he was feeling lucky.  He couldn’t imagine things getting worse.  He even kept reminding himself that it couldn’t get worse.  He was dealt a hand and it was pretty good – and then everything just flushed down the drain.

A disaster with replication and with the storage system – ouch!


Chris YatesChris Yates (b|t) wanted to push his hand a little further than Andy this week.  Chris went all-in on his backups.  At least he went all-in early in his career.

The gamble you ask?  Chris didn’t test the backups until after he learned an important lesson.

“I’ve always been taught to work hard and hone your skill set; for me backups fall right into that line of thinking. Always keep improving, learn from your mistakes.”


Doug PurnellDoug Purnell (b|t) shares another risky move to make.  In this hand, Doug thought he could parlay maintenance plans into an enterprise level backup solution.

What Doug learned is that maintenance plans don’t offer a checksum for your backups.  After learning that, he decided to stay and get things straight.


Jason BrimhallJason Brimhall (b|t) took a different approach.  I took the approach of how these career gambles may or may not impact home, family, health, and career in general.

There is a life balance to be sought and gained.  It shouldn’t be all about work all the time.  And if work is causing health problems, then it is time for a change.

It’s important to have good health and enjoy life away from work.


Jeffrey VerheulJeffrey Verheul (b|t) had multiple hands that many of us have probably seen.  I’d bet we would even be able to easily relate.

In the end, what stuck with me was how more than once we saw Jeffrey up the ante with a story of somebody who was not playing with a full deck.  If you don’t have a full deck, sometimes the best hand is not a very good one overall.


Joey D'Antoni

Joey D’Antoni (b|t) had a nightmare experience that he shared.  We have all seen too many employers like what he described.

The short of it is summed up really will by Joey.

“The moral of this story, is to think about your life ahead of your firms. The job market is great for data pros—if you are unhappy, leave.”


K. Brian KelleyK. Brian Kelley (b|t) brought us the first four of a kind.  Not only did he risk life and limb with SQL 7, but he tried to do it over a WAN link that was out of his control.

When he bets, he bets BIG!  DTS failures, WAN failures, SQL 7, SQL 2000, low bandwidth and somebody playing with the nobs and shutting down the WAN links while laughing devishly at the frustration they were causing.


Kenneth FisherKenneth Fisher (b) thought he would try to one-up Jeffery by getting employers that would not play with a full deck either.

From one POS time tracking system to another POS time tracking system to yet another.  Apparently, time tracking was doomed to failure and isn’t really that important.

That seems to be a lot of hefty wagers somebody is willing to lay down.


Matt VelicMatt Velic (b|t) brought his A-game.  He was in a no prisoner kind of mood.

Matt decided he was going to real you in, divert your attention, and then lay down the wood hard.  Don’t try to get anything past Matt – especially if it wreaks of shifty and illegal.

The way he parlayed his wagers this month was a riot.


Mickey StueweMickey Stuewe (b|t) was the only person willing to Double-down and to even try to place a bet on snake-eyes.  With the two-pronged attack at doubles, she was able to come up with two pairs.

To compound her doubles kind of wagers, she was laying down markers on functions.  Check out her casino wizardry with her display of code and execution plans.


Rob FarleyRob Farley (b|t) was a victim of his own early success.  He had a lucky run and then it seemed to peter out a bit.  In the end he was able to manage an Azure high hand

Rob reminds us of some very important things with his post.  You can get lucky every now and again and be successful without a whole lot of foresight.  Be careful and try to plan and test for the what-if moment.


Bobby TablesRobert Pearl (b|t) rolled the dice in this card game.  He was hoping for a pair of kings with his pair of clusters and the planned but unplanned upgrade.

There is nothing like a last minute decision to upgrade an “active-active” cluster.  In the end Bobby Tables had an Ace up his sleeve and was able to pull it out for this sweet pair.


Russ Thomas

Russ Thomas (b|t) ever have the business buy some software and then thrust it on IT to have it installed last minute?

That is almost what happened in this story that had some interesting yet eventual results.

Russ weaves the story very well, but take your eye of the game at hand!!


Sebastian Meine


Sebastian Meine (b|t) brought needles to the table.  That is wicked crazy and leaves quite the impression.

Maybe he thought he was going to inject some cards into the game to improve his hand.  I was almost certain he had nothing going, but magically he was able to produce some favorable data.

Oh, that was the point of his post!  Have a weakness? It will be found, injected and exploited.


Steve Jones

Steve Jones (b|t) had a crazy house going.  Imagine 2000 or so people all trying to help you make your bets and play your hand.  That is a FULL house.

Of course, his full house was more to deal with a misunderstood risk with the application and causing performance problems in the database.

In the end, they fixed it and it started working better.  A little testing would have gone a long way on this one!


Wayne SheffieldWayne Sheffield (b|t) in perhaps the most disappointing and surprising turn of events, Wayne ended up with a hand that could have won but he folded.

Well, Wayne didn’t fold but there were some bets that resulted in people folding and maybe worse in the story that Wayne shares.  This can happen when you are betting on something you know nothing about and really should get somebody to help make the correct bets for you.



And to recap, the overall winner was…

the HOUSE.  With a winning hand of a royal flush.

Thanks to all of the SQLFamily for participating this month.  There were some really great experiences shared.  The posts were great and it was a lot of fun.  I hope you got as much enjoyment out of the topic and articles this month as I did.

«page 1 of 76

April 2014
« Mar    


Welcome , today is Friday, April 25, 2014