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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date