Log Files!?!?

Hi,

I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.

Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.

How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?

Thanks,

Skip.I'm not aware of a db that is operating normally without a log.

first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.

But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.

You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.

When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.|||Set the Recovery model of database to "Simple" :)|||Alright, now that's nice!

Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.

Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?

Thanks again,

Skip.|||In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.

log files

hi

when size of my lof file become full than it shows the error....so for this i manully delete the content of my log file...than it does not show the error....can anybody tell me any better way to avoid manual deletion...

thanx a lot

Right click the database go to Tasks->Shrink -> Files, select the log option and give a very small size.

after that run this command,

BACKUP LOG <db name> with TRUNCATE_ONLY

your transactional log size will be reduced in this manner.

|||hi thanx for reply....u mean i have to do what u said each time ...here also we are doing manual deletion....any automatic way to do this ?|||You can by developing a SSIS package with a Shrink Database task, call it using a SQL Server agent job and then schedule it as your need be.|||after right click it is showing shrink option disabled.why?|||The options I had said is for SSMS 2005. What are you using ?|||sqlserver 2000|||

Check if this helps....

http://support.microsoft.com/default.aspx/kb/272318

|||

Frequently backup you database and log.This is the way you can keep the log from growing.

LOG files

I moved all my data files from mssql2000 to mssql2005.
problem is that my log files are GB's bigger than the MDF file.
Can I delete the LOG files? IF not, can I reduced the size?
How?
Thanks,
Raul Rego
NJPIESDo you mean transaction log files (typically .ldf) or something else? If you mean transaction log
files, then do not delete them (unless you are prepared to restore the databases from backups). You
can shrink the file size using DBCC SHRINKFILE.
Bu the fact that these are so large is an indication that you or your dba haven't thought out a
suitable backup plan for your database and set the recovery model accordingly.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Raul" <rrego@.njpies.org> wrote in message
news:26041173-37E9-44E0-AA79-CFDFC093EC5B@.microsoft.com...
>I moved all my data files from mssql2000 to mssql2005.
> problem is that my log files are GB's bigger than the MDF file.
> Can I delete the LOG files? IF not, can I reduced the size?
> How?
> Thanks,
> Raul Rego
> NJPIES

LOG files

I have an SQL server with data files which have name_LOG.ldf files much much
bigger than the data files themselves.
How can I delete them or crunch them?
If I just delete them it appears that the data file can no loger be read.
Thanks,
Raul Rego
NJPIES
rrego.njpies.orgHi,
Dont delete, just shrink the file.
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Raul Rego" <rrego@.njpies.org> wrote in message
news:eDV$K5hTEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
> How can I delete them or crunch them?
> If I just delete them it appears that the data file can no loger be read.
> Thanks,
> Raul Rego
> NJPIES
> rrego.njpies.org
>|||> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
Do you have any concept of recovery model? Do you ever back up your
database or transaction logs?
> If I just delete them it appears that the data file can no loger be read.
Yep, the log file is a pretty essential component of a SQL Server database.
http://www.aspfaq.com/2471
If you have no interest in data integrity or backups, you can set the
recovery model to simple, back up the log with truncate_only, then shrink
the file.
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
USE database_name
GO
DBCC SHRINKFILE(database_name_log, 1)
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)

LOG files

I have an SQL server with data files which have name_LOG.ldf files much much
bigger than the data files themselves.
How can I delete them or crunch them?
If I just delete them it appears that the data file can no loger be read.
Thanks,
Raul Rego
NJPIES
rrego.njpies.orgHi,
Dont delete, just shrink the file.
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Raul Rego" <rrego@.njpies.org> wrote in message
news:eDV$K5hTEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
> How can I delete them or crunch them?
> If I just delete them it appears that the data file can no loger be read.
> Thanks,
> Raul Rego
> NJPIES
> rrego.njpies.org
>|||> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
Do you have any concept of recovery model? Do you ever back up your
database or transaction logs?

> If I just delete them it appears that the data file can no loger be read.
Yep, the log file is a pretty essential component of a SQL Server database.
http://www.aspfaq.com/2471
If you have no interest in data integrity or backups, you can set the
recovery model to simple, back up the log with truncate_only, then shrink
the file.
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
USE database_name
GO
DBCC SHRINKFILE(database_name_log, 1)
GO
http://www.aspfaq.com/
(Reverse address to reply.)sql

LOG files

I have an SQL server with data files which have name_LOG.ldf files much much
bigger than the data files themselves.
How can I delete them or crunch them?
If I just delete them it appears that the data file can no loger be read.
Thanks,
Raul Rego
NJPIES
rrego.njpies.org
Hi,
Dont delete, just shrink the file.
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Thanks
Hari
MCDBA
"Raul Rego" <rrego@.njpies.org> wrote in message
news:eDV$K5hTEHA.3480@.TK2MSFTNGP11.phx.gbl...
> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
> How can I delete them or crunch them?
> If I just delete them it appears that the data file can no loger be read.
> Thanks,
> Raul Rego
> NJPIES
> rrego.njpies.org
>
|||> I have an SQL server with data files which have name_LOG.ldf files much
much
> bigger than the data files themselves.
Do you have any concept of recovery model? Do you ever back up your
database or transaction logs?

> If I just delete them it appears that the data file can no loger be read.
Yep, the log file is a pretty essential component of a SQL Server database.
http://www.aspfaq.com/2471
If you have no interest in data integrity or backups, you can set the
recovery model to simple, back up the log with truncate_only, then shrink
the file.
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
USE database_name
GO
DBCC SHRINKFILE(database_name_log, 1)
GO
http://www.aspfaq.com/
(Reverse address to reply.)

LOG files

I moved all my data files from mssql2000 to mssql2005.
problem is that my log files are GB's bigger than the MDF file.
Can I delete the LOG files? IF not, can I reduced the size?
How?
Thanks,
Raul Rego
NJPIES
Do you mean transaction log files (typically .ldf) or something else? If you mean transaction log
files, then do not delete them (unless you are prepared to restore the databases from backups). You
can shrink the file size using DBCC SHRINKFILE.
Bu the fact that these are so large is an indication that you or your dba haven't thought out a
suitable backup plan for your database and set the recovery model accordingly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Raul" <rrego@.njpies.org> wrote in message
news:26041173-37E9-44E0-AA79-CFDFC093EC5B@.microsoft.com...
>I moved all my data files from mssql2000 to mssql2005.
> problem is that my log files are GB's bigger than the MDF file.
> Can I delete the LOG files? IF not, can I reduced the size?
> How?
> Thanks,
> Raul Rego
> NJPIES
sql

log file size

Can anyone tell me in detail, what exactly log file stores. Even i shrik the
database log file does not srhink. Even after i backup,delete n restore db,
log file remain the same. How can i squueze the log file size?
And How large log file effetc the performance of db, quereis etc ?All modifications in a database are reflected in the transaction log file. S
ee
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for considerations and
how to shrink file
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vikram" <aa@.aa> wrote in message news:uB9vy2afGHA.324@.TK2MSFTNGP02.phx.gbl...n">
> Can anyone tell me in detail, what exactly log file stores. Even i shrik t
he
> database log file does not srhink. Even after i backup,delete n restore db
,
> log file remain the same. How can i squueze the log file size?
> And How large log file effetc the performance of db, quereis etc ?
>

log file size

Can anyone tell me in detail, what exactly log file stores. Even i shrik the
database log file does not srhink. Even after i backup,delete n restore db,
log file remain the same. How can i squueze the log file size?
And How large log file effetc the performance of db, quereis etc ?All modifications in a database are reflected in the transaction log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for considerations and how to shrink file
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vikram" <aa@.aa> wrote in message news:uB9vy2afGHA.324@.TK2MSFTNGP02.phx.gbl...
> Can anyone tell me in detail, what exactly log file stores. Even i shrik the
> database log file does not srhink. Even after i backup,delete n restore db,
> log file remain the same. How can i squueze the log file size?
> And How large log file effetc the performance of db, quereis etc ?
>

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 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
>
>|||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...
> 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...
>> > 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
>>|||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 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 From database Sql Server 2000

Hi,
I would like to know what can I do to with a log file that is growing very
fast.
Every 3 days I have to detach the database,delete the log file and attach
the database with out log file. During the process a new log file of 504 kb
is created.
Each day the log file is growing by 1.5 gig which is to much
Actualy I have configure for the log file Automatic Grow file by Percent 10
Also I have restrict file grow to 5000 MB which take about 4 days to reach
Is there a way that I can configure sqlServer that he can manage de log file?
You could set the recovery mode to simple which will truncate the log file
each checkpoint. However I'd double-check that this is the requirement for
this database. I use Simple recovery mode only in certain development
environments where the nightly backup is sufficient. If you need a
point-in-time recovery ability, and can't afford to lose all the work since
the last full backup, I'd recommend setting Full recovery mode and backing
up the log regularly (which will truncate the log).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You are probably using full recovery model and not dumping (or backing up
the transaction log). I would dump it every 5 to 20 minutes or so. The
maintenance wizard will guide you through how to do this. you might also
want to delete files more than 2 days old or depending on your
recoverability requirements.
Another option is to dump to the same file each time if you don't care about
recoverability.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GC" <GC@.discussions.microsoft.com> wrote in message
news:B33A0F0F-BA99-454D-8004-DF4181E56C93@.microsoft.com...
> Hi,
> I would like to know what can I do to with a log file that is growing very
> fast.
> Every 3 days I have to detach the database,delete the log file and attach
> the database with out log file. During the process a new log file of 504
> kb
> is created.
> Each day the log file is growing by 1.5 gig which is to much
> Actualy I have configure for the log file Automatic Grow file by Percent
> 10
> Also I have restrict file grow to 5000 MB which take about 4 days to
> reach
> Is there a way that I can configure sqlServer that he can manage de log
> file?
>
>

log file

How can I delete the transaction logs? Every time I try it errors saying the
file isn't empty and I don't know how to empty it! I've tried everything and
the log file is over 200 mb so far!
Thanks,
ToddHave you tried BACKUP LOG your_db_name WITH TRUNCATE_ONLY?
How did you try to 'delete' the xaction log?
The other thing you might think about is put the DB
recovery option to SIMPLE (equiv to trunc. log on chkpt).
That way, you won't have to manual delete the xaction log.
hth
-a
>--Original Message--
>How can I delete the transaction logs? Every time I try
it errors saying the
>file isn't empty and I don't know how to empty it! I've
tried everything and
>the log file is over 200 mb so far!
>Thanks,
>Todd
>
>.
>|||If you have multiple log files for this database and you want to 'delete'
(i.e. remove one of them), you can try DBCC SHRINKFILE with the EMPTYFILE
option. This do NOT work with the primary transaction log file, though.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Todd Ellington" <todd@.vtserve.com> wrote in message
news:u7zuupjiDHA.2824@.tk2msftngp13.phx.gbl...
> How can I delete the transaction logs? Every time I try it errors saying
the
> file isn't empty and I don't know how to empty it! I've tried everything
and
> the log file is over 200 mb so far!
> Thanks,
> Todd
>

Log file

My transaction log file (.LDF) has reached 53GB on
a (now full) 80GB hard drive.
Is it safe to delete the log file after a check point
is reached (upon DB shut down, for instance?) And why
does the log file not automatically shrink after check-
points?
Thanks,
Andrew> Is it safe to delete the log file after a check point
> is reached
You can not delete the log file, you can run a backup log dbname with
truncate_only if you do not require point in time recovery, of course, if
you don't require point in time recovery you should set the recovery mode of
the database to simple.
>>And why
> does the log file not automatically shrink after check-
> points?
There are two answers here, one, the data stays in the log even after
checkpoint so you can back it up and have the ability to transactionally
recover. Shrinking is another subject, you can not shrink the actual log
file footprint until it is empty, done by either backing up or truncating
it. Also, if you are doing regular backups of the log and your log file is
growing, it doesn't make sense to keep shrinking it only for it to cause you
a performance hit when it auto-grows again.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5201c3c57f$8bfb3340$a301280a@.phx.gbl...
> My transaction log file (.LDF) has reached 53GB on
> a (now full) 80GB hard drive.
> Is it safe to delete the log file after a check point
> is reached (upon DB shut down, for instance?) And why
> does the log file not automatically shrink after check-
> points?
> Thanks,
> Andrew
>

Locks on a table with nonclustered index

If i have a query such as
delete from table1
where col1 = 200
Say i had a non clustered index on col1 and thats the only index that i
have...
1) Can the execution plan include a non-clustered index scan if the
selectivity is low ?
2) If it does use a nonclustered index scan and maybe start of with row
locks and then escalates to table lock, how do you find out if the lock is
on the index pages or on the data pages or on both although in sp_lock it
would show an exclusive table lock ?
3) Even it it just row locks, what info can we get from the resource column
in sp_lock to tell us whether a row in the data page or index page is being
locked.. Would it just be data pages or would it be holding multiple row
locks ..some to delete its rows in data pages and some to delete the rows in
its index pages1. I doubt it would do an index scan in this case, but if the selectivity
were high enough, it could do an index seek.
2. I'm not sure exactly what you're asking. If there is a table lock X,
other processes cannot access the indexes on that table, so whether the
index pages are locked is irrelevant.
3. The resource column doesn't directly tell us, but if you have a page
number, you can use dbcc page to tell what kind of page it is. Also, you can
use the indid column, and if it is >1, then the page is from a nonclustered
index. You can have key locks (which are row locks in an index) on both
index keys and rows/keys in the table itself. In fact, if the table has nc
indexes, you'll usually see both.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"sql" <sql@.hotmail.com> wrote in message
news:u47QCA9hDHA.460@.TK2MSFTNGP12.phx.gbl...
> If i have a query such as
> delete from table1
> where col1 = 200
> Say i had a non clustered index on col1 and thats the only index that i
> have...
> 1) Can the execution plan include a non-clustered index scan if the
> selectivity is low ?
> 2) If it does use a nonclustered index scan and maybe start of with row
> locks and then escalates to table lock, how do you find out if the lock is
> on the index pages or on the data pages or on both although in sp_lock it
> would show an exclusive table lock ?
> 3) Even it it just row locks, what info can we get from the resource
column
> in sp_lock to tell us whether a row in the data page or index page is
being
> locked.. Would it just be data pages or would it be holding multiple row
> locks ..some to delete its rows in data pages and some to delete the rows
in
> its index pages
>
>