Hiatus-I am back!

After some life adjustments, changing hosting platforms, and I am sure many other things, I am back!

Entrance Im Here GIF by Leroy Patterson

The focus of the site is still the same; a random DBA blogging about related topics and hobbies. Speaking of, I will be building a workshop/office in my house. Therefore, I plan on creating some time lapses of the work and posting them here (and maybe Youtube?) So stay turned for that.

So same blog, same face, same content.


Installing SQL Server Sustainably

I heard you were in need of a quick tip to help you install SQL Server on those shiny new VMs you just spun up. Oh? You have 10 to install? Well, thankfully there is an easy way for you to make that happen. It is relatively easy and does not require a third party application. However, the only disclaimer is that your target VMs all have the same resouce setup, as this method only applies when you the target VMs are identical in nature. With that out of the way….

Not Another Install Video

Fire up your install GUI on one of the VMs you plan to install SQL Server on. Go through the GUI completely, but STOP before you hit install. Yes, I get it, you do have to do some work upfront but it will save you plenty of time. I promise. There is a configuration file that is generated once you have made it to the final step before install. This configuration file will contain all the settings from your GUI walkthrough, and also includes some features that are not set by the GUI. Find that config file, copy it somewhere and skim through all the settings to make sure you have everything set.

*This may be a good time too look at some of the settings you do not recognize to become more familiar with them. The good news, however, most of these settings can be changed after install if needed.

So, now, with this config file, if you have not already guessed, you can use to install with the same settings on all of your new servers with ease. The GUI will still load and make you walk through some of the steps, but it cuts down on the time to finish. If you have any AD service users set to run the SQL Server services (which you should anyways as it is BEST PRACTICE), you will need to re-enter the password as the config file does not store the password for security purposes.

Now just do that, about 20 times

And there you have it, a quick tip for installing SQL Server on multiple servers that did not require you to install a third party app in your environment for it to work.

*Not Included

So you didn’t know, eh?

Something new I was recently made aware of was that Reporting Services are no longer updated with SQL Server CUs as of SQL Server 2017. What this means is that you have to download the Reporting Services installation media SEPARATELY. That, my friends, is frustrating.

My colleague found this and shared it with me, which then prompted us to upgrade our Reporting servers to the most recent version we could. Our hope is that by updating the Reporting Service, we might be able to resolve an issue that has been plaguing us for a couple of weeks.

The Problem!

Currently, we have a production Reporting site that houses a decent amount of reports. Nothing too crazy, but enough. Supposedly after we updated to SQL Server 2017 CU23, we had users complaining about their reports not loading, and then not being able to open any report in general. Now, when I say “open a report”, I mean clicking into the folder that houses the report, and then clicking the report itself. The user does not even get to the screen where they can input their parameters; they just receive a loading screen that sits there indefinitely. We threw more RAM/CPU at the server, because it should have been provisioned higher in the first place, but that did not completely solve the issue. The work around required a full server reboot, not just a services reboot, which we found odd. 

Finally, after going down the rabit hole of why Reporting Services are now a standalone install, the report server was updated to SQL Server 2017 CU24 and updated to the latest Reporting Services version for 2017. We have not received any reports of issues, but I am not sold on us resolving the issue.

Did you look at the ReportServer Log?

Yes, we looked into the logs, and yes we looked into the executionlog view (executionlog3) for details of the reports. However, the reports never actually fail, since they never actually GET to even passing the parameters. Maybe you can sense some frustration in this post?

Anyways, hoping that resolved the issue but since it has been awhile since I posted something SQL related, I figured I would share this. Full disclosure, I did not find this info-my colleague did. So thanks to he/she who will not be named due to privacy reasons. However, thank you!

Checking Who Be Active

After talking about Ola’s MaintenanceSolution for SQL Server, I wanted those new/accidental DBAs out there to make sure they add a very helpful (and wildly used) stored procedure to their toolbox.


Created by Adam Machanic, sp_whoisactive provides the user with all the current active connections running at the time of execution. This can be very useful when trying to identify what may be inflicting pain on your SQL Server or, in some cases, help you figure out who keeps executing that pesky “select * From”. It is easy to use, and is well documented. You can read the documentation here or on Adam’s Github page. He has recently moved his updates to Github, so if you want the most up to date version of sp_whoisactive, go there.

As always, I do want to point out a useful parameter that pulls the sleeping spids out as well, so you can see the connections that are still open, but just waiting for the “next task.” This is an over generalization but the idea is that these could still impact your SQL Server’s performance, even if they are not active currently.

sp_whosisactive @@show_sleeping_spids = 2

We can see the session_id 54 is in a “sleeping” status. While it is not currently querying our database server, at any time, this user could execute a new query. Where this is helpful is sometimes identifying a user or even an application, with an open connection to the server that “recently” executed the query (details in the “sql_text” column of the results). We can see system data of the performance impact the query had, and in some aspect, what to expect next time they do it again. I am over generalizing here, and I am sure some of you are screaming at the screen that I am forgeting about X detail, Y context, etc. But, for those out there not “classically” trained in Database Administration, this is what they need. 

Last Bit

Finally, my last tip, just make sure to install the query on the same database (whether that is your master database or a user database created for your tools) as the rest of the stored procedures you use for the maintenance of your servers. Hopefully this quick post helped someone out there. Thanks again for reading.



TempDB; Shrinking Responsibly

I just wanted to post this as a “lessons learned” for me, a very green DBA, as I wish I would have known this before I ran into an issue in production. Yes, production. I hear all the people screaming “You Monster! How could you!” Believe me, I was in the same boat. Thankfully, many other people have done the same!

So, let us get to the point. There will come a time when someone runs a query that bloats your tempdb (and any of the separate data files) and you begin to receive alerts. For some people, the first response should ALWAYS be to just extend the drive, then look at why your tempdb expanded to fill the drive. 

However, some of us may not be as lucky and have to find a way to make space. In this case, we shrink-responsibly!

My first move was to make sure I could even shrink the files. 

USE [tempdb];
DBCC SHRINKFILE (Tempdev, 20480);----remember this number is megabytes. Make sure to be exact. 
DBCC SHRINKFILE (Temp02, 20480);

If all goes well, you should be good right? Some people suggest that you do not need to restart SQL Service. However, in my case I needed to as I had another piece to the problem I have not touched on yet. Regardless, I would strongly suggest if you have already scheduled the downtime to shrink the tempdb files, it would not hurt for the restart.

BUT! My problem was not a rouge transaction that expanded the tempdb files to max out the drive. No, the problem was my stupidity in a previous scheduled downtime to add more tempdb files to match the rest of our database servers. In doing so, I added TOO MANY. Yes…TOO many. So I was going back and fixing my mistake. In this case, the SQL gods were making it a point to teach me a lesson and here is why.

I had to shrink all of the files down, and delete the extra one. So I shrank the files, and went to delete the extra tempdb file. In order to do so, a file needs to be “empty” in order to be removed. So….


And then the problem hit me:

DBCC SHRINKFILE: Page 3|44101 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1

There are work tables present. Of course!

Now, you could try and restart the instance to see if the worktables magically go away, but I want to reduce the amount of times we restart the service. Therefore, I took to the community and came across some suggestions about clearing various caches(sp?). Let’s give it a shot!


But life isn’t easy right? Why would it be! I tried again to empty the file and it did not work. Thankfully,  I have a FANTASTIC mentor, who suggested “Why don’t you just try to shrink it down to 1MB, do a restart, and delete it.” At this point, I was happy for suggestions. So….


Then the restart of the service. So far so good, as the file DID shrink to close to 1MB. Let’s try to delete via SSMS…and SUCCESS! I could not believe it. I spent two nights, during off hours, to try and fix this and the answer was as simple as just shrinking the file to 1mb, and delete. 

I would like to link two wonderful articles that I found in the process of battling the tempdb, and you should definitely follow both Erin and Andy. They are fantastic people, and smart. Very smart.

Moral of the story: Don’t shrink, add space. Because, should you ever really shrink your SQL Server database? Andy has the answer for you

Non-Sysadmins can run SPs Too!

One of the things I have been focusing on lately is improving the processes that are currently in place and to automate tasks to give myself (and my colleague) more time focusing on larger projects. Most recently, I began writing some knowledge base for our help desk that would walk agents through basic troubleshooting steps. The hope was to have them gather some information up front (active connections, blocking sessions etc) to help speed up the resolution time for us. Since the agents are not system admins, they lack the necessary rights to gather certain data…or do they?

Parks And Recreation Reaction GIF

The following articles ( Brent Ozar and Adam Machanic) walk through how to create “certificates” that, through steps you can follow through, will allow non-sysadmins the ability to execute specific stored procedures. In my example, I will use sp_whoisactive (sp for activity monitoring), but ANY stored procedure will work.

Creating A Certificate

USE master 

WITH SUBJECT = 'Who is Active', 
--START_DATE = 'If you want the cert to activate at a certain time, enter it here'
EXPIRY_DATE = '9999-12-31' 

Great, now we have the certificate created, we can move on to creating the user from the certificate.

Creating The User

CREATE LOGIN sp_whoisactive_login

Simple enough right? The next bit is to grant permissions, and since I want this to be as “tied down” as much as possible, we are going to grant the login VIEW SERVER STATE. This will grant the ability to return server state information from Dynamic Management Views and Functions, that can be used to monitor the health of a server and diagnose issues. I do not want the agents to have the ability to anything more than that. More on Dynamic Management Views and Functions can be found here.

Granting Permissions

TO sp_whoisactive_login

With the necessary rights applied, we are ready to move to the final step. We will need to add the signature to the actual stored procedure (sp_whoisactive in this case).

Adding A John Hancock

WITH PASSWORD = 'This is the same password you used for the certificate creation' 

I should mention, it would help if you have sp_whoisactive installed first before you execute the last steps. But after that you are done!

Final Remarks

Huge shout out to Brent and Adam for providing the great details on how to utilize this “feature” in SQL Server, and making it easy to implement. As I said earlier, you can really implement this with any stored procedure. In a future post, I will be showing off a simple powershell script that will help you keep track of updating any of your “third-party” stored procedures that you have implemented into your arsenal across multiple servers. Until next time, thanks for reading, leave a comment below, or reach out to me if you have any questions!