Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>|||Apart from long running transactions in the application or badly configured
replication the most common cause of T-log becoming excessive is the
optimization job. This will make copies of existing data while rebuilding
and if you have particularly large tables this will cause a long running
transaction.
Providing the used part of the log stays reasonable small (say <100MB)
during your busy times you should not suffer performance degredation.
Regards,
Tim G-J
Professional Database Solutions Ltd.
www.pdbsolutions.co.uk
"Marco Pais" wrote:

> Hello Aaron,
> First of all, thanks for the answer.
> The database is backuped once a day, every days of week.
> Can the log file growing be caused by something else? Can it affect
> performance?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>
>

Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais
> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A
|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>
|||Apart from long running transactions in the application or badly configured
replication the most common cause of T-log becoming excessive is the
optimization job. This will make copies of existing data while rebuilding
and if you have particularly large tables this will cause a long running
transaction.
Providing the used part of the log stays reasonable small (say <100MB)
during your busy times you should not suffer performance degredation.
Regards,
Tim G-J
Professional Database Solutions Ltd.
www.pdbsolutions.co.uk
"Marco Pais" wrote:

> Hello Aaron,
> First of all, thanks for the answer.
> The database is backuped once a day, every days of week.
> Can the log file growing be caused by something else? Can it affect
> performance?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>
>

Log File growing urgent Please

Hei,
Log file is growing very fast (Log file size is now more then 3GB and data
file size is not more then 1GB) and i want to shrink log file using this
command
use modbt
go
DBCC SHRINKFILE (modbt, 20)
go
OR
DBCC SHRINKFILE (modbt_Log, 20)
but getting error
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'modbt_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I run sp_helpfile and get result
Name fileid Filename Filegroup Size Maxzie
growth usege
1 Axc_Data 1 D:\....\Modbt.mdf Primary 850688KB unlimited 10%
data
2 Axc_log 2 E:\....\Modbt.mdf Null 3575936KB unlimited
10% log
I run DBCC LOGINFO('modbt') and get STATUS '2' for 40 line but no one using
database that time. STATUS '0' for more then 300 line.
When i run DBCC SHRINKFILE (Axc_log, 20) then got
DbId Field CurrentSize MinimumSize Used Pages EstimatedPages
1: 7 2 446992 63 446992 56
Can any one help me what will be problem? I have backup plan full but i did
not check and it was not working so this happened with my database. Now it is
working please help me.
Nina Paul
Paul
BACKUP LOG file and then DBCC SHRINKFILE command
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9FBC8F5E-38B8-4115-8760-E7A334E62A06@.microsoft.com...
> Hei,
> Log file is growing very fast (Log file size is now more then 3GB and data
> file size is not more then 1GB) and i want to shrink log file using this
> command
> use modbt
> go
> DBCC SHRINKFILE (modbt, 20)
> go
> OR
> DBCC SHRINKFILE (modbt_Log, 20)
> but getting error
> Server: Msg 8985, Level 16, State 1, Line 1
> Could not locate file 'modbt_log' in sysfiles.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I run sp_helpfile and get result
> Name fileid Filename Filegroup Size Maxzie
> growth usege
> 1 Axc_Data 1 D:\....\Modbt.mdf Primary 850688KB unlimited
> 10%
> data
> 2 Axc_log 2 E:\....\Modbt.mdf Null 3575936KB unlimited
> 10% log
> I run DBCC LOGINFO('modbt') and get STATUS '2' for 40 line but no one
> using
> database that time. STATUS '0' for more then 300 line.
> When i run DBCC SHRINKFILE (Axc_log, 20) then got
> DbId Field CurrentSize MinimumSize Used Pages
> EstimatedPages
> 1: 7 2 446992 63 446992
> 56
>
> Can any one help me what will be problem? I have backup plan full but i
> did
> not check and it was not working so this happened with my database. Now it
> is
> working please help me.
> Nina Paul

Log File growing urgent Please

Hei,
Log file is growing very fast (Log file size is now more then 3GB and data
file size is not more then 1GB) and i want to shrink log file using this
command
use modbt
go
DBCC SHRINKFILE (modbt, 20)
go
OR
DBCC SHRINKFILE (modbt_Log, 20)
but getting error
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'modbt_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I run sp_helpfile and get result
Name fileid Filename Filegroup Size Maxzie
growth usege
1 Axc_Data 1 D:\....\Modbt.mdf Primary 850688KB unlimited 10%
data
2 Axc_log 2 E:\....\Modbt.mdf Null 3575936KB unlimited
10% log
I run DBCC LOGINFO('modbt') and get STATUS '2' for 40 line but no one using
database that time. STATUS '0' for more then 300 line.
When i run DBCC SHRINKFILE (Axc_log, 20) then got
DbId Field CurrentSize MinimumSize Used Pages EstimatedPages
1: 7 2 446992 63 446992 56
Can any one help me what will be problem? I have backup plan full but i did
not check and it was not working so this happened with my database. Now it i
s
working please help me.
Nina PaulPaul
BACKUP LOG file and then DBCC SHRINKFILE command
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9FBC8F5E-38B8-4115-8760-E7A334E62A06@.microsoft.com...
> Hei,
> Log file is growing very fast (Log file size is now more then 3GB and data
> file size is not more then 1GB) and i want to shrink log file using this
> command
> use modbt
> go
> DBCC SHRINKFILE (modbt, 20)
> go
> OR
> DBCC SHRINKFILE (modbt_Log, 20)
> but getting error
> Server: Msg 8985, Level 16, State 1, Line 1
> Could not locate file 'modbt_log' in sysfiles.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I run sp_helpfile and get result
> Name fileid Filename Filegroup Size Maxzie
> growth usege
> 1 Axc_Data 1 D:\....\Modbt.mdf Primary 850688KB unlimited
> 10%
> data
> 2 Axc_log 2 E:\....\Modbt.mdf Null 3575936KB unlimited
> 10% log
> I run DBCC LOGINFO('modbt') and get STATUS '2' for 40 line but no one
> using
> database that time. STATUS '0' for more then 300 line.
> When i run DBCC SHRINKFILE (Axc_log, 20) then got
> DbId Field CurrentSize MinimumSize Used Pages
> EstimatedPages
> 1: 7 2 446992 63 446992
> 56
>
> Can any one help me what will be problem? I have backup plan full but i
> did
> not check and it was not working so this happened with my database. Now it
> is
> working please help me.
> Nina Paul

Log and Data on the same RAID?

Maybe my question is too simple because I dont know much of RAID,
but I try:
What is probably "better": One RAID of 4 Disks, where datafile and logfile a
re
placed on, or Two RAIDs of each 2 Disks, where one RAID contains the datafil
e
and the other RAID contains the logfile?
JoachimAs all questions related to computers, the correct response is 'It depends'.
A mirror RAID drive would be good for the log file, a mirrored drive for the
datafile is less than optimal. If you only had 5 drives, you could mirror
the drive for the log and create a RAID 5 for the datafiles.
If have to work with only 4 drives, additional informational needs would
include: type of database (OLTP/OLAP), Number of users, amount of traffic,
concurrency of traffic, average size of queries, amount of INSERT/UPDATE
traffic, etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||It depends on a lot of things but the biggest of which is how many reads to
writes you do. If you have mostly read only operations you may get better
read results by having a 4 disk Raid 10. If you have a lot of writes you may
need to separate the log file from the data files. If you only have 4 disks
you have little choice but to use two Raid 1's.
Andrew J. Kelly SQL MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||I would add to that, IF the RAID array controller is suitable for
write caching (battery backed memory on the controller and other wise
certified for that sort of thing), then the write penalty for RAID5 is
somewhat minimized.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Aug 2006 10:25:44 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>As all questions related to computers, the correct response is 'It depends'
.
>A mirror RAID drive would be good for the log file, a mirrored drive for th
e
>datafile is less than optimal. If you only had 5 drives, you could mirror
>the drive for the log and create a RAID 5 for the datafiles.
>If have to work with only 4 drives, additional informational needs would
>include: type of database (OLTP/OLAP), Number of users, amount of traffic,
>concurrency of traffic, average size of queries, amount of INSERT/UPDATE
>traffic, etc.

Log and Data on the same RAID?

Maybe my question is too simple because I dont know much of RAID,
but I try:
What is probably "better": One RAID of 4 Disks, where datafile and logfile are
placed on, or Two RAIDs of each 2 Disks, where one RAID contains the datafile
and the other RAID contains the logfile?
JoachimAs all questions related to computers, the correct response is 'It depends'.
A mirror RAID drive would be good for the log file, a mirrored drive for the
datafile is less than optimal. If you only had 5 drives, you could mirror
the drive for the log and create a RAID 5 for the datafiles.
If have to work with only 4 drives, additional informational needs would
include: type of database (OLTP/OLAP), Number of users, amount of traffic,
concurrency of traffic, average size of queries, amount of INSERT/UPDATE
traffic, etc.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||It depends on a lot of things but the biggest of which is how many reads to
writes you do. If you have mostly read only operations you may get better
read results by having a 4 disk Raid 10. If you have a lot of writes you may
need to separate the log file from the data files. If you only have 4 disks
you have little choice but to use two Raid 1's.
--
Andrew J. Kelly SQL MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||I would add to that, IF the RAID array controller is suitable for
write caching (battery backed memory on the controller and other wise
certified for that sort of thing), then the write penalty for RAID5 is
somewhat minimized.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Aug 2006 10:25:44 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>As all questions related to computers, the correct response is 'It depends'.
>A mirror RAID drive would be good for the log file, a mirrored drive for the
>datafile is less than optimal. If you only had 5 drives, you could mirror
>the drive for the log and create a RAID 5 for the datafiles.
>If have to work with only 4 drives, additional informational needs would
>include: type of database (OLTP/OLAP), Number of users, amount of traffic,
>concurrency of traffic, average size of queries, amount of INSERT/UPDATE
>traffic, etc.