I am doing some design work on a new system. The plan is to use clustered servers accessing the same backend instance. I want to prepoulate the table with our unique id (for say 5M rows for sake of discussion). Where I am puzzled is how do I guarantee unique ids to the application when both servers are looking for the same next available row? The sql could be something like
select min(id) from table where user_id is null
The trouble our current system runs into is that there are occasions where the users generate the same id which of course messes up the application.
Is this a case where a store procedure would do the insert and return the id to the user is a better solution?
I'd like to find out how people handle monitoring the disk space used and available per database across all of their databases and severs. The information returned in sp_spaceused is what I'm looking for, but it is for one databases only. How is everyone managing many databases on many different severs?
Are there scripts or tools available for this? Has anyone written any vb code using SQL-DMO?
Currently we have a single SQL server. It went down and the higher ups were none to happy. Lots of money lost, down time, unhappy customers....the whole nine yards. They want to throw all sorts of money at the problem and want a solution that is high availability and provides load balancing. I think I came up with a solution.
3x Windows 2003 Enterprise Edition servers running SQL 2005 Standard in a cluster all connected to a SAN.
I'm guessing I need to set up Network Load Balancing in order to load balance the SQL database. What we would love to have happen is if one of the servers goes down, everything else just picks up. That coupled with our database that is increasing in size and transactions gets load balanced.
Am I going down the right path? Something else I should look at? Or that I am missing?
Currently we have a single SQL server. It went down and the higher ups were none to happy. Lots of money lost, down time, unhappy customers....the whole nine yards. They want to throw all sorts of money at the problem and want a solution that is high availability and provides load balancing. I think I came up with a solution.
3x Windows 2003 Enterprise Edition servers running SQL 2005 Standard in a cluster all connected to a SAN.
I'm guessing I need to set up Network Load Balancing in order to load balance the SQL database. What we would love to have happen is if one of the servers goes down, everything else just picks up. That coupled with our database that is increasing in size and transactions gets load balanced.
Am I going down the right path? Something else I should look at? Or that I am missing?
Thanks and sorry for posting this in multiple places!
We shall be taking a bunch of 7.0 instances and moving/upgrading to a SQL 2000 cluster server. I was thinking of creating new named instances on the 2000 cluster and upgrading each 7.0 server to it's respective named instance. Also thought of using the 2000 copy database wizard; I was told this didn't always work. Anyone hear of problems with this? Thanks
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.
We have been experiencing issues with SQL Server 2005 SP2 upgrade on cluster servers. After running the setup, it gives failure message for Database services.
Installing SP2 on 64bit cluster server we get error regarding "passive node not patched properly or run the setup from passive node". However, select serverproperty(€˜productlevel€™) returns the output as "SP2". On more digging we found from SQL Server Error log, that the resourcedb of that server is of level 9.0.0.3199
€œThe resource database build version is 9.00.1399. This is an informational message only. No user action is required.€?
However if we do the SQL Server 2005 SP2 upgrade on standalone machines with the same software repository, it upgrades successfully and also resourcedb upgraded to 3042 version. So if we look at the errorlog of a standalone machine it gives following message.
"The resource database build version is 9.00.3042. This is an informational message only. No user action is required."
I again reiterate that, this is the issue we have faced on 64bit SQL Server 2005 SP2 upgrade only on cluster SQL Servers, however on standalone machine it works fine and 32bit SP2 installation works fine.
I know now that AlwaysOn feature HAS to be installed/configured on a Windows Clustering environment, BUT the secondary replicas, like the Disaster recovery replica residing in a different Data Center HAS to be also in a Windows Clustering environment or can it reside on a SINGLE SQL Server INSTANCE?.
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
Hi, I am trying to do this: UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl') What would be the syntax. Any help appreciated. Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005. The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions 1) Replacing spaces with €œ €? 2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
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).Â
4. net start winmgmt
2<sup>nd</sup>Â Method..
1. Disable and stop the WMI service.
a) Command : - sc config winmgmt start= disabled
b. Command : - Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â net stop winmgmt
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 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!
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.
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
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.
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.....
