Ever get the feeling that things are just getting too big for you to handle? Wondering why your database, which doesn’t seem very big, all of a sudden is much larger than you remember? There are lots of reasons why this could be, not to mention that there is simply more data in there than you realize. Do you need to add a shrink task to your maintenance plan? Let’s take a look and find out.
Disk Usage Reporting
Right-click your database name and choose Reports ➤ Standard Reports ➤ Disk Usage. It takes a second to run, and then you get a cool little graphic similar to the one shown in Figure 9-1.
Figure 9-1. Disk Usage Report
This first thing that I noticed was the pie charts. Looking at those, you can see that they are representative of the percentage of space usage for the data files and the transaction logs, respectively. Now, as I said before, my database is tiny and only has the one table, so these results are probably very different from yours. The information is still important, though, and what it can tell you. Read through this report carefully.
Tip
Believe it or not, you actually do want a bit of unused space in the database.
Why do you want unused space in the database? That makes no sense! The database should be as streamlined as possible, right? In one sense, this is correct. But what is the primary purpose of a database? To provide an organized place for data to live, of course. And what happens when you need to add more data to an extremely compact database? It has to add more space from the available disk space.
Disk Space Considerations
When a database is created, there is a setting to add storage either as a factor of the size of the database or as a percentage. This setting is invoked when space is needed to keep the database functional. Right-clicking the database name and choosing Properties, and then choosing the Files option on the left will show you the settings for the Autogrowth/Maxsize options. The settings for my DEVTEST database are shown in Figure 9-2.
Figure 9-2. Database Properties
Note that I have selected an initial size of 4MB for both the PRIMARY filegroup and the transaction log. This is because I didn’t want to make it too small and then have the database almost immediately request more space. I gave it some room to play, in other words. My PRIMARY filegroup will grow by 1MB as needed, to unlimited growth. Careful monitoring of this database is required, since we said it will have unlimited growth. The transaction log will grow by 5% until it is 2GB, and then a new transaction log will be created, and these will work in conjunction until the logs are truncated. Also note that my Path and File Name sections are set to where I actually want my data, and not buried in the default SQL installation folders.
Consider these options carefully, depending on the expected data insertion and retrieval rate for the intended database purpose.
To alleviate any issues with database size, the Shrink Database option is available. It basically says to the database, “Okay, here’s the deal. You can have as much disk space as you want during the day, but at night, you’re on a diet. I’m taking back any room you haven’t used and returning it to the file system. If you need it later, you know to ask, but you can’t just hold on to it.” In other words, the database maintenance plan needs to run at night or when there are as few users on the system as possible. When it runs, it takes the unused space previously claimed by SQL Server and returns it to the file system. If the database needs it later, it requests the chunk of space from the file system, and if there is room available, then it takes it. The process is then repeated the next time the maintenance plan is set to run.
Consequently, when setting up the Shrink Database option of the maintenance plan, there is an option where you can set the amount of unused space to remain in the database. It is important to remember that this setting works in conjunction with the setting to determine how much space is actually granted to your database on the file system.
Using an 800GB database as an example, when the database is shrunk and 50% of the database size is chosen, our 800GB database reserves 400GB, or 50% of the space. So the 800GB database with maybe 70GB of free space turns out to be 730GB primary database size and 365GB in reserve. See how that works? That will quickly eat up disk space without proper management and oversight. That is where we, as the database administrators, come in.
The Transaction Log
An important part to consider is how the transaction log interacts as a part of the database, specifically in regards to how the shrinking of the database takes place. The purpose of a transaction log is to, um, log the database transactions. It literally keeps a record of the DML statements (SELECT, UPDATE, INSERT, DELETE) that manipulate the database. If you imagine that this will get very big, then you’re absolutely right. In a database with a lot of transactions, this log can get huge very quickly. When shrinking the database, you can shrink it as much as you want, but you’ll never touch the transaction log size unless you shrink the transaction log. Remember, it’s a completely separate entity with a separate file designation, and it needs to have the same attention as the primary data file. Given this, it is still a part of the database and can’t be shrunk without being explicitly told to shrink. Otherwise, your log will either continue to grow until the disk is full or get to its limit and cause an error. Either way, it is not a good thing!
To alleviate this issue, simply set up another database backup task to shrink and truncate the transaction log. Let’s look at how to do that.
Setting Up the Maintenance Plan
This plan is one of the easier ones to set up, honestly. It’s almost exactly the same as the plans in Chapters 7 and 8, so most of this should start to look familiar at this point.
Right-click Maintenance Plans from the Management folder in SSMS and choose Maintenance Plan Wizard. The initial interface needs to be changed to show what you see in Figure 9-3.
Figure 9-3. Select Plan Properties
Change the schedule by clicking the Change… button and set the Occurs option to Daily to make it run once a day. Shrinking the database takes some time, so I wouldn’t set it any more frequently than that. Click Next to continue. You will see the page where you choose the tasks, so choose Shrink Database as shown in Figure 9-4.
Figure 9-4. Select Maintenance Tasks
That’s an interesting definition there as well. It removes empty data and log pages. Recall what this means in terms of the indexes discussed in Chapter 6 and 7. Reorganizing and rebuilding indexes would probably make this task more relevant, don’t you think? If the maintenance plan didn’t have to run and find the empty pages, it would certainly be more efficient.
Note
The Shrink Database task shrinks both the transaction logs and the data file for the selected database. Transaction logs are shrunk first, with the filegroup shrunk afterward.
Click Next to continue. You are taken to the Select Maintenance Task Order screen, shown in Figure 9-5, so click Next here since we are only doing the one task.
Figure 9-5. Select Maintenance Task Order
It seems that we are always bypassing this screen, but we will definitely be using the concept of ordering tasks later, and you will see where this can become quite important. For now, though, just click Next at this screen and you will see the Define Shrink Database Task interface shown in Figure 9-6.
Figure 9-6. Define Shrink Database Task
Go ahead and choose your database from the drop-down, but leave the rest of the options alone.
What this means is that you want to shrink the database if it grows beyond 50MB. Otherwise, do nothing. So even though the maintenance task will run, if the database is 49.9MB, it will not execute. Interesting, huh? So make sure that you change this to a setting you can deal with.
The amount of free space to retain after shrink is important. You don’t want to set this to 0. That would be silly because the first thing your database is going to do is request space from the operating system. You can leave this set to 10%, which shouldn’t be very much depending on the size of the database.
Click Next and you will see the reporting options screen, and just like before, set these to the settings shown in Figure 9-7.
Figure 9-7. Select Report Options
Again, the folder location is set to where we are writing our maintenance text files, so that the maintenance cleanup task can take care of them for us later.
Clicking Next will show the summary, as shown in Figure 9-8.
Figure 9-8. Complete the Wizard
So we defined our Shrink Database task to run every night at midnight on our DEVTEST database when it grows beyond 50MB. We want to leave 10% free space, and write the results to a file in E:SQL ServerBackups. Looks pretty good! Click Finish and hope for the best, which, coincidentally, is shown in Figure 9-9.
Figure 9-9. Maintenance Plan Wizard Progress
Always good to see those green check boxes!
Don’t forget to update the job like in previous chapters. I named the job Shrink Database, as shown in Figure 9-10.
Figure 9-10. SQL Server Agent Jobs
Your Maintenance Plans folder should now look like Figure 9-11.
Figure 9-11. Maintenance Plans
Summary
This chapter was short and sweet, but there is actually a lot to it. Let’s do a quick review.
We looked at how important it is to set up the database initially as far as space requirements are concerned.
We saw how quickly large databases grow, and how important it is to have a good maintenance plan in place to keep the size of the database in check.
And we set up the actual maintenance plan to enforce the shrinking of the database.
If you’ve gotten this far, you’re doing really well. I hope that you’ve learned something in this book so far, instead of just knowing where to click to make things happen.