Size Does Matter: How the Text Backup Files Will Help

This section shows that unloading to text backup systems provides a tool for working out the biggest objects and tables in our databases. If a database is getting large, you will start to wonder about what is taking up all the space. Sure, you can look at the tables, find the one with the most records, and then see if you can reduce its size. But this is a hit-or-miss approach, and you'll miss many good opportunities to decrease your database size. If you think that missing these opportunities is not all that important, ponder this disaster.

User Story

One database I was called in to fix was 100MB large and was performing very poorly. Naturally, I assumed that no one had ever compacted it. Unfortunately, it was in such a mess, it wouldn't compact. After importing all the objects into another database, I managed to compact the database and save 20MB. I then hunted through the system and found a few table changes that saved another 5MB. Next, I tried for another favorite space saver of mine: embedded graphs that have too many rows of data stored directly in the graph object. This repair saved a couple more megabytes. Finally, I came across an innocuous small company logo in the corner of every one of the 100 reports. I took a copy of the database and removed this picture from 20 reports. Bingo—10MB saved. I then discovered that the logo was actually a large picture that had been shrunk to a small size. I asked for permission from the manager to remove the logo from the reports, and the database shrunk to only 20MB. I then split the database, and the front-end database reduced to only 4MB. If I had known the relative size of all the objects in the first place, however, this process would have been so much easier.

It's at this point where text backups are so useful because the size of the text files for both the tables and the objects provides a relative indicator of the size of the objects inside the database. If you look at the exports from the Northwind database, you will find that the Orders and OrderDetails XML table export files are the largest. The size reflects the database where these two tables clearly have the most records. Surprisingly, the Categories table is the third biggest text file. It only has five records, but each record includes a bitmap. If this table had many records, the bitmaps would really consume a lot of space unnecessarily and would probably be better stored outside the database. If you sort the programmable object exports folder by file size, the Catalog report and the Customers form are the largest at 150KB each. Though these are not large objects, both these objects have embedded pictures that may not be necessary. If you remove the picture from these objects, they shrink to 50KB when exported.

Though the Northwind object sizes may not be that exciting to you, this file size assessment technique will have a bigger impact on you when you try saving all objects from your databases.

Now we will have a look at backing up and recovering the information not covered by the table and object exports.