Mathom Solutions Blog-IT

Ok, this isn't directly related to SQL Server but it did help me with moving some data around. 

Recently I inherited yet another SQL Server (2000 with no Service Paks...ouch!) and of course my first call of the day is a user saying that they heard that I took over the server and it is experiencing performance problems.  You just have to love your job some days. 

Upon doing some basic investigation, I saw the C partition was at a roomy 100MB on a good day.  Ok, we all know that isn't enough.  The D partition had about 400GB and both were on the same RAID 5 volume.  Ok, pretty simple, I will just use my Acronis Disk Director Server to resize the partitions and we are back in business. 

Ok, I am neurotic but not crazy so I wanted to make a backup. This server contained one database on the D partition that was 123GB in size.  My first thought was to stop SQL server and copy to a network share but that would take a fair chunk of time and I didn't want to take that long.  I also know that a SQL Backup would take just as long. 

My next thought was to contact the network team and have them do a Symantec's Veritas Backup Exec backup of this database but again that was going to take about 8-10 hours and we wouldn't have enough space to install the local SQL agent.   Still too much down time. 

My next thought was to install Red-Gate's SQL Backup as we use that on our other servers and get a pretty decent compression out of it for our daily backups, but unfortunately, we didn't have enough free space on the c partition.

My next thought was to detach the database and Zip it to something more manageable.  I have been an ardent user of WinZip since the very early years (DOS...remember that?) but after doing some test that was still going to take about 7-8 hours.  Better but still too long as I wanted to get to other things today. 

A few days ago, my network team raved about a new program called 7-Zip and they said it was free and really really awesome...his words not mine.  So I gave them a call and had the install in a few minutes. This baby was small (818K) so it was going to be able to install in the small space we had. 

After playing around with the application a bit, I started my compression and I choose the fastest speed to see how long it would take.  According to my estimated it would take about 4 hours to compress my 123GB database. 

I started it up and let it run.  It was crunching away and was quite impressive although the interface could use some work.  It took my 123GB database and in 3.75 hours it had a compressed file of 9.4GB.  Yes, that is not a typo.  I was floored myself.  Now I could easily copy the compressed file to the network and get on with the repartitioning.  

My full and transaction log backups were running and reporting success but were actually failing.  We recently changed the SQL Server Service Account and rebooted the server. The original setup had the local system account and our standard is to use a domain account with the proper credentials so that we can automate the moving of files in our database domain.  We are running Windows 2003 Server with the latest security patches and SQL Server 2000 SP4.   

The SQL Server job runs under the SQL Server Agent account (which is a domain account that has proper permission as other jobs run successfully) was executing and saying that the job was running correctly but no files were appearing in their respective directories.  The backup job normally takes 30-40 minutes but was now finishing in record time--1 second!  However, I was getting an email from the Red Gate software that there were errors!

The SQL Server log file did not contain any errors and the the SQL Agent log file did not contain any errors either.  Even the Microsoft Event viewer was no help. 

So now I start down the poor man's debugger methodology...knowing that the job calls a stored procedure as it's first step, I decided to grab the code and see if I could execute the stored procedure directly and when I did, I got this error from the RedGate SQL Backup software:

ERRSQB: 5150 (Error opening mutex.) (Global\SQBMutex_): 5 (Access is denied.)

This would explain why I was getting the email saying that there were errors.  Inside the stored procedure I loop through each database and run the backup.  One of the Red Gate command is the OnError MailTo command and I had that setup to notify myself. 

After doing some digging, I stopped and restarted the SQLBackup service from Red Gate and it started working correctly.  Not sure why but my hunch is that even though we change the startup account, rebooted the server and the SQL Server service started with the new domain account, the SQLBackup service needed to do something permission wise with the new startup account in order for it to work correctly.

Hot Dog!  We had an HBA issue on one of our SQL DB servers in the Polyserve Matrix last night and Polyserve saw it, notified us, and failed the instance over correctly and shut down access to the errant server.  Total down time was about 30 seconds.  The instance it moved only had two large databases on it but it worked.  The client application which was coded to retry the connection to the DB if it receives an error code also worked correctly and then retried the transactions and the client never knew there was an outage.  We had a complaint of performance for a short time but I will take that over why is it down any day!

I was a bit worried about the notifiers because it uses a batch file and BLAT.exe (a command line SMTP server).  We weren't able to test it very much during our install and were cautious about using it this way. 

So far so good with Polyserve! 

I don't know about you but I have my own hard earned bag of tricks and scripts that I have developed over the years.  Each new SQL Server that I inherit gets these stored procedures, jobs, scripts put on them without fail.  However, I have always wondered what Microsoft does for their SQL Servers...what scripts?, what jobs?, how often?...and the like.  Well, I came across this the other day and my questions had been answered. 

Microsoft AITScripts.zip  (downloads a zip file from from the MS download site)

The scripts in here are from Microsoft and give you a glimpse into what they do on their own DB servers for transaction logs, backups, error log cycling, index defragmentation, and so on. 

As always, I would recommned looking through the code throughly, installing them on a test server first and playing with them there as I can't be responsible for their content and I am sure MS would say the same.   

For me I was suprised to see how similar things were.  We had a lot of the same jobs/tasks but for me it was more interesting to see their code and how they do things. 

Enjoy!

Yep, you read that right.  The posted an update to version 1.2 and the website says it is FREE.

Here's the link http://www.red-gate.com/products/SQL_Log_Rescue/index.htm so check it out and get a product that can really save you. 

I have used this program many times to recovery data that a errant SQL statement caused.  Actually, it has more than paid for it self (we bought it as a bundle to their backup software...by the way you should check our version 5 of their SQL Backup software) the first time we used it.  We had a Jr DBA run an update query and forgot to select the WHERE clause and updated 250,000 records.  OUCH!!!!  He came running into my office and spilled his guts and I smiled, started up this tool and was able to undo the query. 

The only caveat is that it only works on SQL 2000 and they are still in development on the 2005 version. 

It is finally here!  Red-Gate has released their latest backup software--SQL Backup. The GUI has been completely redesigned and it has something very interesting called time-line management.  This feature allows you to see all of your backups jobs on a timeline control.  This allows you to see where your backup jobs are overlapping so that you can adjust them accordingly. 

For each registered SQL Server instance that is connected, the time line shows a summary of the activities on that SQL Server instance. A grey block represents a scheduled and/or a successful job.  A red block represents indicates a failed activity or conflicting backup jobs.  Backups job show up a little green squares while restores show up as blue blocks.  Finally a future job is designated by a green block. 

If more than one backup or restore operation is scheduled at the same time on the same SQL Server instance, the time slot in which the conflict occurs will be highlighted pink, and the conflicting operations will be highlighted in red. 

Also new are reports.  In previoius versions if, you need to produce a report for your boss or clients, you had to pull up your sleeves and dig into SQL and Excel.  Red-Gate has used the Microsoft Report control (think reporting services reports) to generate some basic reports. 

There are three basic types of reports:
1.  All backup history which lists all backups that have completed.  This includes successful backups, backups that have warnings associated with them, and failed backups.
2.  Backups with warnings lists all completed backups that have warnings associated with them.
3.  Failed backups lists all completed backups that have failed.

From there you are able to generate a report based on a Single Server or across multiple servers. 

They have also added several other features, worked on performance (I can't wait to test this on one of my busiest boxes), worked on threading, made it Vista compatible and a whole host of others things. 

Make sure to check it out  www.red-gate.com/products/SQL_Backup/index.htm . They have done some really great work.