How to enable the database shrink Transaction log - procedure of database cleanup
How can I enable the database shrink Transaction log - procedure of database cleanup? By default this procedure is disabled in the sp_EventAdminDeleteEventsOlderThan.
Follow the steps below to enable shrink procedure of the Transaction log:
1. Open the Enterprise Manager of the SQL server. 2. Go to (point to) you Intrust database. 3. Point to "Stored Procedure". 4. Select stored procedure "sp_EventAdminDeleteEventsOlderThan". 5. Right click "sp_EventAdminDeleteEventsOlderThan" and select properties". 6. For the Intrust 7.1 SP2 at the end of the procedure you will find "/* <-- Add a double dash ("--") at the beginning of this line to uncomment the code below." 7. Add (--) at the beginning of this line and should be like this "-- /* <-- Add a double dash ("--") at the beginning of this line to uncomment the code below". 8. Save the procedure and exit.
By default this sign comment is not presented and by adding the sign comment this will disable the transaction truncate.
DECLARE @strDatabase SYSNAME SET @strDatabase = DB_NAME() BACKUP LOG @strDatabase WITH TRUNCATE_ONLY -- /* <-- Add a double dash ("--") at the beginning of this line to uncomment the code below. -- If uncommented, the code below performs shrinking of the transaction log -- for your events database after it is truncated. You may need this operation -- enabled if your transaction log keeps growing extremely large with every -- InTrust collection/import and you really need to free up the space on the -- hard disk on a regular basis. -- Please note that enabling this operation may make your cleanup procedure take -- considerably longer to complete. DECLARE #curFiles CURSOR FOR SELECT [name] FROM [sysfiles] WHERE [status] & 64 <> 0 OPEN #curFiles DECLARE @strFilename SYSNAME FETCH NEXT FROM #curFiles INTO @strFilename WHILE @@FETCH_STATUS = 0 BEGIN SET @strFilename = RTRIM(@strFilename) DBCC SHRINKFILE(@strFilename) FETCH NEXT FROM #curFiles INTO @strFilename END CLOSE #curFiles DEALLOCATE #curFiles --*/