Truncate Log File For All User Databases

,

The log file is too big? This is the solution

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO







ALTER        Procedure [dbo].[sp_EasyDetAtt] @MinSize int /* = 100 --(MB)*/,@RealDettach char(1)
AS
BEGIN
-- DataFiles List --
SET NOCOUNT ON
if exists (SELECT [id] 
	   FROM tempdb..sysobjects 
	   WHERE [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats 

DECLARE @DBName nvarchar(40)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
 		   SELECT name
    		   FROM master.dbo.sysdatabases
                   WHERE status&512 = 0 
                   AND dbid >4	

-- Create Temp Hold Result Tables --
CREATE TABLE #TempForFileStats([Server Name]          nvarchar(40),
                               [Database Name]        nvarchar(20),
                               [File Name]            nvarchar(128),
                               [Usage Type]           varchar (6),
                               [Size (MB)]            real, 
                               [Space Used (MB)]      real,
                               [MaxSize (MB)]         real,
                               [Next Allocation (MB)] real, 
                               [Growth Type]          varchar (12),
                               [File Id]              smallint,
                               [Group Id]             smallint,
                               [Physical File]        nvarchar (260),
                               [Date Checked]         datetime) 

CREATE TABLE #TempForDataFile ([File Id]             smallint,
                               [Group Id]            smallint,
                               [Total Extents]       int,
                               [Used Extents]        int,
                               [File Name]           nvarchar(128),
                               [Physical File]       nvarchar(260))

CREATE TABLE #TempForLogFile  ([File Id]             int, 
                               [Size (Bytes)]        real, 
                               [Start Offset]        varchar(30), 
                               [FSeqNo]              int, 
                               [Status]              int, 
                               [Parity]              smallint, 
                               [CreateTime]          varchar(20))   


DECLARE @tblConnectedUsers TABLE (SPID int)
DECLARE @KillExp VARCHAR(200),@iSPID   int
 



OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @SQLString = 'SELECT @@SERVERNAME                     as  ''ServerName'', '          + 
                       '''' + @DBName + '''' + '                as  ''Database'', '            +  
                       '        f.name, '                                                      +
                       '       CASE '                                                          +
                       '          WHEN (64 & f.status) = 64 THEN ''Log'' '                     +
                       '          ELSE ''Data'' '                                              + 
                       '       END                              as ''Usage Type'', '           +
                       '        f.size*8/1024.00                as ''Size (MB)'', '            +
                       '        NULL                            as ''Space Used (MB)'', '      +
                       '        CASE f.maxsize '                                               +
                       '           WHEN -1 THEN  -1 '                                        +
                       '           WHEN  0 THEN  f.size*8/1024.00  '                           +
                       '           ELSE          f.maxsize*8/1024.00 '                         +
                       '        END                             as ''Max Size (MB)'', '        +
                       '        CASE '                                                         +
                       '           WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) ' + 

                       '           WHEN f.growth =0                 THEN 0 '                +
                       '           ELSE                                   f.growth*8/1024.00 ' +
                       '        END                             as ''Next Allocation (MB)'', ' +
                       '       CASE  '                                                         +
                       '          WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' '      +
                       '          ELSE ''Pages'' '                                             +
                       '       END                              as ''Usage Type'', '           +
                       '       f.fileid, '                                                     +
                       '       f.groupid, '                                                    +
                       '       filename, '                                                     +
                       '       getdate() '                                                     +
                       ' FROM [' + @DBName + '].[dbo].[sysfiles] f' 
      INSERT #TempForFileStats 
      EXECUTE(@SQLString)
      ------------------------------------------------------------------------
      SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS'
      INSERT #TempForDataFile
      EXECUTE(@SQLString)
      --
      UPDATE #TempForFileStats
      SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
      FROM #TempForFileStats f,
           #TempForDataFile  s
      WHERE f.[File Id]       = s.[File Id]
        AND f.[Group Id]      = s.[Group Id]
        AND f.[Database Name] = @DBName
      --
      TRUNCATE TABLE #TempForDataFile
      -------------------------------------------------------------------------
      SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO'
      INSERT #TempForLogFile
      EXECUTE(@SQLString)      
      --
      UPDATE #TempForFileStats 
      SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) + 
                                       SUM(CASE 
                                              WHEN l.Status <> 0 THEN  l.[Size (Bytes)] 
                                              ELSE           0 
                                           END))/1048576.00
                               FROM #TempForLogFile l
                               WHERE l.[File Id] = f.[File Id])
      FROM #TempForFileStats f
      WHERE f.[Database Name] = @DBName
      AND f.[Usage Type]    = 'Log'
      --
      TRUNCATE TABLE #TempForLogFile 
      -------------------------------------------------------------------------
      FETCH NEXT FROM c_db INTO @DBName
   END
CLOSE c_db
DEALLOCATE c_db
SET @DbName = ''

--- List Of Databases for detach/attaching

SELECT * FROM #TempForFileStats

SELECT  x.[Database Name]	as [Database Name],
	x.[Physical File] 	as ldf,
	y.[Physical File] 	as mdf,
	x.[File Name] 		as logname
FROM #TempForFileStats x
JOIN #TempForFileStats y
	ON (x.[Database Name] = y.[Database Name]
	AND y.[Usage Type] = 'Data'
	AND y.[File Id] = 1)
WHERE x.[Usage Type] = 'Log'
AND x.[File Id] = 2
AND x.[Size (MB)] >= @MinSize
---

DECLARE @DbPhysNameOld SYSNAME
DECLARE @DbPhysNameNew SYSNAME
DECLARE @DbMdfFileName SYSNAME
DECLARE @LogName       SYSNAME	
DECLARE @ShellCMD NVARCHAR(2000)
DECLARE @ShellForDelete NVARCHAR(2000)
DECLARE n_db CURSOR FOR SELECT x.[Database Name],x.[Physical File] as ldf,y.[Physical File] as mdf,x.[File Name] as logname
			FROM #TempForFileStats x
			JOIN #TempForFileStats y
				ON (x.[Database Name] = y.[Database Name]
				AND y.[Usage Type] = 'Data'
				AND y.[File Id] = 1)
			WHERE x.[Usage Type] = 'Log'
			AND x.[File Id] = 2
			AND x.[Size (MB)] >= @MinSize

OPEN n_db	
FETCH NEXT FROM n_db INTO @DbName,@DbPhysNameOld,@DbMdfFileName,@LogName
WHILE @@FETCH_STATUS = 0
	BEGIN
		print ltrim(rtrim(@DbName))+' '+ltrim(rtrim(@DbMdfFileName))
		INSERT INTO  @tblConnectedUsers
		SELECT p.spid
		FROM master.dbo.sysprocesses p WITH (NOLOCK)
		join master.dbo.sysdatabases d WITH (NOLOCK) 
			ON p.dbid = d.dbid
		WHERE d.[name] = @DbName
		IF @@ROWCOUNT > 0
		BEGIN
			WHILE 1 = 1
				BEGIN
					SELECT TOP 1 @iSPID = SPID
					FROM  @tblConnectedUsers
					WHERE SPID > ISNULL(@iSPID, 0) 
					ORDER BY SPID ASC 
					-- Break if no more users
					IF @@ROWCOUNT = 0
					BREAK
					SET @KillExp = 'Kill ' + CONVERT(VARCHAR(10), @iSPID)
					EXEC( @KillExp )	
				END	
		DELETE @tblConnectedUsers		
		END
		-- Detach database 
		if UPPER(@RealDettach)='Y'
			BEGIN
				EXEC master..sp_detach_db @Dbname,TRUE
				WAITFOR DELAY '00:00:05'
				EXEC xp_cmdshell 'cd c:\temp',NO_OUTPUT --check if c:\temp exists
				IF @@error = 1
					EXEC xp_cmdshell 'mkdir c:\temp',NO_OUTPUT --create c:\temp if not exists
              	-- Move (OS) The old log file to c:\temp
				IF CHARINDEX(' ',@DbPhysNameOld)>0
					SET @ShellCMD = 'MOVE "'+LTRIM(RTRIM(@DbPhysNameOld))+'" '+'c:\temp'
				ELSE
					SET @ShellCMD = 'MOVE '+LTRIM(RTRIM(@DbPhysNameOld))+' '+'c:\temp'
				PRINT LTRIM(RTRIM(@ShellCMD))
				EXEC master..xp_cmdshell @ShellCMD,NO_OUTPUT
				WAITFOR DELAY '00:00:05'
		-- Attach the single mdf file
		
			EXEC master..sp_attach_single_file_db @Dbname,@DbMdfFileName
			IF @@ERROR = 0
				BEGIN
					PRINT '****************************************************'
					PRINT @Dbname+' attaching success'
					PRINT '****************************************************'
							
				END
			ELSE 
				BEGIN
					PRINT '***************************************************'
					PRINT 'Check '+@Dbname+' and attach him manualy !!!'
					PRINT '***************************************************'
				END	
		END
		SET @ShellCMD = ''
		SET @DbPhysNameNew = ''
		FETCH NEXT FROM n_db INTO @DbName,@DbPhysNameOld,@DbMdfFileName,@LogName
	END
CLOSE n_db
DEALLOCATE n_db

DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
DROP TABLE #TempForFileStats

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

5 (1)

Share

Share

Rate

5 (1)