Priyanka Chouhan talks about identifying and handling blocked processes:
In order to maintain data integrity within the database, locks are used on resources like tables, rows, pages etc. by any process that wishes to use them. This is done to ensure multiple process don’t alter the same resources at one time leading to data inconsistency. When a process wishes to lock a resource, it sends a request to the server and the server grants it. However, when a process requests lock on a resource that has already been locked by another process, the request is denied. The requesting process is thus placed on “hold” until the resource it is requesting for isn’t released. In this situation, the requesting process is called a blocked process, and such a process could put a halt on other subsequent processes and activities scheduled on the server.
Thus identifying a blocked process and releasing it requires a DBA team to check the application database blocking. Additionally, here are some other techniques that may be used to find out which processes are creating a block on the server:
My favorite method, not mentioned, is Adam Machanic’s sp_whoisactive.
David Alcook grabs backup history:
It’s a very common task that we have to query backup timings and other bits of info from msdb.
Now its pretty straight forward to select this data for a particular database or use the MAX function for example to return the last backup but how do we get the last 5 FULL backups per database?
MSDB has a lot of useful backup information, so if you’ve never dug into it, I recommend taking your time and seeing what is available.
Mickey Stuewe looks at Git support in Red Gate’s SQL Source Control tool:
At the beginning of October 2015, everything changed. That was when Redgate announced SQL Source Control users could now push to and pull from remote Git repositories.
And not just from within SQL Source Control – from within SQL Server Management Studio (SSMS).
Your developers are probably using Git, so you should too. If your developers are using Mercurial, I applaud them. If they’re using SVN, CVS, TFVC, Vault, or anything else (Visual SourceSafe?), flip a coin and decide to use Git/Mercurial or the generally accepted tool…
Windows Management Framework 5.0 is available, says Max Trinidad:
Finally! The Windows Management Framework version 5.0 RTM is available for download for all down level Operating systems: Windows 7, Windows 8.1, Windows Servers 2008 R2, Windows Server 2012, and Windows Servers 2012 R2.
There are several interesting features here. My favorite one is “Just Enough Administration (JEA)”; after all, who wants too much or too little administration?
Kendra Little explains why Always-On Availability Groups are better than database mirroring:
SQL Server Availability Groups are growing up. SQL Server 2016 adds more features and improvements, and these include options to run SQL Server in different domains, or without a domain.
That brings Availability Groups closer to feature parity with Database Mirroring in SQL Server 2016.
The recent improvements caused me to think about the ways Availability Groups are better than Database Mirroring that aren’t listed as “big features”. (By “big features” I mean having more than one replica, having multiple databases fail over in a group, having multiple failover partners in SQL Server 2016, readable secondaries, having a basic load balancing concept in SQL Server 2016.) I think there’s a few.
Thinking about this for a small business with just enough technical expertise to get by but no dedicated DBA, mirroring seemed like a better choice because there were fewer moving parts. With 2016 and AG bugfixes, that might change the calculus.
Steve Jones shows us how to track logins with Extended Events:
I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.
I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.
If you’re not already familiar with Extended Events, that grace period is slowly slipping away. Profiler’s going away sometime, and it’ll be a rude shock for a lot of DBAs. Don’t be one of those…
Do you see what Microsoft did?
Perfect integration of Datazen into Reporting Services!
We even notice the differentiation being made between KPI’s, mobile reports and paginated reports.
Sounds like a good reason to grab 3.2.
Tom Norman has a series going on scrubbing data before moving it to lower environments.
Have you ever heard, “but it works on my machine”? Is this because of data perfection in Development and QA or having specific failure conditions? Can you think of all the data scenarios that accompany Production data? What about performance? Why did the application fail? What happens if I add this index?
Here are the reasons I believe you should get a scrubbed version of your production database into your Development, QA and UAT environments.
All of us have Production database servers and hopefully you also have additional database servers for Development, QA and UAT. Some IT shops will also have a Continuous Integration server and maybe other servers. If you only have Production servers this needs to be addressed and is outside the scope of this post. In the locations where I have worked, we also have a Scrub server. The question is, when a script executes, do you know what environment the query is executing in? Most scripts will not care what environment the script executes in but other scripts could cause damage in a Production environment. For example, if the script is removing email addresses so you don’t spam your clients with automated email messages, you would not want the script to execute in a Production environment.
So how do you make your database server environmentally aware?
The concept of a dedicated scrub server is interesting; it’s not something I’ve thought about before. I’m looking forward to seeing the rest of the series.
Kevin Eckart alerts us to a display bug in SQL Server Reporting Services 2014:
In SSRS 2014 there is a bug in rendering pages in Internet Explorer where the Save, Refresh, and Print buttons will all show on different lines in IE.
Keep those instances patched, folks.
Chris Webb shows us a case in which Power BI Desktop’s DAX calculation might not be what you expect:
The difference here is that the Month Number and Month Name fields are both present – they have to be since the query has to sort by Month Number. In MDX the order of members on a hierarchy can be set inside the model; in a DAX query you can only sort using an ORDER BY clause and for that to work, the field you’re ordering by must be present in the query.
This kind of feels like an edge case, but if you run into it, it’s good to know that it’s not a bug.
Kevin Feasel
2015-12-22
Administration