Category Archives: Backup

So a SQL Server Transaction Log ate Your Free Space.

This weekend I came across an unusual circumstance that I thought I would share with many of those part-time SQL Server admins. I currently maintain more than a couple of SQL servers. Because SQL Server has a good maintenance program I don’t spend the money on a third party software for backup. Instead I setup the maintenance plan to create a backup every 6 hours and then push the file to a network share. For some reason or another, the network share came detached and the backups filled up the local data volume. This effectively shutdown the server. I cleared up the space, restored the mapping, and didn’t think much more about the problem. I noticed that I was getting a backup file from each database but failed to pay attention to the transaction log.
This is where my new problem that consumed my weekend started. Friday night at 7pm I got another phone call about the SQL server being out of disk space again. Again I had no space on the volume, but the space wasn’t consumed by the backups. Instead, the transaction log which is normally a couple of gigs in size had ballooned to 100GB in size. I had attached an external USB drive to push a backup of the transaction log to and tried to shrink the transaction log from the SQL Server Manager. This only give me about 3 GB of storage back, but they were quickly consumed as soon as the end users started using in their application again. I then kicked off a backup of the database and then transaction log. I now had 99% of the space free in the transaction log file, but still could not shrink the database. I had fought and fought with the database trying to get that free space back.

Finally at about 2am, running out of ideas, I deleted the transaction log file and started up the database again which effectively locked the database for a lot of people. Having migrated the database before, and knowing that a simple restore of the database could easily fix the problem, I took the most recent backup which was actually taken after end users were cut off from the server and restored the database. After the restore, I again had the same problem of a database with a 100 GB transaction log file. This time however, I for some reason threw caution to the wind and performed yet another shrink to the transaction log file. Finally, I freed up 75% of the space on the volume which allowed everything to return to normal.
Why I had to backup and restore the database before I could perform an effective shrink of the database, I do not know. If this has happened to other people, I would like to know the reason behind this.
My corrective actions include scripting a compression command on the backups to reduce their size.  I also plan on creating a trigger to notify me by email when the disk space is low, 20% is one of my favorite guide lines as far as that is concerned. I am considering running a network mapping script to reattach the volume of the server before the files are moved over so that the network volume that I monitor won’t be so easily missed with the other backup files that I file on the backup storage volume.  I don’t like using compression because of how having to decompress a file to restore it effectively adds to the lengthy process of getting the database back to working order.  Then again, having a few extra copies of the database around is also handy.

I am open to other input. I thought I would just share my wonderful late night experience with others in hope to get some improvements or perhaps help out other admins who might run into the same problem.

Novell’s Patents and Why CDTN Holdings Wants Them.

The web was a buzz earlier today with news that Microsoft wasn’t the only company being involved in CDTN Holdings and some including ZDNet and ComputerWorld blogger Steven J. Vaughan-Nichols tried to speculate just what patents each of the member companies of CDTN Holdings would want and why.
First many thought that VMware would jump at the opportunity to get an OS to complete their stack and as I found out talking to a few PR employees from VMware, they pretty much already have everything they need from Novell, or so they say. Of course, VMware is owned by EMC who is a partner in CDTN holdings, so the VMware reps didn’t exactly inform me accurately.  After a quick search I did happen to find patent number7,793,101, which is Verifiable virtualized storage port assignments for virtual machines . I think I could see why VMware might want a crack at that patent portfolio now. I noticed that there are several more storage based and virtualization based patents for VMware and their parent company EMC to hand pick through. Keep in mind that Microsoft is also competing in the virtualization space as well.
In the storage space there are a few other gems including:

  • 7,844,787 Techniques for data replication with snapshot capabilities
  • 7,844,580 Methods and systems for file replication utilizing differences between versions of files
  • 7,809,910 Backup archive management
  • 7,774,568 Clustered snapshots in networks

VMware is also dabbling in identity management, also something that Microsoft has been working with for some time. Oracle and Apple also have identity management needs and would probably not hesitate to pick up a couple patents for their own related products.
With Identity Management we have a whole slew of goodies to pick through including:

  • 7,770,204 Techniques for securing electronic identities
  • 7,774,826 System and method for determining effective policy profiles in a client-server architecture
  • 7,793,340 Cryptographic binding of authentication schemes
  • 7,793,342 Single sign-on with basic authentication for a transparent proxy
  • 7,774,827 Techniques for providing role-based security with instance-level granularity

All four companies might be interested in improving their application deployment technologies with the following patents:

  • 7,730,480 System and method for creating a pattern installation by cloning software installed another computer
  • 7,739,681 Delayed application installation
  • 7,730,179 System and method for policy-based registration of client devices

The point I am trying to make is that each of these four companies have much to gain for the capital they put together to get access to these patents.
Many of us know that Microsoft is all about their Operating System, their Active Directory architecture, Search, their entry into Cloud computing.
EMC is the storage giant, but they also own VMware, RSA, Atmos, vBlock, Mozy, RecoverPoint, Documentum and have just as much if not more to gain than Microsoft.
Oracle while everyone knows is a database company, has bought more companies than anyone else, and leverage patents from Identity Management to Virtualization. Don’t forget that they own Sun Microsystems and happen to have Virtual Box.
Lastly we have Apple, who seems to stand out as while being worth more than anyone in this venture, appears to have the least to gain. However, when considering identity management, Apple would be quick to take advantage. Novell has quite a few data synchronization patents that could help out their MobileMe services. Single SignOn could be a big plus for them as well. They don’t really seem to have as much to gain from what I can tell, but then again, Apple doesn’t think like most companies. We could see them try dive into the enterprise with some of these patents or perhaps they could push themselves to the cloud.
All in all, we have four companies that are going to benefit greatly from the jewels of Novell, their patents. And while everyone was too busy worrying about the UNIX copyrights, the patents which I consider much more important were being handed over pretty much going unnoticed by the media.
Trying to figure out the direction that Attachmate will take Novell is very scary, especially handing out all of the patents like they did. As a Novell customer myself, I am concerned. Then again, who really knows the direction of the tech industry in the long term.

The Aftermath of this transaction is most interesting. Novell was a real hot potato that no one company wanted entirely. The market share of Novell has been slipping since the 90’s, and their recognition is even less. When talking to a salesman for a backup software company, he failed to even recognize the name and recommended that I speak to a tech. Yet, much to the dismay of many, when the patents for Novell were up for grabs, these four companies were first in line.  Microsoft, Apple, EMC, and Oracle are bitter enemies on several fronts, and yet they put aside their differences to pick apart this former powerhouse.

Backing up: Why don’t I ever learn my lesson?

So I finally got my FreeBSD machine up & running. I spent all of last night updating all of the installed packages (all to get PHP working right) now that it compiles correctly. And then, I decided to go for broke, and upgrade from FreeBSD 6.3 to FreeBSD 7.0. Immediately before I started, I made backups of everything but I did not test those backups to see if they were good. From the italics, you can see where this story is headed.

Well, the upgrade 7.0 blew up on me. Something went wrong, who knows what. No problems, right? I’ll just boot off of a Live FS CD, run the restore. I’ve done it many times before, even as recently as a few weeks ago. Woops!

For whatever reason, I see to be completely unable to restore from the backups. I don’t know what the problem is, but this time, “restore” is blowing up. So now, I am attempting to rebuild the system from scratch. Luckily, I made a separate backup of the critical data files a few weeks ago, so all is not lost. But needless to say, I have egg all over my face. I know, I seem like the world’s most incompetant sys admin here. Sometimes I am sure I am. But then I think of the mistakes other people make, and I feel a lot better. At least I made the backup in the first place! Ironically, it is the first FreeBSD upgrade that’s ever failed on me, and the first time I ever made a backup before doing an upgrade.

I don’t know what went wrong here; I have never had a problem with the FreeBSD backups in the past. I found the problem; the backup was fine, it was my use of “restore” combined with the Live FS CD that was the problem! But a painfull lesson (yet another) is to be learned here. Before counting on your backups, test them the process from top to bottom!

J.Ja