Automating SQL Profiler

The powers at be have decided that they would like to automatically run

a trace on one of our analysis servers when it processes a cube in the

early hours of the morning. Now I have no problem creating a SQL

Profile to run and store the results in a database table for them, but

I have no idea how to automate it so that it runs everyday, any help

would be greatly appreciated. My apologises for posting this in forums

let's search in the internet with your subject.

you will find a lot of articles. this is one of them.
http://www.lazydba.com/sql/1__18318.html

Automating SQL Profiler

The powers at be have decided that they would like to automatically run

a trace on one of our analysis servers when it processes a cube in the

early hours of the morning. Now I have no problem creating a SQL

Profile to run and store the results in a database table for them, but

I have no idea how to automate it so that it runs everyday, any help

would be greatly appreciated. My apologises for posting this in forums

Option B - Not sure what information you want to capture from trace but if you are processing your cube using SSIS package you can capture log in database table at package level and cube level both. In cube level log there are many options you can select like sql, start, end time and lot more. This way when your SSIS package will run your database table will also get populated.

-Ashok

|||

SP2 is going to come out with little sample application ASTrace. This tiny sample installs as a service and knows how to subsribe to Analysis Services trace and output this trace into SQL Server table.

Drop me a line if you are in urgent need to solve this problem and cant wait for SP2 (which is pretty close).

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Automating copy between 3 servers

I'm developing a small app (Access), based on a SQL Server 2000 DB.
Development occurs on 3 DBs:
- Office DB - (MS SQL server 2000)
- Customer DB - (MS SQL server 2000)
- Portable DB - (MSDE 2000)
Since moving continuosly, as a daily routine I have to copy the latest DB,
and currently I use Enterprise manager to do so (backup/restore); boring,
'cause db paths are different on the 3 machines hosting the db servers.
Is there an easy way to automate the exchange between the 3 servers?

if all of your data is moving only in one direction and all of your tables have PK's on them, think about transactional replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sql

Automating a Backup and Restore across Servers

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

MariaHi Maria,
try if putting the statement

SET XACT_ABORT OFF

in the TSQL-stream of the step solves your problem.

Automatically Create Linked Server?

I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)
Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>
|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of
>
|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of
>
|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;' , 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Automatically Create Linked Server?

I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > I am trying to automatically create linked servers, then extract the DB
> > names from them and delete the linked server. This is for an inventory
> > program I am writing to keep info on all of our SQL Server up to date.
> > The problem I run into is the linked server procedure, even when run
with
> > Exec(), does not run unless I separate it in its own batch, then
variables
> I
> > created in my script are out of scope.
> > Nutshell of the sequence:
> > 1. Create cursor with list of DB servers from table on inventory server.
> > 2. Begin cursor loop.
> > 3. Create linked server for the server from the list.
> > 4. Query server for list of user databases on that server.
> > 5. Begin another loop, using While, to insert data retrieved into
> inventory
> > database.
> > 6. Delete linked server.
> > 7. Go back to beginning and do next server in list.
> >
> > The code:
> > Declare @.DBID Int --Floating DB ID number.
> > Declare @.MaxDB Int --Highest DB ID number.
> > Declare @.DBName VarChar(100)
> > Declare @.LastID Int --The value of the identity field for the last
insert.
> > Declare @.ServerID Int
> > Declare @.Server VarChar(100)
> > Declare @.SQL1 VarChar(500)
> > Declare DBServers Cursor FAST_FORWARD For
> > Select S.seName, S.seServerID From Servers S Inner Join
> > ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> > Open DBServers
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > Print @.Server
> > While @.@.Fetch_Status = 0
> > Begin
> > Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> > @.provider= ''SQLOLEDB'',
> > @.datasrc= ' + @.Server +',
> > @.catalog= ''master'''
> > Exec (@.SQL1)
> > Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> > @.useself= ''True'', @.locallogin = ''domain\username'''
> > Exec (@.SQL1)
> > Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid
<>
> > 0x01
> >
> > While @.DBID <= @.MaxDB
> > Begin
> > Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> > Where dbid = ' + @.DBID
> > Exec(@.SQL1)
> > Insert Into Databases (dbName, dbType)
> > Values(@.DBName, 'MSSQL')
> > Select @.LastID = @.@.Identity
> > Insert Into DBLink (dlDBIDdb, dlServerIDse)
> > Values(@.LastID, @.ServerID)
> > Set @.DBID = @.DBID + 1
> > End
> > Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> > Fetch Next From DBServers Into @.Server, @.ServerID
> > End
> > Close DBServers
> > DeAllocate DBServers
> >
> > This works great, and I have no security issues, if I run the linked
> server
> > part separate from the Select from sysdatabases part. I tried putting a
> "GO"
> > after the sp_addlinkedserver, but that just put all of my variables out
of
> > scope.
> >
> > Any ideas?
> >
> > Thanks,
> > Chris Stamey
> > (reply to newsgroup please)
> >
> >
>|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connection=Yes;', 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Automatically Create Linked Server?

I am trying to automatically create linked servers, then extract the DB
names from them and delete the linked server. This is for an inventory
program I am writing to keep info on all of our SQL Server up to date.
The problem I run into is the linked server procedure, even when run with
Exec(), does not run unless I separate it in its own batch, then variables I
created in my script are out of scope.
Nutshell of the sequence:
1. Create cursor with list of DB servers from table on inventory server.
2. Begin cursor loop.
3. Create linked server for the server from the list.
4. Query server for list of user databases on that server.
5. Begin another loop, using While, to insert data retrieved into inventory
database.
6. Delete linked server.
7. Go back to beginning and do next server in list.
The code:
Declare @.DBID Int --Floating DB ID number.
Declare @.MaxDB Int --Highest DB ID number.
Declare @.DBName VarChar(100)
Declare @.LastID Int --The value of the identity field for the last insert.
Declare @.ServerID Int
Declare @.Server VarChar(100)
Declare @.SQL1 VarChar(500)
Declare DBServers Cursor FAST_FORWARD For
Select S.seName, S.seServerID From Servers S Inner Join
ServerAppLink SAL On S.seServerID = SAL.slServerIDse
Open DBServers
Fetch Next From DBServers Into @.Server, @.ServerID
Print @.Server
While @.@.Fetch_Status = 0
Begin
Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
@.provider= ''SQLOLEDB'',
@.datasrc= ' + @.Server +',
@.catalog= ''master'''
Exec (@.SQL1)
Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
@.useself= ''True'', @.locallogin = ''domain\username'''
Exec (@.SQL1)
Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
0x01
While @.DBID <= @.MaxDB
Begin
Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
Where dbid = ' + @.DBID
Exec(@.SQL1)
Insert Into Databases (dbName, dbType)
Values(@.DBName, 'MSSQL')
Select @.LastID = @.@.Identity
Insert Into DBLink (dlDBIDdb, dlServerIDse)
Values(@.LastID, @.ServerID)
Set @.DBID = @.DBID + 1
End
Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
Fetch Next From DBServers Into @.Server, @.ServerID
End
Close DBServers
DeAllocate DBServers
This works great, and I have no security issues, if I run the linked server
part separate from the Select from sysdatabases part. I tried putting a "GO"
after the sp_addlinkedserver, but that just put all of my variables out of
scope.
Any ideas?
Thanks,
Chris Stamey
(reply to newsgroup please)Chris,
Have you looked at sp_executesql?
Ilya
"Torquin" <Torquin@.nospam.nospam> wrote in message
news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
> I am trying to automatically create linked servers, then extract the DB
> names from them and delete the linked server. This is for an inventory
> program I am writing to keep info on all of our SQL Server up to date.
> The problem I run into is the linked server procedure, even when run with
> Exec(), does not run unless I separate it in its own batch, then variables
I
> created in my script are out of scope.
> Nutshell of the sequence:
> 1. Create cursor with list of DB servers from table on inventory server.
> 2. Begin cursor loop.
> 3. Create linked server for the server from the list.
> 4. Query server for list of user databases on that server.
> 5. Begin another loop, using While, to insert data retrieved into
inventory
> database.
> 6. Delete linked server.
> 7. Go back to beginning and do next server in list.
> The code:
> Declare @.DBID Int --Floating DB ID number.
> Declare @.MaxDB Int --Highest DB ID number.
> Declare @.DBName VarChar(100)
> Declare @.LastID Int --The value of the identity field for the last insert.
> Declare @.ServerID Int
> Declare @.Server VarChar(100)
> Declare @.SQL1 VarChar(500)
> Declare DBServers Cursor FAST_FORWARD For
> Select S.seName, S.seServerID From Servers S Inner Join
> ServerAppLink SAL On S.seServerID = SAL.slServerIDse
> Open DBServers
> Fetch Next From DBServers Into @.Server, @.ServerID
> Print @.Server
> While @.@.Fetch_Status = 0
> Begin
> Set @.SQL1 ='sp_AddLinkedServer @.server= ''Temp'', @.srvproduct= '''',
> @.provider= ''SQLOLEDB'',
> @.datasrc= ' + @.Server +',
> @.catalog= ''master'''
> Exec (@.SQL1)
> Set @.SQL1 = 'sp_AddLinkedSrvLogin @.rmtsrvname = ''Temp'',
> @.useself= ''True'', @.locallogin = ''domain\username'''
> Exec (@.SQL1)
> Select @.DBID = Min(dbid) From Temp.Master.dbo.sysdatabases Where sid <>
> 0x01
> While @.DBID <= @.MaxDB
> Begin
> Set @.SQL1 = 'Select @.DBName = name from Temp.Master.dbo.sysdatabases
> Where dbid = ' + @.DBID
> Exec(@.SQL1)
> Insert Into Databases (dbName, dbType)
> Values(@.DBName, 'MSSQL')
> Select @.LastID = @.@.Identity
> Insert Into DBLink (dlDBIDdb, dlServerIDse)
> Values(@.LastID, @.ServerID)
> Set @.DBID = @.DBID + 1
> End
> Exec sp_dropserver @.server = 'Temp' , @.droplogins = 'droplogins'
> Fetch Next From DBServers Into @.Server, @.ServerID
> End
> Close DBServers
> DeAllocate DBServers
> This works great, and I have no security issues, if I run the linked
server
> part separate from the Select from sysdatabases part. I tried putting a
"GO"
> after the sp_addlinkedserver, but that just put all of my variables out of
> scope.
> Any ideas?
> Thanks,
> Chris Stamey
> (reply to newsgroup please)
>|||I have not. I will look into that and see if it can help this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of[vbcol=seagreen]
>|||No, sp_ExecuteSQL does not help in this situation.
Thanks,
Chris
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%238oTdtz9EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Have you looked at sp_executesql?
> Ilya
> "Torquin" <Torquin@.nospam.nospam> wrote in message
> news:ubnSRYy9EHA.3376@.TK2MSFTNGP12.phx.gbl...
with[vbcol=seagreen]
variables[vbcol=seagreen]
> I
> inventory
insert.[vbcol=seagreen]
<>[vbcol=seagreen]
> server
> "GO"
of[vbcol=seagreen]
>|||How about using an OpenRowSet() instead of the adding and dropping linked
servers.
As part of the query, just pull everything from sysdatabases into a local
table level variable and process it that way?
NOTE: I did not test this, it's just an idea.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Torquin,
Thanks for your posting!
From your descritpions, I understood that you would like to use local
varibales for new batches. Have I understood you? Correct me if I was wrong.
Based on my knowledge, local variables are not able to used in a new batch.
If you will have to use GO in your statements. I am afraid you will have to
use temp table to store these variables.
BTW, I have noticed that you made a replicated topic in
microsoft.public.sqlserver.programming. To keep the integrity of newsgroup,
I will reply your follow-up questions in this thread now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It shows promise, but when I substitute the variable in place of where the
server name goes I get the following error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
This works, with literals:
Declare @.DBID Int
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=ServerDB01;Trusted_Connec
tion=Yes;', 'Select Min(DBID) DBID
From Master.dbo.sysdatabases Where sid <> 0x01')
This gives the error listed above:
Declare @.DBID Int
Declare @.ServerName VarChar(100)
Set @.ServerName = 'ServerDB01'
Select @.DBID = DBID from OpenRowset('SQLOLEDB', 'DRIVER={SQL
Server};SERVER=' + @.ServerName + ';Trusted_Connection=Yes;', 'Select
Min(DBID) DBID From Master.dbo.sysdatabases Where sid <> 0x01')
Any ideas will be appreciated.
Thanks,
Chris
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23J%231H719EHA.1544@.TK2MSFTNGP11.phx.gbl...
> How about using an OpenRowSet() instead of the adding and dropping linked
> servers.
> As part of the query, just pull everything from sysdatabases into a local
> table level variable and process it that way?
> NOTE: I did not test this, it's just an idea.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Hi Torquin,
Have you checked my method of using temp table to store these variables?
How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Automatic Server restart ! very strange

Hi Friends,
Today 8 of my sql servers restarted at the same time. very strange
situation.
I cheked for spyware and virus and they are clean, all are patched and
I don't know any other thing.
Did this happen to anyone else ?
Thanks,
PHave you checked the power? I know this may sound base but I've seen
entire racks restart because a UPS battery was going bad.|||Did you check your Event Viewer logs?
--
Ekrem Önsoy
"Pat.Rick" <PatrickAlexander.email@.gmail.com> wrote in message
news:1192038021.582462.98140@.19g2000hsx.googlegroups.com...
> Hi Friends,
> Today 8 of my sql servers restarted at the same time. very strange
> situation.
> I cheked for spyware and virus and they are clean, all are patched and
> I don't know any other thing.
> Did this happen to anyone else ?
> Thanks,
> P
>

Automatic Job Monitoring

I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day.

Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors.

How do you do your checks every morning?

Thanks,

MarcoYou can look up master SQL Server Agent job in SQL BOL. I personally have never used it except for a long time ago when I was experimenting with it early on.

You can also use SQL Agent Mail to flag when a job has failed and send out an alert. SQL Agent Mail is a pain to set up (have to install Outlook), is not fully supported by MS in an clustered environment (Outlook is not cluster aware) and when it goes South in can really go south in a bad way.

I personally manage about a dozen SQL servers. I use a combination of SQL Agent mail on a "management" SQL server (to which all the other servers forward their alerts) and a web site that pulls from a database on the "management" database. This database is in turn populated by a DTS package that pulls from the sysjobhistory table in the other servers. It was a fair amount of work to set up, but it's been running for almost three years now with very few changes.

Regards,

hmscott|||u can build query on system tables for job history in msdb database like : sysjobs, sysjobhistory, sysjobschedules. to make it automated, just schedule the query, then send a notification email, or store to a table, or however you want.
to make a complete check, notice the schedule, coz only already run steps (succeeded, failed, or cancelled) are logged in sysjobhistory.

Automatic Failover Problem

Setup Configuration:

3 servers
- PRINCIPAL IP: 10.2.5.31 - DNS Lookup: db-server-2.mosside.choruscall.com
- MIRROR IP: 10.2.5.30 - DNS Lookup: sql-mirror.mosside.choruscall.com
- WITNESS ip: 10.2.5.32 - DNS Lookup: sql-witness.mosside.choruscall.com

Each Server is running Windows Server 2003 Enterprise Edition with SQL Server 2005 Enterprise Edition.
All server instances are enabled for remote connections(By default they are not).
All servers have the flag 1400 traceon and have been restarted.
PORT 5022 is unrestricted on network.

The server instances are connecting via certificates. Each server has an endpoint for the certificates to to connect on.

Certificate Setup Proceedure:

Principal_Host:
1. Create Master Key with Password

2. Create certificate with subject

3. Create endpoint for certificate (Listener_Port = 5022, Listener_ip = all)
to connect on for database_mirroring

4. Backup Certificate (principal_cert.cer)

5. Take backed up certificate to Mirror_Host

(Reapeat Steps 1-5 for Witness and Mirror)

Mirror_Host: Create Certificate on Mirror_Host for inbound connections from Principal:

6.(On Mirror_Host) Create Login for Principal using same password in step 1 (principal_login)

7. Create user for login just created. (principal_user)

8. Create local certificate for Principal on Mirror using certificate generated by principal.

ex: Create Certificate Principal_cert Authorization Principal_user FROM FILE='c:\principal_cert.cer'

9. (If an endpoint has been created already on the mirror)Grant connectiion to the login:

ex: Grant connect on endpoint::mirror_endpoint to principal_login

Repeat Steaps 6-9 for Principal and Witness Servers accordingly.

10. Import Database to SQL Server 2005 Principal Instance

11. Backup Database to disk with format

12. Backup Database log file to disk with format

13. Copy backups to mirror

14. Restore Database and log file with norecovery on Mirror_Host

15. Configre Database for Database Mirroring on Principal Server
There are two ways to do this. Via the wizzard or via the Transact-SQL window.
Using the wizzard appears to work since I started using FQDN.

PROBLEM:

After configuration, everythig appears to be correct. That is, the principal displays
that it is the principal and it is synchronized with the mirror. The mirror also displays that it is the
mirror and it is synchronized with the principal and it is in recovery. If I failover manually, the mirror
becomes the principal and the principal becomes the mirror (They form a quarum). If I disconnect the principal
from the network, the mirror is supposed to form a quarum with the witness and promote itself to principal status.
This is not what is happening. The witness recognizes that the principal is down and logs that info into its log file.
The Mirror attempts to contact the witness but cannot log onto the machine. The Mirror Logs the following:

Error: 1438, Severity: 16, State: 2.
The server instance Witness rejected configure request; read its error log file for more information.
The reason 1451, and state 3, can be of use for diagnostics by Microsoft.
This is a transient error hence retrying the request is likely to succeed.
Correct the cause if any and retry.

<<<<<<<MIRROR SERVER >>>>>>>>

2007-09-06 15:08:45.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:08:45.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:05.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:05.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:25.33 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:25.33 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:45.34 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:45.34 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:05.35 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:10:05.35 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:25.36 spid23s Error: 1438, Severity: 16, State: 2.

<<<<<<< WITNESS SERVER >>>>>>>>

2007-09-06 14:19:55.90 spid52 The Database Mirroring protocol transport is now listening for connections.
2007-09-06 15:07:11.64 spid24s Error: 1479, Severity: 16, State: 1.
2007-09-06 15:07:11.64 spid24s The mirroring connection to "TCP://db-server-2:5022" has timed out for database "APS_SQL_DEV" after 10 seconds without a response. Check the service and network connections.
2007-09-06 15:07:43.20 Server Error: 1474, Severity: 16, State: 1.
2007-09-06 15:07:43.20 Server Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://db-server-2:5022'.
2007-09-06 15:08:06.03 spid9s Error: 1474, Severity: 16, State: 1.
2007-09-06 15:08:06.03 spid9s Database mirroring connection error 2 'Connection attempt failed with error: '10060(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.)'.' for 'TCP://db-server-2:5022'.

hello,

from the logs it looks like you are not using the fully-qualified domain name (fqdn) when you are establishing the mirroring sessions. this is most likely the reason for the lack of connectivity.

when all the partners are up, you can use the sys.database_mirroring views on the principal and the mirror to figure out whether they both can talk to the witness (when all three nodes are up). if all connections are up, then automatic failover should happen. in your case most probably the mirror's connection to the witness will be shown as "disconnected", but if you use the fqdns it should work.

hth,

kaloian.

|||Trying giving the fully qualified domain name and see if it works as shown below,

IPCONFIG /ALL
Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' and you then have the partner name.
On the mirror server, you would just repeat the same command, but with the principal server named :

ALTER DATABASE [AdventureWorks] SET PARTNER =
N'TCP://A.corp.mycompany.com:5022'

On the principal server, you next specify the witness server:

ALTER DATABASE [AdventureWorks] SET WITNESS =
N'TCP://W.corp.mycompany.com:5026'

|||

Thank for replying. I appreciate it very much.

I had to add the FQDN in the host file in Windows->system32->drivers->etc->host on the mirror server and principal server inatnaces and I restarted the witness server after I setup failover. Once I did all that...it worked.

Thanks Again!

Chris

Automatic Failover Problem

Setup Configuration:

3 servers
- PRINCIPAL IP: 10.2.5.31 - DNS Lookup: db-server-2.mosside.choruscall.com
- MIRROR IP: 10.2.5.30 - DNS Lookup: sql-mirror.mosside.choruscall.com
- WITNESS ip: 10.2.5.32 - DNS Lookup: sql-witness.mosside.choruscall.com

Each Server is running Windows Server 2003 Enterprise Edition with SQL Server 2005 Enterprise Edition.
All server instances are enabled for remote connections(By default they are not).
All servers have the flag 1400 traceon and have been restarted.
PORT 5022 is unrestricted on network.

The server instances are connecting via certificates. Each server has an endpoint for the certificates to to connect on.

Certificate Setup Proceedure:

Principal_Host:
1. Create Master Key with Password

2. Create certificate with subject

3. Create endpoint for certificate (Listener_Port = 5022, Listener_ip = all)
to connect on for database_mirroring

4. Backup Certificate (principal_cert.cer)

5. Take backed up certificate to Mirror_Host

(Reapeat Steps 1-5 for Witness and Mirror)

Mirror_Host: Create Certificate on Mirror_Host for inbound connections from Principal:

6.(On Mirror_Host) Create Login for Principal using same password in step 1 (principal_login)

7. Create user for login just created. (principal_user)

8. Create local certificate for Principal on Mirror using certificate generated by principal.

ex: Create Certificate Principal_cert Authorization Principal_user FROM FILE='c:\principal_cert.cer'

9. (If an endpoint has been created already on the mirror)Grant connectiion to the login:

ex: Grant connect on endpoint::mirror_endpoint to principal_login

Repeat Steaps 6-9 for Principal and Witness Servers accordingly.

10. Import Database to SQL Server 2005 Principal Instance

11. Backup Database to disk with format

12. Backup Database log file to disk with format

13. Copy backups to mirror

14. Restore Database and log file with norecovery on Mirror_Host

15. Configre Database for Database Mirroring on Principal Server
There are two ways to do this. Via the wizzard or via the Transact-SQL window.
Using the wizzard appears to work since I started using FQDN.

PROBLEM:

After configuration, everythig appears to be correct. That is, the principal displays
that it is the principal and it is synchronized with the mirror. The mirror also displays that it is the
mirror and it is synchronized with the principal and it is in recovery. If I failover manually, the mirror
becomes the principal and the principal becomes the mirror (They form a quarum). If I disconnect the principal
from the network, the mirror is supposed to form a quarum with the witness and promote itself to principal status.
This is not what is happening. The witness recognizes that the principal is down and logs that info into its log file.
The Mirror attempts to contact the witness but cannot log onto the machine. The Mirror Logs the following:

Error: 1438, Severity: 16, State: 2.
The server instance Witness rejected configure request; read its error log file for more information.
The reason 1451, and state 3, can be of use for diagnostics by Microsoft.
This is a transient error hence retrying the request is likely to succeed.
Correct the cause if any and retry.

<<<<<<<MIRROR SERVER >>>>>>>>

2007-09-06 15:08:45.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:08:45.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:05.32 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:05.32 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:25.33 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:25.33 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:09:45.34 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:09:45.34 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:05.35 spid23s Error: 1438, Severity: 16, State: 2.
2007-09-06 15:10:05.35 spid23s The server instance Witness rejected configure request; read its error log file for more information. The reason 1451, and state 3, can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
2007-09-06 15:10:25.36 spid23s Error: 1438, Severity: 16, State: 2.

<<<<<<< WITNESS SERVER >>>>>>>>

2007-09-06 14:19:55.90 spid52 The Database Mirroring protocol transport is now listening for connections.
2007-09-06 15:07:11.64 spid24s Error: 1479, Severity: 16, State: 1.
2007-09-06 15:07:11.64 spid24s The mirroring connection to "TCP://db-server-2:5022" has timed out for database "APS_SQL_DEV" after 10 seconds without a response. Check the service and network connections.
2007-09-06 15:07:43.20 Server Error: 1474, Severity: 16, State: 1.
2007-09-06 15:07:43.20 Server Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://db-server-2:5022'.
2007-09-06 15:08:06.03 spid9s Error: 1474, Severity: 16, State: 1.
2007-09-06 15:08:06.03 spid9s Database mirroring connection error 2 'Connection attempt failed with error: '10060(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.)'.' for 'TCP://db-server-2:5022'.

hello,

from the logs it looks like you are not using the fully-qualified domain name (fqdn) when you are establishing the mirroring sessions. this is most likely the reason for the lack of connectivity.

when all the partners are up, you can use the sys.database_mirroring views on the principal and the mirror to figure out whether they both can talk to the witness (when all three nodes are up). if all connections are up, then automatic failover should happen. in your case most probably the mirror's connection to the witness will be shown as "disconnected", but if you use the fqdns it should work.

hth,

kaloian.

|||Trying giving the fully qualified domain name and see if it works as shown below,

IPCONFIG /ALL
Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' and you then have the partner name.
On the mirror server, you would just repeat the same command, but with the principal server named :

ALTER DATABASE [AdventureWorks] SET PARTNER =
N'TCP://A.corp.mycompany.com:5022'

On the principal server, you next specify the witness server:

ALTER DATABASE [AdventureWorks] SET WITNESS =
N'TCP://W.corp.mycompany.com:5026'

|||

Thank for replying. I appreciate it very much.

I had to add the FQDN in the host file in Windows->system32->drivers->etc->host on the mirror server and principal server inatnaces and I restarted the witness server after I setup failover. Once I did all that...it worked.

Thanks Again!

Chris

Automated db mirroring

I have two database servers. My primary server contains the live database.
I'd like to set up an automated script program to take nightly snapshots of
the live database onto my secondary DB server.
How do I go about doing this?
What you are looking for is called Log Shipping. It is a built-in feature
of Enterprise Edition and can be 'bolted on' to other editions. There is a
simple example in the SQL Server Resource Kit you can adapt for your needs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Ed" <eddiemarino@.hotmail.com> wrote in message
news:OOT502ILEHA.3012@.tk2msftngp13.phx.gbl...
> I have two database servers. My primary server contains the live
database.
> I'd like to set up an automated script program to take nightly snapshots
of
> the live database onto my secondary DB server.
> How do I go about doing this?
>

Automated db mirroring

I have two database servers. My primary server contains the live database.
I'd like to set up an automated script program to take nightly snapshots of
the live database onto my secondary DB server.
How do I go about doing this?What you are looking for is called Log Shipping. It is a built-in feature
of Enterprise Edition and can be 'bolted on' to other editions. There is a
simple example in the SQL Server Resource Kit you can adapt for your needs.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Ed" <eddiemarino@.hotmail.com> wrote in message
news:OOT502ILEHA.3012@.tk2msftngp13.phx.gbl...
> I have two database servers. My primary server contains the live
database.
> I'd like to set up an automated script program to take nightly snapshots
of
> the live database onto my secondary DB server.
> How do I go about doing this?
>

Automated db mirroring

I have two database servers. My primary server contains the live database.
I'd like to set up an automated script program to take nightly snapshots of
the live database onto my secondary DB server.
How do I go about doing this?What you are looking for is called Log Shipping. It is a built-in feature
of Enterprise Edition and can be 'bolted on' to other editions. There is a
simple example in the SQL Server Resource Kit you can adapt for your needs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Ed" <eddiemarino@.hotmail.com> wrote in message
news:OOT502ILEHA.3012@.tk2msftngp13.phx.gbl...
> I have two database servers. My primary server contains the live
database.
> I'd like to set up an automated script program to take nightly snapshots
of
> the live database onto my secondary DB server.
> How do I go about doing this?
>

Automate syncing login ids of master db at remote server

Hi All,
I was hoping for some advice about automating syncing of login ids at
remote servers. I have implemented simple log shipping and that seems
to be working fine, any ids created on the database are replicated to
the standby database without issue, however any changes to the master
database logins are not replicated.
I have looked at the DTS "transfer logins task" however there appear to
be some limitations to this.
I have also looked at several scripts (sp_help_revlogin) provided by
MS, http://www.support.microsoft.com/?id=246133, however this process
seems to be manual, and I would like this to be automated, so that
should there be any changes to logins within the master db, they are
replicated to the standby server the next time the task is run.
Similarly I would like to sync any orphaned users at the standy server.
If anyone could please provide further information I would be grateful.
Many Thanks
Andrew
The way I do this in an automated manner is completely unsupported by
Microsoft.
I create a linked server for the standby. Then alter sp_addlogin as well as
sp_changepassword and add in a call to sp_addlogin and sp_changepassword to
the local server passing the same input arguments along. This causes the
logins to be added to both instances as well as handles password changes.
It is unsupported, because I modify system objects.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142333250.388701.45050@.j33g2000cwa.googlegro ups.com...
> Hi All,
> I was hoping for some advice about automating syncing of login ids at
> remote servers. I have implemented simple log shipping and that seems
> to be working fine, any ids created on the database are replicated to
> the standby database without issue, however any changes to the master
> database logins are not replicated.
> I have looked at the DTS "transfer logins task" however there appear to
> be some limitations to this.
> I have also looked at several scripts (sp_help_revlogin) provided by
> MS, http://www.support.microsoft.com/?id=246133, however this process
> seems to be manual, and I would like this to be automated, so that
> should there be any changes to logins within the master db, they are
> replicated to the standby server the next time the task is run.
> Similarly I would like to sync any orphaned users at the standy server.
> If anyone could please provide further information I would be grateful.
> Many Thanks
> Andrew
>
|||Mike,
Thanks for your help,
It is a novel approach, and ensures up to date login information.
As I understand whenever a user is created via either an application or
Enterprise manager, the sp_addlogin sp is called.
However what if the remote server is unavailable at the time that a new
user is being created on the primary db?
I am not sure if I have the skills to amend these scripts, particularly
given that they are system objects.
Thanks once again,
Andrew
|||I have continued to search the web for a solution and have found some
scripts provided by Umachandar Jayachandran at
http://www.sqlmag.com/Article/Articl...er_25710.html,
which seem to work, in that the logins ids are located within
sysxlogins table within the master db on the standby server, however
these logins are not visible under users within Enterprise Manager,
does this matter?
CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
set ANSI_NULLS OFF
set ANSI_WARNINGS OFF
DECLARE @.logins cursor
DECLARE @.name sysname, @.password sysname,
@.dbname sysname, @.language sysname,
@.sid binary(16), @.isntuser bit
SET @.logins = cursor fast_forward FOR
SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
l.isntuser
FROM [server\instance].master.dbo.syslogins AS l
WHERE l.loginname IS NOT NULL
OPEN @.logins
WHILE(1=1)
BEGIN
FETCH @.logins INTO @.name, @.password, @.dbname,
@.language, @.sid, @.isntuser
IF @.@.fetch_status < 0 break
IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
CONTINUE
IF @.isntuser = 0
EXEC sp_addlogin @.name, @.password, @.dbname,
@.language, @.sid, 'skip_encryption'
ELSE
BEGIN
EXEC sp_grantlogin @.name
EXEC sp_defaultdb @.name, @.dbname
EXEC sp_defaultlanguage @.name, @.language
END
END
DEALLOCATE @.logins
|||They should be visible. Enterprise Manager is looking at the sysxlogins
table.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142602814.147621.269160@.e56g2000cwe.googlegr oups.com...
>I have continued to search the web for a solution and have found some
> scripts provided by Umachandar Jayachandran at
> http://www.sqlmag.com/Article/Articl...er_25710.html,
> which seem to work, in that the logins ids are located within
> sysxlogins table within the master db on the standby server, however
> these logins are not visible under users within Enterprise Manager,
> does this matter?
> CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
> set ANSI_NULLS OFF
> set ANSI_WARNINGS OFF
> DECLARE @.logins cursor
> DECLARE @.name sysname, @.password sysname,
> @.dbname sysname, @.language sysname,
> @.sid binary(16), @.isntuser bit
> SET @.logins = cursor fast_forward FOR
> SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
> l.isntuser
> FROM [server\instance].master.dbo.syslogins AS l
> WHERE l.loginname IS NOT NULL
> OPEN @.logins
> WHILE(1=1)
> BEGIN
> FETCH @.logins INTO @.name, @.password, @.dbname,
> @.language, @.sid, @.isntuser
> IF @.@.fetch_status < 0 break
> IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
> CONTINUE
> IF @.isntuser = 0
> EXEC sp_addlogin @.name, @.password, @.dbname,
> @.language, @.sid, 'skip_encryption'
> ELSE
> BEGIN
> EXEC sp_grantlogin @.name
> EXEC sp_defaultdb @.name, @.dbname
> EXEC sp_defaultlanguage @.name, @.language
> END
> END
> DEALLOCATE @.logins
>

AutoClose option and Personal and MSDE Edition

Hi there,
As per SQL Server's install information the AutoClose option is set to TRUE
for both the Personal and MSDE edition installations. My problem is that any
new databases created (ie snapshots) have the AutoClose option set to TRUE.
How can I change the way this option was set on installation since it's
obviously not paying attention to the model database which has the AutoClose
option set to FALSE.
Any info would be appreciated
thanks
razmanaz wrote:
> Hi there,
> As per SQL Server's install information the AutoClose option is set
> to TRUE for both the Personal and MSDE edition installations. My
> problem is that any new databases created (ie snapshots) have the
> AutoClose option set to TRUE. How can I change the way this option
> was set on installation since it's obviously not paying attention to
> the model database which has the AutoClose option set to FALSE.
> Any info would be appreciated
> thanks
AutoClose and AutoShrink default to True for both the Desktop and MSDE
editions of SQL Server. I think you need to use sp_dboption after
database creation to turn the options off. THe model database cannot
override the default settings. For MSDE, you may want to leave it on to
prevent SQL Server from using too much memory when the database is
inactive, unless this is causing a real performance issue. One way to
prevent this is to keep at least one connection open to the database at
all times while the application is running.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi David,
Thanks for responding.
However I should have mentioned that the AutoClose has to be set to FALSE
for both Personal and MSDE editions. There is no problem for resources so I
do not need to close the databases to save memory/CPU etc.
Also the sp_dboption script "sp_dboption <database>, autoclose, FALSE" won't
work because the snapshots are continually updated and the script cannot run
continually each time a new snapshot is created. This needs to be changed at
the ground floor.
So another question. If I were to install the Standard edition or higher the
install sets the default to FALSE. I just need to know where the low level
setting is so I can change it so that the changes will take effect any
database that is automatically created.
"David Gugick" wrote:

> razmanaz wrote:
> AutoClose and AutoShrink default to True for both the Desktop and MSDE
> editions of SQL Server. I think you need to use sp_dboption after
> database creation to turn the options off. THe model database cannot
> override the default settings. For MSDE, you may want to leave it on to
> prevent SQL Server from using too much memory when the database is
> inactive, unless this is causing a real performance issue. One way to
> prevent this is to keep at least one connection open to the database at
> all times while the application is running.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||razmanaz wrote:
> Hi David,
> Thanks for responding.
> However I should have mentioned that the AutoClose has to be set to
> FALSE for both Personal and MSDE editions. There is no problem for
> resources so I do not need to close the databases to save memory/CPU
> etc.
> Also the sp_dboption script "sp_dboption <database>, autoclose,
> FALSE" won't work because the snapshots are continually updated and
> the script cannot run continually each time a new snapshot is
> created. This needs to be changed at the ground floor.
> So another question. If I were to install the Standard edition or
> higher the install sets the default to FALSE. I just need to know
> where the low level setting is so I can change it so that the changes
> will take effect any database that is automatically created.
>
You can't change the default on a Windows 2000/XP installation since all
you get is the Desktop Edition for those OSes AFAIK.
I'm not clear why sp_dboption won't work. What happens when you execute
the command to change the autoclose option.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi David,
The script to set the AutoClose = FALSE works with no problems when I run
it. It's just that databases in my environment get created all the time. I'm
not sitting there waiting for them and running the script on each and every
database that gets created. That's why I was asking if there was a way to
change the default so that I wouldn't have to do that.
"David Gugick" wrote:

> razmanaz wrote:
> You can't change the default on a Windows 2000/XP installation since all
> you get is the Desktop Edition for those OSes AFAIK.
> I'm not clear why sp_dboption won't work. What happens when you execute
> the command to change the autoclose option.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||If model doesn't cut it, I suggest you create an Agent job that walk the databases where autoclose
is on and for each such database turn it off. Then schedule this to run every day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound like the typical
environment to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"razmanaz" <razmanaz@.discussions.microsoft.com> wrote in message
news:EF1A3B40-5B1D-40F0-8541-5A807631BC65@.microsoft.com...[vbcol=seagreen]
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I run
> it. It's just that databases in my environment get created all the time. I'm
> not sitting there waiting for them and running the script on each and every
> database that gets created. That's why I was asking if there was a way to
> change the default so that I wouldn't have to do that.
> "David Gugick" wrote:
|||razmanaz wrote:
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I
> run it. It's just that databases in my environment get created all
> the time. I'm not sitting there waiting for them and running the
> script on each and every database that gets created. That's why I was
> asking if there was a way to change the default so that I wouldn't
> have to do that.
Why can't you add the autoclose to the scripts that are used to create
the databases?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Auto Update Stats

I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.Is this the one you are referring to?
http://support.microsoft.com/defaul...b;EN-US;q195565
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.

>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>

Auto Update Stats

I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.
Is this the one you are referring to?
http://support.microsoft.com/default...;EN-US;q195565
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.
|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.

>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>

Auto Update Stats

I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.Is this the one you are referring to?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195565
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"GoBoy" <anonymous@.discussions.microsoft.com> wrote in message
news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
I have the above set on all my databases on one of our
2000 production servers. However, this server has a lot
of databases (about 150) and about 300+ Gig on it. I
remember reading an article posted here about an issue
where if a server has a lot of databases, it may need
manual runs of stats.
Can someone repoint me to that article? It actually
outlined the algorithm that Sql Server used, etc.
I couldn't find it in the kb or bol.|||Great article but that's not it. This article dealt more
with how Sql Server "round robins" through the databases
and the timing of that, etc.
>--Original Message--
>Is this the one you are referring to?
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q195565
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"GoBoy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:178a01c4c1c5$ba8f0630$a501280a@.phx.gbl...
>I have the above set on all my databases on one of our
>2000 production servers. However, this server has a lot
>of databases (about 150) and about 300+ Gig on it. I
>remember reading an article posted here about an issue
>where if a server has a lot of databases, it may need
>manual runs of stats.
>Can someone repoint me to that article? It actually
>outlined the algorithm that Sql Server used, etc.
>I couldn't find it in the kb or bol.
>
>.
>