Ever Expanding Log Files

Hi all,
I'm somewhat new to managing an SQL Server installation. I've noticed that
the log files for my database are getting quite large. The amount of actual
data is quite small because a lot has been deleted recently, but the log
files are now about 10 times the size of the actual data just now.
I'm wondering what I'm supposed to do to keep the log file sizes in check.
I mean, the log file at the moment is presumably holding details of every
transaction my database has gone through since it was made. Obviously, I
want to have a robust data recovery mechanism, but can anyone advise me on
how to keep log files to a more manageable size?
Many thanks
SimonBe sure to backup the transaction log periodically. For small to medium
sized databases, you could just create a Database Maintenance Plan that
backups the database and the logs. The log backup can be on a different
schedule than the full backup.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b2136b8c8045756b5c4ff@.news.microsoft.com...
Hi all,
I'm somewhat new to managing an SQL Server installation. I've noticed that
the log files for my database are getting quite large. The amount of actual
data is quite small because a lot has been deleted recently, but the log
files are now about 10 times the size of the actual data just now.
I'm wondering what I'm supposed to do to keep the log file sizes in check.
I mean, the log file at the moment is presumably holding details of every
transaction my database has gone through since it was made. Obviously, I
want to have a robust data recovery mechanism, but can anyone advise me on
how to keep log files to a more manageable size?
Many thanks
Simon|||It looks like you are using the FULL or BULK_LOGGED recovery model.
Normally, one performs log backups periodically between database backups.
In addition to providing the means for forward transaction recovery, log
backups also truncate the log and keep your transaction log size reasonable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b2136b8c8045756b5c4ff@.news.microsoft.com...
> Hi all,
> I'm somewhat new to managing an SQL Server installation. I've noticed that
> the log files for my database are getting quite large. The amount of
> actual data is quite small because a lot has been deleted recently, but
> the log files are now about 10 times the size of the actual data just now.
> I'm wondering what I'm supposed to do to keep the log file sizes in check.
> I mean, the log file at the moment is presumably holding details of every
> transaction my database has gone through since it was made. Obviously, I
> want to have a robust data recovery mechanism, but can anyone advise me on
> how to keep log files to a more manageable size?
> Many thanks
> Simon
>|||I think what he is probably referring to is the physical size of the
log file on the drive.
Take a look at DBCC shrinkfile to shrink your transaction log file
size. Even if you have backed up the log in full mode it will not
recover the physical space it once occupied.
Mike
Dan Guzman wrote:[vbcol=seagreen]
> It looks like you are using the FULL or BULK_LOGGED recovery model.
> Normally, one performs log backups periodically between database backups.
> In addition to providing the means for forward transaction recovery, log
> backups also truncate the log and keep your transaction log size reasonabl
e.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Simon Harvey" <nothanks@.hotmail.com> wrote in message
> news:7c72785b2136b8c8045756b5c4ff@.news.microsoft.com...|||mike.mcn...@.gmail.com wrote:
> I think what he is probably referring to is the physical size of the
> log file on the drive.
> Take a look at DBCC shrinkfile to shrink your transaction log file
> size.
You should shrink the log only in special cases and then only very
rarely. If he hasn't been taking regular log backups then the OP may
want to do this ONE time only. The correct long-term fix however is to
implement log backups or choose the simple recovery model.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You are right that Simon should probably shrink the log file(s) to an
appropriate size after he implements his recovery plan. However, just to
make it clear, the files shouldn't be shrunk as a normal operational task.
The shrink of the out-of-control logs should be a one-time task.
Afterwards, log files should be sized to accommodate expected activity.
Hope this helps.
Dan Guzman
SQL Server MVP
<mike.mcneer@.gmail.com> wrote in message
news:1140458807.508661.198820@.o13g2000cwo.googlegroups.com...
>I think what he is probably referring to is the physical size of the
> log file on the drive.
> Take a look at DBCC shrinkfile to shrink your transaction log file
> size. Even if you have backed up the log in full mode it will not
> recover the physical space it once occupied.
> Mike
>
>
> Dan Guzman wrote:
>|||Hi pals,
am also having a similar problem to that of Simon. The log files of my
server are being stored in D drive of our SQL server which is 120 GB. We hav
e
a database maintenance plan & all mdf & ldf files are backed up on a daily
basis. the problem is that the log file keeps growing & within 4 days it
occupies the memory of the entire drive. am into development & i least know
abt these things. Right now am deleting all those log files once in 3 days &
the ldf file is automatically created with minimum space. I wanted to know
whether this is a right way. What could be done to permanently avoid these
log files into growing? I would be much obliged if anyone could throw some
light on this. We dont have a DBA now & am managing the show. Somebody pleas
e
help me in getting rid of this issue.
Regards,
Rajesh
--
DubaiNat
"Simon Harvey" wrote:

> Hi all,
> I'm somewhat new to managing an SQL Server installation. I've noticed that
> the log files for my database are getting quite large. The amount of actua
l
> data is quite small because a lot has been deleted recently, but the log
> files are now about 10 times the size of the actual data just now.
> I'm wondering what I'm supposed to do to keep the log file sizes in check.
> I mean, the log file at the moment is presumably holding details of every
> transaction my database has gone through since it was made. Obviously, I
> want to have a robust data recovery mechanism, but can anyone advise me on
> how to keep log files to a more manageable size?
> Many thanks
> Simon
>
>|||Raajeshh wrote:
> Hi pals,
> am also having a similar problem to that of Simon. The log files of my
> server are being stored in D drive of our SQL server which is 120 GB. We h
ave
> a database maintenance plan & all mdf & ldf files are backed up on a daily
> basis. the problem is that the log file keeps growing & within 4 days it
> occupies the memory of the entire drive. am into development & i least kno
w
> abt these things. Right now am deleting all those log files once in 3 days
&
> the ldf file is automatically created with minimum space. I wanted to know
> whether this is a right way. What could be done to permanently avoid these
> log files into growing? I would be much obliged if anyone could throw some
> light on this. We dont have a DBA now & am managing the show. Somebody ple
ase
> help me in getting rid of this issue.
> Regards,
> Rajesh
> --
> DubaiNat
>
Are you sure you are doing transaction log backups? It doesn't sound
like you are. You say that backups are done daily but it would be very
unusual to do transaction log backups only once per day. More typically
you would schedule a DATABASE backup once per day, followed by LOG
backups every hour or half hour for example. Also, you say that "mdf &
ldf files" are being backed up, which suggests that you are backing up
files through the file system rather than doing native SQL backups.
If you ARE performing log backups but also deleting the LDF files then
you are invalidating your log backups from that point forward. You also
risk corrupting your entire database. You need to stop deleting files
right now and you need to ensure that you have enough storage for a
transaction log covering the maximum interval between log backups.
Shrinking and growing the log is counter productive.
Read the topics on log and database backups and recovery models in
Books Online. Then decide if you need log backups and implement them on
an appropriate schedule. If backing up once per day is acceptable to
your business then you surely don't need log backups so you can run at
Simple Recovery to prevent log files from growing excessively. Be aware
however, that you are then potentially at risk of losing a day's work,
which would be totally unacceptable in most transaction processing
environments (read: "database support staff could get fired for
that!"). You need to determine what level of risk is acceptable to the
business and implement the backup solution accordingly.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||To add to David's response, I just want to emphasize that not only is
deleting the log files not the right approach, it is downright dangerous. A
database will be unrecoverable if the deleted log contained pending
transactions and you'll need to restore from backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Raajeshh" <Raajeshh@.discussions.microsoft.com> wrote in message
news:86A884CC-E798-415A-A57D-38FA3CC8028D@.microsoft.com...[vbcol=seagreen]
> Hi pals,
> am also having a similar problem to that of Simon. The log files of my
> server are being stored in D drive of our SQL server which is 120 GB. We
> have
> a database maintenance plan & all mdf & ldf files are backed up on a daily
> basis. the problem is that the log file keeps growing & within 4 days it
> occupies the memory of the entire drive. am into development & i least
> know
> abt these things. Right now am deleting all those log files once in 3 days
> &
> the ldf file is automatically created with minimum space. I wanted to know
> whether this is a right way. What could be done to permanently avoid these
> log files into growing? I would be much obliged if anyone could throw some
> light on this. We dont have a DBA now & am managing the show. Somebody
> please
> help me in getting rid of this issue.
> Regards,
> Rajesh
> --
> DubaiNat
>
> "Simon Harvey" wrote:
>|||Hi Guys,
Apologies for only just getting back to this now.
I just wanted to say a big thank you to everyone who has been kind enough
to offer their thoughts.
I now understand that the log is truncated when backups are performed (but
that you need to shrink the log files to reclaim the physical space)
Many thanks again
Simon