Voice of the DBA

Author: way0utwest

  • Easy Database Space with dbatools

    One of the things that I’ve seen DBAs query for is free space in a database. In fact, this is one of the most common questions posted on SQLServerCentral by newbies that are trying to manage their instances and ensure they don’t use too much space or run out of space.

    The most common way to do this is with sp_spaceused. This system stored procedure has been around for a long time and is commonly used. However, it’s cumbersome to me. I get two result sets, I get data formatted in both MB and KB, it’s hard to use for multiple databases, and incorporating it with some automated tracking gets annoying. Not hard, just annoying.

    Get-DbaDatabaseFreespace

    Enter dbatools. One of the cmdlets included is Get-DbaDatabaseFreespace and this does exactly what you’d expect. This takes the name of an instance as a parameter, and when you run it, you get a bunch of information about your databases and their space usage.

    2016-12-29 09_17_01-powershell

    These are good numbers, but with PoSh, I can easily just get the data I want. For example, I can add a –Databases parameter and see specific databases.

    2016-12-29 09_31_21-powershell

    I can also limit reformat the data in a table and limit what’s returned:

    2016-12-29 09_32_29-powershell

    This flexibility to easily see data is one thing that I like about PosH. I could pipe this into a filter and look for those items that are of a certain size, or have a certain percentage value.

    Putting this into a report, or even an alert that might get sent to DBAs is a great way to keep an eye on space and ensure that you don’t run out of space. Of course, tracking this over time is easier with a tool like SQL Monitor, but for quick checks, this dbatool is very handy.

    If you haven’t played with these, I’d recommend you do this. This collection makes working with SQL Server and PoSh much easier.

  • Your 2017 Goals

    Last week I wrote some predictions for 2017, and plenty of you added your own thoughts. Overall, security is a concern for most people, with little hope things will improve. I certainly hope they do, but like most of you, I think expediency and profit will overwhelm any chance that organizations will make patching their systems a priority, much less improving their data security.

    As the new year came and went, I ran across a great post from Buck Woody that’s a few years old. Inthe post, Buck notes that he likes to make goals in the new year with his family, not resolutions. That sounds like semantics, but I agree with Buck. There is power in making goals instead of open ended changes. Goals can help drive you, especially if they’re measurable and time boxed. Having someone else keep you accountable also helps.

    This week, as we start a new year of our careers, I wanted to ask you what goals you might have. List a few and think about asking a friend, spouse, parent, child, someone else to help hold you accountable and check on your progress each month. If you have just a few goals, chances are that you will achieve some of them. Don’t forget, you can always add a few more later in the year if you finish everything.

    Perhaps you want to learn something about a new technology. If that’s the case, make a specific goal. Don’t try to “learn Azure”, but instead think about “building a database to track my own movie reviews in Azure and use it for a year.” If you want to get better at a specific technology, like T-SQL, then think about challenging yourself with a specific set of problems, like the T-SQL challenge. Or complete something like the Advent of Code in T-SQL.

    Set aside some time to improve your career and take a step forward. Data professionals working with SQL Server have no shortage of new features, subsystems, and solutions to practice with as our platform grows wide and deeper each year.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Watch the Manual DBA v Automated Deployment Challenge

    At last year’s SQL in the City, I played the part of a manual DBA making changes and deploying those from development to test, staging, and production environments. Actually, I don’t think we went to production because I didn’t have time.

    My co-worker, Tom Austin, showcased the automated process using SQL Source Control and other Redgate tools. As much as I believe in using tools, and despite the fact I regularly use them,  even I was impressed by how much less work the automated process took.

    Watch our session below.

  • Quick Prompt Tips–Custom Procedure Templates

    One of the things that I often do is create stored procedures. The syntax for doing so is simple, but it has a number of items that need to be included. SQL Prompt makes this much quicker with the “cp” snippet. When I type “cp”, I get this:

    2016-09-13 13_01_43-SQLQuery1.sql - (local)_SQL2016.AlwaysEncryptedDemo (PLATO_Steve (64))_ - Micros

    I can hit Tab and I have a snippet, but it has a lot of things I don’t like in it. Plus, I want to save time coding, not have to remove some commented out items.

    2016-09-13 13_06_17-SQLQuery1.sql - (local)_SQL2016.AlwaysEncryptedDemo (PLATO_Steve (64))_ - Micros

    Let’s make this more efficient. I can go to the Snippet Manager under the SQL Prompt menu and select it. When it opens, the snippets are highlighted, so I type “cp” to get to the Create Procedure snippet.

    2016-09-13 13_36_11-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    I click edit and see the code, which I highlight before deleting this.

    2016-09-13 13_36_45-SQL Prompt - Edit Snippet

    Then I paste in the code that makes more sense to me. Notice that in my case, I have two placeholders, not one (as shown above).

    2016-09-13 13_37_03-SQL Prompt - Edit Snippet

    The code I use has a header in the procedure, and the procedure name is used both for the definition and a GRANT EXECUTE. I include the begin..end structure for the procedure with the cursor starting in the spot where I’d put code. I also have a placeholder for a role name. It looks like this.

    CREATE PROCEDURE $procedure_name$

    /*
    Description:

    Changes:
    Date       Who Notes
    ———- — —————————————————
    */
    AS
    BEGIN
    $CURSOR$
    END
    GO

    GRANT EXECUTE ON $procedure_name$ TO $role_name$

    In practice, when I type “cp” and hit Tab, I get the code with the procedure highlighted. I can enter a name here. Note what I typed is also placed in the GRANT statement at the bottom.

    2016-09-13 13_40_02-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    Once I am done and hit Tab, my cursor jumps to the next placeholder, in this case, the role name. Notice that SQL Prompt knows this is a role and gives me a list of roles and users to choose from.

    2016-09-13 13_40_44-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    When I finish and hit Tab again, the cursor jumps to the point between the BEGIN and End where I will enter my code. Now my job begins.

    2016-09-13 13_42_26-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    This little customization gives all my procedures some standard look as well as ensuring that I can quickly build procedures without a lot of mundane, tedious typing.

    Try out this quick SQL Prompt tip and see how much smoother your coding goes. And if you’re not a SQL Prompt user, download an evaluation today and see how much more efficient you can be when writing T-SQL code.

    You can see a complete list of SQL Prompt tips at Redgate.