Wednesday, November 24, 2010

dump transaction in sqlserver 2008

-- =============================================
-- Author: Asif Ghanchi
-- Create date: 11/24/2010
-- Description: Dump transaction for Sql Server 2008
-- =============================================
CREATE PROCEDURE dump_transaction
@database_name varchar(100)
AS
BEGIN

declare @name varchar(100)

select @name = name from sys.database_files where type = 1

declare @query nvarchar(max)

SET @query = 'USE '+ @database_name +';

ALTER DATABASE '+ @database_name +'
SET RECOVERY SIMPLE;

DBCC SHRINKFILE ('''+ @name +''');

ALTER DATABASE '+ @database_name +'
SET RECOVERY FULL;'

exec sp_executesql @query

END

No comments:

Post a Comment