dynamic use 2

[simian336]

I have a little script to shrink log files.

The problem is when I run

set @sql='DBCC SHRINKFILE ('+ @logfilename +' , 500)'
print (@sql)
exec (@sql)

I need to be in that database and

set @test = 'use [Warehouse]'
print @test
exec @test

does not work. I think I had solved this one time before but I cannot find my solution.

Thanks

Simi

 

[bborissov]

set @sql='USE [Warehouse]
DBCC SHRINKFILE ('+ @logfilename +' , 500)'
print (@sql)
exec (@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[simian336]

Hey Borislav,

I tried that as well. It stays in the Master and the error is...

"Could not locate file 'Build_log' for database 'Warehouse' in sys.database_files. The file either does not exist, or was dropped.

 

[bborissov]

Then maybe you do not have Build_log for Warehouse DB?

It will stays in master because the EXEC is ran in another batch.

Could you execute this w/o using dynamics (just for testing)

USE [Warehouse]
DBCC SHRINKFILE (Build_log , 500)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[simian336]

Hey Borislav,

I'm an idiot, I simplified my example to much and did not notice.

set @dbname= 'Warehouse'
set @test = 'use ' + @dbname
print @test
exec @test

and yes, non-dynamic works.

Thanks

Simi

 

[markros]

Check this blog

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/truncate-log-in-all-your-user-databases

and make sure to read links from the comments as well.

PluralSight Learning Library

 

[simian336]

I think I got it...

set @sql='USE [' + @dbname + '] DBCC SHRINKFILE ('+ @logfilename +' , 400)'

Thanks

Simi