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
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
