Andrew Calvett

Transaction log backup deadlock

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 Restore backup error

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.

The x64 experience

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

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.

SQL 2005: Procedure cache cleared

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

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.

SQL 2005 - Log Shipping Monitoring?

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

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.

SQL 2005 - Maintenance Plans

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

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 which i`m sure will evolve into excellent documents by the end of the project :D

So, having done the basic install which i talked about previously i decided lets tackle maintenance plans as there should not be to many surprises here.........

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

The SQL 2005 roll out project begins

Well i got what i wanted last week! I`m now working on the SQL 2005 roll out in my firm and i suspect at the end of it i`ll have a few grey hairs.

Its a small project at the moment with just 3 technical specialists (yes i`m one of them) and a small number of other available "resources" to work on the third party apps side. I`m sure however it will grow as we approach deployment which will be to around 50 servers in 25 countries which are our mission critical servers. Our other servers will be done in another phase.

So why blog about it? Well i`m responsible for the "Operational" side and have to make SQL 2005 work for the Operations department, deal with disaster recovery, installations, high availability and so on. So here i am to share my experiences as i work my way through.

Day 1

Finally! The test server arrived and we have the SQL 2005 installation disks! Its a good feeling and i think its time to get some easy wins out the way. So i proceed to doing a standard install where i will capture all the screen shots, put them into our install manual for the Ops department and add any required wording. (An automated setup is also on the list).

Having worked through the install wizard its now dealing with copying the files and i decide to leave it working and grab some lunch. I get back 20 minutes later (enthusiasm is an appetite killer :D) and find that the install appears to be hanging setting the security on the files. Surely not! So i killed another 10 minutes and its going no where fast and the setup log is not telling me much.

So welcome to my first "feature", a bit of swearing later and the problem is identified. Basically the server is logged on with a local admin account and i've configured the services with domain accounts. It appears that SQL 2005 is trying to do a lookup for the domain account for each file and encountering a problem (i`ll get the exact details for another post). Solution?  I pulled the network cable and suddenly the installation went ahead at full steam again! I did the install again later logged on as a domain user and the problem did not occur this time so it was definately down to the local user being logged on in my case.

So, feature added to documentation and mental note to self that its not going to be perfect!

Thats its for now! The only other thing i`m doing today is getting some more books ordered. Time to get up to speed quickly.....