April 24, 2002 at 5:39 am
SQL Server 7.0
The Size of my Transaction Log is 1.5GB and I would like to delete them to clear some disk space. So I have planned to do 1 of the following steps and I would like to know which one is the best option:
1. use sp_detach_db and detach the database, delete the .ldf file and reattach the database. This will create a new .ldf
[or]
2. Use Enterprise managers Truncate Log option
[or]
3. Use DBCC Shrinkfile to reduce the size of the transaction log.
[or]
4. Dump Transaction <db name> with no_log ?????!!! I dont know what this option really do?
After either one of the above process I am planning to check the option "Truncate Log on CheckPoint".
What I would like to know is which one of the above option is the best option?
Before I do anyone of the above option I am planning to stop the sql server, copy the .mdf and .ldf files to another drive as a backup process. I dont do any tape backup process because we dont have one. Usually I copy the .mdf as a backup process. In case of any failure I can attach the .mdf as a new database.
please let me know your views.
April 24, 2002 at 6:00 am
With SQL 7 none of those may free the log to allow it to shrink even thou the data is dumped. However here http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26 is a script to force the log to shrink. Setting the log to truncate on checkpoint is fine as long as you are not doing log backups or need to recover a failed server with the log (in other words it is ok to lose data since last full backup). If you need to keep transactions log data then I suggest create a weekly process to shirnk it after the last non-full backup and then do a full backup after you shrink it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 04/24/2002 06:02:35 AM
April 24, 2002 at 6:34 am
Detaching the db and deleting the log file does effectively reduce the size - all open transactions have to be completed before the db can be detached. Stopping the service to copy the files is overkill, just run a full backup to disk at whatever interval seems appropriate.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply