Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Services / Notification Services / October 2009

Tip: Looking for answers? Try searching our database.

Transaction log file

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
smca - 21 Oct 2009 21:09 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.