Saturday, January 15, 2011

T-SQL Used to shrink the transaction log

Not all commands are necessary, some commands are used to fix error countered.


BACKUP LOG TestDB
TO DISK = N'C:\TestDB_Adhoc_TransLog.Bak';
GO

-- Get The file Name
EXEC sp_helpdb 'TESTDB'
GO

USE TestDb
GO
-- SHRINK it to 10MB
DBCC SHRINKFILE ('TestDb_log',TRUNCATEONLY);
GO

USE master
GO
ALTER DATABASE TestDb SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
ALTER DATABASE TestDb SET ONLINE
GO

USE TestDb
GO
-- SHRINK it to 10MB
DBCC SHRINKFILE ('TestDb_log',TRUNCATEONLY);
GO

/*
Cannot shrink log file 2 (TestDb_log) because the logical log file located at the end of the file is in use.

(1 row(s) affected)
*/


USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
GO


USE TestDb
GO
-- SHRINK it to 10MB
DBCC SHRINKFILE ('TestDb_log',TRUNCATEONLY);
GO

/*
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
13 2 63 63 56 56

(1 row(s) affected)
*/
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

ALTER DATABASE TestDb
MODIFY FILE (
NAME = 'TestDb_log',
SIZE = 10);
GO