How To: Shrink Sql Server 2005 Log Files

Shrink Sql Server 2005 Log Files

use YourDbName
backup log YourDbName with truncate_only
dbcc
shrinkfile(YourDbName_log)

if you copy your databases, names are not always the same! based on how do you copy it it still has the same name like the source database.. keep attention when you run it - then it shrinks e.g. 130 GB -> 22 MB .... many information goes away.

Update:

========

today i done some researches and I found the following thread:

http://www.eggheadcafe.com/software/aspnet/30488923/script-which-will-shrink.aspx

after some analyzing and test runs the follwoing solution worked for me:

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

(otherwise search at google for: "osql shrink all db" in it doesn't fit your needs)


the above script you put now simply into a *.sql file (e.g.: shrinkalldatabases.sql).

now you need to create a second, which is simply a batch file (e.g.:startshrink.cmd) : *.bat or *.cmd. Into this file you write the following command:

osql -E -i shrinkalldatabases.sql -o result.txt

more info about osql you can find here: http://technet.microsoft.com/en-us/library/aa213088(SQL.80).aspx

all needed parameters are the following:

-E -> authentication
-i -> input file
-o -> output file

once you add it now to run once a week you have no further problems with your sql db sizes :-)

to have a small history and some additional information, here how I have created my *.cmd file:

@echo start: %date% %time% >> result.log
osql -E -i shrinkalldatabases.sql >> result.log
@echo end: %date% %time% >> result.log

enjoy it.