Database log file

[Tomi]

Hi,

The transaction log for my database have grown so huge. I don’t want to keep the transactions , but want to trim it and put in on simple recovery mode. I would like to create fresh log file for the db. If I stop the services , detach the database and attach only the mdf file, will I loose any data ? . I have tried this and it worked but not sure if I will loose anything etc…

Thanks
Tomi

 

[mrdenny]

You shouldn't loose anything doing this, but you shouldn't use this method to shrink the log file.

Attaching the database and creating a new log file is an emergency method to attach a database which has a corupt log file. It's not a file size managment tool.

First put the database into Simple Recovery mode.

ALTER DATABASE UserDB
set RECOVERY SIMPLE
go

Next purge the log.

backup log UserDB with truncate_only
go

Next shrink the log file down to a more managable size (I like at least 512 Megs in the log).

Use UserDB
go
dbcc shrinkfile (LogFileName, 512)
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com