2 Servers 1 Disk Drive
Sep 10, 1999
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
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
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 Related
Apr 22, 2004
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 Replies
View Related
Jul 7, 2015
My 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 Related
Aug 11, 2005
Trying 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 Related
Sep 27, 2007
outside 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.
View 3 Replies
View Related
Apr 26, 2006
I am having a problem accessing my local A: drive, using the bulk insert command. The actual sql server pc thinks I am accessing its sql server's A: and not my local a: drive. I am trying to read in a simple CSV delimited ascii file into a sql table. Wow what a nightmare just to read a simple ascii file into a table. Unfortunatley I am writing a program in VB.net for an operator to import ascii data from floppy diskettes.
I am using this command below: The command works fine but I have to go to the actual server and put the floppy disk in over there. I want the operator to be able to use there own A: or any location drive for that matter. Sql server over the network is not allowing us to access any local drive to import this data. I get an error 21 because the sql server does not have the floppy in its drive. It seems like some kind of security issue, but I am not sure. Any information would be most appreciated in this matter. Thanks in advance.
Sarah Diane Reid
sample code below:
BULK INSERT Master.dbo.imVBMbAddress
FROM 'a:mbAddressPipes.txt'
WITH (FORMATFILE = 'a:importdatambaddress.fmt',
FIRSTROW=2);
View 3 Replies
View Related
Sep 22, 2006
Hello
I have got a script which checks the percentage of free space on the drivers of the servers and mails the DBA when it falls below a certain percentage , which can be set according to our requirements.
But I am getting blank emails even when , there is no issue of low free space.
Please help me out.
The code , 1st part of it is:
use master
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create PROCEDURE usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @MB bigint , @COUNT int, @Maxcount int,@error varchar(700), @errordrive char(1),@errortotalspace varchar(20), @errorfreespace varchar(20), @free int, @date varchar(100), @query varchar(1300)
SET @MB = 1048576
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The 2nd step which sends the mail is :
set nocount on
declare @msg varchar(8000)
declare @minimumspace int
set @minimumspace = 10
set @msg = 'Running out of Hard Disk space on the Server: '+@@servername
exec usp_diskspace @minimumspace,@msg OUTPUT
print @msg
if @msg is not null
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'fromaddress',
@TO = N'toaddress',
@server = N'smtp address',
@subject = N'Free Space of Drivers Test sqlserver2000!',
@type = N'text/html',
@message = @msg
How can I change to get the mail only when there is a free space issue.
Thanks
View 3 Replies
View Related
Sep 22, 2006
Hello
I have got a script which checks the percentage of free space on the drivers of the servers and mails the DBA when it falls below a certain percentage , which can be set according to our requirements.
But I am getting blank emails even when , there is no issue of low free space.
Please help me out.
The code , 1st part of it is:
use master
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create PROCEDURE usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @MB bigint , @COUNT int, @Maxcount int,@error varchar(700), @errordrive char(1),@errortotalspace varchar(20), @errorfreespace varchar(20), @free int, @date varchar(100), @query varchar(1300)
SET @MB = 1048576
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The 2nd step which sends the mail is :
set nocount on
declare @msg varchar(8000)
declare @minimumspace int
set @minimumspace = 10
set @msg = 'Running out of Hard Disk space on the Server: '+@@servername
exec usp_diskspace @minimumspace,@msg OUTPUT
print @msg
if @msg is not null
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'fromaddress',
@TO = N'toaddress',
@server = N'smtp address',
@subject = N'Free Space of Drivers Test sqlserver2000!',
@type = N'text/html',
@message = @msg
How can I change to get the mail only when there is a free space issue.
Thanks
View 1 Replies
View Related
Dec 28, 2006
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
View 1 Replies
View Related
Sep 10, 2007
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
View 1 Replies
View Related
Nov 13, 2007
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Nov 15, 2006
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
View 4 Replies
View Related
Apr 4, 2008
Hi
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$])
View 3 Replies
View Related
Feb 20, 2001
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
View 1 Replies
View Related
Nov 9, 2000
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.
Has anyone run into this?
View 2 Replies
View Related
Feb 12, 2007
How do you compare SAN drive vs local drive on a 32 bit server?
Is it good idea to move my DB files to a SAN instead of local?
Canada DBA
View 4 Replies
View Related
Nov 28, 2015
Server: SQL 2008 R2
 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
View 13 Replies
View Related
May 7, 2004
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
View 4 Replies
View Related
Jan 31, 2008
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
View 4 Replies
View Related
Jan 16, 2015
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.
View 1 Replies
View Related
Nov 21, 2007
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
View 1 Replies
View Related
Aug 21, 2001
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
View 1 Replies
View Related
Sep 27, 2007
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
View 2 Replies
View Related
Aug 13, 2004
If my computer has a map drive, i want to delete it, how to do it? Is that right if i right click the map drive and click the disconnect?
View 3 Replies
View Related
Oct 26, 2006
I am installing the X86 Executable version of SQL Server. I copied it to my desktop and clicked RUN. It extracts the files and then the Install Wizard tells me there is not enough space on the C Drive to load the program. I have 23 gigabits of free space and the program only takes about 900 megabits.
Is there a solution to this problem?
Thanks,
Modez
View 2 Replies
View Related
Jun 19, 2002
I'm running out of space on my C. Is it possible to store additional DBs on another drive or do I need to load SQL server onto the new drive?
View 6 Replies
View Related
Jun 6, 2001
Would this be ok for a SQL Server?
Here's what we are thinking about doing:
3x18 GB RAID 5 for OS/apps/database/data.
2x9 GB mirrored for the log files.
(we normally don't have apps on the server besides SQL itself)
Thanks!
View 2 Replies
View Related
Aug 7, 2000
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
View 1 Replies
View Related
Aug 15, 2000
How do I move all the databases to a new drive in the same machine?What effects/problems will be?
Thanks!
View 7 Replies
View Related
May 11, 2000
Hi,
I have a shared drive mapped to the h: drive letter on my sql server and I want to create new databases on that drive. Is this at all
possible?
I try it and I couldn't see h: even if I typed it in.
Thanks
View 2 Replies
View Related
Feb 4, 2001
Hi,
What problems will I get if the hard drive for the transaction logs becomes defective? Will this affect transactions and/or backup? What should I do to fix this problem?
View 1 Replies
View Related
Sep 28, 2005
In reading material on the quorum drive on a sql server cluster in mentions this is the drive the logs are written to. Is this referring to sql log files that are dumped by a process or scheduled job, or some other log files?
View 1 Replies
View Related
