Author: way0utwest

It seems that often I see posts that say “I’m out of space on my drive, what do I do?” The answer, as with most anything in SQL Server is, it depends.

Backups

The most common issue I see for running out of space is the lack of deleting database backup files. The maintenance plan wizard instructs you to perform backups, and defaults to making a separate file for each backup, but it doesn’t necessarily clue in the “Accidental DBA” to remove old backups.

You need a maintenance cleanup task here and set it to remove old backups, but be sure that you have a new, good backup first. In other words, the maintenance cleanup comes after the full backup, and then only when that task completes with success.

You can delete old backup files manually, but think about how many you need to keep. Most people probably can get by with 1 or 2.

IIS Log Files

I know lots of people that have IIS on the same box with SQL Server for small to medium web sites. Unfortunately IIS doesn’t have a maintenance cleanup task. It creates new log files on a daily or weekly basis, and those will fill up your disk when you are least expecting it.

These are in the LogFiles folder under System32 on your system drive. Set a reminder in Outlook to trim these down occasionally.

Transaction Log

Another very common issue is that transaction log growing to fill all disk space. This happens because so many new DBAs assume that a full backup also includes a log backup. It doesn’t, for multiple reasons, and it doesn’t matter if it should or no; it doesn’t.

What you need to do here is first make sure you have time, and then clear the log. That’s a log backup with truncate in versions 2000 and prior. It’s setting to Simple mode in 2005 and above and then . Be sure you set the mode back to Full.

Once you have space back, I’d recommend you take a full backup. You can get by with a diff, or maybe wait until your next scheduled one,but I’m conservative. I’d recommend a full backup right then. Once that’s done, you need to get a good size for the transaction log. Unfortunately that requires some trial and error.

The size of your log file depends on the load your database experiences in terms of changes, but also on your log backup schedule. A log backup allows the space in the log to be resused. If you produce 10MB of changes an hour, and back up once an hour, you need a log file that’s 10MB. Actually you’d want it larger to account for a larger load some hours, but you get the idea. If you backup the log once a day, you’d need a 240MB log ( plus pad).

So, what I’d recommend is that you schedule hourly log backups. See how large the backup files are and then size your log file appropriately. As far as the initial size, you can run a DBCC SHRINKFILE to make your log file smaller. I’d lower it to 1GB to start with. That’s just a guess, and you might want to set it to 10% of your data files, but disk space cheap, and 1GB will accommodate lots of databases.

Summary

There are other reasons, like your data file growing, but I’m not trying to cover every situation. If you’re new to SQL Server, and you have a full disk, check these items first. If none of these help you, post a note in our forums and someone will answer.

This is a great write-up that shows what is important in a phone. It’s how you interact with it, not the features, not the specs, it’s how you use it.

I tend to agree with that. With both the G1 and the iPhone, the interaction with the device for the most part, has not been on the phone. It’s been twitter, web, reading, music, and the smoothness of those two devices is unbeatable.

I hope that more phone manufacturers realize this and they start to build up their networks of apps. The app store with Apple, and integration with a PC to allow me to manage things either on the PC or the phone, it’s unbeatable. If I could have read books from Barnes and Noble or Amazon on my G1, I might not have moved to an iPhone, but I couldn’t and that mattered.

I’m not sure how big a deal it is for everyone else, but I see more and more people doing things on their phones that aren’t involving making calls.

People building SQL Server apps, especially BI apps ought to consider these markets and look to build small applications that ensure a smooth interaction with customers. As much as I like browser apps onthe desktop, they don’t work on a cell phone, even one with as large a screen as the iPhone of G1.

We have a couple of boxes at a local Denver co-location facility for the training business, SQL Share. Our firewall died a few weeks ago, and so I called a friend that I’ve typically used for network stuff. He found us one on eBay for $200 and last night was the time to install it. We’d been trying to coordinate things, and last night worked.

I’d given him the IPs and setup, and some preliminary work had been done yesterday, but there are things that you can’t test until you get the real network set up. I should have known things were bad when we arrived and I couldn’t get Jordan in at first. The security is tight and I hadn’t said "2 people" so we needed to wait and get authorization from the company I rent space from. Once that was done, we got in and needed to get cage nuts into the rack for the firewall.

There’s a shelf right above us and we had little space. I managed to use a screwdriver to get the top two nuts in. Then I went to get the last one in and couldn’t. So I tried to squeeze it with finger and slipped, cutting below the thumbnail, and starting to bleed a little. While I sucked on it to get the bleeding stopped, Jordan told me I didn’t need to mess with it that way and then proceeded to do the same thing.

A nice delay while both of us stopped the bleeding. Then we mounted things, got it plugged in, and started to configure it. Jordan did the work while I stood around, coughing in the extremely dry air of the colo. Fortunately we were in the hot aisle, and not the cold one, but it was still hard.

We had issues getting the firewall to first allow things out, and then to allow things back in. It’s a little flaky to work with IOS, and you have to go slow. We kept having issues and things went slow. Our expected 30-60 minutes turned into 120 almost. That wasn’t the way I wanted to spend a Saturday night, but it wasn’t all bad. I saw a guy from the Denver User group there who was rebuilding a few boxes for his company and expected to be there for hours.

We finally narrowed it down to the ARP cache in the ISP switches. At the lowest level of networking, the switches are often set up with specific groups of ports working together. To speed the movement of packets, the switches cache the MAC address of your network card. That’s the address burned into the card. It’s similar to the IP address you have and the DNS caching your machine does. In this case it means when we unplugged the server and plugged that cord into the firewall right away, the switch thought it was a momentary interruption and didn’t reset the ARP cache with the MAC addresses in it’s memory tables.

Once we realized that our rules were correct, we went to the Network Operations Center for the colo and had them clear their switch’s cache. That cleared things up and once that was done, things looked OK. Jordan drove to Barnes and Noble to check things from the outside while I hit the bank for funds.

We met up, thinks were working, and we called it a night.