asked on

SQL 2008 Ldf files growing

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?

YZlat

http://support.microsoft.com/kb/907511

Ramesh Babu Vavilla

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

HKFuey

ASKER

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

Anthony Perkins

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

HKFuey

ASKER

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HKFuey

ASKER

Thanks for the help!