SQL Log files too big!

[ninhovid]

hi everybody...

i'm having some space problems...
i have a database whose log file is almost 7 gb big! (.ldf)
how can i reset it? it doesnt matter if i can restore it from logs... i do backups everday (.BAK) and i would restore it that way..

thanks in advance...

 

[mrdenny]

If you don't need to be able to restore the logs change the database from full recovery to simply recovery. Then run the following.

backup log {DatabaseName} with truncate_only
go
use {DatabaseName}
go
dbcc shrinkfile ({LogFileName}, 200)

Change the 200 to what ever size it should be.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[ninhovid]

ok, i'll look for the simple recovery option, and then i'll run your code...

i guess its 200 mb right?
thanks..

is this dangerous?
or not too much because i plan to restore from bak files?

thanks again...

 

[ninhovid]

mmmm.. i'm getting this error message...

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'db_test' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

what would i be doing wrong?

 

[mrdenny]

You need the name of the log file.

select *
from sysfiles

The file with the id of 2 will be the log.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[ninhovid]

yeah!
it did it and very fast...
thank you
=)