How to shrink your transaction log periodically on SQL Server

The most important thing in maintaining your SQL Server database is shrinking log. You should shrink your LOG data file periodically or your database will pops -i've ever experienced in that case on, hehehe-. Anyway i have a script to maintain your log daily. it would be better if you sign a job to run these queries -cause i use that - using SQL Server Agent.

DECLARE @size INT,
@name VARCHAR(200),
@logSize INT,
@database VARCHAR(200)

SELECT @logSize = 1000, -- specify your log size
@database = 'DATABASE' -- specify your sql server database name

Declare c_cursor CURSOR FAST_FORWARD FOR
    SELECT
        name,
        size
    FROM sys.master_files
    WHERE database_id = (select database_id
    FROM sys.databases where name = @database)
        AND type_desc = 'LOG'

OPEN c_cursor

FETCH NEXT FROM c_cursor INTO @name,@size

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@size >= @logSize)
    BEGIN dbcc shrinkfile(@name,2)
END

FETCH NEXT FROM c_cursor INTO @name,@size
END

CLOSE c_cursor
DEALLOCATE c_cursor

@logSize means how huge your transaction log to be shrink. if you set 0, it means you've job to shrink transaction log daily in case your transaction log in minimum size. Anyway, that is your choice to decide whatever you want. cause there is no side effect for your database.

I suggest you to run these queries at SQL Server is idle. because you don't want to distrub your database transaction, don't you. Maybe you can put your SQL Server Agent Job at Midnight -I did that in here-.Any suggestion ?

Note : @logSize = 1000 means 10MB