Avatar of HKFuey
HKFuey
Flag for United Kingdom of Great Britain and Northern Ireland 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?
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
HKFuey

8/22/2022 - Mon
YZlat

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
HKFuey

ASKER
Thanks for the help!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.