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 / General / Data Warehousing / June 2006

Tip: Looking for answers? Try searching our database.

Shrinking transaction log file in sql server 2000

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ganapathiraman - 28 May 2006 06:04 GMT
I need to shrink the Transaction Log file only  (weekly or monthly) to
certain size automatically ( as a scheduled process) in the Sql Server 2000.
we are taking database backup daily. Please advice, if there is any option
available.
Immy - 28 May 2006 16:34 GMT
Absolutely!
You can go 2 ways.
1. Write your own schedule and use the TSQL to Shrink the File.
2. Use the inbuilt feature of Enterprise Manager and you will be given the
option to shrink based on a schedule.
(right click on the DB in question, go to All Tasks, Shrink Database. In
there you will see the obvious option to schedule it.)

Immy

>I need to shrink the Transaction Log file only  (weekly or monthly) to
> certain size automatically ( as a scheduled process) in the Sql Server
> 2000.
> we are taking database backup daily. Please advice, if there is any option
> available.
Brian R - 24 Jun 2006 20:30 GMT
I believe you need to shrink the logfile rather than the database. I have
come across this a number of times and there may be an excellent
alternative, but this works fine for me. You need to do a full database
backup first. The log file is then effectively redundant. You detach the
database (right click database name in Enterprise manager and use detach).
The data files remain on the system. Leave the data file alone, but delete
the log file (BE CAREFUL HERE) using windows explorer. In Enterprise manager
right click on server and all tasks then attach. Choose the data file. It
will show a missing log file but will create it when you attach the log.
This creates a log from scratch and you can even resize it before you give
it back to the customers.

Not entirely sure why a log grows huge even though you back it up (and
therefore truncate it), but it is to do with not clearing the virtual log
compartments.

Signature

Brian Reincke
Parity Training
Lonbdon

> Absolutely!
> You can go 2 ways.
[quoted text clipped - 12 lines]
>> option
>> available.
 
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



©2008 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.