?
Solved

How do you delete transaction logs to free up disk space in MS SQL Server 8.0?

Posted on 2006-05-04
22
Medium Priority
?
430 Views
Last Modified: 2008-02-26
GooD Day Experts,

How do you delete transaction logs to free up disk space in MS SQL Server 8.0?  I just ran and scheduled backups and the size of MS SQL server folder didn't go down.

Many Thanks,

Ellsworth
0
Comment
Question by:ellsworth2000
  • 9
  • 7
  • 4
20 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16608298
It is not a good practice to clear the transaction log

Run the following in QA to make sure that there are no open transactions ,

dbcc OPEN

Then,

BACKUP LOG urDB
WITH TRUNCATE_ONLY

DBCC SHRINKFILE(yourLogFile, EMPTYFILE )
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608335
0
 

Author Comment

by:ellsworth2000
ID: 16608430
how do i know what my log file is aneeshattingal?

THanks,

Ellsworth
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 

Author Comment

by:ellsworth2000
ID: 16608492
OKay so I'm thinking about just uninstalling MS SQL server and re-installing on to a newly added hard drive.  Is is just the C:\Program Files\Microsoft SQL Server\MSSQL\Backup\MyDatabase.BAK file that needs to backed up so i can transfer over the SQL Database?

Many Thanks,

Ellsworth
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608507
sp_helpDB 'urDbname'
will give you the database files
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608532
If you need to move the database, then the better option is to copy all the log and mdf files (before copying,  stop the sql server ). Then paste it ti the new sql server data path, and attach the database
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608552
Otherwise you can take the backups of all the user databases and restore it in the new sql server installation..

ALso read this

http://vyaskn.tripod.com/moving_sql_server.htm
0
 

Author Comment

by:ellsworth2000
ID: 16608597
anneshattingal,

where do i type sp_help 'urDbname'?

and where are the log and mdf files usually stored?

THanks,

Ellsworth 2000
0
 

Author Comment

by:ellsworth2000
ID: 16608610
Also how do you tell SQL server the new datapatha and how do you reattach the databases?
   
Many Thanks,

Ellsworth
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608631
ellsworth2000,
> where do i type sp_help 'urDbname'?
it is not sp_help , it is sp_helpdb 'urdatabaseName'
Run the above in Query Analyzer

Once you run this, it will give you tha path where the mdf and ldf are stored
default path is
C:\Program Files\Microsoft SQL Server\MSSQL\data\                                                                                                                                                                                          
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608658
ellsworth2000,
>  how do you reattach the databases?
its easy,
use  enterprise manager->expand the servers -> databases
right click ->attach database
browse the mdf files one by one .. that's it
0
 

Author Comment

by:ellsworth2000
ID: 16608690
Do you have to detach the database before moving it or just stopping the MS SQL Service will be enough?

THanks,

ELlsworth
0
 

Author Comment

by:ellsworth2000
ID: 16608737
ALso how do i access the query analyzer?
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16608810
In Enterprise Manger Tools->SQL Query Analyzer
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16608819
<<Do you have to detach the database before moving it or just stopping the MS SQL Service will be enough? >>

You have to dettach it. You cannot move while the files are in use.
0
 

Author Comment

by:ellsworth2000
ID: 16608835
how do you dettach the database before you move it?

Thanks,
Ellsworth
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16608862
From enterprise manager->expand the servers -> databases
right click ->detach database
0
 

Author Comment

by:ellsworth2000
ID: 16608932
OKay so I do this in this order to move my database?

1.)  Detach database.
2.)  stop MSSQL service
3.)  Copy .mdf and .ldf files to new location
4.)  Restart MSSQL Service and re-attach the database.

Thanks,


Ellsworth
0
 

Author Comment

by:ellsworth2000
ID: 16609170
Aneesh and Sirees,

Or, If i am going to just un install and re-install SQL i just need to make a backup of just my database and then once MS SQL server is re-installed i can restore the backups right?

Manyt Thanks ELlsworth
0
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 1000 total points
ID: 16609380
<<Or, If i am going to just un install and re-install SQL i just need to make a backup of just my database and then once MS SQL server is re-installed i can restore the backups right?>>

Yes.

But the easy way is

1. Backup your database (just for safety)
2. Dettach the database
3. Move .mdf and.ldf files to new location
4. Attach the database

You don't have to stop SQL Server Service.



0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question