Thursday, September 16, 2010

Shrink Log file on SQL Server

I write this scripts on SQL Server 2008, and this script is work.
You can test this scripts on your SQL server management studio.




declare @dbName	nvarchar(300)
		,@sqlSyntax nvarchar(2000)
set @dbName = 'DatabaseName'				-- database name to shrink
set @sqlSyntax = 'use ' + @dbName + 
	'
	declare @fileLogNm	nvarchar(300)
	
	set @fileLogNm = (
	select name from sys.master_files
	where type = 1
	and database_id = db_id()
	)

	DBCC SHRINKFILE(@fileLogNm, 1)
	BACKUP LOG ' + @dbName + ' WITH TRUNCATE_ONLY
	DBCC SHRINKFILE(@fileLogNm, 1)
	'				
exec (@sqlSyntax)



Hope this scripts will work with you.

No comments: