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
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.
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.
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.
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.
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.
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.
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.
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!
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.
-- 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
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.
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
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.
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.
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!
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,
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
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.
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.
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.
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?
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?
