My log file is growing huge! Why? Help ASAP! please

Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
seeing this multiple times each day:
---
Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled by
user or timed out after __ milliseconds. Use ALTER DATABASE to set a smaller
FileGrowth value for this file or to explicitly set a new file size.
---
(file growth = 10%)
At the same time, applications are experiencing timeouts on reads/writes,
which is a major problem, probably (I'm guessing) because this is causing a
database lock. This particular DB is about 3 GB and the log file is about
30 GB. The log file was only 1 mb before I did the upgrade. I have no
idea why this is happening. I am not using replication or log shipping or
anything like that.
a) what gets stored in the log file?
b) why is mine growing out of control?
c) how do I stop the growth?
d) how do I get the log file back to a small size again?Do a backup of the transaction log and then shrink the file.
Ben Nevarez, MCDBA, OCP
Database Administrator
"D. Patrick" wrote:
> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
> seeing this multiple times each day:
> ---
> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled by
> user or timed out after __ milliseconds. Use ALTER DATABASE to set a smaller
> FileGrowth value for this file or to explicitly set a new file size.
> ---
> (file growth = 10%)
> At the same time, applications are experiencing timeouts on reads/writes,
> which is a major problem, probably (I'm guessing) because this is causing a
> database lock. This particular DB is about 3 GB and the log file is about
> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
> idea why this is happening. I am not using replication or log shipping or
> anything like that.
> a) what gets stored in the log file?
> b) why is mine growing out of control?
> c) how do I stop the growth?
> d) how do I get the log file back to a small size again?
>
>|||Thanks for the reply, but I don't believe that solves the underlying
problem. Why is it growing? What should look at?
And BOL says that shrinking the file may not actually shrink the physical
file. I am very confused and would appreciate any information or
step-by-step instructions. Thank you.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> Do a backup of the transaction log and then shrink the file.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "D. Patrick" wrote:
>> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
>> seeing this multiple times each day:
>> ---
>> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled
>> by
>> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
>> smaller
>> FileGrowth value for this file or to explicitly set a new file size.
>> ---
>> (file growth = 10%)
>> At the same time, applications are experiencing timeouts on reads/writes,
>> which is a major problem, probably (I'm guessing) because this is causing
>> a
>> database lock. This particular DB is about 3 GB and the log file is
>> about
>> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
>> idea why this is happening. I am not using replication or log shipping
>> or
>> anything like that.
>> a) what gets stored in the log file?
>> b) why is mine growing out of control?
>> c) how do I stop the growth?
>> d) how do I get the log file back to a small size again?
>>|||A reason for the growth is that your DB is perhaps in Fully logged
recovery mode and maybe it was in simple recovery mode on the old
server
Take a look at this it might help you out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||That is true. I changed that option recently. Does that option really use
10x the space of the database (and more, it seems, by the day)? It seems
that it is useless if it uses 3 GB per day on a 3 GB database. Is there
some other setting that removes log info after each database backup, so that
it will only keep a bit of log data?
Can I change that back to simple while the production DB is running?
I have read BOL and your URL links about shrinking database files. Maybe
I'm just stupid, but I still have no idea what to type into the query window
to get back to a nice little 1 MB log file (instead of the 30 GB and growing
it is at right now). Please help. Thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147461310.310058.75290@.g10g2000cwb.googlegroups.com...
>A reason for the growth is that your DB is perhaps in Fully logged
> recovery mode and maybe it was in simple recovery mode on the old
> server
> Take a look at this it might help you out
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||It is normal for the transaction log file to grow. Are you doing regular
transaction log backups?
Try that first (backup and shrink) and let us know what happen.
Ben Nevarez, MCDBA, OCP
Database Administrator
"D. Patrick" wrote:
> Thanks for the reply, but I don't believe that solves the underlying
> problem. Why is it growing? What should look at?
> And BOL says that shrinking the file may not actually shrink the physical
> file. I am very confused and would appreciate any information or
> step-by-step instructions. Thank you.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> >
> > Do a backup of the transaction log and then shrink the file.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "D. Patrick" wrote:
> >
> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
> >> seeing this multiple times each day:
> >>
> >> ---
> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled
> >> by
> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
> >> smaller
> >> FileGrowth value for this file or to explicitly set a new file size.
> >> ---
> >> (file growth = 10%)
> >>
> >> At the same time, applications are experiencing timeouts on reads/writes,
> >> which is a major problem, probably (I'm guessing) because this is causing
> >> a
> >> database lock. This particular DB is about 3 GB and the log file is
> >> about
> >> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
> >> idea why this is happening. I am not using replication or log shipping
> >> or
> >> anything like that.
> >>
> >> a) what gets stored in the log file?
> >> b) why is mine growing out of control?
> >> c) how do I stop the growth?
> >> d) how do I get the log file back to a small size again?
> >>
> >>
> >>
>
>|||I have not been backing up the log. Is that the problem? When you do a log
backup, does that automatically then drop the file back to 1 MB again? A
command example that I can type in the query window would greatly help me.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:1CAE345F-A36B-4840-B190-495705186E06@.microsoft.com...
> It is normal for the transaction log file to grow. Are you doing regular
> transaction log backups?
> Try that first (backup and shrink) and let us know what happen.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "D. Patrick" wrote:
>> Thanks for the reply, but I don't believe that solves the underlying
>> problem. Why is it growing? What should look at?
>> And BOL says that shrinking the file may not actually shrink the physical
>> file. I am very confused and would appreciate any information or
>> step-by-step instructions. Thank you.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
>> >
>> > Do a backup of the transaction log and then shrink the file.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "D. Patrick" wrote:
>> >
>> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
>> >> seeing this multiple times each day:
>> >>
>> >> ---
>> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was
>> >> cancelled
>> >> by
>> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
>> >> smaller
>> >> FileGrowth value for this file or to explicitly set a new file size.
>> >> ---
>> >> (file growth = 10%)
>> >>
>> >> At the same time, applications are experiencing timeouts on
>> >> reads/writes,
>> >> which is a major problem, probably (I'm guessing) because this is
>> >> causing
>> >> a
>> >> database lock. This particular DB is about 3 GB and the log file is
>> >> about
>> >> 30 GB. The log file was only 1 mb before I did the upgrade. I have
>> >> no
>> >> idea why this is happening. I am not using replication or log
>> >> shipping
>> >> or
>> >> anything like that.
>> >>
>> >> a) what gets stored in the log file?
>> >> b) why is mine growing out of control?
>> >> c) how do I stop the growth?
>> >> d) how do I get the log file back to a small size again?
>> >>
>> >>
>> >>
>>|||backup log with TRUNCATE_ONLY will release space, however you still
need to issue a SHRINK command after that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Two questions:
Can I do this on the production server without causing live problems?
Should I do this in a script, in the future, after all full database backups
(assuming that I don't want to roll back prior to the full backup moment)?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> backup log with TRUNCATE_ONLY will release space, however you still
> need to issue a SHRINK command after that
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||My database is similar in size to yours (actually a little larger). I have
SQL Agent backing up the log every 15 minutes with full logging on. This
keeps the log file from growing too large. Also, in the event of a failure,
I am able to restore to the point 15 minutes (sometimes 30) before the
failure. We (OK my boss) decided this was an acceptable trade off vs. the
expense of having a true failover server.
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
> Two questions:
> Can I do this on the production server without causing live problems?
> Should I do this in a script, in the future, after all full database
backups
> (assuming that I don't want to roll back prior to the full backup moment)?
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> > backup log with TRUNCATE_ONLY will release space, however you still
> > need to issue a SHRINK command after that
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
>|||Would you mind posting the script that is running to back up your log file?
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> My database is similar in size to yours (actually a little larger). I have
> SQL Agent backing up the log every 15 minutes with full logging on. This
> keeps the log file from growing too large. Also, in the event of a
> failure,
> I am able to restore to the point 15 minutes (sometimes 30) before the
> failure. We (OK my boss) decided this was an acceptable trade off vs. the
> expense of having a true failover server.
> "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
>> Two questions:
>> Can I do this on the production server without causing live problems?
>> Should I do this in a script, in the future, after all full database
> backups
>> (assuming that I don't want to roll back prior to the full backup
>> moment)?
>>
>> "SQL" <denis.gobo@.gmail.com> wrote in message
>> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
>> > backup log with TRUNCATE_ONLY will release space, however you still
>> > need to issue a SHRINK command after that
>> >
>> > Denis the SQL Menace
>> > http://sqlservercode.blogspot.com/
>> >
>>
>|||It won't make much sense. I used Enterprise Manager and created a DB
Maintenance Plan to do the job. So the script looks like:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8797A992-C063-43C1-AAC4-5C0302531C89 -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "C:\USB_Drive\SQL_Backup" -DelBkUps 4DAYS -BkExt "TRN"'
Using a maintenance plan is pretty easy for something like this that isn't
too complicated.
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:6369g.1950$uM4.783@.tornado.socal.rr.com...
> Would you mind posting the script that is running to back up your log
file?
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> > My database is similar in size to yours (actually a little larger). I
have
> > SQL Agent backing up the log every 15 minutes with full logging on. This
> > keeps the log file from growing too large. Also, in the event of a
> > failure,
> > I am able to restore to the point 15 minutes (sometimes 30) before the
> > failure. We (OK my boss) decided this was an acceptable trade off vs.
the
> > expense of having a true failover server.
> >
> > "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> > news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
> >> Two questions:
> >>
> >> Can I do this on the production server without causing live problems?
> >>
> >> Should I do this in a script, in the future, after all full database
> > backups
> >> (assuming that I don't want to roll back prior to the full backup
> >> moment)?
> >>
> >>
> >> "SQL" <denis.gobo@.gmail.com> wrote in message
> >> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> >> > backup log with TRUNCATE_ONLY will release space, however you still
> >> > need to issue a SHRINK command after that
> >> >
> >> > Denis the SQL Menace
> >> > http://sqlservercode.blogspot.com/
> >> >
> >>
> >>
> >
> >
>|||thank you.
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:eRHv8VgdGHA.564@.TK2MSFTNGP02.phx.gbl...
> It won't make much sense. I used Enterprise Manager and created a DB
> Maintenance Plan to do the job. So the script looks like:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8797A992-C063-43C1-AAC4-5C0302531C89 -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "C:\USB_Drive\SQL_Backup" -DelBkUps 4DAYS -BkExt "TRN"'
> Using a maintenance plan is pretty easy for something like this that isn't
> too complicated.
> "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> news:6369g.1950$uM4.783@.tornado.socal.rr.com...
>> Would you mind posting the script that is running to back up your log
> file?
>>
>> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
>> news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> > My database is similar in size to yours (actually a little larger). I
> have
>> > SQL Agent backing up the log every 15 minutes with full logging on.
>> > This
>> > keeps the log file from growing too large. Also, in the event of a
>> > failure,
>> > I am able to restore to the point 15 minutes (sometimes 30) before the
>> > failure. We (OK my boss) decided this was an acceptable trade off vs.
> the
>> > expense of having a true failover server.
>> >
>> > "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
>> > news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
>> >> Two questions:
>> >>
>> >> Can I do this on the production server without causing live problems?
>> >>
>> >> Should I do this in a script, in the future, after all full database
>> > backups
>> >> (assuming that I don't want to roll back prior to the full backup
>> >> moment)?
>> >>
>> >>
>> >> "SQL" <denis.gobo@.gmail.com> wrote in message
>> >> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
>> >> > backup log with TRUNCATE_ONLY will release space, however you still
>> >> > need to issue a SHRINK command after that
>> >> >
>> >> > Denis the SQL Menace
>> >> > http://sqlservercode.blogspot.com/
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Another way to do it is to "detach" the database and then "attach" it back.
This can be done throught Enterprise Manager. Usually one does that as part
of moving a database, but if you just reattach from the same place, it works
fine and shrinks the log file. We went from 10 GB to under 1 MB. I had
experimented with shrink and truncate before and never could get it quite
right, but the detach/attach worked fine. Alternatively, you can use Query
Analyzer and run sp_detach_db and sp_attach_db.|||"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:NB59g.1899$uM4.1049@.tornado.socal.rr.com...
> I have not been backing up the log. Is that the problem?
Yes, that is exactly the problem.
> When you do a log
> backup, does that automatically then drop the file back to 1 MB again?
Not quite, it removes all complete transactions. If you have transactions
that aren't complete, they'll stay in the log until the next backup.
> A
> command example that I can type in the query window would greatly help me.
backup log <dbname> to disk='f:\log_backups\<dbname>_tlog_200605121724.trn'
But your best bet is to schedule a daily or more often backup.
(we do ours every 20 minutes for example so that we're never more than 20
minutes "out of date" in the event of disaster recovery.)
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:1CAE345F-A36B-4840-B190-495705186E06@.microsoft.com...
> >
> > It is normal for the transaction log file to grow. Are you doing regular
> > transaction log backups?
> >
> > Try that first (backup and shrink) and let us know what happen.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "D. Patrick" wrote:
> >
> >> Thanks for the reply, but I don't believe that solves the underlying
> >> problem. Why is it growing? What should look at?
> >>
> >> And BOL says that shrinking the file may not actually shrink the
physical
> >> file. I am very confused and would appreciate any information or
> >> step-by-step instructions. Thank you.
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> >> >
> >> > Do a backup of the transaction log and then shrink the file.
> >> >
> >> > Ben Nevarez, MCDBA, OCP
> >> > Database Administrator
> >> >
> >> >
> >> > "D. Patrick" wrote:
> >> >
> >> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I
am
> >> >> seeing this multiple times each day:
> >> >>
> >> >> ---
> >> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was
> >> >> cancelled
> >> >> by
> >> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
> >> >> smaller
> >> >> FileGrowth value for this file or to explicitly set a new file size.
> >> >> ---
> >> >> (file growth = 10%)
> >> >>
> >> >> At the same time, applications are experiencing timeouts on
> >> >> reads/writes,
> >> >> which is a major problem, probably (I'm guessing) because this is
> >> >> causing
> >> >> a
> >> >> database lock. This particular DB is about 3 GB and the log file is
> >> >> about
> >> >> 30 GB. The log file was only 1 mb before I did the upgrade. I
have
> >> >> no
> >> >> idea why this is happening. I am not using replication or log
> >> >> shipping
> >> >> or
> >> >> anything like that.
> >> >>
> >> >> a) what gets stored in the log file?
> >> >> b) why is mine growing out of control?
> >> >> c) how do I stop the growth?
> >> >> d) how do I get the log file back to a small size again?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||"D Shirley" <D Shirley@.discussions.microsoft.com> wrote in message
news:E07A48F1-468A-407E-8FCD-885A72C51297@.microsoft.com...
> Another way to do it is to "detach" the database and then "attach" it
back.
> This can be done throught Enterprise Manager. Usually one does that as
part
> of moving a database, but if you just reattach from the same place, it
works
> fine and shrinks the log file.
Umm, this should NOT work that way unless you're deleting the logfile.
Don't do that. it may work and you could end up with a ruined database.
> We went from 10 GB to under 1 MB. I had
> experimented with shrink and truncate before and never could get it quite
> right, but the detach/attach worked fine. Alternatively, you can use
Query
> Analyzer and run sp_detach_db and sp_attach_db.
>
Also, do NOT shrink and truncate unless you really really don't care about
disaster recovery. (or have very simple needs.)
Shrinking can lead to a fragmented file on the disk and truncating means
you've limited what you can recover.