T-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]

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]

If you want to have faster turnaround on your forum questions, you will need to provide enough information to the forum users in order to answer your question. In particular, talking about T-SQL questions, there are three things that your question must include: Table scripts Sample data Expected output   Table Script and Sample data Please make sure that anyone trying to answer your question can quickly work on the same data set you’re working on, or, at least the problematic part of it. [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]

Last week I was in the process of migrating a couple of SQL Server instances from 2008 R2 to 2012. In order to let the migration complete quickly, I set up log shipping from the old instance to the new instance. Obviously, the existing backup jobs had to be disabled, otherwise they would have broken the log chain. That got me thinking: was there a way to keep both “regular” transaction log backups (taken by the backup tool) and the transaction log backups taken by log shipping? [Read More]

Now that my SQL Server 2014 CTP1 virtual machine is ready, I started to play with it and some new features and differences with the previous versions are starting to appear. What I want to write about today is a T-SQL enhancement to DDL statements that brings in some new interesting considerations. SQL Server 2014 now supports a new T-SQL syntax that allows defining an index in the CREATE TABLE statement without having to issue separate CREATE INDEX statements. [Read More]

As a DBA, you probably have your toolbox full of scripts, procedures and functions that you use for the day-to-day administration of your instances. I’m no exception and my hard drive is full of scripts that I tend to accumulate and never throw away, even if I know I will never need (or find?) them again. However, my preferred way to organize and maintain my administration scripts is a database called “TOOLS”, which contains all the scripts I regularly use. [Read More]

Recently I had to assess and tune quite a lot of SQL Server instances and one the things that are often overlooked is the location of the system databases. I often see instance where the system databases are located in the system drives under the SQL Server default installation path, which is bad for many reasons, especially for tempdb. I had to move the system databases so many times that I ended up coding a script to automate the process. [Read More]