I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.
View 7 RepliesI want to move all indexes into new separate drive for this I need to create new .ndf file in particular drive.
Mirroring has configured for the DB but the principal and mirror servers do not have same drives.
I want to move indexes to new drive and the derive is not present in mirror server.
Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
Thanks,
Rick
I've been asked to configure a "hot-backup" server configuration.
However, I'm coming into the situation a bit later, and they would like me to accomplish the hot-backup with their hardware and their methodology.
Here goes.
We have two identical servers. They will both have a SCSI connection to a device refered to as an "EMC". It will be seen as a single drive letter "E:" to both boxes. Only 1 server will be "up" and using the drive at a time.
The idea is to have SQL devices - (oops, database files) located on the "EMC" drives and in the event of a failure of one server, we bring up the backup and it will take right off using the existing Database files (*.mdf, *.ldf files).
1. Can Sql server 2 come up using databases that have been modified
by server 1. (Provided Master database is on this common drive?
2. What about MSDB, can it be located on a drive different than
where SQL program files are located?
3. How about installing all of SQL application and DB's onto
the Shared Drive? Can a different server come up using the same files?
Anyone out there done/seen/heard of a similar configuration?
Any caveats
SQL 2000We took SQL Server offline last night and defragged the SAN. Should wereindex or will be be okay ?Thanks,Craig
View 2 Replies View RelatedMy data folder of SQL as filled up the space of my local drive. I have 150KB free space left in the drive. So I have to delete some records to regain space. But when I run the delete query it says transaction log is full and stops halfway. How can I free space? How can I delete the records? There is no available space to shrink as well.
View 6 Replies View RelatedTrying to install SQL Server 2005 beta version on partition disk drive sayF: (Other than local disk drive c:),Does anyone know how can I do this? During the installation, it never asksme on which drive it install to nor browse button is there to browse thedisk drive for installation...Any help???ThanksJ.
View 1 Replies View Relatedoutside of restore manageability, can multiple filegroups enhance performance when only one disk is attached? I guess I should also ask if it can hurt?
I have two use cases and am a bit unclear after reading lots of material on the subject.
My first use case is an OLTP consisting of a very stable set of "configuration" tables and a volatile transaction intake table with RI on the stable tables. I thought about putting the volatile table in it's own filegroup due to it's behavior but am second guessing myself after seeing that every author on the subject seems to hint that filegroups are only relevant when they map to different disks.
My second use case is an archive (readonly) that I will "range" partition due to volume. My first instinct was to create one filegroup for each partition but again I'm wondering what that will buy me in an environment with only a single disk.
Hi
I have a database(CEB) and my CEB.mdf is on D Drive and CEB.LDF is on
G DRIVE ...NOw I want to move the CEB.LDF on to the different drive ..
can any one suggest me the way and will I have any effect on the database.
It is kind of urgent.
Thanks
RAGHU
Hello All,
Can anyone be so kind as to turn me on to a script to move a database from spilt drives C: and D: to just drive D:. (we have one of those Dell's that comes with C/D partitions so we split the .dta files with a limit on the primary file, but the damn C: drive still ran low on disk space and now we can't install Win 2003 SP2 on it!)
thanks in advance
Bill
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?
We installed MS SQL Server to our drive D drive E was the CDROM Drive.We wanted to move the CDROM drive to drive D and the hard drive to driveE. We change that around and corrected the registry entries -repointing everything to drive E instead of D. SQL Server however willnot start it says it cannot find the databases. Where is thisinformation stored. How can I go about changing it?~Todd
View 2 Replies View RelatedI 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?
Hello all -
I have TFS installed on one machine, and the SQL Server database on another. I made the unfortunate mistake of installing SQL Server to the OS drive (C : ), so the TFS database is writing to this drive. How can I switch this to write to another drive (e.g. E:)?
thanks
I am trying to move a logfile from one disk to another. I've detached the database, moved the logfile to another disk and reattached the database. I used the sp_detach and sp_attach_db to specify the paths of the *.MDF and *.LDF. I've tried it through Enterprise Manager and with boths ways I get an error message saying: "Error 9003: The LSN (201:48:2) passed to log scan in database 'DatabaseName' is invalid". Does anyone know what this means and how I can get around this. I've even tried to attach the database with the logfile in the original path and I still get that error. Need Help Please!
Thanks
Hello,
I need move my SQL Server 2005 installation (Database Engine, Analysis Services) from one disk to another.
C:Program FilesMicrosoft SQL ServerMSSQL.1C:Program FilesMicrosoft SQL ServerMSSQL.2
to
D:SQL2005MSSQL.1D:SQL2005MSSQL.2
May you help me how do this?
--
Regards,
anxcomp
Hi,
I'm trying to move the transaction logs of my databases to a different drive (for fault tolerance). I can create a second transaction log file for each database via Enterprise Manager but I have 2 questions:
1) If two transaction log files exist for a database which one does it use ?
2) How do I force SQL to use the new transaction log file ? (so I can delete old)
Thanks,
Tim
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.
Howdy y'all! :)
I have been instructed to move a large database we have on one of our servers off the current drive (local RAID-5 driveset in the server) to a EMC "drive" (logical drive, off-server).
I know one option is to back up the database, delete the database, re-create the db using the new drive for data/log files, then restore the database.
However, I was wondering if it would be better to just detach the DB, move the data/log files, then reattach to them?
Is it half-doz of one, and 6 of the other?
How should I go about this dastardly deed?
Off to poke around in BOL, but thought I would post first in case it's an incredibly easy answer for y'all
Thanks!
I have a Windows 2003 server with SQL Server 2005 installed. Theserver is on small drive and we would like to upgrade to much largerharddrives. I've been hearing of problems using Ghost to get an imageand placing the image onto the new drive. I think this is more of aWindows 2003 problem, but this server is for nothing but the SQLServer databases. Does anyone have a clear method of moving thisserver to the larger drives?TIA.
View 3 Replies View RelatedI am new to sql server world as I am a controls engineer who is being asked to manage an asset management system at our facility. We have a system that uses SQL 2005 Express. My database is on drive C, but it is only 10gig and it is running out of space. I have a 210 GIG hard drive as drive D. What is the easiest way to move the database from C to D?
View 18 Replies View RelatedHello all ...
is there a standard procedure or document that explains how to:
Expand tempdb onto a faster drive ... making it larger
then ...
Remove the small portion of tempdb from my c: drive to reduce contention?
Thanks!
Doug
I want to move 2 databases to another physical disk. They are both single file databases. It looks like a fairly straigtforward attach and detach procedure, but I have a couple of questions:
The log files (.ldf) currently reside on a separate physical disk from the data files, if I attach and reattach the data files will the logs remain where they are by default or do I have to re specify there location?
Is there any advantage to running the update statistics portion of the sp_detach_db?
Hi all,
I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;).
Now I have to shift the log files to a seperate disk.
I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline?
Anycomments welcome.
Thankyou.
regards,
harshal.
I am using SQL Server 2008 (RTM) Standard Edition.
In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.But when we rebuild indexes, it requires some space on data and log files of database.how can we calculate disk space requirement for index rebuild process ?
I have a database [CarlosDB] that currently has it's .MDF on E: and I need to move the x2 .NDF data files off C: to E:data using a single T-SQL statement:
Code:
database_id file_id db_name disk_path status size read_only
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
7 1 CarlosDB E:dataCarlosDB.mdf ONLINE 384 0
7 2 CarlosDB_log L:logsCarlosDB_log.ldf ONLINE 128 0
7 3 CarlosDB_2 C:sqlCarlosDB_2.ndf ONLINE 128 0
7 4 CarlosDB_3 C:sqlCarlosDB_3.ndf ONLINE 128 0
(4 row(s) affected)
Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:
Code:
EXEC master.dbo.xp_cmdshell 'copy c:sqlCarlosDB_2.ndf e:dataCarlosDB_2/ndf'...
but cleanly using a single T-SQL statement? properly formatting a single T-SQL query to use the xp_cmdshell system stored procedure.
does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again
View 1 Replies View RelatedHello, I have been having a bit of trouble finding help on the safestway to move data files to a different disk on the same server. Mosthelp is about moving data files to a different sqlserver. I just wantto move the files to a different drive on the same server. Any helpwould be appreciated.Thanks,David
View 6 Replies View RelatedDo you have to move the entire content of the log folder to the new location?
View 4 Replies View RelatedWe are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
Hi,
I need to separate the data and indexes onto different drives.Currently they r all on same drive..Now i need to move indexes onto a separate drive.There are no file groups now except the default.
I plan to create a file group on the new drive.I know if i Drop clustered indexes and recreate them on the new file group,it will separate the data pages from index pages.But the data pages need to be on the same drive as it is now.So i need to move index pages or non-clustered indexes only on to the new file group on the new drive.Even this can be done by choosing a different file group for the non clustered index in Enterprise manager.But this very cumbersome if there exists many tables as is the case now like 300 tables..
My Question is,
1.Is there any way to script only indexes so that it includes drop and create commands to be created on new file group???
2.Is there any other way to do this..
Please let me know if any body worked on this before..I really appreciate if some body can give me info on this..
thanks
Vish
I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.
Thanks in advance.
Brad Keck
Scenario:
We run a multiple database environment, with two of the databases receiving most of the user activity. (both write and read). These databases are roughly 25gb each and receive roughly the same amount of activity. Currently both of the .mdf files sit on the same drive shelf. Their log files are located on a separate drive shelf.
Debate: We have an extra fiber channel shelf available for us to use. We are not having too many problems related to performance, but we are always seeking for different ways to increase application/server performance. The debate centers on what to do with the extra shelf. There are two different suggestions on how best to use the shelf. They are:
1)Separate the .mdf files for two most utilized databases. This would separate the databases and the I/O associated with each across two different shelves
2)Break off the indexes for all 5 databases on to the extra shelf. This would leave all the .mdf files on the same shelf, but it would move the I/O associated with the indexes to a different shelf.
Can anyone provide the pros and cons of either suggestion?
I would like to see arguements for either side.
Dave