How to find the size of Dynamics NAV database in SQL Server Management Studio?

I have shown you, how easy it is to find the size of the database from Dynamics NAV client. However sometimes you need to get the same information without the luxury of running Dynamics NAV client. For this, you can turn to SQL Server Management Studio…

Similar as in Dynamics NAV, you can achieve same results by following different routes.

How to find the size of Dyanmics NAV database in SQL Server Management Studio #1
1. In SQL Server Management Studio, right-click on the database and select Properties:
Database Size - SQL Database Properties
2. Click on “General” tab and check “Size” property:
Database Size - SQL Database Properties

How to find the size of Dyanmics NAV database in SQL Server Management Studio #2
1. In SQL Server Management Studio, right-click on the database and select “New Query”:
Database Size - New Query
2. Type sp_spaceused and click “Execute”:
Database Size - New Query

How to find the size of Dyanmics NAV database in SQL Server Management Studio #3
1. In SQL Server Management Studio, right-click on the database and select Reports > Standard Reports > Disk Usage:
Database Size - Reports - Disk Usage
2. Check report’s results:
Database Size - Disk Usage Report

Result!

Technorati Tags: , , , , ,

Posted in Dynamics NAV / Navision, SQL | Tagged , , , , , | Leave a comment

How to find the size of Dynamics NAV database?

How to find the size of Dynamics NAV database? This should be quite easy, right? Well, all you need to do, is to go to File > Database > Information:
Database Size - File Database Information

and in “Database” tab, you will see the field Database Size (KB):
Database Size - Database Size KB

If you are feeling a little bit geeky, you could find same answer by utilizing virtual table “Database File”. Create a new form and enter “Database File” (or better yet, ID: 2000000010):
Database Size - New Form Database File

select all available fields:
Database Size - Database File Fields

and click Preview:
Database Size - Database File Results

Looking at both ways, results should be more or less the same:
Database Size - Compare Results

Technorati Tags: , , , ,

Posted in databases, Dynamics NAV / Navision | Tagged , , , , | 1 Comment

Connect to Dynamics NAV database with one click (Desktop Shortcut)

If you are using mostly RoleTailored Dynamics NAV client, then this tip will not be that helpful. If, however, you are like me, and you are still use Dynamics NAV Classic client a lot, then you will love this tip!

A few days ago I noticed that my colleague, whenever connecting to the TEST/DEVELOPMENT databases was using different desktop shortcuts. Instead, of starting Dynamics NAV client, clicking File > Database > Open, selecting the right database and connecting, he was achieving same result with just one click (ok, ok… one double-click!). Now, I know, this is an old trick (and possibly, everyone uses it, or at least tried using it in the past), but… when I counted how many times per day I was loging in to Dynamics NAV and selecting a different database, I decided that I should give the desktop shortcut one my try.

How to vcreate a desktop shortcut which would connect straight to Dynamics NAV database?

Actually, it’s quite easy, just follow these steps:
1. Create a shortcut, for finsql.exe – go to your Dynamics NAV client folder, find finsql.exe, right-click on it, select Send To > Desktop (create shortcut).
Dynamics NAV Desktop Shortcut - Send to desktop

2. Go to Properties of your shortcut – go to your Desktop, find the shortcut, right click on it, and select Properties.
Dynamics NAV Desktop Shortcut - Properties

3. Define connection string in Target field – go to the end of the “Target” field and type in your connection details in the format: SERVERNAME=Your Server Name, DATABASE=Your Database Name, COMPANY=Your Company Name, NTAUTHENTICATION=1.
Dynamics NAV Desktop Shortcut - Target Property

So, my “Target” property would have a value of:

"C:\Program Files\Microsoft Dynamics NAV\60\Classic\finsql.exe"
SERVERNAME=TOMASZNC6320, DATABASE=Demo Database NAV (6-0),
COMPANY=CRONUS UK Ltd.,NTAUTHENTICATION=1

where NTAUTHENTICATION=1 means, that shortcut will use my Windows Login to connect to the database.

4. Click OK. And that’s it!

Now, if you would double-click on the shortcut, it will automatically connect to your database. File > Database > Information:
Dynamics NAV Desktop Shortcut - Connected

Result!

Technorati Tags: , , ,

Posted in Dynamics NAV / Navision, functionality | Tagged , , , | 4 Comments

Error: You cannot use the file TEMP because it is already in use

You are working in Dynamics NAV, creating your sales orders, and… you were kicked out from the server (getting error message: You cannot use the file TEMP because it is already in use). OK, let’s try to replicate the issue. Go back to Dynamics NAV, create a few more sales orders. Nothing… this time no error message appears. However, after some time, working in another part of Dynamics NAV (posting purchase receipts), you get the error message again, also complaining about TEMP file:
Error You cannot use the file TEMP because it is already in use

So, what is causing this error message? And how can you solve it in Dynamics NAV? Well, you can’t… at least, not in Dynamics NAV directly. Have a look at the icons next to the Date/Time in the right corner of the windows “Start” menu taskbar.
Kaspersky Icon

If you can see “K” icon (yes, I mean Kaspersky), then the issue can (most likely) be resolved. The issue is, that Dynamics NAV client works with temporary files (and, I don’t have too much information on exact details of how and when the TEMP files is used), but Kaspersky antivirus sometimes “reserves” these temporary files for scanning, therefore Dynamics NAV can’t use them anymore.

Because, the anti-virus program is interfering with the files that Dynamics NAV uses, the error message (without any apparent pattern in what users is actually doing in NAV) is displayed. The solution: configure the anti-virus program to include finsql.exe as a trusted application and this will get rid of the error.

Result!

Technorati Tags: , ,

Posted in Dynamics NAV / Navision, errors | Tagged , , | Leave a comment

How to clear Change Log Entry table from SQL?

In my previous article about Change Log, I have explained how to clear Change Lot Entry table from Dynamics NAV. However, I also mentioned, that when clearing entries from Dynamics NAV, system is deleting only one change log entry at a time. To make the process faster and more resource-efficient, you can use this SQL query:

/*

Removes all rows from a table without logging the individual
row deletions. TRUNCATE TABLE is similar to the DELETE statement
with no WHERE clause; however, TRUNCATE TABLE is faster and uses
fewer system and transaction log resources.

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

*/

TRUNCATE TABLE
  [Demo Database NAV (6-0)].[dbo].
  [CRONUS UK Ltd_$Change Log Entry]
;

Compared to the DELETE statement (and to Change Log Entries being delete from Dynamics NAV), TRUNCATE TABLE has the following advantages:

  • Less transaction log space is used.
  • Fewer locks are typically used.
  • Without exception, zero pages are left in the table.

Related Links

Technorati Tags: ,

Posted in Dynamics NAV / Navision, SQL | Tagged , | Leave a comment

My posted sales invoice is missing from Dynamics NAV!

If you go to Sales & Marketing > History > Posted Invoices, and search for invoice by filtering in “Order No.” sometimes you might not find any invoices. There are two possible reasons for that –
1. (Quite common one) If invoice has been created in Sales Invoice screen, using Functions > Get Shipment Lines…, and posted, “Order No.” field will be blank. In this case, in might be easier to find sales invoice document going from sales order to sales shipment (clicking Order > Shipments), and then on the shipment line, using “Line > Item Invoice Lines” function.
2. (Quite uncommon, but still possible) If sales invoice was posted directly from sales order screen, “Order No.” is filled in. But, if sales invoice has been printed, another user (or even yourself) could have delete the posted sales invoice.

Let’s have a look at second example in more details:
1. Find posted sales invoice under Sales & Marketing > History > Posted Invoices. Click Edit > Delete (or F4)
Missing Sales Invoice - Edit Delete
2. You will get error message saying “No. Printed” must not be 0 in Sales Invoice Header No.=’103001′”.
Missing Sales Invoice - Error No. Printed must not be 0 in Sales Invoice Header
3. Click OK. Then click “Print“, which will open “Sales – Invoice” report’s request form. Click “Print Again”.
Missing Sales Invoice - Print Sales Invoice
4. Now, you can see, that “No. Printed” has been set to 1. Let’s click Edit > Delete (or F4) again.
Missing Sales Invoice - Edit Delete (Where No. Printed Equals 1)
5. Pufff! Posted Sales Invoice has been deleted.

Therefore, default Dynamics NAV functionality allows you to delete posted sales invoices! Obviously, all important entries, like customer ledger entries, remain in tact, but it is worthwhile to know, that it can be done. Especially, when someone asks where his posted sales invoice dissapeared.

Technorati Tags: , , , ,

Posted in Dynamics NAV / Navision, functionality | Tagged , , , , | 4 Comments

How to clear Change Log table?

There are a few tables that can become really big in Dynamics NAV. It is expected for our Ledger Entries to grow as time passes, but, quite often I hear people saying about Change Log Entries table is growing out of proportion.

And, not everybody knows, that you can actually delete Change Log Entries to recover the lost space (Here, I am making assumption, that you do not have any modifications which would be built on top of the change log, to track the document changes, etc. Otherwise, you might not want to delete the Change Log!). There is even default report in Dynamics NAV to do just that – to delete Change Log Entries.

Administration > IT Administration > Data Deletion > Delete Change Log Entries…
Change Log - Delete Change Log Entries

The biggest advantage of this report is – that you can apply filters and delete only the entries that you want (either by date, or by table number). The disadvantage – it is deleting one Change Log Entry at a time. Therefore, if you have a lot of data in the table (and here we are talking Gigabytes), deleting the table might be resource and time consuming.

In this case (if delete change log entries with default report is taking ages), you might want to have a look at the possibility of using TRUNCATE TABLE function for Change Log Entry table from SQL Server Management Studio.

Technorati Tags: , ,

Posted in Dynamics NAV / Navision, functionality | Tagged , , | 1 Comment