Cursor for Log Truncation

Question

Post reply

Cursor for Log Truncation

devaraya

Default port

Points: 1455
More actions
March 2, 2006 at 9:33 pm

#111994

I wrote one cursor for backup log and truncate the log file. The cursor is truncating the log file but giving error.
Can any body? The error message is below.
Thanks
set nocount on
set quoted_identifier off
use master
go
begin
declare @db_name varchar(25)
declare @qry varchar(100)
declare db_cursor cursor for select name from master..sysdatabases
where name not in('master','msdb','modal','tempdb')
order by name
open db_cursor
fetch next from db_cursor into @db_name
while @@fetch_status = 0
begin
exec("BACKUP LOG "+@db_name+" WITH TRUNCATE_ONLY")
set @qry= (select "use "+ @db_name+char(13) + "dbcc shrinkfile(" + ltrim(rtrim(name)) + ",1)" from dbo.sysfiles where fileid=2 )
exec (@qry)
fetch next from db_cursor into @db_name
end
end
close db_cursor
deallocate db_cursor
set ansi_nulls on
go
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'mastlog' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'mastlog' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Ramaa

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Typo error here:

'modal'

should be 'model'

Doubt if it helps much.

------------------------------
The Users are always right - when I'm not wrong!

SQLZ SSChampion Points: 12872 More actions · Answer 2

The problem is in the way that you are building the string for the dbcc shrinkfile.

You're performing the select from sysfiles outside of the dynamic sql so it will always select from master.dbo.sysfiles. As a result you're trying to shrink the master databases log file even though you're in another database (inside the dynamic sql).

Secondly, you cannot assume that the log file will always be the file with id=2 in the sysfiles table. If you're going to assume this, then you might as well hardcode the value 2 into the dbcc shrink file command as follows.

set @qry= ('use '+ @db_name+char(13) + 'dbcc shrinkfile(2,1)')

Thirdly, what you are doing is not a very good idea. Why truncate the log and then shrink it down to 1 MB? It will only grow again, taking up resources.

You might as well just set the recovery mode to simple on each database and let SQL Server handle your log files.

If you're really intent on doing what you're doing and you're going to assume that the log file has a file_id of 2 then you can do it in two lines of code.

sp_msforeachdb 'backup log ? with truncate_only'

sp_msforeachdb 'dbcc shrinkfile(2,1)'

Karl
source control for SQL Server

devaraya Default port Points: 1455 More actions · Answer 3

Hi Karl,

My server is up and running no down time. How the check point occurs?

Thank you for ur explanation.

Ramaa

SQLZ SSChampion Points: 12872 More actions · Answer 4

Hi Rama,

Checkpoints occur automatically and they occur on a frequency that is based on the recovery interval.

Look for "Checkpoints and the Active Portion of the Log" in BOL for a description of checkpoints.

Hope that helps,

Karl
source control for SQL Server

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Also note that if you truncate the transaction log, you will not be able to restore the database fully after a failure. Without an unbroken set of tran log backups, you'll be able to restore to the point of the last full (or differential) database backup and no further. If this is a dev or test server that's probably fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

There is no reason to run a Cursor to backup a SQL Server database because the backup and restore wizard is one of the best things in SQL Server. All you need is to use the wizard and schedule a SQL Server Agent Job to backup all your databases. And Checkpoint and active log are related to DBCC Shrinkfile, when you run DBCC Shrinkfile SQL Server returns all space less the section it marks as Active Log.

When you run a Cursor a query processor says you have asked me to perform a task I am not equipped to perform so you think I need five loops but I will take twenty loops. So there is no reason to use Cursor to perform routine tasks like backup and truncate log. My advice spend sometime with the BOL (books online) read up on all tasks, all the DBCC statements and SQL Server Agent Jobs. Hope this helps.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

devaraya Default port Points: 1455 More actions · Answer 7

Hi,

Thank You all for your suggestions and advices.

Ramaa