SQL 2005 - Andrew Calvett

Browse by Tags

All Tags » SQL 2005 (RSS)
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: , , ,
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: , , ,
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: , , , ,
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: , ,
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: , ,
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: , , ,
SSMS Restore backup error
14 September 2007 15:31
We had a requirement to allow someone to create and restore databases on a test server today and i thought to myself "Thats easy, i`ll just grant the "Create Any Database" right to the appropriate user, thats when the pain began! The user was using SSMS connecting using a SQL login to restore a database and when they went to specify the backup location they got... Read More...
by ACALVETT | with no comments
Filed under: , ,
The joy of template parameters
15 July 2007 20:23
I thought I would do a quick blog about template parameters since I have been writing allot of standard deployment scripts for our 2005 builds and have used them extensively. So what are they? Well they are place holders and you would you use them in the same places that you would probably put a variable. The key difference comes when you assign the values. With... Read More...
by ACALVETT | 1 comment(s)
Filed under: , ,
The x64 experience
10 July 2007 00:23
The 64 bit beast has been out there for a while now and new servers are generally 64 bit compatible. The first thing that often comes into my mind when i think 64 bit is performance & memory with "compatibility" hot its heels (and recollection of a good article by Linchi Shea ). My fears around compatibility have been pretty much put to rest and we now recommend... Read More...
by ACALVETT | 2 comment(s)
Filed under: ,
Dotty about maintenance plans
22 June 2007 17:36
I went to remotely edit a maintenance plan the other day and found it took a long time to open any of the objects in the plan. I also found that I was getting errors when clicking the drop down to select a database. The reason for the problem was that the local server connection embedded in the maintenance plan had the server name defined as "." rather than an... Read More...
by ACALVETT | with no comments
Filed under: ,
SQL 2005: Procedure cache cleared
05 June 2006 18:15
I don't normally write about a technet article but i must say that 917828 caught my eye and i felt that it was worth highlighting it. The reason it caught my eye is that it describes a number of scenarios where by the whole procedure cache is flushed which will clearly lead to a decrease in query performance whilst the cache builds up again. The most alarming... Read More...
by ACALVETT | with no comments
Filed under: , ,
SQL 2005 - Log Shipping Monitoring?
12 March 2006 18:08
I took a look at log shipping in 2005 for the first time the other day and as i was curious to see how the monitor functionality had been implemented in SQL Server Management Studio i went hunting for it. I've got to say i was greeted with nothing but disappointment and left feeling that i now have a huge mountain to climb before it can be used in production... Read More...
by ACALVETT | with no comments
Filed under:
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: , , ,
SQL 2005 - Maintenance Plans
03 January 2006 18:24
I made a decision that to really get things going and identify "trouble spots" that i would continue my quick win approach. So basically i`m working through installing and configuring our test SQL 2005 box following our SQL 2000 install instructions. Through out this approach i`m documenting and making necessary changes so i can have first drafts of install docs... Read More...
by ACALVETT | with no comments
Filed under: ,