In a previous post, I wrote about when and how to shrink your database files. The bottom line was that you shouldn't shrink your files, unless you really have to, and if you really have to, then you should do it during low or no activity time and in small chucks.
A few people asked me how they can retrieve the free space in each file in order to know which file they should consider for shrinking. So here are 3 ways to retrieve this information…
- Attempt to Shrink Each File, but Not Really
?In SSMS, right-click on the database, choose "Tasks" and then "Shrink" and then "Files"…?
Then, in the dialog that opens, you can see the "Currently Allocated Space" and the "Available Free Space" for each database file. You can switch between files by choosing the file type, the filegroup and the file name in the 3 dropdown lists.
This is actually the screen in which you perform the actual shrink operation, so don't click "OK"! We're just using this screen, because it provides the allocated and unallocated space in each file, and it's very convenient to use.
Notice that you don't have the option to shrink the file in small chucks in this screen. This is why you shouldn't perform the shrink operation here. Instead, you should use the script provided in the When and How to Shrink Your Database post.
Use the "Disk Usage" Standard Report in SSMS
In SSMS, right-click on the database, choose "Reports" and then "Standard Reports" and then "Disk Usage"…
?You'll see the following report:
In this report you can see the space usage information for the data and log files. On the left you can see a pie chart showing the percentage of each space category. Here is an explanation of what each category means:?
?Data – Space used by data pages (including row-overflow and LOB pages) as well as metadata pages (such as GAM and IAM pages)
Index – Space used by index pages
Unused – Pages that are allocated to some object in the database, but are currently unused. For example, unused pages in mixed extents. This space is not available for the shrink operation, because it's already allocated.
Unallocated – Pages in the data files that just sit there. These pages are not allocated to anything, so they can be removed when shrinking the relevant data file.
On the right, you can see the used and unused parts of the transaction log.
If you have multiple data files in your database and you want to see a breakdown of the data files, then you can click on "Disk Space Used by Data Files" at the bottom. This will open a list of all the data files with information about each one. Notice that Microsoft is trying to confuse us here. They use the term "Space Used", while they actually mean "Space Allocated". The number you see there (191.31MB in the screenshot above) is the allocated space (see definition above) in that specific data file. You need to subtract the "Space Used" from the "Space Reserved" in order to determine how much space is available for shrink.
- Use a Script
I always prefer to use a script. This way you can manipulate it to retrieve the information the way you want it. So here is a script that retrieves information about each file in the current database:
You can use the "UnallocatedSpace_MB" to determine how much unallocated space there is in each file.
Enjoy!
Image courtesy of Gualberto107 / FreeDigitalPhotos.net
