Anyway, I have a question and hope to receive some suggestions.
We have a cluster installation of SQL 2000 Ent SP3a and we had a logical drive Y: on a second incative node: N2. SQL Right now is running of the active node: N1. We moved the Y: drive from Node N2 to the N1 one and it's fully functional and healthy. The only thing SQL doesn't see it. So ti has to be restarted.
Is there a way to refresh a list of logical drives on SQL without having the service to be restarted? (it's not an option for us meantime, it's our prod env.)
In 6.5 , I was NOT able to use mapped network drive for new database device if the MSSQLSERVER service is running under local system account, but if I changed it to using a domain a/c which has the authority to create files on mapped drive, I was able to do it.
But if I tried it in 7.0, it never work and got msg: Server: Msg 5105, Level 16, State 2, Line 1 Device activation error. The physical file name 'e:est2.mdf' may be incorrect. Server: Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
Is it something that's normal for 6.5 and 7.0 ???? i.e. we can only create databases on LOCAL drives ? I am just thinking of in case we run of space locally, we can temporarily make use of remote mapped network drive. (say at time of upgrading)
I've added a new drive to a clustered instance of sql 2000. Go to the node the instance is active on, the drive shows up. But...try to add a backup device that lives on the new drive (which was, after all, the whole point of the exercise) and SQL Server is blind to that drive. I have vague recollections of it recognizing the clustered drives when i set up all this a couple years ago. Could it be that it recognizes all the drives it'll ever recognize at the moment of installation? Anybody know?
I have configured a cluster SQL 2005 (active/passive) and added 4 LUNS to that cluster the cluster works without problems. However I am having issues backing and restoring on 2 drives. I can complete the operation ( a resotre of 100MB ) DB in less than a minute if i use the 2 drives but it takes about an hour if i use the other 2 drives. The wait type is ansynchronous_IOcompletion and backupthread in dm_exec_requests.
I have detached the drives and reformatted it but stil lsame issues any help
We have a simple two-node x86 failover cluster attached to a SAN on which SQL Server 2005 runs. We recently bought two new x64 boxes to upgrade the cluster. My original plan was to just add the new nodes to the existing cluster and then remove the old nodes from the cluster, but I just found out that you can't mix architecture types in a cluster.
So far, it seems our choices are:
1) Install x86 Windows 2003 Server on the new nodes, losing performance. 2) Make a new cluster and migrate the data, possibly imposing downtime.
As part of a migration of data to a new SAN I have hit a bit of a snag in the migration. In summary what will happen is user database data files will be moved from one LUN (say drive F:) to a new LUN (say drive G:). Once all the data is migrated, plan is to remove dependency of that drive from SQL server and remove the drive and delete the LUN. So far, so good.
However one of the LUNs (drive D:) destined to be deleted also hosts the instance default directories, i.e. everything under MSSQL11.MSSQLSERVER (Data, Backups, FTData, JOBS, etc). BOL has articles on how to migrate system databases, including tempdb. But there is no guidance that I could find on how to relocate other folders. There are forums where users have listed registry changes, etc that can achieve this but these are steps I am unwilling to take on a production server.
So my plan is: 1) Add new drive to cluster (drive E:), sufficiently large enough to host instance default folders 2) Shutdown SQL server 3) Copy all default folders to new drive 4) Swap drive letters so that new drive is now D: 5) Start SQL server and if everything works, delete the original drive (which is now drive E:).
I'm trying to build a new cluster with SQL Server 2K Enterprise onWindows 2003 server. When I run the install, I tell it to put theprogram files on the D: (local) drive and the data/log files on shareddrives. However, after the installation is complete, almost all thefiles go in the C: drive in program filesMicrosoft SQL Server, ratherthan the D: drive (it does create a few files on D:). We've experiencedthis on more than one server build.Anyone know any way around this?Post answers publicly, please, or email tosteven.wechsler(at)mtvstaff.com (the posting email address is hosed).Thanks,Steve
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
I am trying to move a log file from one drive to another.
What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:
ALTER DATABASE Uniprodruntime REMOVE FILE m_rk_runtime_log
I get the following error message..
Server: Msg 5020, Level 16, State 1, Line 1 The primary data or log file cannot be removed from a database.
I have also gone into enterprise manager and tried to delete the file and it does nothing.
1: TempDB keeps getting filled. Restart of the server has not fixed it. I shrink it, but the space gets filled again. Now I can't even shrink it anymore 2: TempDB is at the wrong location. Its current location is this :C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
How do I change its location?
C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb Correct location of TempDB should be: TempDB(T:) But its not there
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?
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
During the installation of Adding node to a SQL Server failover cluster(On passive node) getting error like.. The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.We run the below commands but didn’t get any resolution & got the same above error .
1<sup>st</sup> Method…
1. Open console command (Run->CMD with administrator privileges).
2. net stop winmgmt
3. Rename folder %windir%System32WbemRepository to other one, for backup purposes (for example _Repository).
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2 Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid. Msg 3156, Level 16, State 50, Line 2 File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 2 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
Can I build a cluster by adding the cluster service, then the SQL instances, then add the other nodes and their passive SQL instances?I would lean to building the cluster first, the add the SQL instances.
I have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512), @sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX), @column NVARCHAR(MAX); DECLARE @indexname NVARCHAR(512); SELECT name As 'Table'
We are planning to change all IPs of PRODUCTION Failover Cluster Setup. In my cluster setup ... we have 2 Physical Nodes with windows-2008, Roles are MSDTC and SQL-2008R2.
IP change for:
1. Both Nodes(Physical) 2. MSDTC 3. SQL Server 4. windows Cluster
So Almost... All IPs are going to change.
Im DBA here, I need to take care of SQL cluster and MSDTC. But I haven't performed this activity before.So I'm worrying about Impacts and consequences of this change. steps how should I perform this activity.
We have many tables which have cluster index on column with datatype 'Char(200)'. Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
We generally modify the logical name of a data file or log file from the vendor defined to our standard name. Is there any impact that you know about this? If you have 2 seperate database and accidently you have 2 same logical name .. I dont' think that will be an issue ... correct?
ALTER DATABASE XXX_UAT MODIFY FILE (NAME = 'mdd_Data', NEWNAME = 'XXX_UAT_Data') GO
ALTER DATABASE XXX_UAT MODIFY FILE (NAME = 'mdd_Log', NEWNAME = 'XXX_UAT_Log') GO
Hi,I want to change the logical name of the database. My database name and.MDF has the same name, but the logical name is different. Willchanging it impact anything?--sharif
I am trying to upgrade a SQL Server 6.5(Cluster) to SQL Serevr 7.0 (Cluster)..I already have an intsllation of 7.0(On a Cluster),so this means that 6.5 and sql 7 are on seperate cluster's ,if i try to upgrade from 6.5 Cluster to 7.0 Cluster is asks me to uncluster 6.5 and 7.0 is this correct ,assume i cannot break the cluster then what???.. what is the best way i can achieve this functinality.....
I have a query. on my search page i have a number of textboxes e.g. title, subtitle, ISBN where users can enter a search string and when they click on a button the data is displayed in the gridview below. my query seems to be bringing up the right records but with some additional records into the gridview. I have tried the same query within the SQL server management studio and it brings back two records like indicated but within my aspx page it brings back 6 additional records that have no relevance to the query. Im quite baffled as to why this is happening and i have debugged but nothing comes to light. my code in my aspx page is as follows: public void BindData(){String _BookID = TxtBookID.Text;String _Title = TxtTitle.Text;String _Subtitle = TxtSubtitle.Text;String _ISBN = TxtISBN.Text;String AuthorFName = TxtForename.Text;String AuthorLName = TxtSurname.Text; SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID, Book_ID_Internal AS [Book Internal ID], Title, Subtitle, ISBN, Edition, Publication_Date AS [Published Date] FROM Book WHERE Book_ID_Internal = '" + _BookID + "' OR Title = '" + _Title + "' OR Subtitle = '" + _Subtitle + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");if (ds.Tables[0].Rows.Count == 0){//if there are no rows returned-state in the gridds.Tables[0].Rows.Add(ds.Tables[0].NewRow());GridView1.DataSource = ds;GridView1.DataBind();int columncount = GridView1.Rows[0].Cells.Count;GridView1.Rows[0].Cells.Clear();GridView1.Rows[0].Cells.Add(new TableCell());GridView1.Rows[0].Cells[0].ColumnSpan = columncount;GridView1.Rows[0].Cells[0].Font.Italic = true;GridView1.Rows[0].Cells[0].Text = "No Records Found";}else{GridView1.DataSource = ds;GridView1.DataBind();}} if i input the same query into SQL management studio: SELECT * FROM Book WHERE Book_ID_Internal = 'ITD 450' OR Title = 'HTML 4.0' it returns two records which is correct. Can anyone see where i am going wrong?
In the database properties screen, there are four tabs: General, Transaction Log, Options, Permissions. In the General Tab, it lists four columns: File name, Location, Space Allocated, File Group. The string in the File name column has a value such as MY_DATABASE_DAT, whereas the Location column has a value like D:mssql7dataMY_DATABASE.MDF. The Location value is clearly the Windows file name. The "File name" (which I have seen called the "logical file") is a mystery to me. What is it used for? How can it be changed? Is there a problem if two different databases have the same "logical file" name? (For example, if you do sp_detach_db, copy the file to another place and then sp_attach_db the new file to another database name, you have two different databases with the same "logical file" name.) Much thanks to anyone who can shed light on this.
Hi everyone, I have a database (xyz) one Machine A. On the same machine I want to create a copy of the (xyz)database with different name(xyz_1). When I restore with move option it will restore but the thing is logical file names of xyz and xyz_1 are same. I know I can change these file names by manupulating the system table. My question is If I didn't change the logical file names of database xyz_1(new). Is there any problems or issues may arise.
The organisation I'm working for has SQL Server 2005 merge replication up and running on 11 servers in 3 different countries.
Ocasionally we experience problems caused by rows in a logical record relationship being replicated between servers in the wrong order, i.e. an attempt is made to insert child rows before the parent. I am not able to reproduce this behaviour on my test servers.
