SQL Server - shrinking log files

I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file. 

Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now.

Is there any way to shrink the log file with out taking backup of it ?

T

May 26th, 2015 10:40am

DBCC SHRINKFILE (logicalLogFileName)
To find logicalLogFileName following command has to be ran.

Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 10:49am

USE dbname

GO

DBCC SHRINKFILE (2,100)

But I think because you do not have a free space you will get an error. Let us know how was it going.

May 26th, 2015 12:26pm

What is recovery model of database. If it is simple you dont need to perform transaction log backup to truncate logs and then shrink it
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 1:52pm

Run DBCC SHRINKFILE(yourTLogName) command. There is an optional second parameter for the requested size to shrink it.
May 29th, 2015 1:34am

Hello - We need to know what is preventing log truncation. Can you query this and let us know the value for this column for your database:  

SELECT log_reuse_wait_Desc, * from sys.databases 

Assuming you are running your DB with FULL/ BULK LOGGED and you cannot take the back-up then you can change the recovery model to SIMPLE and perform the shrink file action:

ALTER DATABASE SET RECOVERY SIMPLE 
GO
DBCC SHRINKFILE ( logical_file_name, 200)
Note: You will have to change the logical file name in the above code with that of yours and you can know it by executing exec sp_helpfile


 

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 2:18am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics