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 - 21 Oct 2009 21:20 GMT
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
> How can i delete a transaction log file in sql server 2005
>
[quoted text clipped - 4 lines]
>
> SA
Erland Sommarskog - 21 Oct 2009 22:54 GMT
> 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.

Signature
Erland Sommarskog, SQL Server MVP, esquel@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 - 22 Oct 2009 08:34 GMT
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
> How can i delete a transaction log file in sql server 2005
>
[quoted text clipped - 4 lines]
>
> SA
Uri Dimant - 22 Oct 2009 08:39 GMT
Sorry, should be Russell
> smca
>
[quoted text clipped - 18 lines]
>>
>> SA