Mathom Solutions Blog-IT

I am always on the lookout for new tools and when they are free...you can't really complain!

Here are some new ones that are rather interesting so make sure and check them out!

SQL Server 2005 Express Profiler  is a free profiler for the Express Edition of SQL Server.

SQL Scripter is a data export and scripting utility for MS SQL Server.

SSMS Tool Pak  is an Add-In that gives the Management Studio some neat features such as CRUD generation and a whole host of neat little featuers that MS left out.

Sentrigo Hedgehog Standard is a free host-based software solution for real-time database monitoring, auditing and breach prevention.

Red-Gate Log Shipping Monitor is a free tool for collating and summarising SQL Server Log Shipping activity.

I also have a list of several others over at my main website so stop over there and see the full list. 

I have been doing a lot of data migration and disaster recovery synchronization lately and had a need to run DOS commands quite frequently.  It was a pain to switch from SSMS to the command window or prompt and then run the DOS command or even more interesting the batch files I just exported.  So I found a way to run it from within SSMS.  

Type in your command and make sure to prefix it with two exclamation points !!  Then click the SQLCMD button on the tool bar and then hit Execute (F5) and voila, it runs! 

I have been busy building the business and really haven't had time to blog but now that the family is visiting relatives in Russia, I had some time to convert my blog to blogengine.net.

It was very easy to convert my site and I am really impressed with the features, flexible and documentation for this free application!  Kudos to the team that wrote this!

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! 

What are you talking about?  No, we aren't talking about looking inside the box.  But have you ever wanted to see what the internals of what SQL is doing.  Well Danny Gould did and this is a little utility he came up with to learn more about the internals and how data is physically allocated, organized and stored. 

Basically he has created a tool that takes all of the information from Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), Index Allocation Map (IAM), Pages, Extents...and so on and creates a graphical depiction of your memory.   Then you can hover over that visualization and it will tell you what that section of memory is allocated to.  Then if you want you can click on it and it will open the Page Viewer which is a hex dump of the header information, data dump, offset table and row interpretation.  From there you can click again and drill down to the various elements that make up a row, including the internal structure and data values.

Check it out!  SQL Internals Viewer

The good folks just south of me in Kansas City at ClearData Consulting have created a new (beta) utility called ClearTrace. 

This tool imports SQL Server 2000 and 2005 trace (and profiler) files into SQL Server and displays summary performance information.  It is designed to provide the same performance summary as Read80Trace but in a graphical environment and for SQL Server 2005.  Remember this is beta but it pretty handy!

Another tool that I just saw but haven't had a chance to try yet is SQL DMVStats by Tom Davidson.

DMVstats 1.0 is an application that can collect, analyze and report on SQL Server 2005 DMV performance data. (Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis.) DMVstats does not support Microsoft SQL Server 2000 and earlier versions.

Main Components The three main components of DMVstats are:

• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.

Data collection is managed by SQL Agent jobs. The DMVstats data warehouse is called DMVstatsDB. Analysis and reporting is provided by means of Reporting Services reports.

Enjoy!

Idera has some free tools for the taking. 

SQLSafe Freeware Edition:  Idera’s SQLsafe Freeware Edition is free SQL Server database backup and recovery tool based on our award-winning SQLsafe product. SQLsafe Freeware Edition gives you a really high-performance backup and recovery engine, a scriptable interface (command line and XSP) and the ability to backup and compress any size and any number of databases, all for free.

SQLCheck:  SQLcheck is a free performance monitoring tool for SQL Server databases. It provides basic performance information about one SQL Server and runs as a secure screensaver or on demand.

SQLPermissions:  Idera’s SQLpermissions is a freeware tool for copying or moving logins and permissions settings across SQLservers. SQLpermissions automates the time consuming job of configuring logins and permissions on one server to match another by generating a customizable TSQL script to do this for you. SQLpermissions also offers the flexibility to move one login or a group of logins and apply permissions across all databases on the target server, or a single database.