SHRINK LOG File In MSSQL Server

THIS IS NOT A RECOMMENDED PRACTICE for production systems... You will lose your ability to recover.

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Syntax

DBCC SHRINKFILE ([Logical log file name],target size in MB)

Example

-- Shrink the truncated log file to 1 MB.

USE STUDENT

DBCC SHRINKFILE (Student_log, 1);

GO

In this command some time log file size will be  not reduce because of Database recovery MODE is FULL, You need to set recovery mode as SIMPLE.

USE STUDENT

ALTER DATABASE STUDENT

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (Student_log, 1);

GO

-- Reset the database recovery model.

ALTER DATABASE STUDENT

SET RECOVERY FULL;

GO

sp_spaceused ( In MSSQL Server )

sp_spaceused command used in MSSQL 2005 or above version. This command return a current database disk space (hard drive) used.

Let’s see in details.

This command give details of disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Example

USE [Master]

GO

EXEC sp_spaceused

GO

Results

Now, let us understand the above result sets, lets check by the column names

·         database_size: Database size (data files + log files) = 5 MB

·         unallocated space: Space that is not reserved for use either by data or log files (Space Available) = 1.26 MB

·         reserved: Space that is reserved for use by data and log files = 2.74 MB

·         data: Space used by data = 1176 KB/1024 = 1.14 MB

·         index_size: Space used by indexes = 1184  KB/1024 = 1.15 MB

·         unused: Portion of the reserved space, which is not yet used = 448 KB/1024 = 0.43 MB

Need Permission to execute this command:

sp_spaceused is granted to the public role.