Taggs Posted September 21, 2009 Share Posted September 21, 2009 Hi, I am trying to automate a Log Shrink File for all databases on our SQL Server (2005). I have a script that works to a fashion but I am having problems with one command.Declare @database_name varChar(50),@database_name_temp varChar(54)Declare database_name_CURSOR CURSOR FOR select name from sys.sysdatabases where dbid >4 order by nameOPEN database_name_CURSORFETCH NEXT FROM database_name_CURSOR into @database_nameWhile @@FETCH_STATUS = 0Begin Set @database_name_temp= @database_name + '_Log' Use @database_name DBCC ShrinkFile (@database_name_temp,10) Backup log @database_name with Truncate_only DBCC ShrinkFile (@database_name_temp,10) FETCH NEXT FROM database_name_CURSOR into @database_nameEndClose database_name_CURSOR;Deallocate database_name_CURSORThe problem is with the command Use @database_namethe error message I get isMsg 102, Level 15, State 1, Line 8Incorrect syntax near '@database_name'.If I substitute for a real Database name it works for that database (but no others obviously)ThanksTaggs Link to comment Share on other sites More sharing options...
Taggs Posted September 24, 2009 Author Share Posted September 24, 2009 (edited) OK I have a solution (from a post on another forum) The problem is USE looks for an IdentifierThe solution is exec('use ' + @database + '; <other statement/s here>)At the moment the job is reliant on the fact that the lodgical name is the same as the DB nameMy final code looks like thisDeclare @database_name varChar(50),@database_name_temp varChar(54)Declare database_name_CURSOR CURSOR FOR select name from sys.sysdatabases where dbid >4 order by nameOPEN database_name_CURSORFETCH NEXT FROM database_name_CURSOR into @database_nameWhile @@FETCH_STATUS = 0Begin Set @database_name_temp= @database_name + '_Log' exec('use ' + @database_name + '; DBCC ShrinkFile (' + @database_name_temp + ',10); Backup log ' + @database_name + ' with Truncate_only; DBCC ShrinkFile (' + @database_name_temp + ',10)') FETCH NEXT FROM database_name_CURSOR into @database_nameEndClose database_name_CURSOR;Deallocate database_name_CURSOR Edited September 24, 2009 by Taggs Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now