[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transaction logs path in SQL 2005

Posted on 2006-05-22
8
Medium Priority
?
162 Views
Last Modified: 2008-02-01
In SQL 2005 ,how can I change the path where the transaction logs are saved.Also is there a way that I can control the size of these logs.So that it doesn't fill up the disk.
0
Comment
Question by:life_j
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 39

Expert Comment

by:appari
ID: 16739248
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 225 total points
ID: 16739249
1.Dettach the database by


USE master
go
exec sp_detach_db 'databasename'


2. copy the log file to desired destination
3.Attach the database, this time mentioning the new location for ldf.


EXEC sp_attach_db @dbname = N'databasename',
   @filename1 = N'<path>\databasedata.mdf',
   @filename2 = N'< new path>\databaselog.ldf'
0
 
LVL 7

Author Comment

by:life_j
ID: 16739659
Is there a way I can do this for the whole of SQL server databases instead of changing for each database.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Author Comment

by:life_j
ID: 16739663
Aneesha

I am new to SQL server.

Where do I need to run

USE master
go
exec sp_detach_db 'databasename'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16739729
in a new Query window
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16749005
>>I am new to SQL server.
>>USE master
>>go
>>exec sp_detach_db 'databasename'

You have to run this command in sql management studio after clicking on new query
or
You can simply right click on your database in sql mangement sudion all task and then detach.


0
 
LVL 7

Author Comment

by:life_j
ID: 16766528
Is there a way that I can change the transaction logging for all the databases.
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 150 total points
ID: 16774480
No,
 you have to either detach and then copy the file for each database on different location and then attach it.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 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