Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Jul 26, 2002

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 sysfiles

DBCC SHRINKFILE('[Logical name of logfile]', 10) -- 
10 is the size in MB
that you want the log to shrink to
GO

BACKUP LOG [Database name] WITH TRUNCATE_ONLY
GO

SET NOCOUNT ON
GO

CREATE TABLE #foo (bar int)
GO

DECLARE @foo int
SET @foo = 0

WHILE @foo < 10000
BEGIN
	INSERT INTO #foo (bar) VALUES (@foo)
	SET @foo = @foo + 1
END

SELECT 'Inserted ' + CONVERT(varchar(10), COUNT(*)) 
+ ' rows into
temptable' FROM #foo

DROP TABLE #foo

SET NOCOUNT OFF
GO

SELECT * FROM sysfiles
Christoffer Hedgate
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date