SHRINK LOG FILE

SHRINK LOG FILE in SQL Server 2005

---

-- To find out the Free Disk Space
xp_fixeddrives

-- Before Check the DB Size
DBCC SQLPERF(LOGSPACE)

-- Want to see the current db .mdf and .ldf file run this script
SELECT file_id, name FROM sys.database_files
-- or if you know the database name (ex: db Name : Employee)
SELECT file_id, name FROM employee.sys.database_files

--Syntax to Shrink LoG file
BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE (, Size)
-- Size is in MB

-- Shrink LoG File Example:-

BACKUP LOG Employee WITH TRUNCATE_ONLY

DBCC SHRINKFILE (Employee_log, 500)
-- It will shrink 500 MB Size
-- Example Before Shrink your Employee DB size 1000MB
-- After Shrink your Employee DB Size would Be 500MB

-- To find the DB Size Run the following Command
DBCC SQLPERF(LOGSPACE)

--- Shrink Database
USE master;
DBCC SHRINKDATABASE (Employee, 10);
GO


-- Increase Transaction Log size
ALTER DATABASE Employee
MODIFY FILE
(NAME = Employee_log,SIZE = 80MB)

--- Trouble Shot
If Suppose you got the error message like this you need to change the log size which is higher than you previous log file size

Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.

------------------------------------------------------------------------------------
-- To find out all drive space details and Free space details
SET NOCOUNT ON


IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo


DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)

CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null

)

CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
)

INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives


-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)
OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0

EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0

EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO

SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)

END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''

-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace AS [FreeSpace MB]
, (TotalSpace - FreeSpace) AS [UsedSpace MB]
, TotalSpace AS [TotalSpace MB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo

----- Trouble Shot -----------------
/* -- Error Message

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
*/

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

------------------------------------------------------------------------------------