Cursor problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-06 : 09:16:23

Devaraya writes "Hi,
This is my cursor to truncate all log files in the server. It is truncating the log files but giving an error message below. Can U help me out?

Cursor:
set quoted_identifier off
use master
go
declare @db_name varchar(150)
declare @qry varchar(50)
declare db_cursor cursor for select name from dbo.sysdatabases where dbid >4 order by name
open db_cursor
fetch next from db_cursor into @db_name
while @@fetch_status = 0
begin
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
close db_cursor
deallocate db_cursor
go

Error:
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."

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-06 : 18:05:56

This doesn't directly address your question, but I would suggest that you use "sysaltfiles" instead of "sysfiles". That way, you should have all the data you need in one table without having to switch databases all the time (you can then stop having to use the "use ..." command).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-06 : 19:15:07

I hope this code isn't being run on a production server as shrinking your files is not a good thing.

Devaraya, could you explain why you want to shrink all files in all databases?

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources