Log File Too Big.

Hi !
I have a small Sql server database (about 50 Mb) for a pubblic web
application.
But the log's file is is about 4 Gb and it increase very fast.......
It's normal ?
I have to set something ?
The web site has a very little amount of traffic......so there are a
little number of Sql server query.....
Thanks for your help !
Matteo MazzoniMatteo,
Seems like you are using Full recovery model without backing up the log. You
should backup log regularly as well. f you do not eed log backups, then
consider switching to SImple recovery model. Do please read more about
recovery model in Books OnLine. Meanwhile, this article shows ow can you
shrink the log file:
http://support.microsoft.com/defaul...b;EN-US;272318.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matteo Mazzoni" <Matteo Mazzoni@.discussions.microsoft.com> wrote in message
news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
> Hi !
> I have a small Sql server database (about 50 Mb) for a pubblic web
> application.
> But the log's file is is about 4 Gb and it increase very fast.......
> It's normal ?
> I have to set something ?
> The web site has a very little amount of traffic......so there are a
> little number of Sql server query.....
> Thanks for your help !
> Matteo Mazzoni|||Another Idea may be to temporarly changing the recovery
model to simple then performing a shrink database, that
will get your disk space back.
After that you can back up your new streamline DB with the
Full Recovery model, and not use 4gb per backup.
Peter

>--Original Message--
>Matteo,
>Seems like you are using Full recovery model without
backing up the log. You
>should backup log regularly as well. f you do not eed log
backups, then
>consider switching to SImple recovery model. Do please
read more about
>recovery model in Books OnLine. Meanwhile, this article
shows ow can you
>shrink the log file:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Matteo Mazzoni" <Matteo
Mazzoni@.discussions.microsoft.com> wrote in message
>news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
pubblic web[vbcol=seagreen]
very fast.......[vbcol=seagreen]
traffic......so there are a[vbcol=seagreen]
>
>.
>|||How i can changing the recovery
model to simple ?
Thanks again for your help.
"Peter The Spate" wrote:

> Another Idea may be to temporarly changing the recovery
> model to simple then performing a shrink database, that
> will get your disk space back.
> After that you can back up your new streamline DB with the
> Full Recovery model, and not use 4gb per backup.
> Peter
>
> backing up the log. You
> backups, then
> read more about
> shows ow can you
> US;272318.
> Mazzoni@.discussions.microsoft.com> wrote in message
> pubblic web
> very fast.......
> traffic......so there are a
>|||morello
ALTER DATABASE ... SET RECOVERY SIMPLE
"morello" <morello@.discussions.microsoft.com> wrote in message
news:2E0701F1-DC46-41CE-87C7-93D1FB3BB3DA@.microsoft.com...[vbcol=seagreen]
> How i can changing the recovery
> model to simple ?
> Thanks again for your help.
> "Peter The Spate" wrote:
>

Log File Too Big.

Hi !
I have a small Sql server database (about 50 Mb) for a pubblic web
application.
But the log's file is is about 4 Gb and it increase very fast.......
It's normal ?
I have to set something ?
The web site has a very little amount of traffic......so there are a
little number of Sql server query.....
Thanks for your help !
Matteo Mazzoni
Matteo,
Seems like you are using Full recovery model without backing up the log. You
should backup log regularly as well. f you do not eed log backups, then
consider switching to SImple recovery model. Do please read more about
recovery model in Books OnLine. Meanwhile, this article shows ow can you
shrink the log file:
http://support.microsoft.com/default...;EN-US;272318.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matteo Mazzoni" <Matteo Mazzoni@.discussions.microsoft.com> wrote in message
news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
> Hi !
> I have a small Sql server database (about 50 Mb) for a pubblic web
> application.
> But the log's file is is about 4 Gb and it increase very fast.......
> It's normal ?
> I have to set something ?
> The web site has a very little amount of traffic......so there are a
> little number of Sql server query.....
> Thanks for your help !
> Matteo Mazzoni
|||Another Idea may be to temporarly changing the recovery
model to simple then performing a shrink database, that
will get your disk space back.
After that you can back up your new streamline DB with the
Full Recovery model, and not use 4gb per backup.
Peter

>--Original Message--
>Matteo,
>Seems like you are using Full recovery model without
backing up the log. You
>should backup log regularly as well. f you do not eed log
backups, then
>consider switching to SImple recovery model. Do please
read more about
>recovery model in Books OnLine. Meanwhile, this article
shows ow can you
>shrink the log file:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Matteo Mazzoni" <Matteo
Mazzoni@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
pubblic web[vbcol=seagreen]
very fast.......[vbcol=seagreen]
traffic......so there are a
>
>.
>
|||How i can changing the recovery
model to simple ?
Thanks again for your help.
"Peter The Spate" wrote:

> Another Idea may be to temporarly changing the recovery
> model to simple then performing a shrink database, that
> will get your disk space back.
> After that you can back up your new streamline DB with the
> Full Recovery model, and not use 4gb per backup.
> Peter
>
> backing up the log. You
> backups, then
> read more about
> shows ow can you
> US;272318.
> Mazzoni@.discussions.microsoft.com> wrote in message
> pubblic web
> very fast.......
> traffic......so there are a
>
|||morello
ALTER DATABASE ... SET RECOVERY SIMPLE
"morello" <morello@.discussions.microsoft.com> wrote in message
news:2E0701F1-DC46-41CE-87C7-93D1FB3BB3DA@.microsoft.com...[vbcol=seagreen]
> How i can changing the recovery
> model to simple ?
> Thanks again for your help.
> "Peter The Spate" wrote:

Log File size increase

I have set up an alert for Percent log used and i get this message every now and then:

The SQL Server performance counter 'Percent Log Used' (instance 'TelehopBilling') of object 'SQLServer:Databases' is now above the threshold of 90.00 (the current value is 95.00).

My database settings are:

Transaction log files space allocated

file1 1 mb

file 2 3 mb

file 3 1201 mb

automatically grow file : not checked

auto shrink - off

shrinking of database thru auto task: twice a week.

Please guide what is the solution to this. Also suggest me a suitable database settings. This is a production database and the actual log file size is around 2 GB

Thanks,

Why you are using multiple files for Transaction log?

As you might not gain anything using this way, I would suggest to use only one file and set appropriate size to transaction log. At the same time do not waste the SQL resources by shrinking the log regularly. You need to consider the set of processess including day to day, scheduled jobs such as database maintenance tasks, any bulk insert jobs and during these processes it will have impact on size.

Also ensure to maintain the backup log schedule frequently to take care of virtual log size boundaries that will stop unprecedented growth during any process.

As of now your setup seems ok other than having multiple log files, also I don't understand what is the problem you have with this setup.

Log file is increeasing too much

Hi
i have a database and a proccess which delete everything from database and
then import some stuff from other databases.
how can i reduce to increase log file because sometimes import stops because
of it.
I use the following procedures :
backup log mydatabase with truncate_only
dbcc shrinkdatabase (mydatabase)
i was wondering if it's any possible way to put a maximum size for log file
and when it get to the maximum file size to override the existent logs and
not raise me the error that "The log file for database 'mydatabase' is full.
Back up the transaction log for the database to free up some log space."
thanks in advanceIf you are using the FULL recovery model, you would need to backup the log
before it can be reused. Otherwise, you might consider using the simple
recovery model.
Another possibility is that your import process is done within one
transaction, hence all the data is either committed or rolled back. If this
was the case (and you are using the simple recovery model), you might
consider breaking the import into smaller transactions.
If you are deleting everything initially, you might also consider using the
TRUNCATE TABLE option as the row deletion is not individually logged.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance|||Thanks
i think this was the problem. I use delete command instead of truncate. I
turned the recovery mode to simple and I put the option auto shrink on.
Thanks again.
"Peter Yeoh" wrote:

> If you are using the FULL recovery model, you would need to backup the log
> before it can be reused. Otherwise, you might consider using the simple
> recovery model.
> Another possibility is that your import process is done within one
> transaction, hence all the data is either committed or rolled back. If th
is
> was the case (and you are using the simple recovery model), you might
> consider breaking the import into smaller transactions.
> If you are deleting everything initially, you might also consider using th
e
> TRUNCATE TABLE option as the row deletion is not individually logged.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Psycho" <Psycho@.discussions.microsoft.com> wrote in message
> news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> because
> file
> full.
>
>|||You might want to check out my article regarding shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:AC3F1D5E-CA73-47D8-8EA0-D3FBF3315DAA@.microsoft.com...[vbcol=seagreen]
> Thanks
> i think this was the problem. I use delete command instead of truncate. I
> turned the recovery mode to simple and I put the option auto shrink on.
> Thanks again.
> "Peter Yeoh" wrote:
>|||Also Truncate table does NOT work on tables with FKs, so you'll have to
remove those first..
Another alternative is to delete a range of rows ie
set rowcount 10000
delete from mytable
while @.@.rowcount !=0
delete from mytable
set rowcount 0
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance

Log file is increeasing too much

Hi
i have a database and a proccess which delete everything from database and
then import some stuff from other databases.
how can i reduce to increase log file because sometimes import stops because
of it.
I use the following procedures :
backup log mydatabase with truncate_only
dbcc shrinkdatabase (mydatabase)
i was wondering if it's any possible way to put a maximum size for log file
and when it get to the maximum file size to override the existent logs and
not raise me the error that "The log file for database 'mydatabase' is full.
Back up the transaction log for the database to free up some log space."
thanks in advance
If you are using the FULL recovery model, you would need to backup the log
before it can be reused. Otherwise, you might consider using the simple
recovery model.
Another possibility is that your import process is done within one
transaction, hence all the data is either committed or rolled back. If this
was the case (and you are using the simple recovery model), you might
consider breaking the import into smaller transactions.
If you are deleting everything initially, you might also consider using the
TRUNCATE TABLE option as the row deletion is not individually logged.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance
|||Thanks
i think this was the problem. I use delete command instead of truncate. I
turned the recovery mode to simple and I put the option auto shrink on.
Thanks again.
"Peter Yeoh" wrote:

> If you are using the FULL recovery model, you would need to backup the log
> before it can be reused. Otherwise, you might consider using the simple
> recovery model.
> Another possibility is that your import process is done within one
> transaction, hence all the data is either committed or rolled back. If this
> was the case (and you are using the simple recovery model), you might
> consider breaking the import into smaller transactions.
> If you are deleting everything initially, you might also consider using the
> TRUNCATE TABLE option as the row deletion is not individually logged.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Psycho" <Psycho@.discussions.microsoft.com> wrote in message
> news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> because
> file
> full.
>
>
|||You might want to check out my article regarding shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:AC3F1D5E-CA73-47D8-8EA0-D3FBF3315DAA@.microsoft.com...[vbcol=seagreen]
> Thanks
> i think this was the problem. I use delete command instead of truncate. I
> turned the recovery mode to simple and I put the option auto shrink on.
> Thanks again.
> "Peter Yeoh" wrote:
|||Also Truncate table does NOT work on tables with FKs, so you'll have to
remove those first..
Another alternative is to delete a range of rows ie
set rowcount 10000
delete from mytable
while @.@.rowcount !=0
delete from mytable
set rowcount 0
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Psycho" <Psycho@.discussions.microsoft.com> wrote in message
news:26E3911D-CEA7-4389-8E26-1E76B3EEDF11@.microsoft.com...
> Hi
> i have a database and a proccess which delete everything from database and
> then import some stuff from other databases.
> how can i reduce to increase log file because sometimes import stops
because
> of it.
> I use the following procedures :
> backup log mydatabase with truncate_only
> dbcc shrinkdatabase (mydatabase)
> i was wondering if it's any possible way to put a maximum size for log
file
> and when it get to the maximum file size to override the existent logs and
> not raise me the error that "The log file for database 'mydatabase' is
full.
> Back up the transaction log for the database to free up some log space."
> thanks in advance

Log file increase very fast

I installed SQL server 2000 on one NT4.0 server and one Win2000 server. The
SQL server on NT4.0 is working fine. It's log file is always less than 5MB.
But on Win2000 I think there are some problems because the log file is
increasing very fast,now it is 25000MB.The two SQL server almost do same
work and configuration are same. Any help will be appreciated.Charms,
Have you checked the database settings on both servers . You may have one
server set to a higher recovery model.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Charms Zhou" <charmszhou@.hotmail.com> wrote in message
news:uWzADRraDHA.2580@.TK2MSFTNGP09.phx.gbl...
> I installed SQL server 2000 on one NT4.0 server and one Win2000 server.
The
> SQL server on NT4.0 is working fine. It's log file is always less than
5MB.
> But on Win2000 I think there are some problems because the log file is
> increasing very fast,now it is 25000MB.The two SQL server almost do same
> work and configuration are same. Any help will be appreciated.
>|||Also, perhaps you are doing index rebuilds on one machine but not the other?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Greg Obleshchuk" <greg@.ag-software.com> wrote in message
news:unhttPsaDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Charms,
> Have you checked the database settings on both servers . You may have one
> server set to a higher recovery model.
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
> program
> http://www.ag-software.com. Free programming tools
>
> "Charms Zhou" <charmszhou@.hotmail.com> wrote in message
> news:uWzADRraDHA.2580@.TK2MSFTNGP09.phx.gbl...
> > I installed SQL server 2000 on one NT4.0 server and one Win2000 server.
> The
> > SQL server on NT4.0 is working fine. It's log file is always less than
> 5MB.
> > But on Win2000 I think there are some problems because the log file is
> > increasing very fast,now it is 25000MB.The two SQL server almost do same
> > work and configuration are same. Any help will be appreciated.
> >
> >
>