September 19, 2016 at 2:59 pm
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.
September 21, 2016 at 2:58 pm
Any advice?
Thanks.
September 21, 2016 at 6:14 pm
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