How to shrink SQL server db log file

[Elijah]

I am running a website build for my office internal purpose. I am running SQL server on its background. Now a days the database log files are growing larger and larger. It is creating problems for us while taking data backup. I want to know how to shrink this files on the server?

[opaper]

There are some points to that I would like to discuss:

1. Take the complete backup of your database
2. Generally you should take t-log backup for a huge database every 15-30 minutes in order to restrict log files from growing larger
3. However, if you still want then shrink

But according to me, it is better to avoid shrinking.

[fellah]

Any system admin who know MS SQL server can shrink database if a large number of transactions are taking place. It’s simple and the script to do so is here:

CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb', 'distribution') AND type_desc LIKE 'log'

SET NOCOUNT ON
DECLARE @VarDBLogicalName nvarchar(128)
DECLARE @VarDBName nvarchar(128)
DECLARE @VarRowCount int

SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@rowcount
WHILE @VarRowCount <> 0
BEGIN
EXEC('use' + @VarDBName + 'backup log'+ @VarDBName + 'with no_log
dbcc shrinkfile(''' + @VarDBLogicalName + ''', TRUNCATEONLY) WITH NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @VarDBName
SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF

[Zecho]

In previous versions of SQL, it was very difficult to (almost impossible) to shrink log file, howeer it is quiet possible in the later verions as follows:

Basically first import or export the data from the database and then you can shrink it. To import or export data use: "highlight the database->Tasks->Import data" or "highlight the database->Tasks->Export data" command respectively.

Now you can shrink the log file to the desired size lets say 5MB using: "highlight the database->Tasks->Shrink->Files" and then set the file size. Finally click OK to complete.