Voice of the DBA

Category: Blog

  • Keeping the Single User Connection

    A reader was having issues with a script. They ran their code from PoSh (Invoke-Sqlcmd), setting single user and then trying to rename a database and its files. However, occasionally, they’d lose the single user connection to another user. Frustrating, but it wasn’t a PoSh issue.

    The ALTER DATABASE commands don’t really specify how SINGLE_USER works. What happens is that if you set single user, the first user gets the connection, whether that’s sysadmin, dbo, or a regular user. This means that when you set a database to single user mode, you need to be one of the users in the database.

    The change to single used is blocked, until all other connections disconnect. One way to ensure this happens quickly is to add the WITH ROLLBACK IMMEDIATE clause, which disconnects users.

    The original poster’s issue was that while they used the clause, their connection was in the master database. This meant that another user could potentially connect to the database, grab a shared lock, and prevent renaming files.

    If your connection is in the user database, then when you issue the ALTER DATABASE WITH ROLLBACK IMMEDIATE and disconnect users, then you have the only connection. This should prevent any issues.

    Quick Demo

    Let’s see this in a simple way. I’ve got a demo database that I use, and I’ll open three connections. The first will be as a sysadmin here:

    2017-10-24 09_54_17-Connect to Database Engine

    and then the other windows I’ll switch the connection to a non-privileged user.

    2017-10-24 09_55_17-SQLQuery20.sql - Microsoft SQL Server Management Studio

    At this point, I have a few connections to my database.

    2017-10-24 10_00_48-SQLQuery19.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (56))_ - Microsoft

    Let me now reset the database option.

    2017-10-24 10_01_30-SQLQuery19.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (56))_ - Microsoft

    Everything works here, let’s check the next session. If I switch to another tab, I’ll run a query. After a second or two, I get this:

    2017-10-24 10_02_29-SQLQuery21.sql - (local)_SQL2016.EncryptionDemo (AEUser (61)) Executing..._ - Mi

    SQL Server has disconnected my session. If I try to reconnect (this login has EncryptionDemo as the default), I get this:

    2017-10-24 10_17_04-SQLQuery22.sql - Microsoft SQL Server Management Studio

    If you need to perform maintenance, and want to ensure you have the connection, change context to the database before setting single user mode.

  • Adding a Format SQL Button to the Redgate Toolbar

    I’ve gotten used to CTRL+K,Y to format SQL with SQL Prompt, but a customer wanted a button on the toolbar. It’s fairly easy to do, but I thought I should document the process for others.

    First, if you click the small area on the right of a toolbar in SSMS, you’ll get an “Add or Remove Buttons” menu, as shown here.

    2017-11-14 16_29_31-~vs7FB.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft SQL

    If you click that, you’ll see this dialog.

    2017-11-14 16_29_39-~vs7FB.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft SQL

    In  this case, I’m happy with the buttons, I want to customize my toolbar. Click that option. This opens up the dialog below, and I’ll want to click the “add Command” to add a menu item as a button.

    2017-11-14 16_29_50-Customize

    From here, I get a list of all SSMS menus. In this case, I’ve clicked the Dimension item, and I can see all the possible menu items on the right.

    2017-11-14 16_30_02-Add Command

    However, I want a SQL Prompt item, so I need to scroll down the left to SQL Prompt. Once I click that, I see the commands on the right,

    2017-11-14 16_30_11-Add Command

    Now scroll the right to find Format SQL.

    2017-11-14 16_30_23-Add Command

    Click OK and then your new button appears in the list.

    2017-11-14 16_30_32-Customize

    And on the toolbar.

    2017-11-14 16_30_41-~vs7FB.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft SQL

    Now you GUI clickers can reformat SQL quickly.

  • In a basement, a long, long time ago… #tsql2sday

    It’s T-SQL Tuesday time and this is a good one. Read the invitation and proceed.

    Who Influenced Me?

    I’ve been working SQL Server for a long time. Since 1991, in fact, and had held 4 jobs as a DBA or developer by 1999. I was comfortable with the platform, and had actually published some articles in early 1999 for a small UK journal. My career was going well and I felt very comfortable with the platform (v6.5 in my company) at that time.

    As a developer, I knew T-SQL well, and was still learning things from reading articles, but there weren’t many SQL problems I couldn’t come up with some solution for. As a DBA, I knew the tricks to keeping a large, busy instance running, including the DR tricks to ensure my restores worked well on new hardware.

    I was lucky enough to get my company to fund a trip to the initial PASS Summit. I went, along with my wife and infant son, to visit Chicago. We went to Comiskey Park on the last ,day of the baseball season, braving chilly weather along with about 1,500 people to watch the White Sox. I’m not sure my wife has been to a game since…

    In any case, my main memory of that event was being the basement of some hotel in downtown Chicago. There were a few rooms in use, and we packed into see sessions on various topics. One was delivered by Kalen Delaney, @sqlqueen, who was perhaps the person I saw as an amazing speaker and teacher. I’d read her Inside SQL Server 7.0 book, anticipating an upgrade. I was delighted to see her speak, and perhaps more thrilled to shake her hand and get an answer to a question.

    That was quite a highlight in my career.

    Since then I’ve seen Kalen speak many time, and we’ve become friends. I’m always glad to get a hug and a few words with her, but each time, I remember that early, semi-awestruck time when I met her.

    She’s inspired me, both as a data professional and a teacher.

    If you ever get the chance to meet Kalen, take a moment or two with her. You won’t regret making the effort to introduce yourself and ask a question. I know I haven’t.

  • Changing the Default DB for a Login–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

    Then, as a sysadmin, I right clicked to attach a database back.

    2017-10-23 15_59_17-SQLQuery6.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

    and all of a sudden I get this error:

    2017-10-23 15_59_08-Microsoft SQL Server Management Studio

    Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

    Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

    2017-10-23 16_01_20-Connect to Database Engine

    Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:

    ALTER LOGIN [PLATO\Steve] WITH DEFAULT_DATABASE = MASTER

    If you are on an older version, something like this will work:

    exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

    Once that was done, the GUI dialog worked. A quick and easy fix in this case.

    SQLNewBlogger

    As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10  grabbing a few screenshots and putting this post together.

    You could do the same thing. Show that you can recover from errors.