Clean Log files

Hi All,
I have problem with my logs files, they are going so much especially when I
import data.
The best way that I found to clean the log files is:
1. Detach database
2. Delete the log file from the data folder
3. Attach database
Obviously I can't do this during the day until nobody is using the database.
I have 16 databases.
Can I do this using an script?
Any help will be appreciate... tks in advance.
JFB> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
That may well be the best way to corrupt your data. The best way to
control log size is to choose the right recovery model, allocate a
sensible size to start with and then take regular Log and Database
backups as appropriate. If you are still in development then perhaps
database backups are sufficienct in which case choose the Simple
Recovery model.
David Portas
SQL Server MVP
--
http://support.microsoft.com/?kbid=873235|||Much of http://www.aspfaq.com/2446 applies to all databases, not just
tempdb.
Also see http://www.aspfaq.com/2471
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||By "cleaning" do you mean truncating the log or reducing the physical size?
If so, first check the recovery model set for your database.
If logs are not required to be maintained, then you can simply issue a
BACKUP LOG statement with NO_LOG option. To reduce the physical file size,
use DBCC SHRINKFILE. Details of these commands with examples can be found in
SQL Server Books Online.
Anith|||If you are not using BCP.EXE or the BULK INSERT statement to import your
data, then you should consider this. In SQL Server Books Online, read the
article titled "Logged and Minimally Logged Bulk Copy Operations".
Basically, the requirements for non-logged bulk copy operations are:
- The recovery model is simple or bulk-logged.
- The target table is not being replicated.
- The target table does not have any triggers.
- The target table has either 0 rows or no indexes.
- The TABLOCK hint is specified.
If this is an OLTP database, you can use the ALTER DATABASE.. statment to
switch between different recovery models as needed.
You may also want to consider dropping indexes (especially the clustered
index) before the import and re-creating them again afterward. This will
also have the affect of preventing index fragmentation and increasing the
load performance.
If this database is a 24x7 transactional system with many users, then you
may want to re-consider the process by which you import new data and avoid
massive import operations all together.
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||Hi,
Looks like you do not want the Transaction log backup. There are 2 ways to
clear the Log file online.
1. Take the transaction log backup and then shrink the LDF file.
Script:
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
2. Truncate the Transaction log and Shrink.
Script:
Backup log <dbname> with Truncate_only
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
Thanks
Hari
SQL Server MVP
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>