Clearing Log Files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Clearing Log Files

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-18 : 01:18:51

Hi Friend ,I need to Clear The Log Files
on daily basis by scheduling ,can any one correct the below query
Txs in Advance
Declare @ctr int,@i int
Declare @dbname varchar(10)
set i=0
select @ctr=count(*) from sys.databases
while @i<=@ctr
begin
-->HoW to Call the Db name
@str='dump tran '+ @dbname + ' with no_log'
Exec (dbcc @str)
set @i=@i+1
end

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-18 : 04:08:07

Why do you want to truncate your transaction log daily? If you don't need point-in-time recovery, set the DB into simple recovery mode and SQL will discard old log records for you.

If you do need point-in-time recovery, then make sure that you have regular log backups running (SQL job or a maintenance plan)

Edit: Please don't cross post.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110865[/url]

--
Gail Shaw
SQL Server MVP

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-18 : 05:45:41

hi GilaMonster,
Already it is in simple recovery mode only.
Other thing i found is :
Under properties/file/logfile/initial size is 9189 MB (9 gb approx.)
if iam trying to change to 5000 mb.its is not getting reflected..

can u give the solution???

quote:
Originally posted by GilaMonster

Why do you want to truncate your transaction log daily? If you don't need point-in-time recovery, set the DB into simple recovery mode and SQL will discard old log records for you.

If you do need point-in-time recovery, then make sure that you have regular log backups running (SQL job or a maintenance plan)

Edit: Please don't cross post.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110865[/url]

--
Gail Shaw
SQL Server MVP

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-18 : 06:13:09

quote:
Originally posted by sent_sara

hi GilaMonster,
Already it is in simple recovery mode only.
Other thing i found is :
Under properties/file/logfile/initial size is 9189 MB (9 gb approx.)
if iam trying to change to 5000 mb.its is not getting reflected..

can u give the solution???

quote:
Originally posted by GilaMonster

Why do you want to truncate your transaction log daily? If you don't need point-in-time recovery, set the DB into simple recovery mode and SQL will discard old log records for you.

If you do need point-in-time recovery, then make sure that you have regular log backups running (SQL job or a maintenance plan)

Edit: Please don't cross post.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110865[/url]

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-18 : 06:51:21

If it's in simple recovery mode then you do not need to run the above script at all. In simple recovery, the log is automatically truncated when a checkpoint occurs. Note that a truncation won't shrink the log. It just removes inactive log records inside the file.

Do you have replication running? Have you done any large index rebuilds recently?

If you're on SQL 2005, please run the following and see what is listed for the DB in question
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources