safest way to reduce size of ldf file mssql server

in ssms, right click on your database, select properties, in option tab, set Recovery Model as Simple.

well you should regularly take full backup and transaction log backup so you can reclaim space in LDF. that is a good practice but if you want to shrink the file (LDF) right now, I suggest you to take backup of your database first and than shrink log file only.

I wish to reduce the file size of my db.ldf file(85 GB) I have been researching full backups and shrinking databases and have been creating full backups but Microsoft seem a little ambiguous about shrinking databases. From my readings I see that the ldf transaction file placeholds virtual memory in the OS for transactions and the file hasnt grown in 5 months. there is 35GB left on the 146GB drive. There are no shares on the drive. There is no problem yet but my question is what would be best practice to shrink the database after a full backup.
Cheers

why do you want to shrink database? just shrink log file.

the problem i'm having is that when i create a full backup the ldf doesnt decrease in size - when i create a backup of the log file it still doesnt decrease, is there a way to shrink the ldf the same way you can with the mdf - microsoft advise against the simple recovery model - but is it correct thinking that changing it to a simple recovery model will replace the ldf file and then change back to the full recovery model so that all new transactions are safe?
The log file reached the 85GB as the DB was setup initially with no recovery model. MS say not to delete the transaction log file.

sorry ritesh i see the ms link and i left out the DBCC line - hence it not working!

can you show me exact command which you are executing?

The safest way would not be changing your recovery model as Reze stated, this is actually putting yourself at risk of not recovering to a point-in-time if the database crashes and its holding mission critical company data. Let's be careful here.

The safest way of reducing a database file (either MDF, NDF or LDF) would be using DBCC SHRINKFILE command.

This will mean no data loss in case you need to do a point-in-time recovery after a crash.

Run SP_HELPFILE which shows the logical file names and the file paths. Then using the logical file name of the log file, run the DBCC SHRINKFILE to shrink the log to a smaller file size.

Here's the syntax:
DBCC SHRINKFILE(N'My_DB_File', size_to_be_in_MB);

E.g, shrinking a file named mydb_log from 500MB to 64MB will be like:
DBCC SHRINKFILE( mydb_log, 64);

Please also it's worth notting that if your log files grow let's say to total of 100GB when the database has a maintenance plan which backs up these log files, then the DBA should realise that the database activities have grown and that the database will regulary need 100GB of file size for it's operations. For optimal performance, you will need atleast 120GB of disk size to accomodate your log files but that's not the best: 150GB of disk size would do much better performance in terms of swap space.

Also note that your log files will reduce in size by a certain value whenever SQL agent runs a log backup job.

DBA activities like reindexing a database, integrity checks, backups and normal transactions like bulk inserts on a database will lead to growing of a log file for a database.

All the best.
Chris.

I agree with Chris, don't go for changing of recovery model. just shrink your log file with the syntax given above and it is also mentioned in the link I have provided you.

answered straight away...impressive!

Good stuff Ritesh, I guess the brother will not have issues with logs again :-)