How to Shrink .ldf file in SQL Server

Iqbal_FebrianoIqbal_Febriano Member Posts: 66
Hi, we are using Navision 3.70 with SQL Server database.
When I checked the server, I found that the .ldf file's size has grow to 5 GB.
I've learnt that we can't just delete the file :D, but I know that we can shrink it. Is there anyone can tell me the how to shrink it regarding with Navision usage, and any considerations or restrictions
Thanx :wink:
Be fast, be straight, be quiet


  • Options
    Ian_piddigntonIan_piddignton Member Posts: 92
    This is your log file, it should 'shrink' when appropriate backups are taken. You could also try doing a database shrink form enterprise manager. Your SQL DBA should know about this or check books online for information.


  • Options
    sfisfi Member Posts: 10
    Use SQL Server Enterprise Manager and set up a Management Plan under Management.

    In this Management Plan you set up a backup of Transaction Log. On the Optmizations Tab check the "Remove unused space from database files".

    Schedule the backup to run frequently.

    Steen Fisker
    Navision Developer since 1990
    Steen Fisker
    Microsoft Certified Professional
  • Options
    facadefacade Member Posts: 57
    Or just:
    1. Make sure no users are connected - connecting.
    2. Make standard Navision backup.
    3. truncate the translog with sql query manager or sth. else
    4. shrink the translog db.
  • Options
    spycraftspycraft Member Posts: 66
    I have the following query:
    1. Using the SQL Backup function, will this shrink the .ldf file??
    2. If the above is positive, will the Backup function in Navision do the similar as the SQL Backup?
  • Options
    DenSterDenSter Member Posts: 8,304
    In the SQL Enterprise manager, right click on the database, all tasks, shrink database. on the window that pops up, you can click the 'files' button, and then select which file to shrink.

    To see the size of the database files graphically, right click the database, view, and select 'taskpad'. You will then see the total reserved size of the files and the proportion that is actually used.

    To get rid of the transaction log, you will need to do a SQL backup. To prevent the server from making a huge transaction log, you need to alter the database (FROM WITHIN NAVISION!!!!!) and set the recovery model to Simple. You can also set maximum file sizes, but I don't know the behaviour when the files get to that size.
  • Options
    spycraftspycraft Member Posts: 66
    The Shrink database works fine.. :)
  • Options
    dmccraedmccrae Member, Microsoft Employee Posts: 144
    A Navision backup is not the same as a SQL backup - it will not truncate the log afterwards.
    Dean McCrae - Senior Software Developer, NAV Server & Tools

    This posting is provided "AS IS" with no warranties, and confers no rights.
  • Options
    spycraftspycraft Member Posts: 66
    robertc wrote:
    A Navision backup is not the same as a SQL backup - it will not truncate the log afterwards.
    Hi robertc...

    noted your feedback... :)

    1) i understand that navision backup and SQL backup is different... do u mean that by using SQL backup, the transaction log will be truncated afterwards??
    2) pls correct me if i'm wrong: i understand that the above happens only if the recovery model is set to Simple... is there any workaround for databases whose recovery model is set to full or bulk-logged??
  • Options
    WaldoWaldo Member Posts: 3,412
    In my experience, only the command SHRINKFILE in T-SQL (in e.g. SQL Query Analyzer) works the best (immediately). You can find everything in the help of Query Analyzer.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
Sign In or Register to comment.