SHRINK LOG FILE in SQL Server 2005
-- To find out the Free Disk Spacexp_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
DBCC SHRINKFILE (
-- 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
------------------------------------------------------------------------------------
