Hi,
I am using SQL2000 Standard.
The log file running up tremendously after the system life in production.
My ratio of size is 200mb for Data files and 650mb for Log file, in a month.
Apparently I wish to know:
1) What is cause of the Log file turn big? I am worry it was cause by error
(any error eg, ADO or SQL Transaction Log).
2) It is possible to read / study the log file content?
3) How to Backup or Truncate the log file?
4) What is the Standard or Typical settting in SQL Server Properties and
Configuration in regards to Log File maintenance? I am using all default
setting now.
Thanks.> 1) What is cause of the Log file turn big? I am worry it was cause by
error
> (any error eg, ADO or SQL Transaction Log).
Full or Bulk Logged recovery model without a backup plan, I guess in your
case.
> 2) It is possible to read / study the log file content?
Log Explorer - www.lumigent.com.
> 3) How to Backup or Truncate the log file?
With Backup Log T-SQL statement. Check the syntax in Books OnLine.
> 4) What is the Standard or Typical settting in SQL Server Properties and
> Configuration in regards to Log File maintenance? I am using all default
> setting now.
Default settings differ for server and desktop editions for SQL Server, so
you didn't give us much info. Anyway, for a server in production, you should
use Full recovery model with appropriate backup plan.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
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
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
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
Monday, February 20, 2012
Locks in SQL2000
How to lock a Row in SQL2000 so that nobody can select that row.
I applied ROWLOCK, but i am not finding the way.
My query is"SELECT * FROM tablename WITH (ROWLOCK)"
Is this the correct way to write locks.
I would be thankful if u help me
Hi,
Maybe the following link is helpful to you.
Row-Level Locking Using ADO & SQL Server http://support.microsoft.com/kb/252317
Thanks.
|||
Hi all,
I am using SQLServer 2000 & .Net 2003 (1.1 framework)
I had a database calledDATA and the fields ID, Name, Remarks, etc...
I need to give access all the data to my employees in their browser machines.
Number of employees may be from 50 to 150.
If a particular record is fetched by 2 users simultaneously one person is getting an error. For this i need to put locks in the database.
If a particular record is fetched by 1 user, then immediately the another employee who is accessing it, should get another record/data.
Being keeping in mind, how can i solve this scnario?
Any body please help me...
Regards,
Jai Shankar
Subscribe to:
Posts (Atom)