March 11, 2009 at 4:45 pm
Hi,
I am trying to write a script which will go through several databases and shrink their log. I have to retrieve the log file logical name first and then use it in the DBCC statement. The code below shows what I am trying to achieve. However the problem is that the file name variable (@fn) looses scope outside the dynamic SQL statement. I don't want to mess with temp tables, but rather find more elegant way to do this.
Any ideas?
declare @s-2 nvarchar(500)
declare @Dbname varchar(50)
declare @dbs table (Dbname sysname)
insert into @dbs select name from master.dbo.sysdatabases (nolock)
where [name] LIKE 'MyDBName__Reports'
set @Dbname = (select min(Dbname) from @dbs)
while @Dbname is not null
begin
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
set @Dbname = (select min(Dbname) from @dbs where Dbname > @Dbname)
end
March 11, 2009 at 4:52 pm
@fn is being defined within the Dynamic SQL itself. You were trying to put the VALUE of @fn into the string, before it has been defined. Try this instead.
-----------------------------------------------------------
declare @s-2 nvarchar(500)
declare @Dbname varchar(50)
declare @dbs table (Dbname sysname)
insert into @dbs select name from master.dbo.sysdatabases (nolock)
where [name] LIKE 'MyDBName__Reports'
set @Dbname = (select min(Dbname) from @dbs)
while @Dbname is not null
begin
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 -- you'll want to uncomment this line
set @Dbname = (select min(Dbname) from @dbs where Dbname > @Dbname)
end
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 11, 2009 at 5:09 pm
[font="Verdana"]I would use something similar to the following (remembering that it is possible to have more than one log file per database.)
Note code is untested!
declare @databaseName sysname;
declare @sql nvarchar(max);
set @databaseName = N'';
while (1=1) begin
select top 1 @databaseName = [name]
from master.dbo.sysdatabases with (nolock)
where [name] > @databaseName and [name] like 'MyDBName__Reports'
order by [name];
if (@@rowcount = 0) break;
set @sql = N'
use [' + @databaseName + '];
declare @logName nvarchar(200);
set @logName = N'''';
while (1=1) begin
select top 1 @logName = [name]
from sys.database_files
where [name] > @logName and type_desc = ''LOG''
order by name;
if (@@rowcount = 0) break;
print @logName;
dbcc shrinkfile (@logName, 1);
end; -- while
';
exec sp_executesql @stmt = @sql;
end; -- while
[/font]
March 11, 2009 at 5:13 pm
Hi
And my one 🙂
[font="Courier New"]
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql +
CASE WHEN @sql = '' THEN '' ELSE ' UNION ALL ' END +
'SELECT ''' + QUOTENAME(name) + ''', name ' +
'FROM ' + QUOTENAME(name) + '.sys.database_files ' +
'WHERE type_desc = ''LOG''' +
CHAR(13) + CHAR(10)
FROM MASTER.sys.databases
WHERE name LIKE 'Adv%'
DECLARE @dbs TABLE (id INT IDENTITY, name SYSNAME, f_name SYSNAME)
INSERT INTO @dbs
EXECUTE sp_executesql @sql
WHILE EXISTS (SELECT TOP 1 * FROM @dbs)
BEGIN
DECLARE @id INT
DECLARE @db SYSNAME
DECLARE @f_name SYSNAME
SELECT TOP(1) @id = id, @db = name, @f_name = f_name FROM @dbs
SET @sql = 'USE ' + @db + ' DBCC SHRINKFILE(' + @f_name + ')'
PRINT @sql
--EXECUTE sp_executesql @sql
DELETE FROM @dbs WHERE id = @id
END[/font]
Greets
Flo
March 11, 2009 at 5:36 pm
[font="Verdana"]I considered doing something like that, but he said he didn't want to use temporary tables, so I excluded table variables as well.
BTW, don't you need a semi-colon or carriage return after the use? Also, I'd think it would be safer to put square brackets around the database name.
[/font]
March 11, 2009 at 5:45 pm
Hi
I thought the part "I don't want to mess with temp tables, but rather find more elegant way to do this." was more a general statement than a specific requirement. Especially because his own code did use a table variable.
The semicolon is not really required (I just tried and shrinked all my AdventureWorks database logs 🙂 ). The database names have been quoted within the SELECT statement which build up the first SQL Statement but you're right the database file names have not been quoted correctly ;). Thanks!
Flo
March 11, 2009 at 6:14 pm
[font="Verdana"]Yeah, I forgot... SQL Server just needs white space to separate statements, not actually a semi-colon or carriage return. I'm trying to use semi-colons consistently these days (mostly just for readability) so I blame that for forgetting. :P[/font]
March 11, 2009 at 7:00 pm
Doesn't matter. Sometimes I would wish to forgett only this. 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply