MS SQL Server :: Memory Management In A Clustered SQL2000 Environment

Urgent!! - SQL Server Failing On A Clustered Environment -
Hello,

Yesterday, our server 'failed over' to the passive node in our clustered environment. We are running Windows NT, with MSSQL 7.0. What triggered the fail over, according to the logs, was an error in tempdb. Basically, the error said that tempdb could not be expanded, (could not create any more space for tempdb).

The drive that tempdb database is located on has more than enough room. The tempdb is set at 300M, and can grow to over 700M. The drive that tempdb is on
has over 19Gig of space on it.

One other important piece of information is that the drive where our production database is on and the tempdb is quite full. The 19 gig that is left over represents only about 5 percent of the disk.

Can anyone shed some light on why the sql server would have failed over when trying to expand tempdb, when there was sufficient resources to handle that growth? Am I barking up the wrong tree? Could this be another error? When we restarted the server (both nodes and the disk array) the production database came back as suspect. Most of the stuff in BOL points to the space issue. The production database is around 37 gig.

Any help would be appreaciated

thanks,

jim

View Replies !

Uninstalling SQL Server 2005 64-bit In Clustered Environment
I am running into an issue trying to uninstall SQL Server 2005 64-bit. It hangs at the same point: Removing backup files. I then get a message that, "The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it." The instance that I am trying to uninstall is in a cluster hosting two other SQL Server installations. I have tried a few combinations when attempting to uninstall: running the cluster group containing the server I am uninstalling on a different cluster node than all the other cluster groups; running them all on the same node. The only one I haven't tried is moving the Cluster Group containing the IP address of the cluster, cluster name, etc to another node while running the other groups on the other node. Has anyone run into this issue? Each of the nodes has been rebooted several times during these attempts and we are using Windows Server 2003 Clustering. Again, the hang happens at the same point every time. The exact error(s) from the even log are as follows:

Product: Microsoft SQL Server 2005 (64-bit) -- Error 29528. The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it.

Product: Microsoft SQL Server 2005 (64-bit) -- Error 29530. Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 2 (The system cannot find the file specified.

)

Thanks,

Todd

View Replies !

Unable To Send Databasemail On A Clustered Environment
I am able to send databasemail on my staging servers which are non clustered but databasemail fails on my clustered production servers.
Both production and staging servers sit on the same domain(although the domain controller is sitting on one of the staging servers)
I have set up the account and profile and have double checked that it is pointing to the correct SMTP server and to the same one as the staging servers.
I have also enabled database mail in surface area configuration.
The message gets queued, because i see the 'mail is queued' message.
The databaseMail Process also gets started, but during the actual send process I see
The following is the error in the databasemail log.
Any ideas what could be my problem?

Databasemail Log

Date                18/07/2007 18:49:17
Log                  Database Mail (Database Mail Log)

Log ID                        14
Process ID                   964
Last Modified             18/07/2007 18:49:17
Last Modified By                   TT-IXTOOLSixtools

Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID)
   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

btw, I ran this command to send mail

declare @body1 varchar(100)

set @body1 = 'Server :'+@@servername+ ' My First Database Email '

EXEC msdb.dbo.sp_send_dbmail @recipients='mytestemail@email.com',

@subject = 'My Mail Test',

@body = @body1,

@body_format = 'HTML' ;

View Replies !

Is It Necessary To Have A Clustered Test Environment To Support A Production Cluster?
We will be implementing our first SQL cluster in December. Our current plan calls for a shared development/test database server with one physical server, but two SQL Server instances. Our production environment will be a SQL cluster. Is it necessary to create a clustered test environment for testing patches, hot-fixes, etc...?

Thanks, Dave

View Replies !

Restore A SQL2K5 DB Into SQL2000 Environment
I want to copy a SQL2005 database to a SQL2000 environment? I created a backup from the DB in SQL2005 and moved the file to the other server but SQL2000 says the BAK file is invalid since the backup has structure version 611 while the server supports version 539. Any solution?

Canada DBA

View Replies !

Install SQL2000 And SQL2005 In Production Environment?
Hi,

Is it possible to deploy both SQL2000 and SQL2005 on the same server in the production environment?

Is it recommended? Next year we will be deploying a SQL2005 solution and was wondering if we could get away with installing it on the same server as the SQL2000 server, or whether we should put it on another server.

We have up to a maximum of 50 concurrent users.

Thanks.

View Replies !

SQL 2005 TDSSNIClient Initialization Failed With Error 0x32, Status Code 0x1c (Clustered Environment)
I've seen other posts on this topic, but not with error 0x32.

I was attempting to change a CLUSTERED instance of SQL 2005 from dynamic port to static port and in the process have somehow messed it up to the point it will no longer start.    Version is 9.00.2047.00

The ERRORLOG has the following

2007-01-31 15:02:05.77 spid9s      Starting up database 'model'.
2007-01-31 15:02:05.77 Server      Error: 17182, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server      TDSSNIClient initialization failed with error 0x32, status code 0x1c.
2007-01-31 15:02:05.77 Server      Error: 17182, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server      TDSSNIClient initialization failed with error 0x32, status code 0x1.
2007-01-31 15:02:05.77 Server      Error: 17826, Severity: 18, State: 3.
2007-01-31 15:02:05.77 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-01-31 15:02:05.77 Server      Error: 17120, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The values have only been changed using SQL Server Configuration Manager (no direct registry changes have been made).

Thanks for you help.

View Replies !

SQL2000, Sort_in_tempdb, Rebuild Clustered Index
Hello. If this is not the correct forum for S2K quesitons, will someone please provide me the right link? I have 1 question:

In BOL, it appears you can can use tempdb to 'create' an index using the 'SORT_IN_TEMPDB' option. Does anyone know if the index rebuild process can use tempdb as well for sorting or do I still have to reserve 1.2 times the space in the filegroup or database to rebuild the clustered index?

TIA
Gail Wade
Raymond James Financial
gwade@it.rjf.com

View Replies !

Memory Management (fixed Memory, AWE)
Hi,

I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.

I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).

Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?

How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)

Thank

View Replies !

Dynamic Memory In SQL2000
Does anyone mess with this feature? they say that you don't have to change this setting in sql2000. I have 3 gigs of memory and have it still set to dynamically configure the memory. I don't know if i should change this to a fixed memory size, if i did how do you tell what value to set it at? What are the pros and cons if I set it to fixed? Thanks.

View Replies !

SQL2000 Ent. Memory Still 2gb Issue....
Hi:

we just upgrade OS to Windows 2003 Ent version for a prod SQL2000 Ent. (sp4) server, and with 8 gb.

here is the boot.ini
----------------------
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)WINDOW S
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)WINDOWS="Windows Server 2003, Enterprise" /pae /fastdetect /noexecute=optin
----------------------

1. past was 4 gb, with 3 gb to SQL
2. after re-start, from sql server properties--memory,
maximum server memory is 8187(in MB)
3. but from task manager, still allocate only 2 gb
(so actually, 'worse' than previous 3 gb)
4. from SQL log:
Message
Cannot use Address Windowing Extensions because lock memory privilege was not granted.
5. sql service log on with domain/admin
6. on windows Local Security Policy, add domain/admin to
a. Lock pages in memory
b. Log on as a service

Any part we were missing? Kind of urgent issue.
thanks in advance
David

View Replies !

Win2003 / Sql2000 Memory Usage
I have googled by heart out trying to find answers to this and am onlymore confused.DB Server:Windows Server 2003 Enterprise4 GB memorySQL Server 2000 Enterprise Edition, SP4 8.00.2039The SQL server process appears to be using a steady 1.7 gb of memory.There are no other memory intensive applications running on thisserver as reported by Task Manager.I have read all about the 2gb/process limit and the /3gb boot switchand all but most of that seems to apply to windows 2000, not 2003. I'dlike SQLServer to use more of the memory if it can (assuming it wouldbe advantageous for performance).Can anyone sum this up for me?Thanks,Michael

View Replies !

SQL2000 On Win2003 X64 - Memory Config
We have a new Windows 2003 R2 x64 server on which we are installing SQL 2000 EE (for numerous reasons 2005 is not currently an option). This server has 8GB of RAM and 4 dual-core CPUs and will be dedicated to SQL server (no file shares, no other services other than a backup agent). Since the OS is x64, I'm unsure how to get SQL to recognize and use the memory over 4GB. Do I just enable AWE support or do I also need to set something at the OS level for the WOW32 environment?

What is the recommended configuration for this? The server is 95% reads (the writes are just temporary datasets from our analysts).

Thanks

View Replies !

SQL2000 Standard Edition Only Uses 1.75GB Memory
Our production server has 3.3GB of memory and is running Windows 2000 server(service pack 2) and SQL 2000 Standard Edition(service pack 1).
We have configured SQL to use the maximum memory for standard edition (2GB)

In use, however, task manager shows SQL is ONLY using 1.75GB maximum memory
Our database size is 11Gb and we would expect to see SQL using the FULL 2GB

I have heard a similiar story from a colleague at another installation..

Does anyone have any insight into why this would be the case?

View Replies !

SQL CLR Memory Management
Hello,

I have a few questions about the behavior of the CLR host within SQL Server 2005. We are using a UDT (call it MyDateTime) created in C# that represents the COM FILETIME type, in order to have single millisecond resolution. MyDateTime values are stored in the database as binary(8), with the UDT itself being used primarily for display and reporting purposes. I am running performance tests using a prototype (written in C# as well) that runs 20 threads which repeatedly call a stored procedure, which accepts two MyDateTime's, and queries a table based on those MyDateTime's binary string representation. After a certain amount of time (depending on the particular system's resources), threads will start to be aborted. Most of the time the reason is "SQL Exception: .NET Framework execution was aborted by escalation policy because of out of memory." Sometimes, eventually the appdomain will be unloaded, and if I restart the prototype, the process starts over. Sometimes, I will have to restart the server before any more CLR processing can occur (no automatic appdomain unload). While the prototype is running, I'll check the MEMORYCLERK_SQLCLR rows in sys.dm_os_memory_clerks, and see the columns for pages and virtual memory ever increasing, until a threshold is hit (on my system, approximately 225 megs of virtual memory committed), resulting in all 20 threads being aborted, one by one, within 30-45 seconds. During that time some of the remaining threads will still have successful calls, while others are aborted.

I understand the necessity for the CLR in SQL Server to monitor and abort threads, in order to preserve the database server itself, as well as the importance of exception handling client-side, but unless the UDT code itself has a leak in it (I'm fairly confident it doesn't), this behavior confuses me. X amount of stored procedure calls (on my system, approximately 65,000 within an hour) can occur before SQL Server runs out of memory, and will constantly abort any thread trying to use the UDT, until it decides to unload the appdomain? Is it entirely up to the client to catch any threadaborts and retry those transactions, and is there any way to facilitate or predict if/when the appdomain is going to unload? Am I missing something about how garbage collection is functioning within SQL Server, or the CLR itself? Even simple CLR code slowly eats up the memory and causes the same results, if enough transactions are made. Does a long running or high transaction system have to anticipate a regular intervention by the escalation policy?

Any insight you could give me would be greatly appreciated. Have a good day.

-Dan

P.S. I'm running the September CTP of SQL Server and the Release Candidate of Visual Studio, based on our current development requirements--I will upgrade when I can.

View Replies !

SQL2000 Databases Not Showing Up In Enterprises Manager But Do Show Up In Management Studio
I can see the databases in the Query Analyzer Object Browser and with EXEC sp_helpdb.

NT 5.2 (3790) Microsoft Windows Server 2003, Enterprise Edition for 64-Bit Itanium-based Systems
8.00.2039 Enterprise Edition (64-bit)

I did a dbcc checkdb('master') and dbcc checkdb('msdb') and found no errors.

I can't see anything in the Management folder either.

When I try to view a login it gives me and Error 220. It works fine in Management Studio.

Any ideas?

I found a fix at: http://www.ureader.com/message/92216.aspx

I had two databases that were offline that had the Error 220. We took these off line a few weeks back. I'm not sure why this happened but it works now.

I'll include the code in case the link goes away.

USE master
GO
DECLARE @db_name varchar(500)
DECLARE dbn_cursor CURSOR FOR
SELECT [name] FROM sysdatabases;
OPEN dbn_cursor
FETCH NEXT FROM dbn_cursor
INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
select * from sysdatabases where name = @db_name
IF @@ERROR <> 0
BEGIN
     declare @off int
     print 'Restarting database '+@db_name

     exec @off = sp_dboption @db_name, 'offline', 'TRUE'
     IF @off <> 0
  print 'Taking offline failed! Need manual workaround!'
     else
  begin
      declare @on int
      exec @on = sp_dboption @db_name, 'offline', 'FALSE'
      IF @on <> 0
   print 'Bringing online failed! Need manual workaround!'
      else
   print 'Restart successfull!'
         end
END
FETCH NEXT FROM dbn_cursor
INTO @db_name
END
CLOSE dbn_cursor
DEALLOCATE dbn_cursor
GO

View Replies !

SQL Server 2005 Memory Management
Hi all,

I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:

1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)

2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)

3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)

Thanks in advance:

Siol En

View Replies !

SQL Server 2005 Memory Management

Hi all,
I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:
1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)
2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)
3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)

Thanks in advance:
Siol En

View Replies !

2005 Memory Management Questions
I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?

32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (Willing to consume)
Total Server Memory = 690MB (Currently consuming)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB

For the 32-bit server the Target Server Memory matches Maximum Server Memory

64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (Willing to consume)
Total Server Memory = 397MB (Currently consuming)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB

For the 64-bit server the Target Server Memory is less then the Maximum Server Memory

Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?

Thanks, Dave

View Replies !

SQL Dynamic Memory Management And Low Free Buffers/
We are just beginning to use a third party tool to monitor our SQL Servers. One very active server seems to have a few occassions where the Free buffer goes very low (under 10)however, it seems to quickly resolve back to a couple of hundred free buffers. This occurs both during times with heavy user activity and times with little user activity but high sql agent maintenance activity. Other memory numbers and the Buffer cache hit ratio seem fine. Any ideas? Is this normal when SQL Dynamically manages memory?

View Replies !

Speed, Memory Usage Question For Photo Management
Hi all, quick question. A while back I developed a website that allowed upload of photos. At the time, I used ASP and VBasic-behind and wrote code to store and retrieve all photos in an SQL database in binary format. I am looking at a new project, very similar, and was wondering if anybody had any idea how this method might compare to simply storing the image files on the server and using the database to simply point to their location. I am wondering how the two methods compare spped wise and hard drive space they may consume. Does anybody have any idea on a direct comparison?? Any benchmark tests anybody has seen or ran?
Thanks all,
Chris

View Replies !

While Opening Management Studio I Am Getting Error--Attempted To Read Or Protected Memory.

Hi,
I am getting error while opening the Data Base Engine in Sql server Management Studio.
We applied SP2. and restarted the server but no luck.
Error Message:Attempted to read or protected memory. This is often an indication that other memory is corrupt(mscorlib).

View Replies !

How To Deploy Updated Database From Development Environment To Live Environment?
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?

View Replies !

Create Clustered Or Non-clustered Index On Large Table ( SQL Server 7 )
I have large table with 10million records. I would like to create clustered or non-clustered index.

What is the quick way to create? I have tried once and it took more than 10 min.

please help.

View Replies !

Restore A Database On Clustered Server From A Non-clustered Backup File.
Hello,

How do I restore a sql database that is on a clustered server from a sql database backup file that is on a non_clustered server?

Thanks,

Serey

View Replies !

Converting A Clustered Index On A PK Identity Field To Non-clustered
Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.

If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.

So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?

Thanks

View Replies !

How To Alter A Non Clustered Primary Key Constraint To Clustered
Hello,

I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.

Thanks in Advance,

Rohit

View Replies !

Clustered/Non-clustered Indexes And B-Trees
I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how? I tried to find info. on the web but couldn't get much detail...

View Replies !

Changing Clustered Index To Non-Clustered
I have a Clustered index on 2 columns in a table with 105 Million rows occupying 28GB. The table is accesed for inserts and updates 350 times per second.
Is there any way to change the clustered index to a non-clustered index without dropping the index and re-recreating it as non-clustered? Also is there any way to do this without having to halt all activity on the database during the rebuild?

View Replies !

Clustered And Non Clustered Index On Same Columns
I have a table<table1> with 804668 records primary on table1(col1,col2,col3,col4)

Have created non-clustered index on <table1>(col2,col3,col4),to solve a performance issue.(which is a join involving another table with 1.2 million records).Seems to be working great.

I want to know whether this will slow down,insert and update on the <table1>?

View Replies !

Simple Query Chooses Clustered Index Scan Instead Of Clustered Index Seek
the query:

SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')

takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.

expanding the inner select into a list of guids the query runs instantly:

SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')

It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.

The tables involved:

Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.

I'd include .sqlplan files or screenshots, but I don't see a way to attach them.

I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:

SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')

To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?

View Replies !

MSDN Installation Of SQL2000 Vs. Enterprise SQL2000
We have a 5 gig production DB that is has errors occurring during the log file backup. This is the error: BlkHeader from strip 0 At 2d1f200 ExpectedAt 2d49c00 Size 400 PrevSize 400. It corrupts the log file and our server is running really slow. Does anyone know if it could be that we are using MSDN instead of Enterprise?

View Replies !

MSDN SQL2000 To Shrink Wrap SQL2000
Should I uninstall MSDN SQL2000 and then install the shrink wrapped SQL2000, or is there any harm in installing the 'real' SQL2000 over the MSDN version?

View Replies !

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View Replies !

Both Clustered And Non-Clustered On The Same Field
Does creating both a Clustered and Non-Clustered on the same field increase performace or decrease performance?
Or having either one is enough?

View Replies !

Clustered/non-Clustered Index
What does an index add to the performance?
Why do we use Clustered Index and Non-clustered Index?

thanks

View Replies !

Clustered 2k5 Can't Connect To Clustered 2k
I'm building a new fail-over cluster (SQL2k5 Ent on Win2k3 Ent) to replace our current cluster (SQL2k Ent on Win2kAS). One problem I keep running into seems to be related to the SSIS so I hope I've posted this in the correct place.

While attempting to simply connect to the 2k instance from the 2k5 instance using "Management Studio" I get an error stating:

"An error has occured while establishing a connection to the server. When connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)"

Changing the protocol to TCP makes little difference:

"An error has occured while establishing a connection to the server. When connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)"

I have many other SQL servers at my disposal and so I have found that I am able to connect in both directions from any of these 2k5 and 2k servers with no problems. However, these other servers are not clusters. So, single server 2k5 can connect to clustered 2k, and clustered 2k5 can connect to single 2k. I can even use Management Studio on a non-clustered 2k5 instance to connect to both the 2k cluster and the 2k5 cluster, but any attempt to use the CDW results in the following:

"InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

so I figured enough, I'll just restore a full backup for now just so we can finish our testing of the new system, then on "light switch" day, I'll just detach/attach and call it good. However, this app works closely with other apps, that just so happen to have their databases hosted on the 2K server we are moving away from. So I setup a linked server and no problem... until I attempt to query said Linked Server:

"OLE DB provider "SQLNCLI" for linked server "OM-IDRIVE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "OM-IDRIVE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53]."

Does anyone have any idea what the heck is going on? I have noticed that on the node that currently does not own SQL the services are as follows:

SQL Server - not started - manual - (domain service account)

SQL Server Active Directory Helper - not started - disabled - Network Service

SQL Server Agent - not started - manual - (domain service account)

SQL Server Browser - Started - Auto - (domain service account)

SQL Server FullTextSearch - not started - manual - (domain service account)

SQL Integration Services - Started - Auto - Network Service

SQL Server VSS Writer - Started - Auto - Local System

View Replies !