Kriki’s Dynamics NAV blog

Ever tried to shrink your transaction log and it doesn’t shrink?

(VERY IMPORTANT: Generally it is NOT a good idea to shrink the logfiles, but because it is NOT a production server and I need space instead of performance, it is a good idea to shrink the logfiles [oh, and I also have simple recovery model]).

First, the transaction log is used to write all transactions in it and when it is committed, SQL Server moves that changed data to the database-file(s) (How SQL Server does this is not in the scope of this post.)

Internally, SQL Server is using virtual log files inside the transaction log file (see http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/).

Why doesn’t my transaction log shrink?

Generally the virtual log file in use is the last one in the physical file. That means that physical file cannot be shrunk until that virtual logfile is not active anymore. (It can also be active because of other reasons like replication, log backups, log shipping, … but again this is out of scope of this post [and you need full recovery model].)

Now, how to make SQL Server move to another virtual log file?

Easy, use the database. Or better : make writes to it like filling up a table with some data and then delete it again until you get to another virtual log file.

And this is what I did this time, but it didn’t work. I created an extra transaction log and make sure SQL was writing in that one. But how do I know it is writing in that new physical logfile and that the first physical logfile is not active anymore?

"DBCC LOGINFO" gives me that info (see again http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/).

It generates something like this (I have 2 physical log files with 1 virtual log file in each):

FileId        FileSize        StartOffset        FSeqNo        Status        Parity        CreateLSN

2        1966014464        8192        440        2        64        0

5        13041664        8192        428        0        64        0

The first column is the "FileId". In SQL, each file in the DB has its own Id.

"Status" is the field that I am interested in. 2 means it is in use and 0 means it is not in use. So I know that the first virtual (and in this case also physical) logfile is in use.

I started creating and deleting records, until I got to this situation:

FileId        FileSize        StartOffset        FSeqNo        Status        Parity        CreateLSN

2        1966014464        8192        440        0        64        0

5        13041664        8192        428        2        64        0

The current size of the logfile is around 1.9 GB (the column "FileSize" is in Bytes).

Fine, the first logfile is not active anymore, so I can shrink it.

But it didn’t shrink the file. When I launched the shrink using the GUI I didn’t get any error but maybe there was some message anyway.

So I made it generate the code and launched it manually.

DBCC SHRINKFILE (N’CRONUS_Log’ , 100)

This command shrinks (or at least tries to…) the logfile of the logical databasefile name "CRONUS_Log" to 100 MB but it failed.

Lets see if there is some message…

In the Messages window I got something like this:

Cannot shrink log file 2 (CRONUS_log) because total number of logical log files cannot be fewer than 2.

So what?

Well, I have only 2 virtual logfiles in it. Even if I have more virtual logfiles in the second logfile, I would still be unable to shrink the first logfile because you cannot shrink a logfile to a size SMALLER than the first virtual logfile.

Removing the physical logfile then? No, you can’t because it is the primary logfile and you cannot delete that.

BTW: it is also a good idea to generate the commands that will be run when doing something using the GUI. Sometimes you get some interesting (or important) message and also you can learn something.

Filed under: SQL | Make a Comment »

I already blogged about the book I was going to read.

I haven’t finished the book yet, but I didn’t want to wait to write a review. Finishing the book will take me some more weeks because I am not reading it full time (lack of time) but I read it on my smartphone whenever I have some minutes to spare.

How does the book read? To be honest, it is quite boring (it is a technical book after all) … if you make the same mistake as I am doing: reading it as I do when you have some time to spare.

So, how do you need to read it? You should be behind your computer with NAV2013 installed (preferably with a developer-license) and while reading it, you should dirty your hands following the books’ hands-on project used to explain the various parts and also doing other experiments as they come to mind. If you are an experienced C/AL developer, you might also skip the books’ hands-on project and definitely(!) do some experimenting when something comes to mind.

Like I said, reading it in the way I do is not the best way, but still there are some things for me to learn (even with over 10 years of C/AL experience) and also some things I bookmarked because I like experimenting with new things!

In short: A must for people who want to start programming in C/AL in NAV2013. But don’t make the mistake to read the book as I do. You need to dirty your hands while reading it. And also don’t think to finish it in a short time (even if you read it as I do)! And also a must for veteran C/AL programmers. You will definitely find something new to learn!

Filed under: C/AL, Dynamics, NAV, NAV2013, RTC | Make a Comment »

I finished reading "Implementing Microsoft Dynamics NAV 2013" about which I wrote in another post.

I took me a little longer than expected. Not because it so difficult to read but because of some private I have been very busy lately (both privately as for work).

As a rule, technical books have the habit of being a little boring to read but I found this book quite interesting to read.

Something I really liked about the book is that there is a list of the functionalities and what they do. Each functionality is explained briefly but to the point. Even when I know (most) of the functionalities of NAV, I did really like it.

And one phrase from the book I really like to quite: "We’ve placed our kanban board next to the coffee machine.". It makes me wonder about which other places are used to place the kanban board.

Also for developers/implementers/…. with lots of NAV-experience it is a nice book. It also explains a lot about the NAV-philosophy of how things work. A lot of it I know intuitively but never saw it written somewhere or was told about it and a lot of it I told others but now I can explain it better and even refer to a book were it was written.

It also explains how to write customized code. This is not to be taken as "the" correct way, but it definitely a good start.

Also interesting is an example on how to start using the Mergetool, Powerpivot, Jet Reports, Zetadocs.

In short: I recommend the book for people who start implementing with NAV2013. Even if you have been implementing NAV for years, you will definitely have some "WOWS!" (I had some anyway!).

Is it useful for customers? Depends. It definitely is useful for the customer’s project leader.

Filed under: C/AL, Dynamics, NAV, NAV2013, RTC, WebService | Make a Comment »

I just read this article : Windows Azure: General Availability of Infrastructure as a Service (IaaS).

You can also sign up for a free trial.

And for MVP’s, with your MSDN, you can have a VM with 2 CPU’s permanently on-line.

I have one since July 2012 and use it for testing and I am very happy with it!

What is it?

In short : it is a VM like another running on commodity hardware, so don’t expect super-performance on it (commodity but you can add lots of memory and CPU’s).

Filed under: AZURE, SQL, Virtualization, WINDOWS | Make a Comment »

These tables show the object-permissions you have with current license and security-roles (<NAV2013) / Permission roles (NAV2013).

Now that the forms and dataports have died, I would expect not to see them anymore in these virtual tables.

And indeed, I don’t see “Form” or “Dataport” as value in the field “Object Type”. But I do still see values “2” and “4”.

Filed under: Dynamics, NAV, NAV2013 | Make a Comment »

Question 1:

What happens if you compile these 3 commands and you are using the servicetier?

Object.”BLOB Reference”.EXPORT(‘c:\xx\y.txt’);

Object.”BLOB Reference”.EXPORT(‘c:\xx\y.txt’,FALSE);

Object.”BLOB Reference”.EXPORT(‘c:\xx\y.txt’,TRUE);

Answer 1:

You get this warning twice: “Function ‘EXPORT’ is obsolete for Microsoft Dynamics NAV Server.”

Question 2 (Surprised): Twice? Why not three times?

Answer 2: The first line (without the second parameter) does NOT generate the warning! And when running it on the servicetier, it works!

Question 3 (Even more surprised): I tried it and I don’t get ANY warning. Even worse. When I run it, I get “The EXPORT() method is obsolete.”

Answer 3: Question 1 and 2 were done on NAV2009R2 and now you are on NAV2013! But without the second parameter, it still works. But a warning would still be useful while compiling.

Filed under: C/AL, Dynamics, NAV, NAV2013, RTC, WebService | Make a Comment »

And another book that is on my (e-)shelf : "Programming Microsoft Dynamics NAV 2013" (http://www.packtpub.com/programming-microsoft-dynamics-nav-2013/book).

I am not starting it immediately, but after "Implementing Microsoft Dynamics NAV 2013", I will be "Programming Microsoft Dynamics NAV 2013"!

Filed under: C/AL, NAV, NAV2013 | Make a Comment »

I just started reading the book.

I just finished Chapter 1 “Introducing Microsoft Dynamics NAV 2013”. It explains in short the functional areas in NAV and a first hint of the different clients. I thought this chapter would be boring (after all, I have been working with NAV since 1999) because I know most of it. But I read it with pleasure. It has some pictures of pages but not too many and not too few to make it and keep it interesting.

For a first chapter, it definitely started well.

You can order a printed book or an eBook here http://link.packtpub.com/aCCk7N.

One last thing I wanted to mention: I am reading it on my NOKIA LUMIA 900, using the epub-version with the Freda-app.

Filed under: NAV, NAV2013 | 1 Comment »

Today I had a problem with a table that has around 5 million records. The table has as primary key “Header No.”,”Transaction No.”.

In the table is a field “Order No.” that at the moment is blank for all records.

I also had an index on that field, because I will need to search for it. I think that in the future only 1 in several 1000 records will have an order no. So the index will be highly selective because I will have only 1 record per order no.

The code is like this:

Table.RESET;

Table.SETCURRENTKEY(“Order No.”);

Table.SETRANGE(“Order No.”,TheOrderNo);

IF Table.FINDFIRST THEN BEGIN

It should go lightening fast but it takes almost 1 second for each FINDFIRST.

Why?

The SQL plan gave me this:

Top[2,1];Nested Loops[3,2];Index Seek($2)[4,3];Clustered Index Seek(Company Name$Table$0)[6,3]

WHAT? A CLUSTERED index Seek?????

it should have been an index seek.

The select was what I expected:

SELECT TOP 1 * FROM “Company Name$Table” WITH (READUNCOMMITTED)   WHERE ((”Order No_”=?)) ORDER BY “Order No_”,”Header No_”,”Transaction No_” OPTION (OPTIMIZE FOR UNKNOWN)

But because SQL knows that the Field “Order No.” contained only blanks, it decided to do a clustered index seek because of the ORDER BY (at least this is what I think SQL decided).

So how to fix it NOW (I don’t want to wait that it goes into production and it takes days or months before the problem fixes itself.

What is the code for?

The FINDFIRST serves because I want the Invoice No. of that record and it has to be a non-blank value.

So, I changed the index from “Order No.” to “Order No.”,”Invoice No.”. It makes the index a little bigger, but it shouldn’t be a big problem (or at least I hope so).

The C/AL code I changed like this:

Table.RESET;

Table.SETCURRENTKEY(“Order No.”,”Invoice No.”);

Table.SETRANGE(“Order No.”,TheOrderNo);

Table.SETFILTER(“Invoice No.”,’<>%1’,’’);

IF Table.FINDFIRST THEN BEGIN

Even with all blank values in “Order No.”,”Invoice No.” at the moment, SQL has changed its plan to an index seek and the code has become very fast.

Filed under: C/AL, NAV, PERFORMANCE, SQL, Uncategorized | 2 Comments »

I just read this post: http://mibuso.com/blogs/zenandtheartofcsidedevelopment/2013/02/22/consuming-nav-web-services-using-powershell/.

And I wondered if it was so easy as is looked. So I tried it out and indeed: It was so easy.

Now, I need something a little more complicated: I want to be able to send a parameter (I only need 1!) into the function. And I want to put the command in SQL Server Agent.

So I created a codeunit and I published it. Nothing new here.

Then I created a function in that codeunit that receives 1 parameter:

SomeParm(Parameter : Text[1024]) : Text[1024]

EXIT(’The Parameter=’ + Parameter);

Now I changed the powershell code a little to be able to pass that parameter:

$test = New-WebServiceProxy -Uri http://127.0.0.1:7047/DynamicsNAV/WS/Some Company/Codeunit/powershelltest -UseDefaultCredential true

$test.SomeParm("xx")

and I got:The Parameter=xx

So that works!

Now I need this in SQL Server Agent!

So, I create a new SQL Server Agent Job.

I change the “Type” in the job step to “PowerShell” and put the previous powershell-code in it.

I save all, and run it and got some error. NNNNOOOO!!!!

But reading the error I noticed that the error was not as bad as it first looked. SQL Server Agent had still the standard Network System as user. And that user has no access-rights in my NAV DB. So changing the SQL Server Agent user, restarting the service, re-running the job gave me the correct result:job completed with success!

In my company, we developed a small C# program that did all this job, but we need Visual Studio to do it, compile the program and copy the executable to the server were we want to use it. Powershell on the other hand, sis a part of Windows (at least the more recent versions), so it is a lot easier to use.

I already had Powershell on my to-learn list, but it just got some more priority now.

Next on my list to try, is this:

An XML-file somewhere on the disk, a function with an XML-port as a parameter in the codeunit published as a webservice. The powershell must read the file and then call the webservice with the content of the XML-file.

But that is for another post.

Filed under: C/AL, NAV, Powershell, SQL, WebService | 3 Comments »