Log just keeps growing!

> Hi,
>
> My 600Mb database continually has a 4Gb log file with it, which is a bit
of
> a pain as we have a very small number of inserts during the day so there's
> no need for the log to be this big.
>
> We do have a load process that loads approx 150,000 - 200,000 rows
running
> once a week, but, even still I can't see why the log grows as it does.
Every
> night we have a full backup running.
>
> So, I've got a couple of questions:
>
> 1) - What backup mode do I need to have set on the DB to ensure once
> it's backed up the log is then truncated correctly? I'm guessing the log
> itself is being truncated but the actual file isn't being shrunk?
>
> 2) - I keep detaching the DB and then reattaching it without a log
to
> get rid of the huge log file. Is this the best way to keep this huge log
> under control or am I risking data corruption?
>
> 3) - Are there any jobs I can run during the day that can keep this
> log under control?
>
> Any advice appreciated.
>
> Thanks
>
>You can use simple recovery mode. For more information check these out:
http://www.support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=272318
http://www.support.microsoft.com/?id=317375
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>|||Bear in mind, the transaction log supports point-in-time database recovery.
If you need this functionality, truncating the log frequently during the day
will cause a problem.
What you should do is schedule frequent transaction log backups (to disk if
you plan point-in-time recovery, then make sure to back those up to tape
quickly, so you can clear off disk space). To recover the newly freed
space, schedule a job to run fairly frequently in the database that does
dbcc shrinkfile (<database_name>_log, 0) (in SQL 7, do not put the ,0 in).
Anyhow, here is your detail on backup modes:
Full recovery model (select into/bulk copy disabled, trunc. log on chkpt.
disabled) -- This is the default. It enables you to do point-in-time
restores, but the transaction log will grow unless you back it up frequently
to disk, and then shrink the log file afterwards.
Bulk logged recovery model (select into/bulk copy enabled, trunc. log on
chkpt. disabled) -- This improves the performance of bulk data loads and
other data processes (eg create index) other than simple INSERTs, UPDATEs
and DELETEs by only minimally logging the event. If you plan to backup the
transaction log so you can restore from it later (point-in-time restore),
you need to a full or differential database backup AFTER EVERY database
operation other than a INSERT, UPDATE or DELETE. You still need to backup
the log frequently, and schedule dbcc shrinkfile to return the inactive
space to the OS
Simple Recovery model (trunc. log on chkpt and select/into bulk copy
enabled). Not only are major database operations not logged, the
transaction log is automatically emptied out at regular intervals
(checkpoints). The only backups you can restore from here are full database
backups or differential backups. You may need to still run dbcc shrinkfile
to keep the log file under control.
If you have more questions, you can try emailing me, I'm not sure if my
account is still getting spammed out of control or not.
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andy_mcdba@.yahoo.com
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
Andy_mcdba@.yahoo.com gets filled up to the account
limit with spam every couple of hours now so replies may
not be possible. I will remove this disclaimer once every
ISP involved with relaying the spam can help me out.
*******************************************************************
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>

Log file partition

I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
>
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
>
> EdComments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> > Diane,
> >
> > The standard 3 drive setup is to put the operating system on the C
drive,
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
> >
> > Ed
>
>|||Stev
Most sites (and most people) have different views on the perfect disk set up
The way we do it
'C' Operating system (NT4 or Windows 2000/2003
'D' Install SQL Server, System databases, User databases primary filegroups containing system tables onl
'E' User databases user table
'F' User databases transaction log
'G' Backup
'H' Application Dat
D, E and F are usually RAID 1 + 0 all other drives are mirrored
We find this gives us good performance and good resilience
Regard
John

Log file growth concern

I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!

Forgot to mention that the database is in Full Recovery mode.

|||

there is something wrong in you backup stratergy. You need to relook it as soon as possible. You said it is in Full recovery model. So my first question is , do you take regular backup of Transaction Log(TL) . I doubt , you don't and that is the reason of this outgrown log file.

these are few guidelines

(a) first check whether you need Full recovery model , if not change it to Simple

(b) WHat is the Backup policy. if you are taking the TL backup then increase the frequency of the TL backup

(c) in any case this TL log size is not advisable. You will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.

Refer :

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

Madhu

|||

Running the T-SQL below shrunk the log file down to 1 MB. I am not doing TL backups at this time. I never did them in SQL2000, however it now looks like I need to re-examime SQL2005 requirements.

(a) It appears from the article you referenced that if I stay in Full Recovery mode then I will need to add maintenance to backup up Transaction logs, truncate transaction logs, and run update statistics daily.

(b) We backup databases to disk daily and these are written to tape the same night

(c) This solved the current large log file issue:

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

|||

You said you are in full recovery mode but you are not doing any log backups. So why are you in full recovery mode? What are the recovery requirements from the business? I'm guessing you may not be able to meet those requirements. If you can lose whatever data since your last full backup, you should be able to use simple recovery. If not then you need to be doing log backups. And just to clarify one other statement, keep in mind that truncating the log is not physically shrinking the log. It's good to understand that whole concept. Books Online covers it pretty well under Truncating the Transaction Log:
http://msdn2.microsoft.com/en-us/library/ms189085.aspx

Another thing to keep in mind in that regularly performing physical shrinks of your logs is not necessarily a good idea. You should manage the log size with your transacation log backups. You don't want the logs continually growing, shrinking, etc. You want the logs to be at the appropriate size need for the database activities and managed with log backups. Read the following article about shrinking files:

http://www.karaszi.com/sqlserver/info_dont_shrink.asp

-Sue

|||

Thank you for the clarification Sue. Your comments have helped me to wrap my mind around how backup strategies work in SQL2005. A full database back up to disk is performed each night and then written to tape. In the event of a failure, we would only lose 24 hours which at this time is acceptable to the company. To provide better recovery I believe I will perform full backups several times throughout the day.

With this said, it would appear that a good strategy for us would be to change the recovery mode to simple, to manage the transaction log files size, and perform full database backups every 2 hours.

Thanks again!