Transaction log file

[smca]

How can i delete a transaction log file in sql server 2005

I need to delete an old and big file and create a new one with default size

Thanks

SA

[Russell Fields]

SA,

You cannot delete the primary log file, which is probably what you have.
You can shrink it by using:

DBCC SHRINKFILE (Database_Log, <size in megabytes>)

For guidance, see: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

RLF

"smca" <sergiom...@gmail.com> wrote in message
news:%23i6Olro...@TK2MSFTNGP06.phx.gbl...

[Erland Sommarskog]

smca (sergiom...@gmail.com) writes:
> How can i delete a transaction log file in sql server 2005

DROP DATABASE. Yes, that will get rid of the entire database, but
that is exactly what you are asking for. Never delete a log file, unless
you want to lose your database.

> I need to delete an old and big file and create a new one with default
> size

First decide what kind of recovery you need in case of a disaster. Are
you content with restoring from the last good backup? Or do you need
up-to-the-point recovery. In the first case you can set recovery to simple
and then use DBCC SHRINKFILE to shrink the log to a reasonable size, for
instance 1 GB. (But what is a reasonable size depends very much on how the
database is used.)

If you need up-to-the-point recovery, you better study closely how
BACKUP and RESTORE works, because right now you are heading for a disaster.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Uri Dimant]

smca

Well, Rassel and Erland have alredy answered you. I would like to add

> I need to delete an old and big file and create a new one with default
> size

Erland is correct, you need to drop the database , as you said 'an old' log
files means that db is not in used anymore, right?

However , if the db is still in use you need
1) BACKUP LOG file
2) DBCC SHRINKFILE

"smca" <sergiom...@gmail.com> wrote in message
news:%23i6Olro...@TK2MSFTNGP06.phx.gbl...

[Uri Dimant]

Sorry, should be Russell

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:%23UwPxnu...@TK2MSFTNGP02.phx.gbl...