Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Shrink logs
Message
From
10/07/2009 16:47:12
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Environment versions
SQL Server:
SQL Server 2005
Miscellaneous
Thread ID:
01411487
Message ID:
01411519
Views:
29
Thank you all for your efforts. I did try this also, and while the IF condition is important, it is not critical. The bigger trouble is that it does not find the correct sysaltfiles and I get several errors:

Invalid object name 'dbo.sysaltfiles'.

>Hi Boris,
>
>Looks like IF ? in condition is ignored.
>
>I tried
>
>
>declare @Str varchar(800)
>
>set @Str = 'IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')' + CHAR(13) + 'BEGIN ' + CHAR(13) +
>'ALTER DATABASE ? SET RECOVERY SIMPLE' + CHAR(13) + 'DBCC SHRINKFILE (N''?_log'',0,TRUNCATEONLY)' + char(13) + 'END'
>
>execute sp_msForEachdb @Str
>
>and got
>
>
>Msg 5058, Level 16, State 1, Line 1
>Option 'RECOVERY' cannot be set in database 'tempdb'.
>Msg 8985, Level 16, State 1, Line 1
>Could not locate file 'ReportServer$IOTECHSQL2008_log' for database 'Test' in sys.database_files. The file either does not exist, or was dropped. 
>Msg 8985, Level 16, State 1, Line 1
>Could not locate file 'ReportServer$IOTECHSQL2008TempDB_log' for database 'Test' in sys.database_files. The file either does not exist, or was dropped. 
>DBCC execution completed. If DBCC printed error messages, contact your system administrator.
>
>
>>The same way :-):
>>
>>sp_MSForEachDb 'declare @LogFile nvarchar(2000)
>>                declare @ExeString nvarchar(2000)
>>                IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
>>                   BEGIN
>>                      USE [?]
>>                      SELECT @LogFile = dbo.sysaltfiles.name
>>                             FROM master.dbo.sysdatabases
>>                      INNER JOIN dbo.sysaltfiles ON master.dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>>                      WHERE (master.dbo.sysaltfiles.fileid = 1) AND (master.dbo.sysdatabases.name = ''?'')
>>                      ALTER DATABASE [?] SET RECOVERY SIMPLE
>>                      SET @ExeString = ''DBCC SHRINKFILE ('' + QUOTENAME(@LogFile) + '', 1)''
>>                      EXEC (@ExeString)
>>                    END'
>>
>>Check for errors, because I wrote this directly here and didn't test it :-)
Thank You

Rollin Burr

Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform