Truncate SQL Logs

I am off today.

Yeah, right.

The webmaster called me in a panic because the Content Management Server was really poking along and causing people to complain of web-site slowness. He determined that we needed, "more RAM, more processors, or maybe a new machine." Which I doubted.

I looked around at the MS SQL Server 2000 that resides behind the CMS and found an 18GB log file slowly growing larger. What to do, what to do?

Truncate the Logs.

When you truncate the transaction logs for a database you are, in essence, telling the database to stop worry about any transaction logs that are older than those that will fit in the allotted space so, there are some important steps that need to be taken before you do this.

First, you must make sure you have a good backup up the db in question. If your last scheduled full backup (and you DO have scheduled backups...right) was relatively recent, you should be fine. In this case, I had one taken on Sunday night. However, if you do not have a good backup, you can quickly run one by either re-running your existing job or just creating a new one. This is important. You MUST alter the RETAINDAYS statement in your existing job to be fewer than the number of days until the next backup. For instance, if you run backups once a week on Sundays, your RETAINDAYS is probably set to 6. If you want to run a job on Wednesday, you would have to alter the RETAINDAYS value to 3, otherwise your backups on Sunday will not run, since the last backup has not yet expired.

If you would rather just run a special job, you can do so rather easily as below:

USE %databasename%;
go
BACKUP DATABASE [%databasename%] TO [%backupdevice%]
WITH FORMAT

%databasename% and %backupdevice% will need to replaced with parameters from your site. The 'use' statement above is a bit redundant, but I always use it in order to be POSITIVE I am dealing with the right DB. Also note that using the WITH FORMAT option will overwrite anything you had previously stored to that device.

Assuming you do not have a backup device built, you can backup to a file directly with:

USE %databasename%;
go
BACKUP DATABASE [%databasename%] TO DISK = 'C:\Path\to\file.bak'
WITH FORMAT

Again, place the appropriate names in the appropriate places.

Once that is done, you will need to actually truncate the logfile. This is done with the code shown below:

USE %databasename%;
BACKUP LOG %databasename% WITH NO_LOG
go;

Rather than using NO_LOG you could use TRUNCATE_ONLY, they are equivalent in this case.

Now that you have gotten rid of the excess transaction logs, you will need to let the database know you have done so and that it can release the space on the disk. Time for some DBCC!

USE %databasename%;
DBCC SHRINKFILE (%logfilename%|%logfileid%);
go;

You may use the fully qualified log filename or you may use the file id. I usually use the file id because it is a smallish number. In my case, it was '2.' You can find the fileid in the sysfiles table in the database you are working with. Like so:

SELECT fileid,
size,
filename
FROM sysfiles

You will now need to make another full backup, as shown above, in case you have some disaster between now and the next backup, you won't need to re-truncate your logs. Also, since most of the transaction logs were just dropped, you need to run a full backup to have a valid retore path anyway.

Unable to Qualify My Own Domain Name

Today a network engineer called me and said that he needed a file that was supposedly residing on a decommissioned ftp server.

Wonderful.

I spent my morning digging through the pile of yet-to-be-surplussed hardware and found what I think might be the correct machine. Yet another 280R being revived and pressed into service. (Incidentally, 280R's are heavy, do not lift it by yourself as you will probably hurt your back or tear your pants, or both.) Turns out my first pick was the wrong machine. Somebody conveniently removed the machine labels when they went to the surplus pile. My second, equally heavy, pick is the correct machine.

So, I wrangle the beast into place and plug everything in and power it up, I also have a laptop plugged into the RSC card because I know better than to think it will boot happily. Turns out, though, that I'm wrong. It boots up quite nicely, and immediately begins to complain about not being able to qualify it's name:

unable to qualify my own domain name (servername) -- using short name

Now, when a Solaris machine throws that error, you can be sure that nothing to do with the network will function. This error is caused because the machine cannot find its DNS server on the network. That could mean that the DNS server is gone or, more likely, that the network is not configured properly.

Since I know that this machine was in a different building before it was decommissioned, I imagine that the jack it is plugged into is not correctly set up to support the network configuration on the server. To find out what the machine expects, I try an ifconfig -a:

prompt $> ifconfig -a

lo0: flags=2001000849
inet 127.0.0.1 netmask ff000000
eri0: flags=1000843
& inet xxx.xxx.69.99 netmask ffff0000 broadcast xxx.xxx.69.127

Welp, I wasn't root when I ran that command, so the MAC address is not showing. Network engineers ALWAYS want the MAC address so they can trace the machine through the network. So, I'll have to try again as root:


prompt #> ifconfig -a

lo0: flags=2001000849
inet 127.0.0.1 netmask ff000000
eri0: flags=1000843
inet xxx.xxx.69.99 netmask ffff0000 broadcast xxx.xxx.69.255
ether 0:3:ba:xx:xx:xx

OK, we see the IP address and the MAC address. Good. Be aware that the MAC does not display proceeding zeros. Some network engineers will not understand that format. I always relay it like this:

00:03:BA:XX:XX:XX

The other thing I notice is the dreaded interface type. Experience tells me that 'eri0' does not cooperate well with Cisco switches that auto-detect speed settings. So I pass this info along to the engineer who sets up my port correctly and sets the speed to run at 100Mb.

Still doesn't work.

After some arguing with the engineer, I find that the network is set up to use a small CIDR block. That leads me to look at the ifconfig -a output again:

eri0: flags=1000843
inet xxx.xxx.69.99 netmask ffff0000 broadcast xxx.xxx.69.255
ether 0:3:ba:xx:xx:xx

That netmask value, 'ffff0000' looks a lot like a Class B address, so does the broadcast address. I check with the engineer. Sure enough, they are wrong. He tells me the correct netmask and the correct broadcast address. Now I need to set those values. Ifconfig is, again, your friend:

ifconfig eri0 netmask 255.255.255.192 broadcast xxx.xxx.69.127

Now we check to see if it took:

prompt #> ifconfig -a
lo0: flags=2001000849
inet 127.0.0.1 netmask ff000000
eri0: flags=1000843
inet xxx.xxx.69.99 netmask ffffffc0 broadcast xxx.xxx.69.127
ether 0:3:ba:xx:xx:xx

Huzzah! But does the network actually function?

prompt #>nslookup yahoo.com

Server: xxx.xxx.67.1
Address: xxx.xxx.67.1#53

Non-authoritative answer:
Name: yahoo.com
Address: 66.94.234.13
Name: yahoo.com
Address: 216.109.112.135

I call the engineer back and give him the login/password info he needs, and he gets his file! Finally, I can turn the beast off and move it out of my office. It was starting to get hot. As an additional precaution, I take the time to make a copy of the file in question on my PC, so that I won't have to play this game again in 6 months time.