dynamic t query -- unable to add the string to the last column as "DBCC SHRINKFILE(<TransactionLogName>, 1)"

  • Hi Procters

    I was trying to gather a query with data to get all the shrink tlog commands, but not working as is:

    Basically I want my query to produce the DBCC SHRINKFiLE ( ) string in the last column. Please have a look and suggest how to update the column with the details

    -- create a temporary table to insert the above mentioned output against each databases.

    SET NOCOUNT on

    create table #db_files(

    DB_name VARCHAR(MAX),

    db_files varchar(4000),

    file_loc varchar(4000),

    filesizeMB decimal(9,2),

    spaceUsedMB decimal(9,2),

    FreespaceMB decimal(9,2),

    cmd varchar(4000))

    declare @strSQL nvarchar(2000)

    DECLARE @dbName varchar(MAX)

    DECLARE @getDBname CURSOR

    SET @getDBname = CURSOR FOR

    select name FROM sys.databases

    WHERE state_desc = 'ONLINE'

    OPEN @getDBname

    FETCH NEXT

    FROM @getDBname INTO @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --PRINT @dbName

    select @strSQL =

    '

    use ' + quotename(@dbname) + '

    INSERT INTO #db_files

    select '''+ @dbname+'''

    , name

    , filename

    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB

    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB

    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB

    , ''''

    from dbo.sysfiles a

    where groupid = 0 -- signifies log file

    '

    --select @strSQL

    exec sp_executesql @strSQL

    UPDATE #db_files

    SET cmd = 'DBCC ShrinkFile('

    FETCH NEXT

    FROM @getDBname INTO @dbName

    END

    CLOSE @getDBname

    DEALLOCATE @getDBname

    GO

    select * from #db_files

    ORDER BY FreespaceMB DESC

    --DBCC SHRINKFILE(<TransactionLogName>, 1)

    -- drop the temporary table

    drop table #db_files

    SET NOCOUNT OFF

    Thanks.

  • Any advice?

    Thanks.

  • Shrinking files is not a good thing and is rarely needed if you're managing your log files. Things can come up as a one time, blew up a log and that space is not needed ever kind of thing.

    Shrink every log of all user and system databases? And any snapshots? And everythng to 1 MB? Is that really what you want to do? There is a lot wrong with the logic of that outside of the effects it has on the logs and the system resources. I understand what you are doing but have no idea why it would be done.

    Anyway, you can change the select to include the logical file name, add a variable for the logical file name and then you use the two variables to select into from the cursor. So the cursor definition would use something like:

    SELECT d.name , f.name

    FROM sys.databases d

    INNER JOIN sys.master_files f

    ON d.database_id = f.database_id

    WHERE f.type = 1

    and d.state_desc = 'ONLINE'

    FETCH NEXT FROM YourCursor INTO @dbname, @logname

    You would also have to include the @logname in the second Fetch Next. Then you can just include the log name variable in the dynamic SQL statement your using to populate the temp tables last column (no need for an update) like: ''DBCC ShrinkFile(' + @logname + ',1)''

    It's just so wrong though to ever do the Cmd in the last column of the table and that's likely why no one has answered.

    Sue

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply