THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

  • SSMS 17.2 is now available

     

    The newest version of SQL Server Management Studio is now available for download. Lots of new features and fixes. The details and download links can be found here.

    Happy managing,

    Andy Kelly

  • SQL Updates Newsletter – July 2017

     

     

    SQL Updates Newsletter – July 2017

    It has been a few months since my last post on these but here is the latest update about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month. This shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/07/31/sql-updates-newsletter-july-2017/

    Andy

  • Free E-Books From Microsoft Again

     

    The Director of Sales Excellence at Microsoft is once again giving away free E-Book downloads.  Apparently millions of them in fact on all sorts of topics and products.  Well not millions of different E-Books but millions of them were downloaded last year and I suspect this year will be even more. In any case it is a great opportunity and there there is no catch so what are you waiting for? The link below will get you to the blog with the details on how to download them.

    Free Microsoft EBook Giveaway

    Enjoy,

    Andy

  • Getting Excited about my Practical Performance Monitoring Pre-Con

     

    I was working on the demo’s for my Practical Performance Monitoring Pre-Con at this years IT / Dev Connections in San Francisco and I have to admit I got a little giddySmile.  The session is geared towards helping a DBA get started in monitoring their SQL Server Instances, especially when they don’t have the ability to buy a 3rd party utility. This doesn’t mean to imply that you have to be a SQL Server beginner to appreciate the session. I know lots of more experienced DBA’s who simply never had the time or training to get a handle on daily performance monitoring. That is where this shines and is one of the reasons I got giddy if you will. You see I use a lot of this code that I was prepping for the demos daily. In fact most if not all stemmed from real life needs. I try to concentrate on the aspects of monitoring that give the biggest bang for the buck and make a real difference in the average DBA’s daily chores. Hence the “Practical” part of the title for this session.  I have been teaching and presenting for many years now but I still get excited with the thought of passing on my knowledge and experience with sessions like this. If any of you are in need of this type of training I am positive you won’t be disappointed with what you can walk away with. Like I said this is code and techniques I use every working day and I know you can immediately use it in your environment as well. I can’t wait and I hope to see you there.

    Andy Kelly

  • Finding When Someone Last Ran a DBCC

     

    The other day someone on Twitter asked how to find out when or who last ran a DBCC FREEPROCCACHE command on their SQL Server instance. Since this is not an unusual request I figured I would post a few examples of how to find DBCC commands or even many similar commands or events. If you had a regular or Extended Event trace going you could easily look for these types of commands assuming you had the correct events. However there are two ways that I know of to find these using built in default features that I will outline here.  The first is actually a trace but I am talking about the black box trace that runs on every SQL Server by default unless you explicitly change that.  A little disclaimer here in that BOL states this feature will be removed in a future version of SQL Server but it is in all the current versions so I won’t lose any sleep over that right now. This trace which is also known as the default trace captures a number of events and writes them to a series of log files in the log folder underneath where the SQL binaries normally reside.  It uses a set of 5 (if memory serves me right) files that start with one named log.trc. Once that file gets full or you restart SQL Server it will open a 2nd file with the name log_1.trc and increment the _n each time this rollover process occurs. When you get the 5th file it will go back and overwrite the 1st one giving it a new name and thus there will only be 5 total.  As you can guess this means the data is transient and can and usually will be overwritten at some point in the future depending on how much activity or restarts you may have.

    If you don’t know where the files reside or what the current names are don’t worry. The query below will tell you which is the current log file for the default trace. I encourage you to look at BOL for the definitions of each of the columns as there are some other useful information in there as well.

    SELECT t.[id], t.[path] FROM sys.traces AS t WHERE t.[is_default] = 1 ;

    Normally the default trace ID is 1 but check to be sure. To see which events the default trace captures you can run the query below. Note that I used 1 in the query. I have listed the ones for SQL Server 2016 below that are included with the default trace.

    SELECT DISTINCT [eventid], [name]
        FROM  fn_trace_geteventinfo(1) AS e
            INNER JOIN sys.trace_events AS t  ON e.[eventid] = t.[trace_event_id] ;

    image

     

    As you can see one of the events is Audit DBCC Event and for this example that is exactly what we were looking for. So to see if anyone has run this command in the timeframe the trace files cover you can run the script listed below.

    DECLARE @Path VARCHAR(500) ;
    SELECT @Path = path FROM sys.traces WHERE [is_default] = 1 ;

    SET @Path = SUBSTRING(@Path,1,LEN(@Path) - CHARINDEX('\',REVERSE(@Path))) + '\Log.trc' ;
     
    SELECT * FROM ::fn_trace_gettable(@Path,default)
        WHERE TextData LIKE 'dbcc free%'

    Let me explain a few things about the script. I am getting the path of the current trace file and placing it into a variable. The current file name will almost certainly have a suffix of _nn just before the .trc extension.  If I were to run the script as is I would only be reading the current log file and not the other 4 that preceded it. If all you care about is the current log file then fine but most will want to search all the existing log files. One way to do this is to simply replace the current file name with just log.trc and use default as the 2nd parameter as I did above in the fn_trace_gettable function. The default parameter value tells the function to read all files from that one onward. even though log.trc doesn’t actually exist it knows how to handle it and reads all of the existing trace files in order.

    So if the string that we search on (here we use ‘dbcc free%’) is in any of the files it will return the matching rows. You may have to adjust the wildcards and such but I think you get the idea. Again remember that the data is transient so always look at the StartTime column in the logs to ensure you know which Date and Time range you are looking at. You can do something like this but I will leave that up to you.

    SELECT MIN(StartTime) AS [Begin], MAX(StartTime) AS [End]  FROM ::fn_trace_gettable(@Path,default)

    A word of caution in that I never bothered to see just how resource intensive this function is. while I don’t expect any issues with normal use it is not something you want to be searching on every second. Be sensible and you should have no problems.

    The 2nd way to look for things like this is to read each of the SQL Server error logs and search for your particular string. Keep in mind that the text in the error logs may not match the text in the trace files. Actually it probably won’t match so you may have to adjust your search string accordingly. The idea here is similar to searching the trace files only we will use some built in stored procedures called sp_readerrorlog and sp_enumerrorlogs instead of the trace function since we are looking in a completely different set of files.  Let me give you a brief walk thru of the script below. First we declare some regular and table variables and assign our search string. We then use sp_enumerrorlogs to populate one of the table variables with a row for each SQL Server error log file. Since the number of files used is easily configurable we won’t know how many there are without this step. We get the max number (the first log is always 0) and start looping thru each one and call the sp_readerrorlog procedure passing 3 parameters. The 1st parameter defines the log number we want to read. The 2nd parameter is a 1 which denotes to read the SQL Server error log vs. say the SQL Agent log. And the 3rd parameter is the search string we want to search for. You can also pass an option 4th parameter to further refine the search if needed. We place any results into the other table variable and read them all at the end.

    DECLARE @MaxID INT, @LogNum INT = 0, @SearchFor VARCHAR(256) ;
    DECLARE @Logs TABLE ([Archive #] INT, [Date] DATETIME, [Log File Size (Byte)] BIGINT) ;
    DECLARE @LogData TABLE ([LogDate] DATETIME, [ProcessIngfo] VARCHAR(64) , [Text] VARCHAR(MAX)) ;

    SET @SearchFor = 'DBCC FREE' ;

    INSERT INTO @Logs ([Archive #], [Date], [Log File Size (Byte)] ) EXEC sp_enumerrorlogs ;

    SET @MaxID = (SELECT MAX([Archive #]) FROM @Logs)  ;

    WHILE @LogNum <= @MaxID
    BEGIN

        INSERT INTO @LogData ([LogDate], [ProcessIngfo], [Text]) EXEC sp_readerrorlog @LogNum, 1, @SearchFor ;

        SET @LogNum = @LogNum + 1 ;
    END ;

    SELECT * FROM @LogData ORDER BY LogDate ;

     

    You may find events in the error log that are not in the trace and visa versa. For instance you can see data or file autogrowth in the default trace but not in the error logs. And the error logs will show failed login attempts or backups and the trace will not. So use this as you see fit but each can be a vital tool to see what is happening in your SQL Server instance on a daily basis.

    Enjoy,

    Andy Kelly

  • Seldom Used Keyboard Tricks

     

    I was giving an internal talk on SSMS productivity trips and there were a few that I believe are seldom used but can be a real time or keystroke saver that I would like to mention. To the best of my knowledge these will work in any version of SSMS from at least 14.0 onward and likely earlier but I can’t verify older versions at this time. Since these are basically Visual Studio shortcuts they also work in Visual Studio and SSDT to the best of my knowledge. The first is the ability to select text in a vertical fashion as shown in the examples below. Before we get to how to do that I know your first question is why would you want to do that. Well I will leave all the possibilities up to you since everyone has slightly different techniques and circumstances. However I am confident that after seeing a few examples it will spark interest in many of you and you will immediately think of times when this will help you code more efficiently.

    OK now on to the HOW to do this. That part is really pretty simple and there are two ways that I know of off hand. BTW, I am sure that after playing with this for just a few minutes you will have figured it all out anyway but I will post some instructions and examples just the same. The first way is to position your mouse cursor on the line at the point in which you want to start. Then you can use your keyboard and press SHIFT – ALT and then use the arrow keys as you see fit. Or you can press only the ALT key and move your mouse in the direction you wish to highlight or operate on. Both techniques mentioned will give you the same results. I find the mouse method a little easier overall but YMMV. I will use the block of code shown directly below as the basis for most of the subsequent examples.

    image

    Keep in mind that there are 2 types of processes or operations that you might use this for. The first is typically used when you want to insert something into existing code at a certain spot but across multiple lines at once. The example directly below shows what that would look like when selecting the rows at a specific position or insertion point. If I placed my cursor just in front of the number 1 in the first row and then pressed SHIFT – ALT – Down Arrow 7 times I would get a thin (in this case blue) line like the one with the arrow pointing to it. More on this technique shortly.

    image

     

    However if I needed to use the second type of operation I would take that one step further and move my cursor / mouse left or right to highlight one or more characters in each row as shown below.

    image

    Once I have highlighted a set of rows / columns I can then replace that highlighted text with new text of my choosing in a number of ways. If I had something in my clipboard I can do a paste and all of the highlighted sections in each row will be replaced with the clip board contents.  OK I hear someone saying well you could have just done a search and replace. Well sure but if you look closely you will see each character that I had highlighted in the vertical column was a different value in each row. I would have had to do 8 different search and replaces instead of just one using this method. Or I can simply start typing and you will see the original text is instantly replaced with what ever I type as shown in the partially completed replacement below.

    image

    Another great benefit of this technique is that you can be selective in which blocks of code you want the text replaced or acted upon. For instance I want to operate on only 4 of the 8 lines of code. If I did a search and replace I would have to do this one line at a time and choose which line I wanted to act upon and which I wanted left alone.

    image

    Here I selected the middle 4 lines and upper cased the word select. How did I do that? Well this technique also works with standard keyboard short cut’s such as Ctrl- Shift-U which will upper case what ever is highlighted as I did below.  I am sure you can begin to see the possibilities of this technique.

    image

     

    In this example shown below I use the other technique mentioned earlier to position the cursor at a certain place vertically across what ever rows I need.

    image

    I often have to terminate a number of rogue processes at the spur of the moment and this technique comes in very handy for that. I can identify the session_id’s using something like sp_whoisactive but when there are multiple sessions it can get tedious and error prone to manually type the “KILL nnn” commands for each one. Now I can simply copy the list of session_id’s and paste them into a query window and set the insertion point directly in front of all the session_id’s at once. Now I simply type “KILL “ and it instantly fills this in at the correct spot before all the lines at once. Again this is impossible to do with search and replace as each row is different text. Now all I had to do is execute the batch and smile as I rest assured the SQL Server instance is once more safe from these rogue processesSmile.  Obviously you will find more uses for this based on your own environment but I think you get the idea.

    image

    And finally I wanted to point out that if you are using the pure keyboard method don’t forget to use the SHIFT along with the ALT. If you simply press just the ALT key plus an up or down arrow you will shift the line the cursor is on up or down one line for each arrow press. The example below shows what happens if the cursor was anywhere on line 3 and the ALT and up arrow key was pressed. While that tip can certainly come in handy as well it may not do what you expect. If you use the ALT and Mouse method this will not happen.

    image

    image

    I realize this was a long winded set of examples but I had fun outlining them. This is a very under utilized set of features that is built into some of your most important daily tools so remember to take advantage of them. I have also include a few links to some more extensive lists of short cuts for SSMS that you should find useful.

    BOL SSMS Keyboard Shortcuts

    Andy Mallon’s SSMS Keyboard Shortcuts

    Red-Gate SSMS Keyboard Shortcuts

     

    Enjoy,

    Andy

  • SQL Updates Newsletter–May 2017

     

    I missed posting the last few months of these but here is the latest update about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month. This shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/05/31/sql-updates-newsletter-may-2017/

    Andy

  • New Cumulative Updates for SQL Server 2014

     

    Microsoft announced two new CU’s for SQL Server 2014 yesterday. Below are the links to the KB’s and some quick facts for each CU.

    Cumulative Update 12 for SQL Server 2014 SP1

    SQL Server 2014 Service Pack 1 Cumulative Update #12 Quick Facts

    12 Issues Resolved

    BI\Analysis Services

    1

    BI\Reporting Services

    1

    Integration Services\Engine

    1

    SQL Engine\High Availability and Disaster Recovery

    2

    SQL Engine\Replication

    4

    SQL Engine\Service Broker

    2

    SQL Engine\SQLOS

    1

    Grand Total

    12

    Build: 12.0.4511.0

    Cumulative Update 5 for SQL Server 2014 SP2

    SQL Server 2014 Service Pack 2 Cumulative Update #5 Quick Facts

    24 Issues Resolved

    BI\Analysis Services

    4

    BI\Reporting Services

    3

    SQL Engine\Backup_Restore

    1

    SQL Engine\Column Stores

    1

    SQL Engine\DB Management

    1

    SQL Engine\Extreme OLTP

    1

    SQL Engine\High Availability and Disaster Recovery

    2

    SQL Engine\Metadata_Infrastructure

    1

    SQL Engine\Query Execution

    1

    SQL Engine\Query Optimizer

    1

    SQL Engine\Replication

    4

    SQL Engine\Search

    1

    SQL Engine\Service Broker

    2

    SQL Engine\SQLOS

    1

    Grand Total

    24

    Build: 12.0.5546.0

     

    Andy

  • Latest SQL Server 2014 CU Updates

     

    Microsoft released a few more Cumulative Updates for SQL Server 2014 as listed below. There are a number of interesting fixes so I highly recommend if you have SQL Server 2014 to take a look at the list of hotfixes included in each update.

    SQL 2014 SP1 CU11

    SQL 2014 SP2 CU4

    Happy patching,

    Andy

  • Stopping Execution of Future Batches

     

    There is a command that as far as I can tell has been in the product since SQL2008 but I was woefully unaware of. Actually I find many others are unaware as well and hence the blog post. The command is SET NOEXEC which has options for ON or OFF.  Normally this is OFF by default and the issue I was trying to solve was this:

    I have a TSQL Script with N individual batches separated by a GO. Or simply N many individual statements or sets of statements that I don’t ever want to be executed all at once. In real life I often have a single script with lots of pieces (batches) vs. many individual scripts for convenience, ease of use etc. However I don’t want to accidently run the entire script or any other statements that I don’t manually highlight and run. A simple example is shown below:

    PRINT 'Got Here 1' ;
    GO
    PRINT 'Got Here 2' ;
    GO
    PRINT 'Got Here 3' ;
    GO
    PRINT 'Got Here 4' ;
    GO

    If we were to hit F5 (or however you execute your TSQL statements in SSMS) without highlighting any statement(s) they would all be executed, one batch after the other. Even if one batch were to fail or we had a THROW in that batch it would fail at that point but execution would continue immediately after the next GO until the end. This is where SET NOEXEC ON comes into play. If I add that at the beginning of the script all succeeding code would not be executed. The statements would only be compiled and not actually run. It would look like this:

    SET NOEXEC ON;

    PRINT 'Got Here 1' ;
    GO
    PRINT 'Got Here 2' ;
    GO
    PRINT 'Got Here 3' ;
    GO
    PRINT 'Got Here 4' ;
    GO

    And if needed you could always set it back OFF as the example below show:

    SET NOEXEC ON;

    PRINT 'Got Here 1' ;
    GO
    PRINT 'Got Here 2' ;
    GO
    PRINT 'Got Here 3' ;
    GO
    SET NOEXEC OFF;

    PRINT 'Got Here 4' ;
    GO

    If you run each of these in SSMS you will quickly see how this setting affects the actual execution of the statements in the batches. Adding this command at the top of your scripts will help to prevent unwanted execution of the code in that script whether it be the entire set of statements or any individual ones. Once this is in the script you would either have to comment out the SET NOEXEC ON statement or manually highlight the statement or set of statements you want to run before hitting F5. I am pretty confident that many of you will find this tip useful. I know I did and wish I knew about it much earlier.

    Good luck,

    Andy

  • SQL Updates Newsletter – January 2017

     

    Here is the latest update about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month which shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/01/31/sql-updates-newsletter-january-2017/

    Andy

  • Latest SSMS Download (16.5.3)

     

    There was a slight issue with the SSMS version that was released a few days back (16.5.2) which has been fixed and replaced with release 16.5.3. If you want the latest SQL Server SSMS 2016 release or if you already loaded the 5.2 version you should get this one.

    SSMS 2016 Version 16.5.3

    Andy

  • Latest SQL Server Updates

     

    There are some new updates for SQL Server 2012 and SQL Server 2016 that were released today. I am not sure what happened to SQL 2014 but I suspect those will be out shortly as well. Here are the links and the associated KB’s.

    SQL Server 2012 Service Pack 2 CU16KB3205054

    SQL Server 2012 Service Pack 3 CU7   – KB3205051

    SQL Server 2016 RTM  CU4KB3205052

    SQL Server 2016 Service Pack 1 CU1KB3208177

     

    Andy

  • SQL Updates Newsletter – December 2016

     

    Another friendly reminder about the monthly newsletter that a good friend of mine (Fany Carolina Vargas) from Microsoft puts together each month which shows a whole host of goodies related to SQL Server which include but are not limited to the following:

    • Recent Releases and Announcements
    • Recent White Papers, E-Books, Training and Tutorials
    • Monthly Script Tips
    • Issue Alerts
    • Recent Blog Posts and Articles
    • Recommended KB’s to Review

    The main page of the monthly newsletters can be found here and this months update can be found here. I highly encourage each of you to look this over now and each month as I am confident there is something useful in there for each and every one of you. Here is the full URL for this months newsletter as well.

    https://blogs.msdn.microsoft.com/sqlupdates/2017/01/03/sql-updates-newsletter-december-2016/

    Andy

  • SQL Server 2014 CU3 for SP2 and CU10 for SP1 are Available

     

    Just an FYI for those of you on SQL Server 2014 SP2 that there is a new CU available for download can can be found here. Also if you are still on SP1 for SQL Server 2014 you can get CU10 as well here.  https://www.microsoft.com/en-us/download/details.aspx?id=51186  There are a number of interesting fixes but one in particular that has caused issues for me is described in this KB here. It is when statistics are blown away when you rebuild a partition on a partition aligned index. I for one am happy to see this finally fixed but there are many more. Check them out and happy updating.

    Andy

More Posts Next page »

This Blog

Syndication

Privacy Statement