RSS

Tag Archives: T-SQL

Adventures in Fixing VLF issues on a database in an Availability Group, backed up with DPM, with multiple secondaries


Hey all,

We had a server failover recently during a demonstration to one of the C-level folks, and as you might imagine, it was brought to my attention. The reason for the failover wasn’t something I could control, but the length of time the relevant database took to come up was something I could – so when it finally came up, I noticed the errorlog with this little note:

Database <dbname> has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Hmm…

More than 1000, huh?

Slow recovery from failover, huh?

Hmm…

Where had I seen this before?  Oh yeah, some years back, when we were doing datacenter maintenance, and the *one* sharepoint database  that had all our documentation on (ahem) “How to bring up a datacenter.”  in it took a half an hour to come back – I remember now!

So if you don’t know – having a metric buttload (note – that’s a real term there, go look it up) of virtual log files means that every time your server fires up a database, it has to replay the transaction log to get all up to date and everything, and if the transaction log’s got a bunch of virtual log files, it’s like trying to run data backwards through a shredder… it takes time, and that’s the issue.

I remembered Michelle Ufford’s ( blogtwitter ) post that I’d found back then about counting Virtual log files and tried it on this server now that it was up.

And got this nice little message:

Msg 213, Level 16, State 7, Line 1

Column name or number of supplied values does not match table definition.

Back to “Hmmm…”

Well, I knew dbcc loginfo(dbname) from her script was inserting into a table that the script created, and it had worked before. It had been awhile since I’d run it – so I figured maybe there was some change in what it returned based on the SQL version. So I decided to see what it was inserting…

Well hey – the original script was missing a field (first column above)

So I added it (RecoveryUnitID – the first one below)

…and it worked fine.

Turns out SQL 2008R2 (where the original script worked) returns different fields than 2012 and 2014 (where it didn’t).

I figured I didn’t want to find out which version of the script to use every time I needed to run it on a server, so I told the script to figure that out by itself, and then run the appropriate hunk of code (example below)

So what you see in that code is a combination of this:

https://msdn.microsoft.com/en-us/library/ms174396.aspx from Microsoft to check the version number,

and this

http://sqlfool.com/2010/06/check-vlf-counts/ from Michelle Ufford.

A couple of things to understand here:

  1. This code won’t fix the problem (read Kimberly’s posts below for that)
    1. http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
    2. http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
  2. It will, however, show you that a problem exists.
  3. …that you have to fix and manage (Back to Kimberly’s posts)

You’ll want to do some fixing that involves:

  1. Lots of log backups and log file shrinking, then log growing (See Kimberly’s posts for more info on this)
  2. Adjusting the auto-growth setting from the 1 mb/10% default setting to something significantly larger (Meanwhile, back in Kimberly’s posts… Seriously, check them out)
  3. Oh, and make those adjustments on the Model database so that every new database you create has decent growth patterns instead of the 1 mb/10% bit)
  4. Tadaa – you should find much faster failovers on your clustered systems (assuming you’re still using them and not Availability groups)

So… that made it look easy, right? Well, let me give you the Paul Harvey Version, “The Rest of the Story.”

What I learned, and what complicated things a bit…

My system was an Availability group with two nodes running synchronously, and two nodes in two different datacenters running asynchronously.

They’re backed up by Microsoft’s Data Protection Manager (DPM) that is configured to back up the secondary node.

So my plan was to back up the secondary – only when I did, it told me that the last recovery point (what a backup’s called in DPM) had something to do with Marty McFly, namely that it had been backed up in the future – about 8 hours into the future.

Hmm… Okay, that told me the secondary it was backing up was one of the asynchronous ones, and it was either in the land of fish and chips, or the land of beer and pretzels, either way – it was a weird way to think about trying to preserve the recovery chain.

I created recovery points through DPM, which did it on one of those secondaries, which, strangely, helped to clear the log on the primary.

Then I shrunk the log, in stages, on the primary, which then replicated out to the various secondaries locally and across the pond.

I was able to get the VLF count down from well north of 33,000 to just over 600. The log wouldn’t shrink any more than that, which told me there was a transaction hanging on toward the end of one of the the virtual log files, and frankly, for right then, that was okay.

To fix it further, I almost entertained the idea of flipping the recovery model from full to simple, clearing the log file completely, then flipping it back to full, but that would have totally broken my recovery chain, and with three secondaries, I had no desire to try to fix all the fun that would have created. (Translation: Rebuilding the availability group – Uh… No.)

I then grew the log file to an appropriate size for that database, then increased the growth rate on the database files to something far more in line with what Kimberly mentioned above.

All of this was done on the primary, and the changes I made propagated out nicely, so I only had to make them in one spot.

Oh, while I was researching this…

(I’d done this before – reducing VLF counts, but never on an Availability Group, much less one with multiple secondaries in different time zones where DPM is backing up one of the secondaries, where it’s not obvious which one of the secondaries is the one being backed up)

…I noticed other people had clearly run into this same issue (reducing VLF counts) – and had not only entertained the idea of automating VLF reduction, but taken the idea out drinking, dancing, and somehow ended up with a receipt for a cheap motel room – and then put it all into a stored procedure.

That ran…

In a job…

On a schedule…

Um.

I decided that breaking the recovery chain was bad enough, but shredding it was so much worse – so this part will not be automated.

At all.

Oh – and here’s the code. It’s Michelle’s, with a couple of tweaks from me and one from Microsoft.

--Code to determine how many virtual log files there are in a database.
--modified from the original code here: http://sqlfool.com/2010/06/check-vlf-counts/
--to check for multiple versions of SQL because of schema changes between versions
--if any of the results are > 1000, do multiple log backups (or DPM Recovery points
--to clear out the transaction log + shrinking the tlog to get rid of
--the extra virtual log 'fragments'.
--As always - test this in your test environments first.  Your mileage may vary. Some 
--settling of contents may have occurred during shipping.  You know the drill folks.
--Best reference out there for managing this:
-- http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
-- http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
--the code below handles SQL 2008R2, 2012, and 2014. If you're running something other than that,
--just run a dbcc loginfo(YourDatabaseName) and make sure the #stage table has the same
--columns in it
--if it doesn't, add a section below.
DECLARE          @sqlversion SYSNAME
SET              @sqlversion =(SELECT CONVERT(SYSNAME,SERVERPROPERTY('ProductVersion')))
PRINT            @sqlversion
-- SQL 2008R2

IF @sqlversion LIKE '10.50.%'
     BEGIN
          CREATE TABLE #stage
                                  (
                                     FileID        INT
                                   , FileSize      BIGINT
                                   , StartOffset   BIGINT
                                   , FSeqNo        BIGINT
                                   , [Status]      BIGINT
                                   , Parity        BIGINT
                                   , CreateLSN     NUMERIC(38)
                                  );

          CREATE TABLE #results
                                  (
                                     Database_Name SYSNAME
                                   , VLF_count     INT
                                  );
          EXEC sp_msforeachdb  N'Use [?];
                                Insert Into #stage
                                Exec sp_executeSQL N''DBCC LogInfo([?])'';
                                Insert Into #results
                                Select DB_Name(), Count(*)
                                From #stage;
                                Truncate Table #stage;'
         SELECT         *
         FROM           #results
         ORDER BY       VLF_count DESC
         DROP TABLE     #stage
         DROP TABLE     #results
     END
     --sql 2012/2014 - note - new temp table names because sql thinks old ones are there.
IF @sqlversion >= '11'
     BEGIN
          CREATE TABLE #stage2
                                  (
                                     RecoveryUnitID INT
                                   , FileID         INT
                                   , FileSize       BIGINT
                                   , StartOffset    BIGINT
                                   , FSeqNo         BIGINT
                                   , [Status]       BIGINT
                                   , Parity         BIGINT
                                   , CreateLSN      NUMERIC(38)
                                   );
          CREATE TABLE #results2
                                  (
                                   Database_Name SYSNAME
                                   , VLF_count INT
                                   );
          EXEC        sp_msforeachdb N'Use [?];
                                     Insert Into #stage2
                                     Exec sp_executeSQL N''DBCC LogInfo([?])'';
                                     Insert Into #results2
                                     Select DB_Name(), Count(*)
                                     From #stage2;
                                     Truncate Table #stage2;'
          SELECT         *
          FROM           #results2
          ORDER BY       VLF_count DESC
          DROP TABLE     #stage2
          DROP TABLE     #results2
     END

Thanks to the #sqlhelp folks for their advice, suggestions, good-natured harassment, and ribbing:

Allen McGuire (blog | twitter)
Argenis Fernandez (blog | twitter)
Tara Kizer @tarakizer (blog | twitter)
Andre Batista @klunkySQL (blog | twitter)

and of course, the ones whose reference material I was able to use to put this together:

Michelle Ufford (blog | twitter)
Kimberly Tripp (blog | twitter)
Paul Randal (blog | twitter)

<outttakes>
When I told Argenis about the DPM involvement and secondaries in multiple timezones, his response 🙂

 
4 Comments

Posted by on January 5, 2016 in Uncategorized

 

Tags: , , , , ,

Problem Solved: Lync Reporting issues


Awhile back we had a pretty significant series of issues with our Lync implementation that took some time to resolve. Now that it is, I thought I’d write up something for one of them to help others that have run into the same problem so that it can be fixed up once and for all.

Note: these notes come from my own personal experience with a dash of a case we had open with MSFT at the time.

Lync, for those of you who aren’t familiar with it, is Microsoft’s corporate instant messenger suite – which brings voice, (both individual and conferencing), sharing, and IM into one package. If there was ever any program I’ve ever used that’s changed how I work, this is it.  Calls, conferences, desktop sharing, all from the same app.  Truly well done.

That’s on the front end.

On the backend, however, it gets a little more involved – and for those running the app, there’s a lot of reporting that will allow you to monitor things like call quality, what the system is doing, and if there is trouble in paradise, as it were, you can use this reporting to start narrowing down the issues you’re running into, and that will help you both troubleshoot and resolve them.

And that’s the part that was giving us trouble.

It’d work for a bit, then give us weird errors, and the folks trying to troubleshoot global call/connection issues were completely blocked. So they were frustrated, and we needed to figure out how to fix it – not just this once, but fix it period.

The problem:

Lync reporting, when we got to the Monitoring Dashboard report, would often render an error like this:

What you'll get if the stored procedures haven't been run

What you’ll get if the stored procedures haven’t been run

If you can’t read the image, there are two error messages:

“Report processing stopped because too many rows in summary tables are missing in the call detail recording (CDR) database.  To resolve this issue, run dbo.RtcGenerateSummaryTables on the LcsCDR database.”

“Report processing stopped because too many rows in summary tables are missing in the Quality of Experience (QoE) database.  To resolve this issue, run dbo.RtcGenerateSummaryTables on the QoEMetrics database.”

You can find that I’m not the only one that ran into this error by just using your favorite search engine – Google, Bing, DuckDuckGo, whatever. (the results are pretty similar)

Bottom line – the error is the same: Some level of activity that’s been happening on the server is not reflected in the troubleshooting reports you want – and the error message has both good and bad parts to it.

The good: As far as error messages go, this one is surprisingly clear. Basically do what it says, and, depending on how much it has to summarize, it takes a few minutes and sums up a bunch of information that is amazingly useful in the reports our Lync team needs.

The bad: It exposes names of objects that someone might not actually want to have exposed, though people who see this are often the people who need to, so it’s a bit of a two edged sword.  The other thing is that there’s nothing to give us any indication of how often what’s mentioned in the message needs to run. I figured (as did many of the others who’ve run into this) there was some process that called this set of stored procedures at some defined period, but from what I saw in my research and what I experienced myself, that was not happening for the folks who were running into this. On a hunch, while was on a call with MSFT on a related issue, I discovered that the stored procedure referenced in the above screenshot needs to be run at least daily.

Well that’s not hard…

So my suggestions below are based on the following assumptions:

That you’ve got SQL installed on your server with SQL Agent running – this was something that seemed to be the culprit in a lot of the issues in the links above.

We depend on SQL Agent to run our automation, so it was running but the process/job/scheduler to run the needed code wasn’t there at all.  The below instructions fix that.

So I created a SQL job, and scheduled it to run once daily. Since the stored procedures are in different databases, I just wrote the execution commands (below) fully qualified, so you could do the same.

I also created some output files on the jobs just to be sure I could monitor what the jobs were doing for the first little bit, and guess what?

It worked.

Problem solved.

So – if you’re experiencing the issues described above and don’t have a job scheduled on your Lync SQL Server, do this:

  1. Create a SQL job on the SQL server that has your QoEMetrics and LcsCDR databases on it.
  2. Give the job a name that makes sense and fits your job naming conventions..
    1. I named mine: Lync Reporting Monitoring Dashboard Summary Numbers
    2. Put something like the below in the description field so that once the braincells you’re now using to solve this problem have been overwritten, you’ll still have a clue as to why you did this:
      1. LYNC – Error appears in reporting when tables haven’t been populated frequently enough. This code was missing and it is confirmed that it needed to be run daily with MSFT. It is scheduled as such. Questions? Contact <you or your SQL operations team>
    3. Click on ‘steps’
    4. Click’new’
    5. Add one step – you’ll have to name it… I named mine: RTC Generate Summary Tables – because that’s what it does.
    6. I leave the database set to master and paste the following code in.

–this code should be run daily per MSFT

EXEC      QoEMetrics.dbo.RtcGenerateSummaryTables

GO

EXEC      LcsCDR.dbo.RtcGenerateSummaryTables

GO

  1. If you want an output file, find your log drive and paste this in there (editing the <drivepath> below as appropriate):
    1. <drivepath>\MSSQL\Log\LyncPopulateSummaryTables_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt (that’s a little snippet from some of the best code I’ve seen at http://ola.hallengren.com)
  2. I scheduled mine to run at some hour when I’m asleep – and it now takes about 12 seconds to run daily.  You may want to adjust the schedule as needed for your environment
  3. Do that, and you should be able to forget about that report, and your Lync team should be able to know that it’s there every day – whenever they need it.

Take care – and good luck.

Tom

 
Leave a comment

Posted by on September 15, 2014 in Uncategorized

 

Tags: , , , , , , ,

Now WHERE did I put that backup?


Ever run into a situation where you’re in a hurry, working on a dev box, for a presentation, and you back up your database in an “easy” place, and then when it’s time to do the restore, can’t remember where you put it?

Yeah, me neither…

<this is your cue to chuckle a bit, because I think we’ve all done it a time or two>

In fact, it was Karen Lopez (twitter | blog) who got me looking for this script, and I thought I’d put it out there.  Nothing proprietary about it – it’s just a script to find a backup – but it can be useful when you’re trying to find out where in all the clutter of files you’ve got on boxes the one backup you actually need really is.

At any rate, some time ago I wrote this script that showed me where the last backup was of a database – run from within that database.

Take a look – let me know what you think.  If you can improve it, by all means, feel free to let me know that too.  Enjoy.

DECLARE @BACKUPNAME SYSNAME
SELECT @BackupName = (
SELECT     TOP (1) backupmediafamily.physical_device_name
FROM         msdb.dbo.backupmediaset AS backupmediaset_1
INNER JOIN        msdb.dbo.backupmediaset
ON                msdb.dbo.backupmediaset.media_set_id = backupmediaset_1.media_set_id
INNER JOIN        msdb.dbo.backupset
ON                msdb.dbo.backupmediaset.media_set_id = msdb.dbo.backupset.media_set_id
AND                backupmediaset_1.media_set_id = msdb.dbo.backupset.media_set_id
INNER JOIN      msdb.dbo.backupfile
INNER JOIN        msdb.dbo.backupfilegroup
ON                msdb.dbo.backupfile.backup_set_id = msdb.dbo.backupfilegroup.backup_set_id
ON                msdb.dbo.backupset.backup_set_id = msdb.dbo.backupfile.backup_set_id
AND             msdb.dbo.backupset.backup_set_id = msdb.dbo.backupfilegroup.backup_set_id
INNER JOIN        master.sys.sysdatabases
ON                backupset.database_name = master.sys.sysdatabases.name
INNER JOIN         msdb.dbo.backupmediafamily
ON                msdb.dbo.backupmediaset.media_set_id = msdb.dbo.backupmediafamily.media_set_id
AND             backupmediaset_1.media_set_id = msdb.dbo.backupmediafamily.media_set_id)
print        ‘backupname ‘
print        @backupname

 
Leave a comment

Posted by on May 7, 2012 in Uncategorized

 

Tags: , ,

It’s just ones and zeroes, right?


So I was debugging some rather frustrating code the other day, and it got me to thinking – and I know, it’s dangerous, but still, I did it…

I knew that the whole thing about our lives as computer professionals involves ones and zeroes.  So I thought, “Hmmm… I wonder what this code would look like if I translated it into binary? And how easy it would be to debug?”

So I did that, and was overloaded with a bunch of binary, so then took a simple backup script and translated that.  The result, below, is what the computer sees when it executes this little backup script.

Note: what’s below, translated from t-SQL into binary, is code that worked.  (yes, you’ll have to scroll for a bit, but there’s some human readable stuff in the middle, and some more at the end…)

01110011 01100101 01101100 01100101 01100011 01110100 00100000 01000000 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00001101 00001010 01100100 01100101 01100011 01101100 01100001 01110010 01100101 00100000 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00001001 00001001 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110011 00110000 00101001 00001101 00001010 01100100 01100101 01100011 01101100 01100001 01110010 01100101 00100000 01000000 01101001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01101110 01100001 01101101 01100101 00001001 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110011 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01110010 01100101 01110000 01101111 01110010 01110100 01100101 01111000 01100101 01100011 01110101 01110100 01100101 00100000 00100000 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110001 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00100000 00100000 00100000 00100000 00001001 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110001 00110000 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01100010 01100001 01100011 01101011 01110101 01110000 01101110 01100001 01101101 01100101 00100000 00100000 00100000 00100000 00100000 01010110 01000001 01010010 01000011 01001000 01000001 01010010 00100000 00101000 00110101 00110000 00101001 00100000 00100000 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01100011 01101101 01100100 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00001001 01010110 01000001 01010010 01000011 01001000 01000001 01010010 00101000 00110100 00110000 00110000 00110000 00101001 00100000 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01110011 01101111 01110101 01110010 01100011 01100101 01110000 01100001 01110100 01101000 00100000 00100000 00100000 00100000 00100000 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110010 00110101 00110000 00101001 00100000 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01110100 01100001 01110010 01100111 01100101 01110100 01110000 01100001 01110100 01101000 00100000 00100000 00100000 00100000 00100000 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110010 00110101 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01101110 01101111 01110111 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00001001 01000011 01001000 01000001 01010010 00101000 00110001 00110100 00101001 00100000 00100000 00100000 00001101 00001010 00001101 00001010 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00100000 00111101 00100000 00101000 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01110110 01100001 01101100 01110101 01100101 00100000 01100110 01110010 01101111 01101101 00100000 01100100 01100010 01100001 00101110 01100100 01100010 01101111 00101110 01010011 01100101 01110010 01110110 01100101 01110010 01010000 01110010 01101111 01110000 01100101 01110010 01110100 01101001 01100101 01110011 00100000 01110111 01101000 01100101 01110010 01100101 00100000 01100100 01100001 01110100 01100001 00100000 00111101 00100000 00100111 01010011 01100101 01110010 01110110 01100101 01110010 01001110 01100001 01101101 01100101 00100111 00101001 00001101 00001010 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01000000 01101001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01101110 01100001 01101101 01100101 00100000 00111101 00101000 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01110110 01100001 01101100 01110101 01100101 00100000 01100110 01110010 01101111 01101101 00100000 01100100 01100010 01100001 00101110 01100100 01100010 01101111 00101110 01010011 01100101 01110010 01110110 01100101 01110010 01010000 01110010 01101111 01110000 01100101 01110010 01110100 01101001 01100101 01110011 00100000 01110111 01101000 01100101 01110010 01100101 00100000 01100100 01100001 01110100 01100001 00100000 00111101 00100000 00100111 01001001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01001110 01100001 01101101 01100101 00100111 00101001 00001101 00001010 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 00100000 01000000 01110010 01100101 01110000 01101111 01110010 01110100 01100101 01111000 01100101 01100011 01110101 01110100 01100101 00100000 00100000 00111101 00100000 00100111 01000101 01011000 01000101 01000011 01110101 01110100 01100101 00100111 00100000 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00100000 00100000 00100000 00100000 00001001 00111101 00100000 00100111 01101101 01100001 01110011 01110100 01100101 01110010 00100111 00100000 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 00100000 01000000 01110011 01101111 01110101 01110010 01100011 01100101 01110000 01100001 01110100 01101000 00100000 00100000 00100000 00100000 00100000 00111101 00100000 00100111 01011100 01011100 00100111 00101011 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00101011 00100111 01011100 00100111 00101011 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00101011 00100111 01011111 00100111 00101011 01000000 01101001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01101110 01100001 01101101 01100101 00101011 00100111 01011111 01100010 01100001 01100011 01101011 01110101 01110000 00100111 00001101 00001010 01010011 01000101 01010100 00001001 00001001 01000000 01101110 01101111 01110111 00001001 00001001 00001001 00111101 00100000 01010010 01000101 01010000 01001100 01000001 01000011 01000101 00101000 01010010 01000101 01010000 01001100 01000001 01000011 01000101 00101000 01010010 01000101 01010000 01001100 01000001 01000011 01000101 00101000 01000011 01001111 01001110 01010110 01000101 01010010 01010100 00101000 01010110 01000001 01010010 01000011 01001000 01000001 01010010 00101000 00110101 00110000 00101001 00101100 00100000 01000111 01000101 01010100 01000100 01000001 01010100 01000101 00101000 00101001 00101100 00100000 00110001 00110010 00110000 00101001 00101100 00100000 00100111 00101101 00100111 00101100 00100000 00100111 00100111 00101001 00101100 00100000 00100111 00100000 00100111 00101100 00100000 00100111 00100111 00101001 00101100 00100000 00100111 00111010 00100111 00101100 00100000 00100111 00100111 00101001 00100000 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00001001 01000000 01100010 01100001 01100011 01101011 01110101 01110000 01001110 01100001 01101101 01100101 00001001 00001001 00111101 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00101011 00100000 00100111 01011111 01100100 01100010 01011111 00100111 00100000 00101011 00100000 01000000 01101110 01101111 01110111 00100000 00100000 00001101 00001010 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 01000000 01100011 01101101 01100100 00100000 00111101 00001001 00100111 01100010 01100001 01100011 01101011 01110101 01110000 00100000 01100100 01100001 01110100 01100001 01100010 01100001 01110011 01100101 00100000 00100111 00100000 00101011 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00101011 00100000 00001101 00001010 00001001 00001001 00001001 00001001 00100111 00100000 01110100 01101111 00100000 01100100 01101001 01110011 01101011 00100000 00111101 00100000 00100111 00100111 00100111 00100000 00101011 00100000 01000000 01110011 01101111 01110101 01110010 01100011 01100101 01110000 01100001 01110100 01101000 00100000 00101011 00100000 00100111 01011100 00100111 00101011 00100000 01000000 01100010 01100001 01100011 01101011 01110101 01110000 01101110 01100001 01101101 01100101 00100000 00101011 00100111 00101110 01100010 01100001 01101011 00100111 00100111 00100111 00100000 00101011 00100000 00001101 00001010 00001001 00001001 00001001 00001001 00100111 00100000 01110111 01101001 01110100 01101000 00100000 01110011 01110100 01100001 01110100 01110011 00100111 00100000 00001101 00001010 00001101 00001010 00001101 00001010 01010000 01010010 01001001 01001110 01010100 00100000 00100000 01000000 01100011 01101101 01100100 00100000 00001101 00001010 00001101 00001010 01001001 01000110 00100000 00100000 00100000 00100000 00100000 01000000 01110010 01100101 01110000 01101111 01110010 01110100 01100101 01111000 01100101 01100011 01110101 01110100 01100101 00100000 00111101 00100000 00100111 01000101 01011000 01000101 01000011 01110101 01110100 01100101 00100111 00100000 00001101 00001010 00100000 00100000 00100000 01000010 01000101 01000111 01001001 01001110 00100000 00001101 00001010 00100000 00100000 00100000 00100000 00100000 00100000 00100000 01000101 01011000 01000101 01000011 00100000 00101000 01000000 01100011 01101101 01100100 00101001 00001101 00001010 00100000 00100000 00100000 01000101 01001110 01000100 00100000 00001101 00001010 00001101 00001010

And this piece of code won’t work…

01110011 01100101 01101100 01100101 01100011 01110100 00100000 01000000 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00001101 00001010 01100100 01100101 01100011 01101100 01100001 01110010 01100101 00100000 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00001001 00001001 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110011 00110000 00101001 00001101 00001010 01100100 01100101 01100011 01101100 01100001 01110010 01100101 00100000 01000000 01101001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01101110 01100001 01101101 01100101 00001001 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110011 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01110010 01100101 01110000 01101111 01110010 01110100 01100101 01111000 01100101 01100011 01110101 01110100 01100101 00100000 00100000 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110001 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00100000 00100000 00100000 00100000 00001001 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110001 00110000 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01100010 01100001 01100011 01101011 01110101 01110000 01101110 01100001 01101101 01100101 00100000 00100000 00100000 00100000 00100000 01010110 01000001 01010010 01000011 01001000 01000001 01010010 00100000 00101000 00110101 00110000 00101001 00100000 00100000 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01100011 01101101 01100100 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00001001 01010110 01000001 01010010 01000011 01001000 01000001 01010010 00101000 00110100 00110000 00110000 00110000 00101001 00100000 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01110011 01101111 01110101 01110010 01100011 01100101 01110000 01100001 01110100 01101000 00100000 00100000 00100000 00100000 00100000 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110010 00110101 00110000 00101001 00100000 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01110100 01100001 01110010 01100111 01100101 01110100 01110000 01100001 01110100 01101000 00100000 00100000 00100000 00100000 00100000 01110110 01100001 01110010 01100011 01101000 01100001 01110010 00101000 00110010 00110101 00110000 00101001 00001101 00001010 01000100 01000101 01000011 01001100 01000001 01010010 01000101 00100000 01000000 01101110 01101111 01110111 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00100000 00001001 01000011 01001000 01000001 01010010 00101000 00110001 00110100 00101001 00100000 00100000 00100000 00001101 00001010 00001101 00001010 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00100000 00111101 00100000 00101000 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01110110 01100001 01101100 01110101 01100101 00100000 01100110 01110010 01101111 01101101 00100000 01100100 01100010 01100001 00101110 01100100 01100010 01101111 00101110 01010011 01100101 01110010 01110110 01100101 01110010 01010000 01110010 01101111 01110000 01100101 01110010 01110100 01101001 01100101 01110011 00100000 01110111 01101000 01100101 01110010 01100101 00100000 01100100 01100001 01110100 01100001 00100000 00111101 00100000 00100111 01010011 01100101 01110010 01110110 01100101 01110010 01001110 01100001 01101101 01100101 00100111 00101001 00001101 00001010 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01000000 01101001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01101110 01100001 01101101 01100101 00100000 00111101 00101000 01110011 01100101 01101100 01100101 01100011 01110100 00100000 01110110 01100001 01101100 01110101 01100101 00100000 01100110 01110010 01101111 01101101 00100000 01100100 01100010 01100001 00101110 01100100 01100010 01101111 00101110 01010011 01100101 01110010 01110110 01100101 01110010 01010000 01110010 01101111 01110000 01100101 01110010 01110100 01101001 01100101 01110011 00100000 01110111 01101000 01100101 01110010 01100101 00100000 01100100 01100001 01110100 01100001 00100000 00111101 00100000 00100111 01001001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01001110 01100001 01101101 01100101 00100111 00101001 00001101 00001010 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 00100000 01000000 01110010 01100101 01110000 01101111 01110010 01110100 01100101 01111000 01100101 01100011 01110101 01110100 01100101 00100000 00100000 00111101 00100000 00100111 01000101 01011000 01000101 01000011 01110101 01110100 01100101 00100111 00100000 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00100000 00100000 00100000 00100000 00001001 00111101 00100000 00100111 01101101 01100001 01110011 01110100 01100101 01110010 00100111 00100000 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 00100000 01000000 01110011 01101111 01110101 01110010 01100011 01100101 01110000 01100001 01110100 01101000 00100000 00100000 00100000 00100000 00100000 00111101 00100000 00100111 01011100 01011100 00100111 00101011 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00101011 00100111 01011100 00100111 00101011 01000000 01110011 01100101 01110010 01110110 01100101 01110010 01101110 01100001 01101101 01100101 00101011 00100111 01011111 00100111 00101011 01000000 01101001 01101110 01110011 01110100 01100001 01101110 01100011 01100101 01101110 01100001 01101101 01100101 00101011 00100111 01011111 01100010 01100001 01100011 01101011 01110101 01110000 00100111 00001101 00001010 01010011 01000101 01010100 00001001 00001001 01000000 01101110 01101111 01110111 00001001 00001001 00001001 00111101 00100000 01010010 01000101 01010000 01001100 01000001 01000011 01000101 00101000 01010010 01000101 01010000 01001100 01000001 01000011 01000101 00101000 01010010 01000101 01010000 01001100 01000001 01000011 01000101 00101000 01000011 01001111 01001110 01010110 01000101 01010010 01010100 00101000 01010110 01000001 01010010 01000011 01001000 01000001 01010010 00101000 00110101 00110000 00101001 00101100 00100000 01000111 01000101 01010100 01000100 01000001 01010100 01000101 00101000 00101001 00101100 00100000 00110001 00110010 00110000 00101001 00101100 00100000 00100111 00101101 00100111 00101100 00100000 00100111 00100111 00101001 00101100 00100000 00100111 00100000 00100111 00101100 00100000 00100111 00100111 00101001 00101100 00100000 00100111 00111010 00100111 00101100 00100000 00100111 00100111 00101001 00100000 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00001001 01000000 01100010 01100001 01100011 01101011 01110101 01110000 01001110 01100001 01101101 01100101 00001001 00001001 00111101 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00101011 00100000 00100111 01011111 01100100 01100010 01011111 00100111 00100000 00101011 00100000 01000000 01101110 01101111 01110111 00100000 00100000 00001101 00001010 00001101 00001010 01010011 01000101 01001100 01000101 01000011 01010100 00100000 01000000 01100011 01101101 01100100 00100000 00111101 00001001 00100111 01100010 01100001 01100011 01101011 01110101 01110000 00100000 01100100 01100001 01110100 01100001 01100010 01100001 01110011 01100101 00100000 00100111 00100000 00101011 00100000 01000000 01100100 01100010 01101110 01100001 01101101 01100101 00100000 00101011 00100000 00001101 00001010 00001001 00001001 00001001 00001001 00100111 00100000 01110100 01101111 00100000 01100100 01101001 01110011 01101011 00100000 00111101 00100000 00100111 00100111 00100000 00101011 00100000 01000000 01110011 01101111 01110101 01110010 01100011 01100101 01110000 01100001 01110100 01101000 00100000 00101011 00100000 00100111 01011100 00100111 00101011 00100000 01000000 01100010 01100001 01100011 01101011 01110101 01110000 01101110 01100001 01101101 01100101 00100000 00101011 00100111 00101110 01100010 01100001 01101011 00100111 00100111 00100111 00100000 00101011 00100000 00001101 00001010 00001001 00001001 00001001 00001001 00100111 00100000 01110111 01101001 01110100 01101000 00100000 01110011 01110100 01100001 01110100 01110011 00100111 00100000 00001101 00001010 00001101 00001010 00001101 00001010 01010000 01010010 01001001 01001110 01010100 00100000 00100000 01000000 01100011 01101101 01100100 00100000 00001101 00001010 00001101 00001010 01001001 01000110 00100000 00100000 00100000 00100000 00100000 01000000 01110010 01100101 01110000 01101111 01110010 01110100 01100101 01111000 01100101 01100011 01110101 01110100 01100101 00100000 00111101 00100000 00100111 01000101 01011000 01000101 01000011 01110101 01110100 01100101 00100111 00100000 00001101 00001010 00100000 00100000 00100000 01000010 01000101 01000111 01001001 01001110 00100000 00001101 00001010 00100000 00100000 00100000 00100000 00100000 00100000 00100000 01000101 01011000 01000101 01000011 00100000 00101000 01000000 01100011 01101101 01100100 00101001 00001101 00001010 00100000 00100000 00100000 01000101 01001110 01000100 00100000 00001101 00001010 00001101 00001010

Why won’t it work?

Because it’s missing an apostrophe.

So if that’s a bunch of ones and zeroes that have to be in the right order just to back up a database – how many ones and zeroes need to be in the right order to keep our databases and systems running right?  I mean really, if you think of your T-SQL code, then the SQL executables and the binaries there, and the operating system it rides on, and if your systems are clustered and virtual, you’ve got your clustering layer, and your virtualization layer, and all the software and firmware of your SAN, not to mention the app that might ride on top of your code.  It actually gets pretty mind boggling that it all works at all, and makes it that much more important that the code we as SQL folks write, is flawless.

Given that we often think of the big, complex things about SQL, I thought I’d just bring a small bit to your attention.

Take care – be safe out there.

Oh – By the way… the code is below:

select  @@servername

declare @servername     varchar(30)

declare @instancename   varchar(30)

DECLARE @reportexecute  varchar(10)

DECLARE @dbname         varchar(100)

DECLARE @backupname     VARCHAR (50)

DECLARE @cmd            VARCHAR(4000)

DECLARE @sourcepath     varchar(250)

DECLARE @targetpath     varchar(250)

DECLARE @now            CHAR(14)

select @servername = (select value from dba.dbo.ServerProperties where data = ‘ServerName’)

select @instancename =(select value from dba.dbo.ServerProperties where data = ‘InstanceName’)

SELECT  @reportexecute  = ‘EXECute’

SELECT  @dbname         = ‘master’

–Note: I’ve got a set of shares set up on all my servers, so tend to write backup scripts as below.

–You may need to change that to a path that’s valid on your system to get this to work.

SELECT  @sourcepath     =

‘\\’+@servername+’\’+@servername+’_’+@instancename

+ ‘_backup’

SET     @now                  = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),

GETDATE(), 120), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”)

SELECT  @backupName     = @dbname + ‘_db_’ + @now

SELECT @cmd             =     ‘backup database ‘ + @dbname

+ ‘ to disk = ”’ + @sourcepath + ‘\’+ @backupname

+ ‘.bak”’ + ‘ with stats’

PRINT  @cmd

IF     @reportexecute = ‘EXECute’

BEGIN

EXEC (@cmd)

END

 
Leave a comment

Posted by on September 30, 2011 in Uncategorized

 

Tags: , ,