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.

sp_whoisactive

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.

http://whoisactive.com/

https://github.com/amachanic/sp_whoisactive/releases

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];
GO
DBCC SHRINKFILE (Tempdev, 20480);----remember this number is megabytes. Make sure to be exact. 
GO
DBCC SHRINKFILE (Temp02, 20480);
GO

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….

DBCC SHRINKFILE (LogicalFilename, EMPTYFILE);
GO

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!

DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESESSIONCACHE;
GO
DBCC FREESYSTEMCACHE ( 'ALL');
GO

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….

DBCC SHRINKFILE (Temp05, 1);
GO

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?

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 
GO

CREATE CERTIFICATE sp_whoisactiveCERT
ENCRYPTION BY PASSWORD = 'EnterYourPasswordHERE' 
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' 
GO

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
FROM CERTIFICATE sp_whoisactiveCERT
GO

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

GRANT VIEW SERVER STATE 
TO sp_whoisactive_login
GO

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

ADD SIGNATURE TO sp_WhoIsActive 
BY CERTIFICATE sp_whoisactiveCERT
WITH PASSWORD = 'This is the same password you used for the certificate creation' 
GO

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!