Press "Enter" to skip to content

Author: Kevin Feasel

Watch Named, Nested Transactions

Published 2015-12-17 by Kevin Feasel

Gail Shaw finishes her outstanding series on transactions:

The error was thrown by the ROLLBACK statement. As such, the transaction is still open, the locks are held and the transaction log space can’t be reused. Unless the application that called this was checking for open transactions, that transaction could potentially be left open for quite some amount of time, causing blocking and/or the transaction log to grow.

It’s not just that someone in the future might call the code from another stored proc within a transaction, it’s also that it might be that the code is called from an application which started a transaction. Or called from SSIS which started a transaction. It’s very hard to ensure that code is never called from within an existing transaction

Read the whole thing.

Comments closed

Set SQL Server Startup Parameters With Powershell

Published 2015-12-17 by Kevin Feasel

Mike Fal has a function for managing SQL Server startup parameters:

Looking back at the previous blog post, changing the the startup parameters through the SMO is pretty easy with the ManagedComputer class. In some ways, it is too easy. As Shawn calls out, you could easily overwrite the full string and remove the startup locations for your master database (and breaking your instance). This is where tool building can be such an aid, because by wrapping the change code in a function, we can build some safety mechanisms to protect us (or others) from doing harm when trying to make this sort of change. The function I wrote is not terribly long, but I’ll spare you the whole thing by letting you view it on GitHub. We’ll use our time better by going over how I constructed it while focusing on some of my tool building principles.

Thanks to Mike for making that available to the community.

Comments closed

Dynamic Pivoting

Published 2015-12-16 by Kevin Feasel

Michael Bourgon has a pivot script:

Say you have records from your monitor that happens to Track EventLogs.
We’ll call it EventLog_Tracking for argument’s sake (hint hint http://thebakingdba.blogspot.com/2015/05/powershell-eventlogtracking-capturing.html) and want to look at trends over time.

Dynamic pivoting is possible (I have an example en passant in a tally table presentation I created a couple years back), but it’s not the easiest thing in the world.

Comments closed

Keep .Net Framework Up To Date

Published 2015-12-16 by Kevin Feasel

Allan Hirt with a public service announcement:

Microsoft recently published an official .NET team blog post reiterating that .NET Framework versions 4, 4.5, and 4.5.1 will no longer receive security updates, support, or hotfixes as of January 12, 2016. This was first announced back in August of 2014, so it’s not like this is new news, but I can say from experience virtually no one is talking about it. MS’ new post talks more about the upgrade path. To sum it up, you need to install .NET Framework 4.5.2, 4.6, or 4.6.1 to be considered supported when it comes to your .NET Framework version. Security is a real issue for many, and those responsible may not know that your version of .NET Framework could be a possible attack vector. Is your security team aware of this impending problem? How will this affect your version matrices (you do have those, right?)?

This is a cross-cutting concern, and I know a majority of database administrators aren’t directly responsible for .Net Framework patches, but work with whoever is responsible and keep them up to date.

Comments closed

Mass Table Renaming

Published 2015-12-16 by Kevin Feasel

Monica Rathbun needed to rename a table in a lot of scripts.  Here’s how she did it:

The quick answer that I came up with is to script out all of the stored procedures into a single query window.  This can be done easily through the GUI.  Once that is complete, I can easily do a “Find & Replace” on the table name and we’re done!

Ideally, those stored procedures are in source control already and you do your find-and-replace there, but sometimes you can’t control that.

Comments closed

Beware Statistics Corruption

Published 2015-12-16 by Kevin Feasel

Robert Davis shares a story of statistics corruption causing certain queries to fail:

I suspected that there was some difference between the queries that failed and the ones that were successful in SSMS. It ran the query they gave me, and I got the same error. I got disconnected and no further error info was returned. I also verified that the same query was successful on the otehr two tables mentioned. No errors on the other tables.

I wanted to know what error was causing the connection to be terminated, so I checked the SQL log and discovered that every time it failed, it was generating a stack dump. Before I was done investigating, it had generated 21 stack dumps. The key user-usable error info in the log was:

* Exception Address = 00007FF93BCF7E08 Module(sqlmin+00000000001E7E08)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000000

It turns out that CHECKDB & CHECKTABLE do not look at statistics.  If you find yourself in this situation, it’s not a bad idea to see if this is the cause.

Comments closed

Measuring IOPs

Published 2015-12-16 by Kevin Feasel

Joey D’Antoni shows us how to measure IOPs (I/O Per Second) on a SQL Server instance/server:

That handy SQL Server:Resource Pool Stats counter and it’s Disk Read IO/sec and Disk Write IO/sec provide you with the data you need to give your SAN admin. In this screenshot this is an Enterprise Edition instance, and you can see my resource pools on the left side—so if you are using resource governor, you could use this to classify IO workload by application for potential chargeback situations.

Very useful, and when combined with Resource Governor, can help you throttle I/O effectively (as opposed to wildly flailing in the general direction of a fix).

Comments closed

Visualizing SQL Saturday Data

Published 2015-12-16 by Kevin Feasel

Tamera Clark analyzes SQL Saturday Nashville data:

Select the funnel from the visualizations (1), select track in the field list (2) and drag track to the values box (3). (Image 5 below) Now we need to customize this visualization.  Select the paint brush to edit. (Image 6 below) I recommend giving each of the tracks a different color. Since Tracks are determined by the organizer the data maybe similar so you might want to use the same colors for more than one data point. You should also update the title Count of Tracks by Track sounds silly. Now we have a lovely display of session distribution by track.

She came up with a nice-looking set of information describing sessions and presenters for SQL Saturday Nashville 2016.  I love seeing this kind of thing and hope it becomes mainstream among SQL Saturday organizers (maybe to the point where some of this is built into the SQL Saturday website).

Comments closed

Fractals In SSMS

Published 2015-12-15 by Kevin Feasel

Slava Murygin gives us a script to generate fractals:

That is most difficult operation. At first, SSMS can’t show more than 5000 separate objects at the same time. In order to show more we have to construct “MULTIPOLYGON” or “GEOMETRYCOLLECTION”. That only the way to fit more objects into SSMS screen. However it is still limited.
In order to combine triangles in a single object we divide them in buckets (Line 106).
In this example I just making number of buckets approximately equal to a number of objects within each bucket. Making lower number of buckets will increase processing speed, but produce less colors. All objects in one collection will have the same color.
Also, I wrapped the last query in extra CTE to have more flexibility on results formation.

This is a fun post showing some of the power and limitations of geometry types in SQL Server and their display in SSMS.

Comments closed