Shrink the Transaction Log of a SQL Server Database

Shrink the Transaction Log of a SQL Server Database

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
See also  Monetize TikTok For Your Business

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist