Non-unique indexes that should be unique
Posted on January 29, 2014
| 2 minutes
| spaghettidba
Defining the appropriate primary key and unique constraints is fundamental for a good database design.
One thing that I often see overlooked is that all the indexes with a key that includes completely another UNIQUE index’s key should in turn be created as UNIQUE. You could argue that such an index has probably been created by mistake, but it’s not always the case.
If you want to check your database for indexes that can be safely made UNIQUE, you can use the following script:
[Read More]SQL Server Agent in Express Edition
Posted on January 23, 2014
| 4 minutes
| spaghettidba
As you probably know, SQL Server Express doesn’t ship with SQL Server Agent.
This is a known limitation and many people offered alternative solutions to schedule jobs, including windows scheduler, free and commercial third-party applications.
My favourite SQL Server Agent replacement to date is Denny Cherry’s Standalone SQL Agent, for two reasons:
It uses msdb tables to read job information. This means that jobs, schedules and the like can be scripted using the same script you would use in the other editions.
[Read More]COPY_ONLY backups and Log Shipping
Posted on January 22, 2014
| 3 minutes
| spaghettidba
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]SQL Server services are gone after upgrading to Windows 8.1
Posted on October 24, 2013
| 2 minutes
| spaghettidba
Yesterday I upgraded my laptop to Windows 8.1 and everything seemed to have gone smoothly.
I really like the improvements in Windows 8.1 and I think they’re worth the hassle of an upgrade if you’re still on Windows 8.
As I was saying, everything seemed to upgrade smoothly. Unfortunately, today I found out that SQL Server services were gone.
My configuration manager looked like this:
My laptop had an instance of SQL Server 2012 SP1 Developer Edition and the windows upgrade process had deleted all SQL Server services but SQL Server Browser.
[Read More]Check SQL Server logins with weak password
Posted on September 9, 2013
| 2 minutes
| spaghettidba
SQL Server logins can implement the same password policies found in Active Directory to make sure that strong passwords are being used.
Unfortunately, especially for servers upgraded from previous versions, the password policies are often disabled and some logins have very weak passwords.
In particular, some logins could have the password set as equal to the login name, which would by one of the first things I would try to hack a server.
[Read More]SQL2014: Defining non-unique indexes in the CREATE TABLE statement
Posted on June 28, 2013
| 4 minutes
| spaghettidba
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]A viable alternative to dynamic SQL in administration scripts
Posted on April 22, 2013
| 9 minutes
| spaghettidba
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]Moving system databases to the default data and log paths
Posted on March 22, 2013
| 4 minutes
| spaghettidba
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]dba_runCHECKDB v2(012)
Posted on February 27, 2013
| 3 minutes
| spaghettidba
If you are one among the many that downloaded my consistency check stored procedure called “dba_RunCHECKDB”, you may have noticed a “small” glitch… it doesn’t work on SQL Server 2012!
This is due to the resultset definition of DBCC CHECKDB, which has changed again in SQL Server 2012. Trying to pipe the results of that command in the table definition for SQL Server 2008 produces a column mismatch and it obviously fails.
[Read More]SQL Server and Custom Date Formats
Posted on March 23, 2012
| 1 minutes
| spaghettidba
Today SQL Server Central is featuring my article Dealing with custom date formats in T-SQL.
There’s a lot of code on that page and I thought that making it available for download would make it easier to play with.
You can download the code from this page or from the Code Repository.
CustomDateFormat.cs formatDate_Islands_iTVF.sql formatDate_Recursive_iTVF.sql formatDate_scalarUDF.sql parseDate_Islands_iTVF.sql I was also asked to include a performance chart for the different methods included in the article.
[Read More]