SQL

Let’s face it: column enlargement is a very sensitive topic. I get thousands of emails every month on this particular topic, although most of them end up in my spam folder. Go figure… The inconvenient truth is that enlarging a fixed size column is a long and painful operation, that will make you wish there was a magic lotion or pill to use on your column to enlarge it on the spot. [Read More]

A couple of years ago I blogged about Installing the SQL Server 2014 Language Reference Help from disk. With SQL Server 2016 things changed significantly: we have the new Help Viewer 2.2, which is shipped with the Management Studio setup kit. However, despite all the changes in the way help works and is shipped, I am still unable to download and install help content from the web, so I resorted to using the same trick that I used for SQL Server 2014. [Read More]

This will be no surprise to those who have been working with SQL Server for a long time, but it can be puzzling at first and actually I was a bit confused myself when I stumbled upon this behavior for the first time. SQL Server treats windows users in a special way, a way that could lead us to some interesting observations. First of all, we need a test database and a couple of windows principals to perform our tests: [Read More]

Don’t be fooled by the title of this post: while counting the number of rows in a table is a trivial task for you, it is not trivial at all for SQL Server. Every time you run your COUNT(*) query, SQL Server has to scan an index or a heap to calculate that seemingly innocuous number and send it to your application. This means a lot of unnecessary reads and unnecessary blocking. [Read More]

One of the things I hate the most about “old” databases is the fact that unused tables are kept forever, because nobody knows whether they’re used or not. Sometimes it’s really hard to tell. Some databases are accessed by a huge number of applications, reports, ETL tools and God knows what else. In these cases, deciding whether you should drop a table or not is a tough call. Search your codebase The easiest way to know if a table is used, is to search the codebase for occurences of the table name. [Read More]

Does anybody need another good reason to avoid setting AUTO_CLOSE on a database? Looks like I found one. Some days ago, all of a sudden, a database started to throw errors along the lines of “The log for database MyDatabase is not available”. The instance was an old 2008 R2 Express (don’t get me started on why an Express Edition is in production…) with some small databases. The log was definitely there and the database looked online. [Read More]

Some weeks ago I had to wipe my machine and reinstall everything from scratch, SQL Server included. For some reason that I still don’t understand, SQL Server Management Studio installed fine, but I couldn’t install Books Online from the online help repository. Unfortunately, installing from offline is not an option with SQL Server 2014, because the installation media doesn’t include the Language Reference documentation. The issue is well known: Aaron Bertrand blogged about it back in april when SQL Server 2014 came out and he updated his post in august when the documentation was finally completely published. [Read More]

Lately I spent some time evaluating some monitoring tools for SQL Server and one thing that struck me very negatively is how none of them (to date) has been reporting database free space correctly. I was actively evaluating one of those tools when one of my production databases ran out of space without any sort of warning. I was so upset that I decided to code my own monitoring script. [Read More]

I haven’t been blogging much lately, actually I haven’t been blogging at all in the last 4 months. The reason behind is I have been putting all my efforts in a new project I started recently, which absorbed all my attention and spare time. I am proud to announce that my project is now live and available to everyone for download. The project name is ExtendedTSQLCollector and you can find it at http://extendedtsqlcollector. [Read More]

In my last post I showed a query to identify non-unique indexes that should be unique. You maybe have some other indexes that could be unique based on the data they contain, but are not. To find out, you just need to query each of those indexes and group by the whole key, filtering out those that have duplicate values. It may look like an overwhelming amount of work, but the good news is I have a script for that: [Read More]