Google Answers Logo
View Question
 
Q: SQL Server 7.0 Transaction Log - truncate and shrink ( No Answer,   3 Comments )
Question  
Subject: SQL Server 7.0 Transaction Log - truncate and shrink
Category: Computers > Internet
Asked by: grantant-ga
List Price: $10.00
Posted: 07 Dec 2005 15:05 PST
Expires: 06 Jan 2006 15:05 PST
Question ID: 602852
I'm running a database in SQL Server 7.0 and it currently has an mdf
file that is 4 Gigs and a transaction log file that is 8 Gigs. Why do
I have such a big log file. What can I do to reduce the amount of
space this database is taking up. I've done a "shrink" through
Enterprise Manager but it didn't do much. Do I need to truncate the
data in the log somehow?
Answer  
There is no answer at this time.

Comments  
Subject: Re: SQL Server 7.0 Transaction Log - truncate and shrink
From: europria-ga on 08 Dec 2005 09:16 PST
 
1.
A Back-up should commit all the data in the transaction log. If you
still have 8GB file you can try shrink at that point.

2.
If 1 does not work, unattach the database, delete the transaction log
and reattach it again.
Subject: Re: SQL Server 7.0 Transaction Log - truncate and shrink
From: guysoffer-ga on 12 Dec 2005 08:36 PST
 
the above comment is basically correct.

the commands are (under the sa username in query analyser):
exec sp_detach_db 'dbname' to detach
exec sp_attach_db 'dbname' to attach

please note that while the DB is detached it is NOT available to anyone.

Another thing to do - to prevent this problem in the future, is to
check the "truncate log" option under the enterprise manager otions
for this log file.
Subject: Re: SQL Server 7.0 Transaction Log - truncate and shrink
From: lordmichaels-ga on 12 Dec 2005 12:51 PST
 
A log of this size will usually require a lot of backups until it is cleared.
If you don't need the log for anything, you can convert the database
to simple recovery mode, which doesn't create a transactional log.
To do this, get the database properties up in enterprise manager, go
to the last tab, and you will find the option to set the recovery
model.
After you have set this you should be able to delete the log.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy