Log File Full

My production database is very big in size and due to contious transactions, my log file becomes full frequently. I manually shrink log file when it becomes large. But i believe shrinking many times is not a good practise in terms of performance. So what should i do? Can anyone suggest a better approach.

How much frequency are you taking backup of transaction log ?

For example, if you are taking transaction log backup every 30 minutes, change it to every 15 minutes.

Thanks

Naras.

|||

Yes, you rightly said that shrinking is not a good practice. You should control the growth of Transaction log and if space is not there in the current drive, you should shift TL to a better place. You can control the growth by taking backup of transaction log more frequently. When you shrink the transaciton log the backup chain breaks. so the first thing you do should be full backup. If you dont want point in time restore you can change the recovery model to simple which will restirct the gwoth of TL to a certain limit.

How to stop the transaction log of a SQL Server database from growing unexpectedly

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

Madhu

|||I take trn log backup every 2 hours. As i want point in time restore, i need to take tl back up atleast 2 hr.|||But if i don't shrink it grows beyond 2 GB in week. I need a poin in time restore so i had to keep full recovery model.|||

what is the frequency of TL backup of this database.

Madhu

|||Every 2 hr TL Backup, daily Diff backup and weekly Full backup.|||

You can still achieve a point in time restore, even if you take transaction log backup every 30/60/90/120 minutes.

Thanks

Naras.

|||

schedule TL backup more frequently... say every 15 min... this is the better approach

Madhu

|||

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=263

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

Addition to Madhu's references.

Log file becomes ungrowable every morning.

Have a site with a wierd problem. A 3rd party application is accessing a SQL
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.On 23.04.2007 08:12, Mal Osborne wrote:[vbcol=seagreen]
> Have a site with a wierd problem. A 3rd party application is accessing a S
QL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has alwa
ys
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this[/vbcol
]
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?

> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Log file becomes ungrowable every morning.

Have a site with a wierd problem. A 3rd party application is accessing a SQL
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.
On 23.04.2007 08:12, Mal Osborne wrote:
> Have a site with a wierd problem. A 3rd party application is accessing a SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert
|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?

> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Log file becomes ungrowable every morning.

Have a site with a wierd problem. A 3rd party application is accessing a SQL
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.On 23.04.2007 08:12, Mal Osborne wrote:
> Have a site with a wierd problem. A 3rd party application is accessing a SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Log File

If your database is set to unrestricted log file growth can anyone tell me why the log file becomes full? I get this error. "Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002)" After about three or four days now on a consistent bases. I don't believe it is because I am running out of disk space. ???

TomWhat is your current filesystem ... FAT or NTFS ?|||It has to be the case, you can't get this error for any other reason other than when running out of disk...It is NTFS, isn't it?

Log Backup

Hi,
I read in a book that when a database becomes suspected, the first thing to
do is backing up the log file. I tried to simulate suspecting but I could
not backup the log file( I deleted a NDF file):
Server: Msg 945, Level 14, State 2, Line 3
Database 'Northwind' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
What's the solution if I want to keep my data in such situations?
Thanks in advance,
Leila
Have a look at this:
http://support.microsoft.com/kb/253817/EN-US/
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I read in a book that when a database becomes suspected, the first thing
> to
> do is backing up the log file. I tried to simulate suspecting but I could
> not backup the log file( I deleted a NDF file):
> --
> Server: Msg 945, Level 14, State 2, Line 3
> Database 'Northwind' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> Server: Msg 3013, Level 16, State 1, Line 3
> BACKUP LOG is terminating abnormally.
> --
> What's the solution if I want to keep my data in such situations?
> Thanks in advance,
> Leila
>
|||Thanks Andrew,
But I cannot rename the log file of suspected database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMta6q$tFHA.3628@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Have a look at this:
> http://support.microsoft.com/kb/253817/EN-US/
>
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
could
>

Log Backup

Hi,
I read in a book that when a database becomes suspected, the first thing to
do is backing up the log file. I tried to simulate suspecting but I could
not backup the log file( I deleted a NDF file):
--
Server: Msg 945, Level 14, State 2, Line 3
Database 'Northwind' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
--
What's the solution if I want to keep my data in such situations?
Thanks in advance,
LeilaHave a look at this:
http://support.microsoft.com/kb/253817/EN-US/
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I read in a book that when a database becomes suspected, the first thing
> to
> do is backing up the log file. I tried to simulate suspecting but I could
> not backup the log file( I deleted a NDF file):
> --
> Server: Msg 945, Level 14, State 2, Line 3
> Database 'Northwind' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> Server: Msg 3013, Level 16, State 1, Line 3
> BACKUP LOG is terminating abnormally.
> --
> What's the solution if I want to keep my data in such situations?
> Thanks in advance,
> Leila
>|||Thanks Andrew,
But I cannot rename the log file of suspected database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMta6q$tFHA.3628@.TK2MSFTNGP14.phx.gbl...
> Have a look at this:
> http://support.microsoft.com/kb/253817/EN-US/
>
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I read in a book that when a database becomes suspected, the first thing
> > to
> > do is backing up the log file. I tried to simulate suspecting but I
could
> > not backup the log file( I deleted a NDF file):
> > --
> > Server: Msg 945, Level 14, State 2, Line 3
> > Database 'Northwind' cannot be opened due to inaccessible files or
> > insufficient memory or disk space. See the SQL Server errorlog for
> > details.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > BACKUP LOG is terminating abnormally.
> > --
> > What's the solution if I want to keep my data in such situations?
> > Thanks in advance,
> > Leila
> >
> >
>

Log Backup

Hi,
I read in a book that when a database becomes suspected, the first thing to
do is backing up the log file. I tried to simulate suspecting but I could
not backup the log file( I deleted a NDF file):
--
Server: Msg 945, Level 14, State 2, Line 3
Database 'Northwind' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
--
What's the solution if I want to keep my data in such situations?
Thanks in advance,
LeilaHave a look at this:
http://support.microsoft.com/kb/253817/EN-US/
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I read in a book that when a database becomes suspected, the first thing
> to
> do is backing up the log file. I tried to simulate suspecting but I could
> not backup the log file( I deleted a NDF file):
> --
> Server: Msg 945, Level 14, State 2, Line 3
> Database 'Northwind' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> Server: Msg 3013, Level 16, State 1, Line 3
> BACKUP LOG is terminating abnormally.
> --
> What's the solution if I want to keep my data in such situations?
> Thanks in advance,
> Leila
>|||Thanks Andrew,
But I cannot rename the log file of suspected database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMta6q$tFHA.3628@.TK2MSFTNGP14.phx.gbl...
> Have a look at this:
> http://support.microsoft.com/kb/253817/EN-US/
>
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
could[vbcol=seagreen]
>