SET NOCOUNT ON
DECLARE @DBName varchar(100)
DECLARE @FileName varchar(100)
DECLARE @TempTxt varchar(1000)
DECLARE @Cnt smallint
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
DECLARE cmdtxt_cursor CURSOR FOR SELECT RTRIM(master..Sysdatabases.Name) as [DBName],
RTRIM(master..Sysaltfiles.Name) as [FileName]
FROM master..Sysdatabases
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID
WHERE master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0
ORDER BY Left(master..Sysaltfiles.FileName, 1)
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
OPEN cmdtxt_cursor
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing : ' + @DBName
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases
Where master..Sysdatabases.[Name] = @DBName)
BEGIN
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) +
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'
--PRINT @TempTxt
EXEC (@TempTxt)
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
END
END
CLOSE cmdtxt_cursor
DEALLOCATE cmdtxt_cursor
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
GO