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