SQL 2008 Ldf files growing

HKFuey
HKFuey used Ask the Experts™
on
I'm Running SQL 2008 R2 server 2003. A job does a full backup daily at 5.00AM then another job runs Transaction log backups every hour using this command: -
BACKUP LOG Mydb
TO Mydb_DailyBackup
WITH NOINIT

My problem is my LDF files keep growing (I have just had to change Recovery Model to simple and Shrink to stop my server crashing)

Does anyone know the syntax to shrink my db?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
to shrink the log file changing the database from FULL Recovery to SIMPLE Changes the LSN (Log Sequence of the database).it changes the database lsn number and you cant restore the transaction log files of the database.better take a FULL Backup of the database after changing its Recovery mode.
run the belwo command in the ssms
DBCC OPENTRAN to check for any open transactions in the database

Author

Commented:
I looked at the MS support link then went here for an example: -
http://forum.katarincic.com/Default.aspx?g=posts&t=34

I added this step and it seems to be working: -
DBCC SHRINKFILE (MyDB_Log, 50 )

'MyDB_Log' found in Database properties 'Logical Name'

'50' (Mb) is the size to shrink to.

I will monitor the size over the next few days and report back
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2012

Commented:
DBCC SHRINKFILE
I trust you are not planning on doing this on a scheduled basis.  That is a very bad idea.

Author

Commented:
Hi acperkins, why is it bad?
I do an overnight full backup then a trans log every hour of the day followed by the DBCC SHRINKFILE.

This stops the trans log filling up the disk.
Top Expert 2012
Commented:
I do an overnight full backup then a trans log every hour of the day followed by the DBCC SHRINKFILE.
Simply put you are fragmenting your Transaction Log into oblivion.  The only worse thing you could do is set Auto-shrink on (no, please don't do that).

But don't take my word for it look up the phrase "VLF Fragmentation".

Also, do the following:
DBCC LOGINFO(YourDatabaseName)
if you have more than 50 it is time to fix that.  See here for more info:
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Author

Commented:
Thanks for the help!

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