Press "Enter" to skip to content

Curated SQL Posts

HDFS Cheat Sheet

Published 2016-11-25 by Kevin Feasel

Tim Spann has produced a guide for HDFS shell commands:

There are number of commands that you may need to use for administrating your cluster if you are one of the administrators for your cluster.   If you are running your own personal cluster or Sandbox, these are also good to know and try. Do Not Try These In Production if you are not the owner and fully understand the dire consequences of these actions.   These commands will be affecting the entire Hadoop cluster distributed file system.  You can shutdown data nodes, add quotas to directories for various users and other administrative features.

Many of the commands in this list should be familiar if you know much about Linux or Unix administration, but there are some Hadoop-specific commands as well, like moveFromLocal and moveToLocal.

Comments closed

Changing Font Sizes

Published 2016-11-25 by Kevin Feasel

Jay Robinson has a couple Visual Studio Settings files to change Management Studio font sizes:

I present technical sessions now and then – my local PASS group, SQL Saturdays, internal groups at my workplace, etc. I frequently find myself adjusting the fonts inside SQL Server Management Studio to make sure my material is readable on the big screen. I’ve also been in the audience plenty of times, watching with sympathy as one of my cohorts agonizingly navigates this problem.

Usually, it goes something like this. They first find the [100%] tucked away in the lower left corner of the text window, and blow that up to 150 or 200 percent. Then they run their query to find that the results are still at 100%. So then they eventually find the Options dialog under the Tools menu, find the Fonts and Colors branch of the tree, and then groan when they realize they have to figure out which three or four of the 30 different fonts they need to change. Sometimes, they’ll give up there and just go use ZoomIt (which any good technical presenter should have available anyway), but constantly bouncing around with ZoomIt will get old quickly over the course of an hour-long session.

Having VSSettings files is a good idea, although I’ve noticed oddities when also trying to change colors (e.g., darker theme for regular development but a lighter theme for presentations), so test out any settings files you want to use and make sure you can swap back and forth without seeing weird behavior.

Comments closed

Viewing Power BI Audit Logs

Published 2016-11-25 by Kevin Feasel

Ginger Grant shows how to give a Power BI Administrator rights to view the audit logs:

The Audit Logs are the third menu item in the Power BI Admin Portal. As you can tell by looking at a copy of the screen below, Audit Logs are not really part of Power BI. Yes the ability to log all of the content in Power BI exists in the Audit Logs, but so does the ability to review the audit logs for things like Exchange Mailbox Activities and User Administration Activities.

If the Office 365 Administrator has granted a user Power BI Administration rights, this is what the newly minted Power BI Administrator will see when trying to access any search activities. It appears that you the user has rights, until that user tries to do anything on the screen. At that point, this error window appears.

Click through to see how to grant audit log access.

Comments closed

HAL0003

Published 2016-11-25 by Kevin Feasel

Kenneth Fisher is on a mission:

  • HAL0001 randomly stopped you from making DDL changes.

  • HAL0002 stopped you from using NOLOCK in code.

  • HAL0003 will not let you touch a given table (DiscoveryOne as it happens) and will disable your login and kill your connection if you try.

He’s got a ways to go, but I applaud his long-term vision.

Comments closed

Uninstalling SQL Server 2016 SP1

Published 2016-11-25 by Kevin Feasel

David Alcock sounds an important note:

All of that is very good however a word of warning has been issued from the MSSQL Tiger Team if you are using SP1 on the “lower” version:”you might see some unforeseen errors or databases might even be left in suspect state after uninstallation of SQL Server 2016 SP1. Even worse would be if the system databases are using new features for example, partitioned table in master database, it can lead to SQL Server instance unable to start after uninstalling SQL Server 2016 SP1″.

This makes sense:  if you’re using new functionality and try to revert back to a version without that functionality available, there could be an issue.  David links to a test script you can use to see if your database is using any new features.

Comments closed

Building Custom Containers

Published 2016-11-23 by Kevin Feasel

Andrew Pruski grabs the vNext Docker image and creates a new container and image with his modifications:

Once the command has executed you can connect remotely via SSMS using the server name and the port we specified above. The database that we created in the original image will be there, along with the data that we entered!

This is where containers start to come into their own in my opinion. You can build your own custom images and quickly spin up multiple instances that already have all the databases that you require!

Containerizing databases is something I haven’t quite got my head wrapped around yet (because we want to maintain that state over time, even if the image gets deleted), so I’m interested in seeing where this series goes.

Comments closed

Locks In Sp_configure

Published 2016-11-23 by Kevin Feasel

Kendra Little discusses the locks entry in sp_configure:

Each lock uses 96 bytes of memory. On the instance in question, 25,000 locks  = 2,400,000 bytes.

That’s only 2.3 MB of memory devoted to locks. Even though 25K  sounds like a lot, the memory footprint for that is pretty darn small.

I checked back with our questioner, and their instance has 32GB of memory. That’s a pretty small amount in the grand scheme of things (as of SQL Server 2014, Standard Edition can use up to 128GB of memory for the Buffer Pool), but 2.3 MB isn’t anything to worry about, percentage wise.

Read on for advice if you’re seeing your SQL Server instance take a very large number of locks.

Comments closed

SQL Server On Linux Is Boring

Published 2016-11-23 by Kevin Feasel

Grant Fritchey, defying the common opinion (or is he?):

Suddenly though, it was boring. I tested a few Redgate tools (SQL Compare is right there above, connected to Linux) to be sure they worked. No major issues encountered. Great. However, now, it’s just another instance of SQL Server.

I guess I could start complaining that SQL Agent isn’t there… except I’m not that big a fan of SQL Agent, and I can schedule all sorts of stuff to run in Linux using the sqlcmd command line tool.

This is a good kind of boring.

Comments closed

Preventing DBCC DBREINDEX?

Published 2016-11-23 by Kevin Feasel

Dave Mason is looking for a way to prevent a user from running DBCC REINDEX:

After a little digging, I discovered the related database user is a member of the db_ddladmin fixed database role. Members of that role are permitted to run DBCC REINDEX. Since I have existing (more sensible) code in place for index maintenance, I don’t want the DBCC REINDEX operations to continue. Here’s the problem: I can’t find a direct way to DENY a database user from running DBCC commands. T-SQL syntax doesn’t support something like DENY DBCC TO <user> or DENY DBCC REINDEX TO <user>. MSDN documentation tells me the equivalent ALTER INDEX command requires at minimum ALTER permission on the table or view. I guessed that revoking or denying ALTER TABLE privileges might prevent a user from executing DBCC DBREINDEX, but that does not appear to be the case.

That’s painful.

Comments closed