Browse by Tags

The overhead of a non-unique clustered index
20 October 2008 00:21

So, we all know that if we create a clustered index that is not unique that we will incur a 4 byte overhead right? Well not always because as usual, it depends..... When you create a non-unique clustered index SQL server must maintain uniqueness so it adds a hidden 4 byte column which is populated for each non-unique row (not every row) but what many people may... Read More...

by ACALVETT | 3 comment(s)

Filed under: SQL 2005, SQL 2000, Tips, Database Engine

Changing the Data Files Location after Installation
10 August 2008 10:28

The other day i wanted to change the "Data Files" location for a 2005 database engine installation and a 2005 Analysis Services installation which you can specify under the advanced options during installation. I quickly found out that there appears to be no documented ways to do this other than uninstall SQL Server and install again specifying a new... Read More...

by ACALVETT | with no comments

Filed under: SQL 2005, Tips, Analysis Services, Database Engine

Transaction log backup deadlock
19 February 2008 23:10

Recently we started to see deadlock errors when backing up our transaction logs. The "important" part of the error is shown below. Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful. What this meant was that the transaction log backup... Read More...

by ACALVETT | with no comments

Filed under: Maintenance Plans, SQL 2005, Tips, System Procedures, Deadlock

SSMS Log file viewer and Deadlock Graphs
23 January 2008 16:02

Firstly I must say a big thank you to Microsoft for the new deadlock trace flag 1222. Compared to the trace flag output for 1204 & 1205 that you had to use in SQL 2000 it’s a walk in the park to interpret. Anyway, back to the post at hand! This is a quick FYI as i`m not going to go through how to interpret a deadlock graph because Bart Duncan does a fantastic... Read More...

by ACALVETT | with no comments

Filed under: SQL 2005, Tips, Deadlock

The Job whose owner kept coming back......
30 December 2007 19:25

I thought i would share this little quirk about the SQL Agent jobs for maintenance plans. One of our members of staff had left and we had the usual case of a few jobs failing with: "Unable to determine if the owner (DOMAIN\xxx) of job <JOB_Name> has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))." So, we went around and updated... Read More...

by ACALVETT | 3 comment(s)

Filed under: Maintenance Plans, SQL 2005, Tips

My old mate sp_recompile
12 October 2007 11:33

As soon as i saw the error messages in the logs i thought to myself "Oh my, that did not happen in testing" (ok, maybe it was more colourful than that). We were creating a clustered index on a tiny little table and the index went through fine. However, the application started to generate the message " Could not complete cursor operation because the table schema... Read More...

by ACALVETT | 2 comment(s)

Filed under: SQL 2005, SQL 2000, Tips, System Procedures

2005 - Alter failed for server XXX
13 February 2006 18:04

Thats exactly what i thought!! What the hell does that mean? Well, for me this occured in the context of running the maintenance plan integrity check task. It had been running fine for weeks but suddenly boom every time it ran it would fail with "Alter failed for server XXX". There was no obvious reason for this error so i had to dig out profiler to see what... Read More...

by ACALVETT | with no comments

Filed under: Maintenance Plans, SQL 2005, Tools, Tips