Catching Up on The #SQLPASS Virtual Chapters

(Didn't realize until I was posting that this was my 100th post...whew!)

--

A few months ago we purchased a new "smart" Blu-Ray player and one of its intriguing features was its built-in WiFi with pre-installed Internet apps like Amazon Video, YouTube, Vudu, etc. (Previously we had a 6-year-old "dumb" DVD player that was barely smart enough to eject its tray when you pushed the button).

https://i.ytimg.com/vi/4pvHKG4VMDs/maxresdefault.jpg

Interestingly, the limitations of these pre-installed apps became one of my frustrations as we started to use the player more and more.  I wanted to be able to view Pluralsight videos and TED Talks, and my wife wanted to watch Craftsy videos, and lacking customized applications we couldn't do so.

http://www.famousmarketer.com/images/but_wait.jpg

After some searching on my laptop I found that TED Talks are available on YouTube!  Some more digging and I found a few PASS videos as well - and more intensive searching found that almost every single PASS Virtual Chapter has its own YouTube channel where they upload recordings of their presentations!

Add captionhttp://www.sqlpass.org/images/chapterlogos/logo_pass_vc.png

Below I have lifted the list of PASS Virtual Chapters (current as of May 2016) and the descriptions provided by PASS.  I have gone through YouTube and found the link for each chapter's YouTube channel and listed them below.

(I apologize to any international readers but I have omitted the non-English speaking chapters as I don't have the language skills to properly search for their content.)

Chapter Name	Short Description	Virtual Chapter URL	YouTube Recordings
Application Development	Training and information for Application Developers	http://appdev.sqlpass.org/	https://www.youtube.com/user/PASSappdev
Big Data	Discuss Big Data technologies & Hadoop-based systems	http://bigdata.sqlpass.org/	https://www.youtube.com/channel/UCkOKmMW_LEsACOqE8C1RWdw
Business Analytics	The PASS Business Analytics Chapter provides virtual train...	http://bavc.sqlpass.org/	https://www.youtube.com/user/PASSBAVC
Business Intelligence	Connecting BI Professionals globally	http://bi.sqlpass.org/	https://www.youtube.com/user/PASSBIVC
Cloud	Enabling cloud knowledge sharing	http://cloud.sqlpass.org/	https://www.youtube.com/channel/UCbM9EKPyh6G6F4gK_YOaC8Q
Data Architecture	Focusing on your data architecture concerns	http://dataarch.sqlpass.org/	https://www.youtube.com/channel/UCoYaPVyNHzpt2cT6ZuO5APw
Data Science	Advanced Analytics, Machine Learning, Data Mining	http://datascience.sqlpass.org/	https://www.youtube.com/channel/UCqB3xWdwjA9soFV6EOu7qfg
Database Administration	Forum for discussion on DBA topics	http://dba.sqlpass.org/	https://www.youtube.com/channel/UCN-UYSZ3yERcsNVOcMGlrgg
DBA Fundamentals	Rock solid foundations!	http://fundamentals.sqlpass.org/	https://www.youtube.com/channel/UCfo-suRFU3sBL5eOOv4XOVg
Excel Business Intelligence	Helping users achieve excellence in Excel	http://excelbivc.sqlpass.org/	https://www.youtube.com/channel/UCCXBjWCPexQTTGs-eChtmYA
Healthcare	Connecting SQL Server Pros in the healthcare industry	http://healthcare.sqlpass.org/	https://www.youtube.com/channel/UCcLgVdylvd1Xonb6zoaqeWw
High Availability and Disaster Recovery	Reduce the risk and impact of system faults and outages	http://hadrvc.sqlpass.org/	https://www.youtube.com/channel/UCVXM_bmBuTEln3K4GHeuBAQ
Hybrid	Cover all technologies and solutions that may be integrate...	http://oracle.sqlpass.org/	N/A
In Memory VC	Take advantage of the new In-Memory features	http://imvc.sqlpass.org/	https://www.youtube.com/channel/UCTHLNg5xR_-2YJWTaJoqI2Q
Performance	Discuss SQL Server performance-related content	http://performance.sqlpass.org/	https://www.youtube.com/channel/UCvLXc2p2itK-Na0ndF7CxBg
PowerShell	Learn and share best practices around PowerShell	http://powershell.sqlpass.org/	https://www.youtube.com/channel/UCFX97evt_7Akx_R9ovfiSwQ
Professional Development	Join the conversation and share resources	http://professionaldevelopment.sqlpass.org/	https://www.youtube.com/channel/UCDP2iCgt7rRu6zyvxwz4uug
Saturday Night SQL	Forum for discussion about BI and Databases	http://saturdaynightsql.sqlpass.org/	https://www.youtube.com/channel/UCBWJHarYauw_-Wpv90cdGOQ
Security	Guidance and education on SQL Server security topics	http://security.sqlpass.org/	https://www.youtube.com/channel/UC77XLfCsUex0Rvt4M22xYig
Virtualization	Improving management of SQL Server in virtual environments	http://virtualization.sqlpass.org/	https://www.youtube.com/channel/UCbX5pO6ligM2gyoQZW9i3Nw
Women in Technology	Forum for discussion of issues pertinent to WIT	http://wit.sqlpass.org/	https://www.youtube.com/channel/UCe2KECKcaYV37kDarDLhL6A

What I like to do is to "subscribe" to each of the relevant channels and that makes it easy for me to receive content on our Blu-Ray player (as well as on the YouTube app on my Android).

The Virtual Chapter websites listed above do have archives of the recording of their meetings as well, but they aren't centralized and they are only available from a regular web browser as opposed to a custom app on your TV or phone.

There are also several other useful  YouTube channels I recommend:

• TED Talks - https://www.youtube.com/user/TEDtalksDirector

• TEDx Talks (from local TEDx events) - https://www.youtube.com/user/TEDxTalks

• SQLSentry (vendor tools but also a *lot* of non-brand SQL material) - https://www.youtube.com/user/SQLSentry

• Brent Ozar Unlimited (Weekly "Office Hours" and other training videos) - https://www.youtube.com/user/BrentOzar

• PASSTV (official PASS functions such as videos from Summit) - https://www.youtube.com/user/SQLPASSTV

• TechEd North America (Microsoft's admin/architecture conference) - https://www.youtube.com/channel/UC-B8GuZPEnvFGiSKpjIVKKg

• Ntirety (my employer) - https://www.youtube.com/channel/UCC42FtgrqDGBU27WKqYqwvg

• HOSTING (our parent company) - https://www.youtube.com/channel/UCavSm--VAVf33w5rn5gQ6mg

--

Can't beat free info - it's free, and it's info!

http://blogs.opentext.com/wp-content/uploads/2016.01-Iheartfreestuff-new-660px.jpg

--

Hope this helps!

Upcoming SQL Saturdays - June 2016

I am speaking at two different SQL Saturdays in June!

--

First up on June 4th is SQL Saturday #517 Philadelphia (#SQLSAT517) - I am speaking at 2:45pm on "Getting Started with Extended Events."  This is quickly becoming my favorite of the talks that I give because I learn something new every time I present it:

Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events.  Many DBA's continue to use Profiler and SQL Trace even though they have been deprecated for years.  Why is this? 

Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them.  Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace. 

Come learn how to get started - the basics of sessions, events, actions, targets, packages, and more.  We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way.  You may never go back to Profiler again!

Register for SQL Saturday Philadelphia now (before it goes to wait-list!) at https://www.sqlsaturday.com/517/registernow.aspx!

I am also excited about this event because I will be attending Karen Lopez's (blog/@Datachick) Friday pre-con "Model-Driven Database Development: Physical to Implementation" - Karen is a very engaging and entertaining speaker who really knows her stuff.  There are still tickets available at https://www.eventbrite.com/e/model-driven-database-development-physical-to-implementation-karen-lopez-tickets-24628652964! 

 --

The following Saturday June 11th is SQL Saturday #523 - Iowa City (#SQLSatIowa) - I am up at 10am with "Does it Hurt When I Do This? Performing a SQL Server Health Check":

How often do you review your SQL Servers for basic security, maintenance, and performance issues?  Many of the servers I "inherit" as a managed services provider have quite a few gaping holes. It is not unusual to find databases that are never backed up, servers with constant login failures (is it an attack or a bad connection string?), and servers that need more RAM/CPU/etc. (or sometimes that even have too much!)  

Come learn how to use freely available tools from multiple layers of the SQL Server stack to check your servers for basic issues like missing backups and CheckDB as well as for more advanced issues like page life expectancy problems and improper indexing. If you are responsible in any way for a Microsoft SQL Server (DBA, Windows Admin, even a Developer) you will see value in this session!

This talk covers a wide array of tools to check out your servers, including the awesome DMV scripts from Glenn Berry (blog/@GlennAlanBerry) of SQLskills.

Register for SQL Saturday Iowa City now at https://www.sqlsaturday.com/523/registernow.aspx!

I am not headed to any pre-cons in Iowa City but there is an impressive line-up from many top speakers including my buddy David Klee (blog/@kleegeek) - he is covering "SQL Server Infrastructure Tuning for Availability and Performance" - tickets for his (and the other) pre-cons are available at http://www.eventbrite.com/e/sqlsaturday-iowa-city-2016-pre-conference-sessions-registration-24792389705?ref=ebtn

--

I have written multiple times about how amazing SQL Saturdays are as both training and networking opportunities.  If you are in the area either of these weekends come see me and check it out!

--

Hope this helps!

SQL 2016 - The Time Has Come!

Microsoft dropped it on us this morning:

Get ready, SQL Server 2016 coming on June 1st

https://media.makeameme.org/created/yes-finally.jpg

I read the article and was pleasantly surprised, although after some consideration it was more like this:

https://i.imgflip.com/4hu4p.jpg

If you haven't already started working with SQL Server 2016 this is definitely the time.  Here are some suggestions:

• Get a copy of SQL Server Developer Edition - Microsoft told us at the end of March that Developer Edition is now free for SQL Server 2014, and that it will be continue to be free when it is released for SQL Server 2016 later this year.  As always Developer Edition is a fully-featured edition of the SQL Server product (with all of the same features as Enterprise Edition) that is intended for use in development and test environments and "and not for production environments or for use with production data" (a curve ball called out in the linked release article above that many people don't consider).  Even though SQL 2016 isn't out yet, prepare yourself by getting a free copy of 2014 Developer Edition now so that you are ready to move to 2016 when it comes out.

• Attend SQL Saturday sessions - I have written before about how amazing SQL Saturdays are and the many reasons for taking part, and now the impending release of the new SQL Server 2016 product makes it even more important than usual!  I don't have any talks in my repertoire about the new version, but both of the SQL Saturdays I am scheduled to speak at in early June have multiple sessions about SQL Server 2016 (as do most other SQL Saturdays - check the full SQL Saturday schedule here):

• SQL Saturday #517 - Philadelphia - June 4th
• Tamera Clark - Microsoft Business Intelligence 2016 – More Love for Your Data
• Kerry Tyler - New Engine Features in SQL Server 2016
• Adam Machanic - SQL Server 2016 "Hidden Gems"
• Sharon Dooley - The New SQL Server 2016 Query Store
• Justin Dearing - An Introduction to Always Encrypted
• Kevin Goff - In-Memory Technology in SQL 2016 (Columnstore Indexes and In-Memory Tables)

• SQL Saturday #523 - Iowa City - June 11th
• Jared Zagelbaum - SQL Server 2016 - New Security Features
• John Pertell - "R" You Ready For SQL 2016?
• Ameena Lalani - SQL Server 2016: What's new?
• Ed Leighton-Dick - Understanding SQL Server 2016 Always Encrypted
• George Bryant - SQL Server 2016 for BI

• Read Blogs and Whitepapers online

• Microsoft has an array of whitepapers and demo videos for SQL Server 2016 available on their official 2016 site.  
• Several major tools vendors have plenty of blogs about SQL Server 2016, especially SQL Sentry and Solarwinds.
• The community has a wide array of blogs related to SQL Server 2016 - a great place to start is on SQLServerCentral

--

Get in front of this now - we can't all be like this:

http://cdn1-www.dogtime.com/assets/uploads/gallery/funny-dog-memes-part-4/funny-dog-meme-learning-tricks-doesnt-matter-when-youre-really-really-ridiculously-good-looking.jpg

--

Hope this helps!

Where Is My Primary Replica Again?

We have many clients with multi-node Availability Groups - that is, AGs with more than two replicas.  One of the problems I have always had with Availability Group management via the GUI (admit it, you use the GUI sometimes all you non-PowerShell geeks) is the fact that most of the work needs to be done from the primary replica.  You can connect to the Availability Group manager on a secondary replica, but you can only see limited data about that particular secondary replica and can't see much about the other replicas, including *which* replica is the current primary replica!

To perform management you almost always need to connect to the primary replica, but how can I figure out which one is primary without just connecting to the instances one by one until I get lucky?

https://cdn.meme.am/instances/500x/55766239.jpg

Enter the T-SQL:

SELECT
AG.name AS AvailabilityGroupName
, HAGS.primary_replica AS PrimaryReplicaName
, HARS.role_desc as LocalReplicaRoleDesc
, DRCS.database_name AS DatabaseName
, HDRS.synchronization_state_desc as SynchronizationStateDesc
, HDRS.is_suspended AS IsSuspended
, DRCS.is_database_joined AS IsJoined
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as HAGS
ON AG.group_id = HAGS.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS HARS
ON AR.replica_id = HARS.replica_id AND HARS.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS DRCS
ON HARS.replica_id = DRCS.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS HDRS
ON DRCS.replica_id = HDRS.replica_id
AND DRCS.group_database_id = HDRS.group_database_id
ORDER BY AG.name, DRCS.database_name

This query can be run on any of the replicas, and it will return information about the Availability Groups and their member databases, *including* the name of the primary replica instance!

http://vignette4.wikia.nocookie.net/randycunningham9thgradeninja/images/9/97/YES_cat.jpg/revision/latest?cb=20150330230809

Hope this helps!

How Long Did That Job Run?

When you use the msdb tables to try to gather information on your SQL Server Agent jobs, inevitably you will end up in a situation like this:

SELECT TOP 10 
sj.name as JobName
,sjh.run_date as RunDate
,sjh.run_time as RunTime
,sjh.run_duration as Duration
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
on sjh.job_id = sj.job_id
where step_id=0

JobName	RunDate	RunTime	Duration
ServerA_RESTORE_FROM_PROD_V2	20140904	222821	4131
ServerA_RESTORE_FROM_PROD_V2	20140904	231032	3055
ServerA_RESTORE_FROM_PROD_V2	20140904	231130	5003
Bloomberg_Pkg	20140904	231233	205
ServerA_RESTORE_FROM_PROD_V2	20140904	231350	2343
DatabaseIntegrityCheck - SYSTEM_DATABASES	20140923	1500	433
DatabaseBackup - SYSTEM_DATABASES - FULL	20140923	10000	23
syspolicy_purge_history	20140923	20000	36
DatabaseBackup - USER_DATABASES - FULL	20140923	40000	2333
DatabaseIntegrityCheck - SYSTEM_DATABASES	20140924	1500	113

So....when did "DatabaseIntegrityCheck - SYSTEM_DATABASES" start? At 1500 - is that 3pm?  You may be able hash out that this translates to 12:15am local time...but what if you want to perform datetime-style math on the RunDate/RunTime?  Sure you can do multiple leaps to say (RunDate>X and RunDate<=Y) AND (RunTime>A and RunTime<=B), but you then need to explicitly format your X, Y, A, and B in the appropriate integer-style format.  Wouldn't it be easier to just be able to datetime math?

The next part is even worse - quick - how long did the first instance of "ServerA_RESTORE_FROM_PROD_V2" run?

4,131 somethings (seconds, ms, etc), right?

http://ct.fra.bz/ol/fz/sw/i51/5/7/25/frabz-no-191096.jpg

In truth, the job ran forty-one minutes and thirty-one seconds.

http://upandhumming.com/wp-content/uploads/2015/11/serious.jpg

Yes, yes I am.  No precision beyond seconds, and no quick way to do math.  if you want to figure out how much longer instance 1 of the job ran than instance 2, you have to do some serious goofiness to figure it out.

Here is the fix for both of these problems!

--

The first item is easy although not directly obvious without a little help from that old standby:

https://imgflip.com/i/12hu6g

(Yes I am that old...)

Microsoft included a system scalar function msdb.dbo.agent_datetime(run_date, run_time) that turns the combination  on run_date and run_time into a datetime:

SELECT TOP 10
sj.name as JobName
,sjh.run_date as RunDate
,sjh.run_time as RunTime
,msdb.dbo.agent_datetime(sjh.run_date,sjh.run_time) as RunDateTime
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
on sjh.job_id = sj.job_id
where step_id=0

JobName	RunDate	RunTime	RunDateTime
ServerA_RESTORE_FROM_PROD_V2	20140904	222821	09/04/2014 22:28:21
ServerA_RESTORE_FROM_PROD_V2	20140904	231032	09/04/2014 23:10:32
ServerA_RESTORE_FROM_PROD_V2	20140904	231130	09/04/2014 23:11:30
Bloomberg_Pkg	20140904	231233	09/04/2014 23:12:33
ServerA_RESTORE_FROM_PROD_V2	20140904	231350	09/04/2014 23:13:50
DatabaseIntegrityCheck - SYSTEM_DATABASES	20140923	1500	09/23/2014 00:15:00
DatabaseBackup - SYSTEM_DATABASES - FULL	20140923	10000	09/23/2014 01:00:00
syspolicy_purge_history	20140923	20000	09/23/2014 02:00:00
DatabaseBackup - USER_DATABASES - FULL	20140923	40000	09/23/2014 04:00:00
DatabaseIntegrityCheck - SYSTEM_DATABASES	20140924	1500	09/24/2014 00:15:00

I agree with the author of this post who calls the agent_datetime() function "undocumented" since there wasn't a record of it in Books Online - I checked around and couldn't find any standard documentation of it on MSDN or TechNet.

Now that we have a datetime, we can perform all of the regular datetime manipulation functions such as DATEDIFF() on the values.

--

The second part is a little more obnoxious - there isn't a quick Microsoft function (documented or otherwise) to make the run_duration into a process-ready value.

To hash the run_duration into a useful value I wrote a CASE statement some time ago:

SELECT TOP 10
sj.name as JobName
,CASE len(sjh.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(sjh.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(sjh.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(sjh.run_duration,3),1)
+':' + right(sjh.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(sjh.run_duration,4),2)
+':' + right(sjh.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(sjh.run_duration,5),1)
+':' + Left(right(sjh.run_duration,4),2)
+':' + right(sjh.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(sjh.run_duration,6),2)
+':' + Left(right(sjh.run_duration,4),2)
+':' + right(sjh.run_duration,2) as char (8))
END as 'Duration'
,run_duration
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
on sjh.job_id = sj.job_id
where step_id=0

JobName	Duration	run_duration
ServerA_RESTORE_FROM_PROD_V2	00:41:31	4131
ServerA_RESTORE_FROM_PROD_V2	00:00:03	3
ServerA_RESTORE_FROM_PROD_V2	00:00:05	5
Bloomberg_Pkg	00:00:02	2
ServerA_RESTORE_FROM_PROD_V2	00:00:02	2
DatabaseIntegrityCheck - SYSTEM_DATABASES	00:00:43	43
DatabaseBackup - SYSTEM_DATABASES - FULL	00:00:02	2
syspolicy_purge_history	00:00:36	36
DatabaseBackup - USER_DATABASES - FULL	00:00:02	2
DatabaseIntegrityCheck - SYSTEM_DATABASES	00:00:11	11

Interestingly I recently discovered a more elegant solution (while looking for a answer to a different problem) that utilizes the STUFF() function.  Look at this forum post on SQLServerCentral.  The fourth item down from "Mudluck" is almost exactly the same as my CASE statement above, but look at the reply below it from "JG-324908":

SELECT stuff(stuff(replace(str(run_duration,6,0),' ','0'),3,0,':'),6,0,':') FROM sysJobHist

Sure enough, running this STUFF() code results in the same output as the much more complicated CASE statement above.  JG notes that he/she found it in a forum post somewhere and I dug around a little to see if I could find the original author without any luck :(

--

As with many other bits of code, I keep these things in a big NotePad file of useful code snippets (some people like OneNote instead, and there are dozens of choices - use the one you normally prefer) so that I can quickly reference them when needed.  I always note the website or forum post where I found the code if I didnt create it myself as well as the original author.  This lets me give credit where credit is due when showing the code to others, but it also gives me an idea of someone to approach in the future if I have a question about a similar topic.

It was especially interesting to find the STUFF() code because STUFF() isn't a function I often use, and in this case it was perfect.

--

Hope this helps!

Don't Use Delimiters In Your Object Names!

It started with a failed backup status check:

Msg 50000, Level 16, State 1, Line 73
** Backups listed in this file are missing / out of date **

db                status   recovery model   last full backup   Full BU hours old
[FinanceDB]       ONLINE   SIMPLE           NULL             NULL

Check out the database name - [FinanceDB],  At first my response was something like:

http://s.quickmeme.com/img/15/151d67b9a73228d44b1bfabea0d012b54b9cd2821a25bf4b4be1bad10c41a95d.jpg

I went and looked on the server, and sure enough in Management Studio I saw one database named "FinanceDB" and a database named "[FinanceDB]".

This was on a SQL 2008R2 instance, but as a test I created a database named [test] on my local SQL 2014 instance and sure enough it worked!

The source of the problem at the client was the LiteSpeed maintenance plan.  Even though the backup task was set to backup all user databases, it wasn't picking up the square-bracketed database.

On my test system I set up a "regular" SQL Server maintenance plan, and it had did not have the same problem - the backup job did pick up the square-bracketed [test] database.

I next ran the Ola Hallengren DatabaseBackup that I already had installed, and it also picked up the rogue database, creating a backup named TestServer_[test]_FULL_20160404_143721.bak.

--

Since I don't have LiteSpeed on my test system I created a quick test on the client's test system  (SQL 2008R2) - I created a new database named [test] and created a new LiteSpeed maintenance plan to back up *just* that database - the job failed with this error:

Executed as user: Domain\User. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:47:35 PM  Progress: 2016-04-04 15:47:35.81     Source: {D8AD0CC9-710A-4C59-A8E6-1B9228562535}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp_ma...".: 100% complete  End Progress  Error: 2016-04-04 15:47:37.01     Code: 0xC002F210     Source: Fast Compression Backup 1 Execute SQL Task     Description: Executing the query "execute master..xp_slssqlmaint N'-BkUpMedia DISK -..." failed with the following error: "LiteSpeed? for SQL Server? 8.1.0.644  ? 2015 Dell Inc.    Database 'test' is invalid: not found    Msg 62401, Level 16, State 1, Line 0: Database 'test' is invalid: not found    ".   End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:47:35 PM  Finished: 3:47:37 PM  Elapsed:  1.809 seconds.  The package execution failed.  The step failed.

That was strange - remember the original backup job succeeded (although not backing up the square-bracketed database).  I considered and realized in the original situation there were two databases - one with brackets and one without.  Sure enough when I created a database just named "test" and included it in my new maintenance plan, the job succeeded.

How did it succeed?  It backed up "test" TWICE:

The LiteSpeed Fast Compression backup backed up the "test" database twice (Fast Compression runs a FULL every so often and DIFF's in-between - this is why two backups in a row results in a FULL and a DIFF).  I ran the job again and saw the same thing:

I verified by creating an object in [test] and running the backups again and running a restore - what I was backing up was truly "test" over and over.

--

This is not about bashing Dell/Quest LiteSpeed - it is about pointing out a shortcoming in how SQL Server allows us to name objects and a cautionary tale on that naming.

Although it worked in most of the test scenarios above, you can see how the square-bracketed database name failed under one maintenance scenario - and not only did it fail, it failed in a particularly nefarious way because the backup job *didn't* fail - it just didn't pick up the database.

--

I re-created my [test] database on my local instance and wanted to see what the syntax would look like for a DROP - I went through the SCRIPT action in Management Studio and ended up with this:

USE [master]

GO

/****** Object:  Database [[test]]    Script Date: 4/4/2016 2:00:55 PM ******/

DROP DATABASE [[test]]]

GO

Note the *three* square brackets at the end of the DROP statement – two open brackets but three close brackets.

A syntax checker parse showed statement OK – when I removed one of the closing brackets, so that I had two open and two close, the parse showed me this:

Msg 105, Level 15, State 1, Line 5

Unclosed quotation mark after the character string '[test]

GO

'.

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '[test]

GO

…and an execution resulted in the same errors.  When I re-added the third close square bracket, my DROP statement succceeded.

A script of a simple BACKUP statement resulted in the same thing:

BACKUP DATABASE [[test]]]

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\[test].bak' WITH NOFORMAT, NOINIT,  NAME = N'[test]-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--

This third bracket may be the source of the issue in LiteSpeed and it may not - but it shows the importance of handling object names properly and how touchy the parser can be when you don't make it happy.

--

At the end of the day - just don't do it.  There is always going to be some other name that is just as useful as any name that includes a delimiter, and you don't need to handle the bizarre and inconsistent scenarios that will arise.

Hope this helps!

Why I Love #SQLSaturday

PASS SQLSaturdays are free 1-day training events for SQL Server professionals that focus on local speakers, providing a variety of high-quality technical sessions, and making it all happen through the efforts of volunteers. Whether you're attending a SQLSaturday or thinking about hosting your own, we think you'll find it's a great way to spend a Saturday – or any day. - sqlsaturday.com

Sounds simple, doesn't it?  Here are the top three reasons I *LOVE* SQLSaturdays:

The opportunity to learn - the quality and quantity of free training available at a SQLSaturday never ceases to amaze me.  Most SQLSaturdays have anywhere from three to six tracks, resulting in 15-30 sessions on everything from database administration to business intelligence to professional development and everything in between.  Sessions range from 100-level introductory up through 400-level expert.

 

If you want to pay $100-$150, most SQLSaturdays offer full-day "pre-conference" sessions on the Friday before the event (and sometimes even the Thursday before as well). While it isn't free, there aren't very many places to get a full day of high end training for a hundred bucks!

Another aspect of this is *who* provides the training.  I regularly see sessions from Microsoft Certified Masters (MCM's) and Most Valuable Professionals (MVP's) as well as amazing content from first-timers dipping their toes in the pond.  At a recent SQL Saturday (in Boston) I saw an MCM present a great talk on working with virtualized SQL, one MVP speak on working with your infrastructure team, and another MVP talk about the Query Store, an upcoming feature from the unreleased next version of SQL Server.  Having said that, one of the best SQLSaturday sessions I have ever seen came a couple years ago from a first-time speaker excitedly presenting a new way she had devised to analyze trace data. 

All of these speakers share their expertise without pay or reward (other than usually a nice dinner the night before).

The opportunity to share - another fun part of SQLSaturday for me is being one of those speakers sharing what I know for free.  I have written before about the benefits of blogging and speaking, and they are many.  The biggest benefit to me personally (not counting the joy of helping others) is how creating a presentation helps force me to learn a new topic or a better way to do something I already do.

The presentation I used to give all the time was about doing health checks using Glenn Berry's DMV scripts, Ola Hallengren's maintenance solution, and how to make them work together to check client servers.  The presentation I frequently give now is about Extended Events (XEvents) - I told myself about a year ago I needed to learn more about XEvents and Powershell, and I knew (for me) creating presentations would help.  I submitted an Intro to Extended Events 100-level session to a couple of SQLSaturdays, and when it was chosen I was suddenly very motivated to learn more about the topic to produce the content!

The first presentation - the health check talk - highlights an aspect of how I work with SQL Server, and it is shared by many others.  The are lots of free tools and shared knowledge out there about SQL Server, and you don't need to recreate the wheel nine times out of ten - a little Google-Fu or #sqlhelp will usually give you an answer or at least give you raw material you can mold into an answer.  Just because you are working with someone else's raw material does not mean you can't write or speak about the situation - just make 100% sure you give credit (and notation) whenever it is due.  If you read my blog or see me speak with any regularity you will see that a lot if what I write about is *how* I use someone else's scripts, whether modified or "straight from the box," as opposed to creating completely new scripts, but I also reference those authors' blog posts, forum answers, and Twitter feeds.

You don't have to create a completely new way to do something for it to be useful to share!

The opportunity to network - I list this third, but it can often be the most important. While it can be very useful to interact with the #sqlfamily online, there is no substitute for being able to sit down across the table from an expert on a topic you need help with and getting hundreds of dollars worth of free consulting while forming friendships that continue on after the event ends. There is nothing like the #sqlfamily, and it is fun to watch people from other areas as their eyes open wide. I have seen an Oracle DBA visit a SQLSaturday and watched their jaw hit the floor when they heard a speaker sharing their expert knowledge for free; I have had a manager ask me "How did you get the answer so fast?" and reply "I got on Twitter and asked the person who wrote the feature and he told me"; I have watched people who have never met in person raise thousands of dollars for charitable causes suggested by #sqlfamily members.

Another way to get involved and network is to volunteer at a SQLSaturday. I mentioned the speakers are volunteers, but so ate all of the others involved - coordinators, room monitors, check-in staff, and the rest.

This networking is invaluable to your career in other ways - two of my last three jobs came from a member of the #sqlfamily notifying me directly of an opening or making a key introduction to help me start the process.

SQLSaturdays are awesome!!!