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!
Shrink log files
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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> )
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> )
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?
By the way, you should find why they filled. There is any transaction log backup job set for the databases?
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.
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial