Andrew Calvett

Transaction log backup deadlock

ACALVETT — Wed, 20 Feb 2008 04:10:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/
The full version of this post can be found here.

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 was occurring but the entry in the msdb was not being made as it was being chosen as a deadlock victim so we investigated the cause of the problem as we had some processes that used this information to copy transaction logs to other servers and we needed it to be complete.

We used trace flag 1222 to output the deadlock information to the error log and found the culprit to be a Microsoft stored procedure called "sp_delete_backuphistory" that is called by SQL 2005 maintenance plans when you use the "History cleanup task" and tick the "Backup and Restore History".

To read the complete version of this post go to here.

SSMS Log file viewer and Deadlock Graphs

ACALVETT — Wed, 23 Jan 2008 21:02:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/
The full version of this post can be found here.

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...

To read the complete version of this post go to here.

The Job whose owner kept coming back......

ACALVETT — Mon, 31 Dec 2007 00:25:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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 has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))..................

To read the complete version of this post go to here.

My old mate sp_recompile

ACALVETT — Fri, 12 Oct 2007 10:40:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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 changed after the cursor was declared"..........

To read the complete version of this post go to here.

SSMS Restore backup error

ACALVETT — Fri, 14 Sep 2007 14:38:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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 an error to the effect "Cannot access the specified path or file on the server".........

To read the complete version of this post go to here.

Server level VLF report

ACALVETT — Wed, 25 Jul 2007 22:43:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

I read Tony Rogerson's blog on Virtual Log Files today and it reminded me that i really should knock up a little report to list all databases on a server and the number of VLF's per database. Since I had been busy writing some other Operational reports I was in the right frame of mind so knocked up what you see below.........

To read the complete version of this post go to here.

The x64 experience

ACALVETT — Mon, 09 Jul 2007 23:55:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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 SQL 2005 x64 as our base build. However it does seem that 64 bit builds need a bit more attention. I say this because recently.........

To read the complete version of this post go to here.

Dotty about maintenance plans

ACALVETT — Fri, 22 Jun 2007 17:12:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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...........

To read the complete version of this post go to here.

Back to the Blog

ACALVETT — Fri, 22 Jun 2007 12:54:00 GMT

I recently got an e-mail from a friend pointing out it had been a year since I had last blogged so I made the usual mental note to myself that I really should pull my finger out and get blogging again. This time I am actually going to do something! There have been reasons for my absence but now i`m getting back into gear and will be posting regularly so watch this space!

I have also decided to take this opportunity to move my blog to http://sqlblogcasts.com/blogs/acalvett/ as I no longer feel that the SqlJunkies site is a suitable home. I will however cross post for a while.

SQL 2005: Procedure cache cleared

ACALVETT — Mon, 05 Jun 2006 23:06:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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...........

To read the complete version of this post go to here.

SQL 2005 - Database Snapshots

ACALVETT — Mon, 03 Apr 2006 20:58:00 GMT

Today was a big day for me that made me proud as my first article was published on the Simple Talk website! Its about database snapshots in SQL 2005 and you can find it here.

I really enjoyed writing the article and must say that although i thought i knew how much effort it took to write an article its only now that i truly understand how much work people put into their articles!

In the mean time i intend to continue submitting articles and look forward to any comments you may have about my first one.

What is that SPID doing?

ACALVETT — Mon, 27 Mar 2006 21:44:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

This is certainly not a new topic but something that i felt was worth revisiting and reminding people about, especially because i find many people are not aware of the ability to extract the statement within a stored procedure being executed........

To read the complete version of this post go to here.

SQL 2005 - Log Shipping Monitoring?

ACALVETT — Sun, 12 Mar 2006 22:20:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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......................!

To read the complete version of this post go to here.

2005 - Alter failed for server XXX

ACALVETT — Mon, 13 Feb 2006 08:52:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/

The full version of this post can be found here.

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".........

To read the complete version of this post go to here.

Configuring a secure domain account for ASP.NET on Windows 2000

ACALVETT — Thu, 26 Jan 2006 22:40:00 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/
The full version of this post can be found here.

I recently had to enable the ASP.NET account on a Windows 2000 machine to use a domain account so we could use NT Authentication to the SQL backend and the implementation of the domain account had to be secure. The hardest bit was securing the account, I wanted to give the account as few privileges as possible and make sure the account details could not be easily compromised.........

To read the complete version of this post go to here.