JetResizeDatabase question

Feb 15, 2013 at 6:39 PM
I came across JetResizeDatabase yesterday and got excited since it seemed to offer shrinking of online databases. But when adding it to StockSample at the very end (after the using (var table = new Table(session, dbid, TableName, OpenTableGrbit.None)) block in Main()) the program exited abruptly and "StockSample (1772) Terminating process due to non-recoverable failure: db.cxx (4276)".

The inserted code was this:
    int actual;
    Microsoft.Isam.Esent.Interop.Windows8.Windows8Api.JetResizeDatabase(session, dbid, 1, out actual, 
    Console.WriteLine("Final pages: {0}", actual);
catch (InvalidOperationException)
I didn't actually expect it to shrink the database in this instance, but I was surprised at the outcome. Is there anything else that needs to be done to enable shrinking?

Feb 15, 2013 at 7:10 PM
Short answer: no. Database files don't shrink.

Long answer: The developer who did most of the work on shrinking the database left the team unexpectedly. Even though it worked most of the time, there were some known bugs in the area. Since we were down a team member, we had to make tough decisions on what to do with the finite resources. Unfortunately, that meant Database Shrink was not done for Windows 8. (Even more details: it would only shrink the database if all of the free space was at the end of the file. That's not a perfect solution, but it was definitely a step in the right direction.)

Yes, we are aware that database files that don't shrink are a common pain point.

Feb 15, 2013 at 7:14 PM
Oh, and it's supposed to only return JET_errDisabledFunctionality if you pass in anything other than JET_bitResizeDatabaseOnlyGrow.
Because you passed in JET_bitNil, then it resulted in an EnforceFail. There should have been an event logged in the Application Log...

Feb 15, 2013 at 9:40 PM
Understandable about the resources. I got so excited when I saw it that I hoped I was was just doing something wrong, maybe it was just a system parameter that I wasn't enabling. I had additional questions following that (e.g. how do I cancel a shrink? but since it would have just released free space at the end of the file, there are not that many meaningful cancellation points before a call to SetEndOfFile). I was also curious why you guys weren't shouting this from the rooftops considering how frequently the issue comes up :)

This is possibly just academic at this point since whatever a potential future shrink may not take the same form, and I can't really do anything with the answer, but does JetDefragment coalesce free space at the end of the file?

Also, the exact sequence in the Application Log is this:
"StockSample (1772) Terminating process due to non-recoverable failure: db.cxx (4276)".
Followed by an event with the source "Application Error" with an exception code matching STATUS_FAIL_FAST_EXCEPTION. As far as I can tell, that all happened long before any potential return of JET_errDisabledFunctionality. This was also technically on Server 2012, but the esent dlls seem to be identical between Win8 and Win2012 -- unless it's special cased behavior for the 2012 version.
Unless I completely misunderstood your second message.

Feb 15, 2013 at 9:56 PM
JetDefragment() does not coalesce free space at the end of the file.
The problem comes down to the fact that tables are internally kept track of by the page number of their root page. It gets hard to move them around. And it is harder still to move them around safely and scalably in a multi-threaded environment. By locking the entire database exclusively.

Yes, I misread the code (I misread an #ifdef). It will crash with the Enforce().

Windows 8 and Win 2012 have exactly the same esent.dll binary. I can't think of any special-case behaviour in esent.dll on Server SKUs (other binaries may do it though...)