Shrink log files

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
I have a situation where the log files on two db's filled up a partition.
I just moved the recovery mode to simple for both of these db's

now...
what is the next best way for it to "take action" and shrink just the logs files....
should I restart the server?
is there a less intrusive process.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
No, don't restart the server.

Just CHECKPOINT the dbs and then shrink the logs.

USE db1
CHECKPOINT
DBCC SHRINKFILE ( 2, <#mb_to_shrink_log_to> )
USE db2
CHECKPOINT
DBCC SHRINKFILE ( 2, <#mb_to_shrink_log_to> )
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
You should perform an immediate full backup of the databases.
By the way, you should find why they filled. There is any transaction log backup job set for the databases?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
After you get the logs back down to a reasonable size, if you put the db back into FULL recovery model, you'll need to take a full backup of the db then.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
If your database in FULL recovery model you have to take transaction log backup otherwise keep it in simple recovery mode.

You can query sys.databases system view here check the log_reuse_wait_desc column,  value should be nothing,
otherwise you can see the log file locked by process.
eg: replication, active_log_backup, Active_transactions.

these are the few example which lead the log grow large.
Take appropriate action accordingly.
Steve WalesSenior Database Administrator
Commented:
Please have a read of this article:  http://www.experts-exchange.com/articles/11077/How-to-shrink-a-bloated-log-file.html

In Full Recovery mode, your log files will grow  forever until you take a log backup.  Taking a log backup truncates the log file (that is not the same as shrinking - it just means marks the file as available for reuse).

Fix the existing situation (see Shrink commands above) and make sure you manually regrow the log file to a size that meets your transaction volume needs then make sure you're taking regular log backups.

Commented:
Above is the approx same explanation as well.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial