Shrinking a transaction log in SQL Server can be tricky, especially if you need it shrunk ASAP. You could use DBCC SHRINKFILE() to shrink it, but it will not shrink immediately, depending on where the physical log is placed amongst the virtual log. The following script can be used to hurry this process up:
SELECT * FROM sysfilesDBCC SHRINKFILE('[Logical name of logfile]', 10) -- 10 is the size in MBthat you want the log to shrink toGOBACKUP LOG [Database name] WITH TRUNCATE_ONLYGOSET NOCOUNT ONGOCREATE TABLE #foo (bar int)GODECLARE @foo intSET @foo = 0WHILE @foo < 10000BEGIN INSERT INTO #foo (bar) VALUES (@foo) SET @foo = @foo + 1ENDSELECT 'Inserted ' + CONVERT(varchar(10), COUNT(*)) + ' rows intotemptable' FROM #fooDROP TABLE #fooSET NOCOUNT OFFGOSELECT * FROM sysfiles
