the simple procedure to shrink database log error

Question

I write a simple procedure to shrink database log,just need to input the database name:

ALTER proc [dbo].[sp_shrinkDBAndDBLog]
            @databaseName nvarchar(100)
        as
        begin

        declare @logName nvarchar(100),
                @dynamicSQL nvarchar(500)

        set @dynamicSQL='ALTER DATABASE '+@databaseName+' SET RECOVERY SIMPLE WITH NO_WAIT'     

        exec(@dynamicSQL)

        --select name from HLJEDI_SYS.sys.sysfiles where groupid=0;         

        set @dynamicSQL=N'select @logName= name from '+@databaseName+'.sys.database_files where type_desc=''LOG'''

        exec sp_executesql @dynamicSQL,N'@logName nvarchar(100) output',@logName output         

        --select * from sys.sysfiles where groupid=0                     

        set @dynamicSQL='DBCC SHRINKFILE (N'''+@logName+''',11,TRUNCATEONLY)'

        exec(@dynamicSQL)

        --DBCC SHRINKFILE (N'CUC_OA_LOG' , 11,  TRUNCATEONLY)           


        set @dynamicSQL='ALTER DATABASE '+@databaseName+' SET RECOVERY FULL WITH NO_WAIT'       

        exec(@dynamicSQL)
        --ALTER DATABASE OA SET RECOVERY FULL  --(Restore to Full Schema)       
    end

but when i execute with:

exec sp_shrinkDBAndDBLog 'DBName'

it has error: sys.database_files can't find database 'master' file 'eca2_log'。The file was been deleted and not exists。

And what is the problem? Thank you if you tell me and show the detail and princeple.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Oct 08 '13 at 04:55

score 1 · Accepted Answer · answered Oct 08 '13 at 05:08

1

Looks like you might be missing a USE { database } command before you execute the DBCC SHRINKFILE command, and so the DBCC is not getting executed on the correct database.

answered Oct 08 '13 at 05:08

blitz_jones

1,048
2
10
22

Could you show the actual code that fixes the problem? – frenchie Jul 02 '14 at 13:37
that line has to be inserted right after the line that says: set @dynamicSQL='DBCC SHRINKFILE (N'''+@logName+''',11,TRUNCATEONLY)' – blitz_jones Jul 02 '14 at 18:31

the simple procedure to shrink database log error

1 Answers1