SQL Server multiple shrink operations

I'm trying to shrink all databases (files and logs) in SQL Server 2008 R2.

I have finished the script, but the problem is that when I loop over all databases and execute the query to do shrink file the first 3 or 4 shrinks work but them I have this error :

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

The script :

declare @db_name as varchar(30)
declare @db_recorvery_model as varchar(30)
declare @db_files_name as varchar(250)
declare @db_files_physical_name as varchar(250)

declare get_files cursor for 
     select b.name, a.name  
     from sys.master_files as a, 
          sys.databases as b 
     where a.database_id = b.database_id  
     order by b.name  

open get_files

fetch next from get_files into @db_files_name, @db_files_physical_name

set @db_files_name = (select @db_files_name)
set @db_files_physical_name = (select @db_files_physical_name)

DECLARE @Command as nvarchar(max)
set @Command=''

while(@@FETCH_STATUS=0)
BEGIN
   if (@db_files_name='master' or @db_files_name='msdb' or @db_files_name='tempdb' or @db_files_name='model')
   BEGIN
      print 'Bases de dados do sql server: '+@db_files_name
   END
   ELSE
   BEGIN
      set @Command = 'USE ' + '[' + @db_files_na开发者_StackOverflow社区me + '] DBCC SHRINKFILE ("'+@db_files_physical_name+'", 1 )'
      EXEC sp_executesql @Command
      print @Command
   END

   fetch next from get_files into @db_files_name, @db_files_physical_name

   set @db_files_name = (select @db_files_name)
   set @db_files_physical_name = (select @db_files_physical_name)
END

close get_files
deallocate get_files

Does anyone have any ideas ?

PS: I know that I shouldn't shrink but is a very special environment and not productive.

Can you determine which database flags the error? Can you try running your script on the single database that has the problem and see if it is consistently the same database that triggers the error? Perhaps it's a special database that you've missed that cannot be shrunk that way.

I have a similar environment containing temp databases that are not for long term storage, and I use the following script which has worked perfectly for hundreds of databases:

CREATE procedure [dbo].[ShrinkLog]
    @DB varchar(200)
as

declare @LogFile varchar(200)
declare @Sql varchar(500)

SELECT @LogFile = name
FROM sys.master_files
where type_desc = 'LOG'
and db_name(database_id) = @DB


set @Sql = '
    Use [' + @DB + ']
    DBCC SHRINKFILE([' + @LogFile + '], 1)
'
print(@sql)
exec(@sql)

Keep in mind also that you don't want to run this command unless your server has plenty of hard drive/memory space as well.

Best regards,

If you want to do a log shrink, this will be the best code. I am using it for a while and it never crash to me.

declare @SQL nvarchar(max)

select @SQL = coalesce(@SQL + char(13) + char(10),'') + N'
Use ' + QUOTENAME(d.[name]) + ';' + CHAR(13) + '
ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;
DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1);
ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY FULL;'
FROM sys.databases d
INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]
WHERE
    d.[database_id] > 4 --no sys dbs
    AND d.recovery_model = 1
    AND d.is_read_only = 0
    AND mf.[type] = 1 --log files
ORDER BY d.name

--print @SQL

execute (@SQL)