How to Shrink .ldf file in SQL Server

Iqbal_Febriano

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 , 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

Ian_piddignton

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.


regards

Ian

sfi

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.

Regards,
Steen Fisker
Navision Developer since 1990

facade

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.

spycraft

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?

DenSter

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.

spycraft

The Shrink database works fine..

dmccrae

A Navision backup is not the same as a SQL backup - it will not truncate the log afterwards.

spycraft

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??

Waldo

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.