September 3, 2009 at 7:36 pm
Hi,
I have a job, which restores a number of databases in a loop. It goes through the list builds restore statement, then executes it, and then builds a log file shrink statement:
set @s-2 = 'DECLARE @fn sysname
SELECT @fn = [name] FROM ' + @dbname + '.sys.database_files WHERE type_desc = ''LOG''
USE ' + @dbname + ' DBCC SHRINKFILE (@fn, 1) '
print @s-2
exec master.dbo.sp_executesql @s-2
When the log file shrink executes, sometimes it produces a strange error message:
Msg 8985, Sev 16, State 1, Line 3 : Could not locate file 'MyDB2_log' for database 'MyDB2' in sys.database_files. The file either does not exist, or was dropped. [SQLSTATE 42000]
The error is intermittent, and does not always happen.
The problem is that the database I am trying to shrink is not MyDB2, it is MyDB1. MyDB2 has not even started to restore.
Any ideas?
Thanks.
September 4, 2009 at 8:14 am
Where and how are you setting @dbname? It sounds like you have issue there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 14, 2009 at 11:57 pm
and why using dynamic sql to do a maintenance task. Also why are you shrinking the log file. Are you taking any log backups.
"Keep Trying"
December 8, 2009 at 6:28 pm
Ok, found it out, the weird error happens because of the use of master database name in the execution string:
set @s-2 = 'DECLARE @fn sysname
SELECT @fn = [name] FROM ' + @dbname + '.sys.database_files WHERE type_desc = ''LOG''
USE ' + @dbname + ' DBCC SHRINKFILE (@fn, 1) '
print @s-2
exec master.dbo.sp_executesql @s-2
If I replace it with this:
exec dbo.sp_executesql @s-2
everything works fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply