My Dynamics NAV Database is Huge! What is using up all that space?
“We’re gonna need a bigger boat.”
Roy Scheider, “Jaws”
Maybe a disk drive is starting to get full. Maybe backups take too long to run. Whatever the reason, sometimes it’s nice to know what is using up all of that database space. There are two ways to figure out which tables are to blame – one is from inside Microsoft Dynamics NAV, and the other is using SQL Server Management Studio.
Option 1: Inside NAV
NAV has several hidden tables, numbered in the 20 million range. One of these is table 2000000028, named Table Information. You can create a new Page or Form that sits on top of that table, and that’s all there is to it! You can even apply a filter on the Size (KB) field.
Figure 1 – Screenshot of Database Spaced Used from inside NAV
This particular solution works whether you are using SQL or the old-school NAV database server (why are you still doing that? Switch to SQL!) Do note however, that the table size you see here is for the data only. If you total it up, it will be smaller than the actual database file size because it doesn’t include keys, indexes, and transaction log space.
Option 2: Using SQL Server Management Studio (SSMS)
SQL has a canned report that can show you space used. In SQL Server Management Studio (SSMS), right click on the NAV database, click Reports, then choose Disk Usage by Top Tables.
Figure 2 – Screenshot of SSMS Disk Usage by Top Tables menu
This output is similar to the NAV option, sorted by size descending.
Figure 3 – Screenshot of the SSMS Disk Usage by Top Tables report
I am not going to talk about what to do to reduce your database size, but I will caution you: do not just start deleting things. Consult with your NAV Partner and come up with a plan. It’s my opinion that it makes more sense to throw disk drives at the problem since they are so cheap these days (I will cover how to move SQL data around in a future blog). Even if your NAV Partner does advise deleting some data, make sure that is done through NAV and not directly from SQL so that NAV business rules are obeyed; use your superpowers for good, not evil.
There are also settings inside NAV, such as costing method, posting expected cost to the G/L, and change log being enabled that can affect how quickly your database grows. I’m more of a technical person than a functional one, but I encourage you to search our site for more about costing, and to discuss some of these settings with your NAV Partner.