Category: T-SQL Tuesday

Everyone’s A Sysadmin

Published 2017-03-15 by Kevin Feasel

Raul Gonzalez has a script to see if you’re in a terrible security situation:

I’m not sure what it’d scare me more, if there are many [sysadmin] or just [sa] because the first one is scary, but the second involves to find out who knows the [sa] password and knowing who did what, can be a real pain in the neck.

One way or another, as I said, I want to know the different people and level of access to my server[s], so back in the day I created this stored procedure which now I want to share with you guys.

We can find all that info using DMV’s and in my case I use sys.server_principals, sys.server_role_members and sys.server_permissions and some stored procedure which I bet it’s not that well known, sys.xp_logininfo which help to get more granular picture from Windows AD Groups.

Click through for the script.

Comments closed

Diplomacy In Action

Published 2017-03-14 by Kevin Feasel

Mala Mahadevan describes a bad backup situation:

I dug into the script – it was simple – it pulled an alphabetical list of databases from system metadata and proceeded to back them up. It didn’t do this one simple thing – leave TEMPDB off the list. So when the backups got down to TEMPDB, they promptly failed. Now as a smart person – I should have just communicated this to her and had it fixed quietly. But, I was young and rather hot headed at that time. It amazed me that a DBA with several years of experience did not know that TEMPDB cannot be backed up. So, I waited until the team meeting the next day. And when the said job failure came up again – I said that I knew the reason and stated why. I also added that this was a ‘very basic thing’ that junior DBAs are supposed to know. I was stopped right there. It did not go down well. Her face was flaming red because a consultant showed her up in front of her boss in a bad light. She said she would talk to her boss and collegues the next day (several of whom were nodding their heads disapprovingly at me) and meeting was abruptly adjourned.

In this case, I don’t think there were any good actors.

Comments closed

Heaps Of Fun

Published 2017-03-14 by Kevin Feasel

Rob Farley talks about an issue with heaps:

What I found was that the list of IDs was being stored in a table without a clustered index. A heap. Now – I’m not opposed to heaps at all. Heaps are often very good, and shouldn’t be derided. But you need to understand something about heaps – which is that they’re not suited to tables that have a large amount of deletes. Every time you insert a row into a heap, it goes into the first available slot on the last page of the heap. If there aren’t any slots available, it creates a new page, and the story continues. It doesn’t keep track of what’s happened earlier. They can be excellent for getting data in – and Lookups are very quick because every row is addressed by the actual Row ID, rather than some key values which then require a Seek operation to find them (that said, it’s often cheap to avoid Lookups, by adding extra columns to the Include list of a non-clustered index). But because they don’t think about what kind of state the earlier pages might be in, you can end up with heaps that are completely empty, a bunch of pointers from page to page, with header information, but no actual rows therein. If you’re deleting rows from a heap, this is what you’ll get.

This guy’s heap had only a few rows in it. 8 in fact, when I looked – although I think a few moments later those 8 had disappeared, and were replaced by 13 others.

But the table was more than 400MB in size. For 8 small rows.

Read the whole thing, including Rob’s reluctance to post on this topic.

Comments closed

Test Accounts

Published 2017-03-14 by Kevin Feasel

Andy Yun had the task of cleaning up test accounts:

Monday morning came along and as scheduled, our Production DBAs executed my script before start of business. 30 minutes later, the frantic calls started to reach us. Seems some of our clients could no longer “make widgets!” Accounts that they needed to route data were gone! My manager and I looked at one another in horror – we were only deleting internal accounts!!! We didn’t hesitate and immediately had our Prod DBAs back out the change with my backout script, before the rest of the United Stated started business. The backout was executed immediately and all was back to normal, but business folks were pissed and wanted to know what happened.

It was good foresight to have a backout script.

Comments closed

Rolling The CHECKDB Dice

Published 2017-03-14 by Kevin Feasel

Arun Sirpal tells a harrowing story of a Recovery Pending situation:

Looks like I had open transactions while my transaction log got lost during an outage. I tried switching the database online but that failed.

Msg 5181, Level 16, State 5, Line 4 Could not restart database “FAT”. Reverting to the previous status. Msg 5069, Level 16, State 1, Line 4 ALTER DATABASE statement failed.

Accessing the database is the real challenge now.

Moral of the story: have backups and have good luck.

Comments closed

T-SQL Tuesday 87 Roundup

Published 2017-02-20 by Kevin Feasel

Matt Gordon has the roundup for T-SQL Tuesday 87:

Finally, as a first time host, I was obviously hoping that this topic would garner some responses, but you never know until you hit that post button whether you’ve selected something of interest to the community or not. Thankfully, this month’s topic picked up views from over 20 countries and over 20 blog responses. The list (with a brief post-by-post commentary from me) is below. Happy reading and thanks again for reading/writing/participating!

Read on for the full list of respondents.

Comments closed

String Splitting And Concatenation

Published 2017-02-16 by Kevin Feasel

Aaron Bertrand and Steve Hughes talk about string splitting, and Aaron also discusses string concatenation. First Aaron:

That may not look like a massive simplification, but don’t forget about all the logic buried behind the table-valued function in the first example. And if you’re like several shops I know, if you look across your codebase and see all the messy uses you have for either of these methods, the benefits should be even more clear – and testing should bear that the performance savings compared to traditional, expensive methods are the sweetest part of the deal.

And Steve:

The STRING_SPLIT function will return a single column result set. The column name is “value”. The datatype will be NVARCHAR for strings that are NCHAR or NVARCHAR. VARCHAR is used for strings that are CHAR or VARCHAR types.

These two functions are small, but come in handy quite frequently.

Comments closed

AT TIME ZONE And Reports

Published 2017-02-16 by Kevin Feasel

Rob Farley shows how to use AT TIME ZONE without sacrificing performance:

Because how am I supposed to know whether a particular date was before daylight saving started or after? I might know that an incident occurred at 6:30am in UTC, but is that 4:30pm in Melbourne or 5:30pm? Obviously I can consider which month it’s in, because I know that Melbourne observes daylight saving time from the first Sunday in October to the first Sunday in April, but then if there are customers in Brisbane, and Auckland, and Los Angeles, and Phoenix, and various places within Indiana, things get a lot more complicated.

To get around this, there were very few time zones in which SLAs could be defined for that company. It was just considered too hard to cater for more than that. A report could then be customised to say “Consider that on a particular date the time zone changed from X to Y”. It felt messy, but it worked. There was no need for anything to look up the Windows registry, and it basically just worked.

But these days, I would’ve done it differently.

Now, I would’ve used AT TIME ZONE.

Read on for the scenario.

Comments closed

Instant File Initialization In DMVs

Published 2017-02-16 by Kevin Feasel

Rodney Landrum shows off a couple new columns in SQL Server 2016 SP1 DMVs:

Microsoft announced many new features in SQL Server 2016 SP1 and the fanfare was mostly centered around the Enterprise features now available in SQL Server 2016 Standard Edition. Many may have missed some hidden gems in the announcement. Two of these are columns added to the existing DMVs, sys.dm_server_services and sys.dm_os_sys_info. The columns provide information for two specific features that previously had to be gathered by opening gpedit.msc and/or scrolling through SQL error logs. I am referring to Lock Pages in Memory and Instant File Initialization (enabled via Perform Volume Maintenance Tasks privilege).

It is now possible to simply query the DMVs to determine if these are being used for the running SQL Server instance.

Click through for the details.

Comments closed

Session Context

Published 2017-02-16 by Kevin Feasel

Ewald Cress looks at SESSION_CONTEXT() as a replacement for CONTEXT_INFO():

SESSION_CONTEXT() brings two major innovations. Firstly, it replaces a 128-byte scalar payload with a key-value structure that can accommodate 256kB of data. You can really go to town filling this thing up.

The second change is less glamorous, but possibly more significant: it is possible to set an entry to read-only, meaning that it can safely be used for the kind of contextual payload you don’t want tampered with. This makes me happy, not because I currently have a great need for it, but because it neatly ties in with things I have been thinking about a lot lately.

Read on for more.

Comments closed