question

Antonette avatar image
Antonette asked

My Sql Server 2008 ldf file is bigger than my mdf file

I have a Daily Full Backup of my DB as well as my Transaction log, I'm using full recovery model e.g. Database size is 1347 megabytes and my log files is 11379 megabytes But i don't just want to do a shrink on my log file, as a quick fix i want to know what am i missing and why its so large and what can i do to improve this? Any ideas?
sql-server-2008database-filesshrink-log-filelog-file-size
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
MPughDBA avatar image
MPughDBA answered
Are you backing up the transaction log with truncate? Has the log file been this size for sometime or was there a specific event that caused it to grow to this size? Check the autogrowth on the database to see how often it is growing.
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MPughDBA avatar image MPughDBA commented ·
Hi, When running a FULL recovery model you need to perform transaction log backups as well as data backups. It is the transaction log backup that manages the size of the log file. Without it the log file will keep growing until it consumes all available storage. The frequency of the log backups can be dictated by the volume of transactions going through the database, storage availablity and primarily your Recovery Point Objective. If your RPO is an hour then the log backup should occur on an hourly basis. How are you backups being performed? Using SQL Server maintenance plan, SQL Server Agent or third party software?
1 Like 1 ·
Antonette avatar image Antonette commented ·
HI No i am not backing it up with truncate. I realised we are only taking full backup's hence i took a transaction log backup today. As i noticed my ldf file being so extremely large due to only full backups being taken. S0 the log have been this size for awhile. Auto-growth is set to 300MB. Is there perhaps a proper maintenance task that can be setup daily>
0 Likes 0 ·
Antonette avatar image Antonette commented ·
Ye thats why i ran a transactional backup now earlier. I just made use of the maintenance plan wizard and run one for this specific database, once off
0 Likes 0 ·
MPughDBA avatar image MPughDBA commented ·
The same backup method you are using for your daily database backups can be used for the transaction log backups. How are you existing backups being performed?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
"Is there perhaps a proper maintenance task that can be setup daily" <-- that would be the transaction log backup
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.