Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Weixin Wu]

Hi all,

This may not be NAV related problem, our transaction log usage is growing fast and our database is near max capacity.

Does anyone know how to safely reduce the transaction log or backup the file?

What I research online is to shrink the database files. Does anyone done this that can share the experience?

#NAV2015

??

——————————
Weixin Wu
TMI Trading Corp.
Brooklyn NY
——————————

[Ron Ketterling]

You need to run a backup (there are other maintenance functions, too, but to much to write on my phone.) You can backup 3 times and the Tran log will be cleared out. Then you can shrink it.

Based on the limited info in your request, you are probably past the point of being able to do that because of disk space. Do this right away before NAV blows up because of SQL is out of space. Get everyone out of NAV, shut down NAV services. Detach the database, delete the Tran log file and reattach. SQL will rebuild the Tran file. Restart NAV.

This process will give you time to put proper maintenance in place. Of you don’t have a SQL DBA on staff, ask your VAR to do it. You will probably see performance improvement.

Sent from my Verizon, Samsung Galaxy smartphone

——Original Message——

Hi all,

This may not be NAV related problem, our transaction log usage is growing fast and our database is near max capacity.

Does anyone know how to safely reduce the transaction log or backup the file?

What I research online is to shrink the database files. Does anyone done this that can share the experience?

#NAV2015

??

——————————
Weixin Wu
TMI Trading Corp.
Brooklyn NY
——————————

[Bryan Christian]

I really wouldn’t recommend deleting the log file.  True, there’s a good chance SQL Server can just handle it, but it risks data loss and there’s no reason for it.  If you absolutely can’t take a backup to flush the logs (which is what you should be doing) then change the database to simple recovery, shrink the file back to to a more reasonable size, and then swap back to full.  This will break point in time recovery, but unlike the process of just deleting the transaction log file doesn’t risk corruption*.  That’s why taking a backup is a much better option. 

On a different note, if your backup software isn’t handling SQL transaction logs properly and you’re taking an application aware backup then contact support for your backup product immediately and ask for help.  It should be keeping log growth in check.  If you’re not taking application aware backups then please revisit your backup process or talk to the vendor for your backup product about changing that.  

* – Corruption by deleting the log is possible if there is an uncommitted transaction in flight for any reason when you perform the action.  If you have all users get out, shutdown all NAV application servers, then properly detach the database it shouldn’t happen.  My opinion on this, however, is why risk it.  Changing to simple recovery mode then shrinking the data files to reclaim the space eliminates that risk because it means SQL is aware of what you’re doing.

——————————
Bryan Christian
IT Systems Manager
VPT, Inc.
Blacksburg VA
——————————
——————————————-

[Major Williams]

There is a lot of good information on maintaining a SQL server DB. It would be good to review a few sites. We run a daily plan to manage transaction log files.  Backing up both the DB and transaction log files. Shrinking the size of the transaction log could impact performance.  SQL likes to hold onto space even if it is not allocated to information.  The real key is not to have it continuously grow and have it maintain its size.

As everyone has eluded, there is a lot to consider when designing a maintenance program. If you are not as familiar with the process. It would be good to get some help. A good maintenance plan, will improve the performance of the DB. A poor one can have a negative impact on the DB.

——————————
Major Williams
Farmers Investment Co
Sahuarita AZ
——————————
——————————————-

[David Maxwell]

A common issue with running out of space is that you have a Test or Development database sharing the same SQL Server as Production with backup set to Full instead of Simple.

——————————
David Maxwell
Intelice Solutions, LLC
Frederick MD
——————————
——————————————-

[Tri Luong]

Hi,

I believe the transaction log is maintained by SQL each time a full backup is taken.  Try to make a full backup of the database then shrink the file.

——————————
Tri Luong
Jaipur Rugs Inc
Norcross GA
——————————
——————————————-

[Torolf Haug]

As others have mentioned, you first need to a do a little bit of root cause analysis and see why they are ballooning in the first place.  Changing them to Simple recovery or performing backups more often can solve the issue in some cases.

There are a few different DMVs that SQL keeps that will tell you how much of the log file is actually being taken up by transactions.  It’s possible that the backups are being taken successfully, but you had a spike due to some activity which increased the size of the log, and they need to be shrunk.

You can truncate the logs, which is different than shrinking them.  Take a backup command of the logs in SQL and send it to NULL is more or less what you would be doing.  The usual caveats apply.  You want to make sure you know why they are so large, and if they are dirty or not.

Based on that chart above, it certainly appears that the database is NOT being backed up correctly, so I’d look there first.  Shrinking them won’t solve this problem as shown.——————————————-

[Weixin Wu]

Thank you all for great suggestion, our consultor did shrink log file and did daily and weekly maintenance for production server. Total usage is reduced to less than 100gb now.

——————————
Weixin Wu
TMI Trading Corp.
Brooklyn NY
——————————
——————————————-