Registered users    
MembershipMembership:
Latest New UserLatest:Maurice Tutor
New TodayNew Today:5
New YesterdayNew Yesterday:7
User CountOverall:23064
Private messaging    
You must be logged in to use this module.
Top 10 posters    
NamePosts
Mariette Knap12622
Marina Roos12280
Eriq Neale2105
Michael Patrick1906
Stan Guinn1840
Robert Pearman1724
Nick Pieters1425
Stewart Brown609
Kevin D.563
william warren548
Welcome unauthorized visitor    
If you want to join us in the discussions on this forum you need to register first. Registration is free! If you are already a registered user please login to join the forum.
Small Business Server Support Forum    
Forums > Microsoft Smallbusiness Server > SQL Server 2000
Subject: How to purge transaction log file
Prev Next
You are not authorized to post a reply.

Author Messages
Lloyd Wolf User is Offline
United States
Member since
9/23/2006

Platinum Membership
Posts: 133
10/16/2007 08:35 PM  

Hello. I am looking for a little help.

I am fairly experienced with SBS, Microsoft Windows Server and Exchange Server, but I am much less familiar with the details of Microsoft SQL Server.

We just started working with a new client.  They are running an application that is using SQL Server 2000.

They have a database file that is about 3GB, and the transaction log file is about 70GB.  I made a backup of the database (Enterprise Manager->right-click on database->All Tasks->Backup Database). This completed successfully, but the transaction log file did not get any smaller.

What do I need to do to purge the log file for this database? 

Thanks very much, in advance.

--
Lloyd Wolf (MCSE)
Wolf Consulting, Inc. (Microsoft Gold Certified Partner, Pittsburgh PA)
Lloyd Wolf User is Offline
United States
Member since
9/23/2006

Platinum Membership
Posts: 133
10/16/2007 08:44 PM  
one more note.... I read some articles that said performing a back-up the transaction log files would purge the log files. But in this case, I do not have enough disk space availabel tobackup the transaction log files (>70GB).

Lloyd
Mariette Knap User is Offline
The Netherlands
Member since
3/24/2005

Forum Admins
Posts: 12622
10/16/2007 08:46 PM  
Lloyd,
 
It depends a bit on the type of application but if you set that database to 'Simple' instead of Backlogged it will not grow that much...
Mariëtte Knap Smallbizserver.Net AdministratorMission accomplished. We have joined the branch office to our SBS 2003 Headquarters and have the same user experience on the branch office as we have on our local  network at the Headquarters. Want to know how? Signup up for a subscription and get instant access to the article series 'How to add an additional Domain Controller from a remote office to the SBS domain'
Mariette Knap User is Offline
The Netherlands
Member since
3/24/2005

Forum Admins
Posts: 12622
10/16/2007 08:49 PM  
Also read this:
 
How to stop the transaction log of a SQL Server database from growing unexpectedly:
http://support.microsoft.com/kb/873235
Mariëtte Knap Smallbizserver.Net AdministratorMission accomplished. We have joined the branch office to our SBS 2003 Headquarters and have the same user experience on the branch office as we have on our local  network at the Headquarters. Want to know how? Signup up for a subscription and get instant access to the article series 'How to add an additional Domain Controller from a remote office to the SBS domain'
Edwin Sarmiento User is Offline
Singapore
Member since
2/13/2007

Microsoft MVP
Posts: 133
10/17/2007 09:42 AM  
I wouldn't purge the transaction logs unless you really, really need it. If I were you, do a full backup of your database. Next, execute this script in Query Analyzer

BACKUP LOG your_database_name
WITH TRUNCATE_ONLY

After running this statement, your transaction log file will still be the same size. This command will only truncate the transaction log. In order to shrink the transaction log files, use Enterprise Manager, select your database > All Tasks > Shrink Database. Under the Shrink Files section, click the Files button. On the Shrink File window, click the Database File dropdown and select the option with the File Type:Log File. Specify the size you want and click OK. I don't really recommend changing to SIMPLE recovery model for disaster recovery purposes.
MCP MCDBA MCAD MCSD MCT MCTS MCITP : DBA MCSA
http://bassplayerdoc.blogspot.com
Need a remote SQL Server DBA?
Mariette Knap User is Offline
The Netherlands
Member since
3/24/2005

Forum Admins
Posts: 12622
10/17/2007 10:04 AM  
Thanks Edwin!
Mariëtte Knap Smallbizserver.Net AdministratorMission accomplished. We have joined the branch office to our SBS 2003 Headquarters and have the same user experience on the branch office as we have on our local  network at the Headquarters. Want to know how? Signup up for a subscription and get instant access to the article series 'How to add an additional Domain Controller from a remote office to the SBS domain'
You are not authorized to post a reply.
Forums > Microsoft Smallbusiness Server > SQL Server 2000 > How to purge transaction log file


ActiveForums 3.7
Forum policy    
These Discussion Forums are dedicated to the discussion of the Small Business Server and related server and client software. For the benefit of the community please observe the following posting guidelines:
  1. No Advertising. This includes promotion of commercial products and non-commercial products which are not directly related to Small Business Server and related server and client software.
  2. No Flaming or Trolling.
  3. No Profanity, Racism, or Prejudice.
  4. Site Moderators have the final word on approving/removing a thread or post or comment.
Copyright 2002-2008 Concentrix b.v.    :   Page generated in 0.2500032 seconds.   :   Terms Of Use   :   Privacy Statement