Hello SQLServer gurus,
We created the database with transaction log files and datafiles on the same drive. Now we want to change the transaction log file
location to a different drive. Please let me know the steps to follow. THanks...
I'm setting up a new server and would like the default location for the transaction logs to be on a different drive from the databases. Seems like the best way to do this would be to configure the Master database log location for the drive & directory where I want all trans logs stored. So far, I've been unable to accomplish this. I know I can specify the log location manually each time I create a database, but I'm hoping to configure a default. Is this possible to configure? And if so, how?
Environment is W2k with SQL7.
TIA, Sara
Is it possible to move .ldf's to another drive. For example I would like to move a database transaction log file from C:mssql7data to D:mssql7logs.
How can this be done?
TIA!
JJ
Hello,
In process of installing TFS, I unintentionally installed SQL server on C drive as I could'n see any option of selecting the drives.
Is there any way in which I can move the database folder to another drive without reformatting and going through the same procedure.
TIA
Hello all..
I need your precious expertise in resolving one problem . I have transaction log file devices created on on two drives i.e c and d drives .
I want to move the the log device from d drive to c drive or vice versa .
How can i do it . Can somebody help with detailed steps pl .
What are the things necessary to check after the device has been moved to either of the locations , to ensure that everythingh works well .
I have SQL Server 7.0 with SP 3 .
Will the database be marked suspect ? I that case what should i do ?
Any kind of help on the issue will help me a lot .
Thank you all in advance
Annalina .
How do I move the location of the tempdb. I wish to place it on a separated
disk to to increase performance.
Parg
I've got some users that created a database with the log file on a drive that doesn't have a lot of space. I'd like to truncate the log and move it to a different drive. I can truncate it, but is moving it as easy as changing the files properties through SSMS?
Thanks
I currently have about 4 databases on our SAN located in one of the drives. These databases are going to expand massively and I want o seperate 1 onto seperate drives located on the SAN. I figured using SQL Server Management Studio I could complete this with an easy "Detach / Attach" operation. When I go to attach the files back into SQL, it doesn't read any other drive other than the current drive all of the databases are located on.
Is there a way to do this?
Please share any experience and/or suggestions on moving the T-logs to a separate physical drive. What I have read so far suggests that the best way is to sp_detach, physically copy (not move) the logs, and then sp_attach again.. but some questions are unanswered.
For some reason, everything I've found has you copying both .mdf and .ldf files to the new drive... I guess that was just an oversight on the part of the individuals who wrote the documents.. can't imagine wanting to copy the .mdf files too.
When you sp_attach, must you specify the original location for the .mdf files as well as the new location for the .ldf files? Do you have to go into server properties, Database settings/ and change the location of 'default log directory' manually?
Is there any easier way to move the T-logs?
JJones
Hey guys i want to relocate my database datafile and transaction logs from C: drive to D:
From what i have in mind , correct me if i am wrong: First I will create the same folder on D drive as they are on C drive then copy the datafile from C to D , then come back and change the paths on the database files to point on D.
Hi, I am working on a new installation which I did not set up and realized was using the wrong partition of the server to store the data and log files, I have already created several databases, I want to use another partition for these databases without having to drop them and create them all over again.
In BOLine i saw this command but want to make sure its safe, hope somebody can comment on this or if I am missing something.thank you
proposed command:
"MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path/os_file_name ' )"
Running SQL Server 2000. I have been asked to move the transaction logs to another drive. I was able to do that for the user databases using detach/attach.
However I don't see that as an option for moving the logs for the system databases.
Is there any way I can move the system database logs? I'm guessing it might be possible through a restore.
Any thoughts ?
Thanks in advance.
Gerry
Running SQL Server 2000. I have been asked to move the transaction logsto another drive. I was able to do that for the user databases usingdetach/attach.However I don't see that as an option for moving the logs for thesystem databases.Is there any way I can move the system database logs? I'm guessing itmight be possible through a restore.Any thoughts ?Thanks in advance.Gerry
View 2 Replies View RelatedI'm using SQL Server 2012 and was attempting to move the msdb, model, and tempdb databases to a new location and accidentally gave their log files an mdf extension instead of ldf when providing the new pathfilename. After the server wouldn't start I checked my script and noticed my error. I have good backups of my system databases, so I was hoping to start the MSSQLSERVER service in single-user mode (using the -m startup parameter) and then just restore master using sqlcmd.
Unfortunately the service was starting but I couldn't connect via sqlcmd using any of the three protocols (it said the server was not found or not accessible each time). I also tried using the dedicated Admin connection but I got the same error. Then I went into the Templates folder and copied the master, msdb, model, and tempdb templates into the DATA folder and tried to restart SQL Server but still no luck (now the MSSQLSERVER service won't start at all). Is there an easy way to fix this mess without having to reinstall from the setup application?
version:SQL Server 2000.
db size: 25.6GB
trans log file: 32GB
so far: I have read the Forum FAQ on "clearing the transaction file" and some DBCC SHRINK in Books Online.
I am trying to set up maintenance plan or backup plan. File sizes are getting very large and performance is horrible since we have had no regular maintainence in the past (No DBA in house).
1. What is the best way to identify the location of the transaction log file associated with a particular database?
When I right-click on the database name in Enterprise Manager and select properties, I can see one location for the transaction log.
However, If I right-click on the database > All Tasks > Shrink Database > click Files and select Temp_Table_Log, I get a different location.
2. We delete then repopulate about 105000 records in one particular table each day. In addition, we do the same with about a hundred rows in several other tables daily.
-- Should I be doing Full Backups nightly?
-- I have the option set to "AutoShrink" on the db. Will this truncate and shrink the transaction log as well as shrink the db when I do a full backup?
Thanks,
Più pranzo libero!
What is the best way to clear the transaction logs. My backup job each night is ending because it says it is running out of disk space and I need to clear up the transaction logs. Any help is appreciated. I see many different options (trucate option, auto shrink, etc.), just need some assistance tosome more specific best approaches. Thanks
View 3 Replies View RelatedIm having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.
Regards
SeTi Ni
When we backup the transaction log,will it truncate the log or it will grow from there?Truncate log on check point is desabled.
Thanks!
to take the transaction log back up regularly I should have truncate log on check point false. If I do so then how will I truncate the log.
regards,
Renu
I am trying to import records via bcp (about 1,500,000 records) and I keep running out of disk space. Is there any way to limit or do away with the transaction log (and still be able to import)?
View 2 Replies View Relatedhow do i decrease the transaction log example 500mb to 100mb, can you help me with the syntax pls!!
View 1 Replies View RelatedI need to move transaction logs to a different drive and am looking for a way to do it without detaching the db.
Can I create a second transaction log file for the DB and then delete the original log file?
What is the best way to do this for db's that are being replicated?
Thanks,
Ken Nicholson
I need to seperate my SMS data files and transaction logs onto two seperate drives. Can anyone assist me with this?
thanks...
HELP!!!!
When I look at the Database maintience plan history entry for backup I have a message that reads: "Backup can not be performed on this database. This sub task is ignored".
Have anyone come across this error before?? As part of the Maintenance plan some transaction log are being backup and some aren't instead they receive the message above.
Thanks
Lystra
Has anyone ever had the problem of the IT department not backing up thier transaction logs simply because they didnt find it necessary??
View 3 Replies View Relatedi have several sql servers doing maintenance plans and backing up the transaction logs to tape. unfortunately it seems that the server keeps adding the transaction logs to the same tape, without overwriting them. Nowadays a transaction log backup to disk takes 2 minutes but when done to tape it is taking up 1hr54minutes. What can i do so that the tape is automatically initialised without having to do it manually... Thanks
View 1 Replies View Relatedi was wondering if smeone could give me a breif over view how far back do these go
View 12 Replies View RelatedHI All,
Is this is a good idea or not?
If i attached my SQL Server to SAN only for DATA and LOG (Ldf) drives
through the FC. then we put *.bak and *.trn on to local disk RAID 1 drive?
Any performance isues?
Is there a way to view the transactions from a .TRN transaction log file? If so can I overwrite some of the transactions on the file and then restore from it? I am just curious.
View 2 Replies View RelatedHi, I know just about nothing about SQL Server. I am getting this error:The log file for database 'my_database' is full. Back up the transaction logfor the database to free up some log space.I can't access the transaction logs to back them up. I am told that my ISP'stech support should have it set up to shrink those logs automatically everyso often. Is that true?Why are they needed? Up till Monday, the logs are just of our getting SQLServer set up, so couldn't the logs just be deleted? Most of my site can'trun with this problem.I'd appreciate any enlightenment anyone can give me!Thanks, Jill
View 1 Replies View RelatedThis seems like it'd be a really stupid question, but for the life of me Ihaven't been able to find an answer that works....I have a database that's approximately 400MB when shrunk... and thetransaction logs are at about 4.8GB when shrunk... I can't seem to get thetransaction log any smaller, no what I try. How can I get the log downbelow 1GB and keep it there? I've only got an 18GB hard drive and I need torun 2 copies of this database.-steve
View 4 Replies View RelatedI asked this before and was told that it was an option on setup, but for the life of my I can't find it.
I have a requirement for all the transaction logs to go to a separate drive (L:), I would like to be able to specify this location as part of the install but I don't see the option. I have been successful doing so after the install but it is a bit annoying to have to do it that way when it seems like the install should have this option.
I am reaching out once more to see if anyone can tell me if I am missing something in the install process that will allow me to do this?
Thanks in advance.
Future guru in the making.
Hello,
I have a requirement to have all the transaction logs for our SQL Server databases go to a drive letter L: - I would like to do this during the install process but I don't see any way to specify in the setup. I am able to go in and do it after the install but it is a bit of a pain to do.
Anyone know how I can do this in the install itself?