Is there any way to execute a stored procedure, get it's thread id and pause it in someway...particularly in another stored procedure?
If not, I guess I could manually create a mechanism. This would most likely be used for stored procedures that run for long amounts of time in a while loop where i could just check a flag in a record representing the instance of the stored procedure at the top of the loop and do a WAITFOR DELAY within a nexted loop and have the condition set to end when the flag is marked as 'run' as opposed to 'halt.'You can look at the SP. Find a query that it hasn't executed yet and lock a table that is involved in that query.
You could also dedicate a resource table to the SP, code a select or update of that table before every statement then locking that table will pause the SP.|||Does the SQL Profiler read information from a table in the master database or something? I am wondering how it knows what stored procedures are running and if I can use that information in this experiment.
Showing posts with label pausing. Show all posts
Showing posts with label pausing. Show all posts
Wednesday, March 21, 2012
Pausing SQL Server from transact SQL
We have a database that automatically restores at night from another server.
We use a kill all users job and place the database in single user mode.
Unfortunately there is an app on the network that will automatically
reconnect if it's spid is killed.
I'd like to be able to Pause the server from within transact SQL.
The only way I can see to do this is:
exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
Kill the spids >= 50
exec sp_dboption 'Database Name', 'dbo use only', true
exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
Is there a way of pausing without using xp_cmdshell?
PaulThis might work...
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE yourdb
GO
RESTORE ...
That way you should be the single_user before the application can reconnect.
--
HTH
Ryan Waight, MCDBA, MCSE
"Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> We have a database that automatically restores at night from another
server.
> We use a kill all users job and place the database in single user mode.
> Unfortunately there is an app on the network that will automatically
> reconnect if it's spid is killed.
> I'd like to be able to Pause the server from within transact SQL.
> The only way I can see to do this is:
> exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> Kill the spids >= 50
> exec sp_dboption 'Database Name', 'dbo use only', true
> exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> Is there a way of pausing without using xp_cmdshell?
> Paul
>|||Or set the db in restricted user mode, assuming that that nasty app doesn't
use a login that is a member of the db_owner, db_creator or sysadmin roles.
ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
Ryan, your code will not work. _no one_ can be in the database when you want
to restore it, not even the connection that is doing the restore, that
should be connected to a different database. You can do ALTER DATABASE...
and then RESTORE when you are connected to the master database and hope the
application won't be able to log back in between the statements, but that
won't always work.
--
Jacco Schalkwijk
SQL Server MVP
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
>
Since a database can't be restored while it's in use, the database
context needs to be changed to some other database:
USE master
GO
RESTORE ...
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user
mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||Apologies all. USE yourdb will of course use up the SINGLE_USER.
Should have read :-
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
RESTORE ...
--
HTH
Ryan Waight, MCDBA, MCSE
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>|||Thanks guys. Too busy looking at set dboption to remember alter database.
Paul
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>
We use a kill all users job and place the database in single user mode.
Unfortunately there is an app on the network that will automatically
reconnect if it's spid is killed.
I'd like to be able to Pause the server from within transact SQL.
The only way I can see to do this is:
exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
Kill the spids >= 50
exec sp_dboption 'Database Name', 'dbo use only', true
exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
Is there a way of pausing without using xp_cmdshell?
PaulThis might work...
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE yourdb
GO
RESTORE ...
That way you should be the single_user before the application can reconnect.
--
HTH
Ryan Waight, MCDBA, MCSE
"Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> We have a database that automatically restores at night from another
server.
> We use a kill all users job and place the database in single user mode.
> Unfortunately there is an app on the network that will automatically
> reconnect if it's spid is killed.
> I'd like to be able to Pause the server from within transact SQL.
> The only way I can see to do this is:
> exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> Kill the spids >= 50
> exec sp_dboption 'Database Name', 'dbo use only', true
> exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> Is there a way of pausing without using xp_cmdshell?
> Paul
>|||Or set the db in restricted user mode, assuming that that nasty app doesn't
use a login that is a member of the db_owner, db_creator or sysadmin roles.
ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
Ryan, your code will not work. _no one_ can be in the database when you want
to restore it, not even the connection that is doing the restore, that
should be connected to a different database. You can do ALTER DATABASE...
and then RESTORE when you are connected to the master database and hope the
application won't be able to log back in between the statements, but that
won't always work.
--
Jacco Schalkwijk
SQL Server MVP
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
>
Since a database can't be restored while it's in use, the database
context needs to be changed to some other database:
USE master
GO
RESTORE ...
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> This might work...
> ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> USE yourdb
> GO
> RESTORE ...
> That way you should be the single_user before the application can
reconnect.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > We have a database that automatically restores at night from another
> server.
> > We use a kill all users job and place the database in single user
mode.
> > Unfortunately there is an app on the network that will automatically
> > reconnect if it's spid is killed.
> >
> > I'd like to be able to Pause the server from within transact SQL.
> > The only way I can see to do this is:
> >
> > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > Kill the spids >= 50
> > exec sp_dboption 'Database Name', 'dbo use only', true
> > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> >
> > Is there a way of pausing without using xp_cmdshell?
> >
> > Paul
> >
> >
>|||Apologies all. USE yourdb will of course use up the SINGLE_USER.
Should have read :-
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
RESTORE ...
--
HTH
Ryan Waight, MCDBA, MCSE
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>|||Thanks guys. Too busy looking at set dboption to remember alter database.
Paul
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uamRLnZjDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Or set the db in restricted user mode, assuming that that nasty app
doesn't
> use a login that is a member of the db_owner, db_creator or sysadmin
roles.
> ALTER DATABASE yourdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> Ryan, your code will not work. _no one_ can be in the database when you
want
> to restore it, not even the connection that is doing the restore, that
> should be connected to a different database. You can do ALTER DATABASE...
> and then RESTORE when you are connected to the master database and hope
the
> application won't be able to log back in between the statements, but that
> won't always work.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eZhceRZjDHA.1004@.tk2msftngp13.phx.gbl...
> > This might work...
> >
> > ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > GO
> > USE yourdb
> > GO
> > RESTORE ...
> >
> > That way you should be the single_user before the application can
> reconnect.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Paul Cahill" <NOTpaul.cahillNOT@.blueyonder.co.uk> wrote in message
> > news:%23QjaYGZjDHA.1456@.tk2msftngp13.phx.gbl...
> > > We have a database that automatically restores at night from another
> > server.
> > > We use a kill all users job and place the database in single user
mode.
> > > Unfortunately there is an app on the network that will automatically
> > > reconnect if it's spid is killed.
> > >
> > > I'd like to be able to Pause the server from within transact SQL.
> > > The only way I can see to do this is:
> > >
> > > exec master..xp_cmdshell "net pause mssqlserver", No_OUTPUT
> > > Kill the spids >= 50
> > > exec sp_dboption 'Database Name', 'dbo use only', true
> > > exec master..xp_cmdshell "net continue mssqlserver", No_OUTPUT
> > >
> > > Is there a way of pausing without using xp_cmdshell?
> > >
> > > Paul
> > >
> > >
> >
> >
>
Pausing Replication
Several times over the course of a month, our network admin team performs
necessary maintenance due to company moves and general growth. During this
time the connection between my Publisher and Distributor is disconnected. My
Subscriber and Distributor are on the same server. I have three
transactional publications set up and I would like to Pause them until the
connection is restored since I know when the connection is going down.
Is this the correct way to handle this?
From the Distributor in the enterprise manager drill down through the
replication monitor to the Distibution Agents and click on Stop Agent for
all of the publication that I have. Do I also have to Stop Agent on all of
the publications in the Log Reader Agents folder?
This also brings up the question of unplanned connection loss. If for some
reason the connection is lost late at night and I dont know about it until
the next morning do I have to recreate snapshots or re-initialize the
publication?
I would stop SQL Server agent, disable the jobs in the management folder and
then restart the agents. You should only have to stop the distributor or
merge agents.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Butler via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FFDFDD1EDC20@.droptable.com...
> Several times over the course of a month, our network admin team performs
> necessary maintenance due to company moves and general growth. During
> this
> time the connection between my Publisher and Distributor is disconnected.
> My
> Subscriber and Distributor are on the same server. I have three
> transactional publications set up and I would like to "Pause" them until
> the
> connection is restored since I know when the connection is going down.
> Is this the correct way to handle this?
> From the Distributor in the enterprise manager drill down through the
> replication monitor to the Distibution Agents and click on "Stop Agent"
> for
> all of the publication that I have. Do I also have to "Stop Agent" on all
> of
> the publications in the Log Reader Agents folder?
> This also brings up the question of "unplanned" connection loss. If for
> some
> reason the connection is lost late at night and I don't know about it
> until
> the next morning do I have to recreate snapshots or re-initialize the
> publication?
|||Does it matter what order I do this in?
|||no, you can disable the jobs first, which will mean that if they are
scheduled they won't run the next time they are scheduled. However if they
are in continuous mode, you must stop them before or after disabling them.
Then you should bounce SQL Server agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Butler via droptable.com" <forum@.droptable.com> wrote in message
news:4FFEBB746E630@.droptable.com...
> Does it matter what order I do this in?
necessary maintenance due to company moves and general growth. During this
time the connection between my Publisher and Distributor is disconnected. My
Subscriber and Distributor are on the same server. I have three
transactional publications set up and I would like to Pause them until the
connection is restored since I know when the connection is going down.
Is this the correct way to handle this?
From the Distributor in the enterprise manager drill down through the
replication monitor to the Distibution Agents and click on Stop Agent for
all of the publication that I have. Do I also have to Stop Agent on all of
the publications in the Log Reader Agents folder?
This also brings up the question of unplanned connection loss. If for some
reason the connection is lost late at night and I dont know about it until
the next morning do I have to recreate snapshots or re-initialize the
publication?
I would stop SQL Server agent, disable the jobs in the management folder and
then restart the agents. You should only have to stop the distributor or
merge agents.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Butler via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FFDFDD1EDC20@.droptable.com...
> Several times over the course of a month, our network admin team performs
> necessary maintenance due to company moves and general growth. During
> this
> time the connection between my Publisher and Distributor is disconnected.
> My
> Subscriber and Distributor are on the same server. I have three
> transactional publications set up and I would like to "Pause" them until
> the
> connection is restored since I know when the connection is going down.
> Is this the correct way to handle this?
> From the Distributor in the enterprise manager drill down through the
> replication monitor to the Distibution Agents and click on "Stop Agent"
> for
> all of the publication that I have. Do I also have to "Stop Agent" on all
> of
> the publications in the Log Reader Agents folder?
> This also brings up the question of "unplanned" connection loss. If for
> some
> reason the connection is lost late at night and I don't know about it
> until
> the next morning do I have to recreate snapshots or re-initialize the
> publication?
|||Does it matter what order I do this in?
|||no, you can disable the jobs first, which will mean that if they are
scheduled they won't run the next time they are scheduled. However if they
are in continuous mode, you must stop them before or after disabling them.
Then you should bounce SQL Server agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Butler via droptable.com" <forum@.droptable.com> wrote in message
news:4FFEBB746E630@.droptable.com...
> Does it matter what order I do this in?
Pausing Log Shipping Plans
We are doing an IP Restructuring on our DR site over the weekend due to some business and management requirements.I actually have 2 questions. I have implemented log shipping plans for our databases. Which means I will have to pause (disable) my log shipping plans while this is being done and resume (re-enable) after everything is comlete.
Any insights on what else I need to do apart from these?
My second question is this. I am generating transaction log backups every 15 minutes and is averaging at 350MB (this much data every 15 minutes - not to mention that I've got around 40+ databases, all configured for log shipping just for this instance alone). My alert threshold is 45 minutes. My log shipping plan for one database starts failing probably because 15 minutes is not enough to transfer 300+MB-sized backup file over a WAN locate across the globe on a not-so-high bandwidth and restore it to the destination. Any recommendations on this apart from increasing the threshold? Your assistance is highly appreciated
Log shipping should not be affected by changing the IP addresses unless you change ones targetted directly by the log shipping jobs. For the WAN case, you can consider adding a step to compress the transaction log backups prior to copying them over the network. This typically improves the end-toend throughput over a WAN.
Regards,
Matt Hollingsworth
Sr. Program Manager
SQL Server High Availability
|||Hi Matt,
Thanks for your reply. Actually, we will be changing the IPs of the destination servers 
Paused? I don't have no stinking time for pausing!
Ok, why are my indices all paused?
Ran out of diskspace the other day. Stopped the Service, moved the indices
to another drive (had a little problem with 1-2 of them which I believe I've
fixed.)
Have restarted, and every full text index on the machine, including one in a
different DB is in a paused state.
They have data and a search on them returns data.
But select fultextcatalogproperty('foo','populatestatus') returns 2.
What's up with that?
(This is on SQL 2000. Thankfully finally I'm moving off of SQL 7.0).
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
MSSearch will pause for several reasons; low disk space; memory pressures,
or if you are running on battery power.
Use the gatherlog utility to see if it gives any more hints as to why they
are paused. You can find it in C:\Program Files\Common Files\MSSearch\Bin
and it is called gthrlog.vbs. Copy this file to %windir%\system32
Then open a command prompt and navigate to the C:\Program Files\Microsoft
SQL Server\MSSQL\FTData\SQLServer\Gatherlogs
then do a dir /od locate a recent log and do this
cscript.exe gthrlog.vbs SQL0000600005.1.gthr
Replacing SQL0000600005.1.gthr with the name of your log.
This should reveal more details as to why MSSearch is pausing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Axfge.25950$ia6.18501@.twister.nyroc.rr.com...
> Ok, why are my indices all paused?
> Ran out of diskspace the other day. Stopped the Service, moved the
indices
> to another drive (had a little problem with 1-2 of them which I believe
I've
> fixed.)
> Have restarted, and every full text index on the machine, including one in
a
> different DB is in a paused state.
> They have data and a search on them returns data.
> But select fultextcatalogproperty('foo','populatestatus') returns 2.
> What's up with that?
> (This is on SQL 2000. Thankfully finally I'm moving off of SQL 7.0).
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O0yvusjVFHA.3176@.TK2MSFTNGP12.phx.gbl...
> MSSearch will pause for several reasons; low disk space; memory pressures,
> or if you are running on battery power.
> Use the gatherlog utility to see if it gives any more hints as to why they
> are paused. You can find it in C:\Program Files\Common Files\MSSearch\Bin
> and it is called gthrlog.vbs. Copy this file to %windir%\system32
> Then open a command prompt and navigate to the C:\Program Files\Microsoft
> SQL Server\MSSQL\FTData\SQLServer\Gatherlogs
> then do a dir /od locate a recent log and do this
> cscript.exe gthrlog.vbs SQL0000600005.1.gthr
> Replacing SQL0000600005.1.gthr with the name of your log.
>
Ok, to reduce the number of variables, I did this on the full text index in
a different database that I did not touch: (i.e. it's not due to me moving
stuff, etc...)
Results are:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
5/10/2005 11:22:04 AM Add The gatherer has started
5/10/2005 11:22:12 AM Add The recovery has completed
5/10/2005 11:38:52 AM Add The gatherer has started
5/10/2005 11:39:04 AM Add The recovery has completed
5/10/2005 12:13:52 PM Add The gatherer has started
5/10/2005 12:14:02 PM Add The recovery has completed
5/10/2005 1:28:02 PM Add The gatherer has started
5/10/2005 1:28:10 PM Add The recovery has completed
5/10/2005 2:03:02 PM Add The gatherer has started
5/10/2005 2:03:12 PM Add The recovery has completed
Yet, the index is still paused.
Just tried to STOP the population on this paused index.
The tail of the new gather file shows:
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004418 Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004c9a Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/000043e6 Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM Add Completed Incremental crawl
And the index still shows itself as paused.
Looking at a gather file on the index (which I did move) in the original DB:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
5/10/2005 11:22:04 AM Add The gatherer has started
5/10/2005 11:22:10 AM Add The initialization has completed
5/10/2005 11:38:52 AM Add The gatherer has started
5/10/2005 11:39:00 AM Add The initialization has completed
5/10/2005 12:13:52 PM Add The gatherer has started
5/10/2005 12:13:58 PM Add The initialization has completed
5/10/2005 1:28:02 PM Add The gatherer has started
5/10/2005 1:28:08 PM Add The initialization has completed
5/10/2005 2:03:02 PM Add The gatherer has started
5/10/2005 2:03:08 PM Add The initialization has completed
And
select fulltextcatalogproperty('foo_bar_1998','populatest atus') as
populatestatus , count(*) as '1998',
fulltextcatalogproperty('foo_bar_1998','itemcount' ) as itemcount,
fulltextcatalogproperty('foo_bar_1998','IndexSize' ) as IndexSize,
fulltextcatalogproperty('foo_bar_1998','UniqueKeyC ount') as
UniqueKeycount,fulltextcatalogproperty('foo_bar_19 98','Logsize') as
LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge') as
PopulateCompletionAge from foo_bar_1998 (nolock)
returns:
Populate status 1998 itemcount, indexssize, uniquekeycount,
logsize populatecompletionage
2 441 442 2
72004 328 482772780
Now, this index had been fully populated before any of these problems
happened, so it should be fine. (i.e. no reason to pause, etc.)
I'm bewildered at this point.
Any other thoughts. (I mean worse comes to worse I'll simply destroy the
indices and rebuild them, but then I'll lose a week or two of time waiting
for the larger ones.)
> This should reveal more details as to why MSSearch is pausing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:Axfge.25950$ia6.18501@.twister.nyroc.rr.com...
> indices
> I've
in
> a
>
|||I am not sure what you mean by restarted in your original post? Do you mean
a reboot? If not, I would reboot your server to see if this clears the
condition. It is baffling.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cnvge.26735$ia6.20761@.twister.nyroc.rr.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O0yvusjVFHA.3176@.TK2MSFTNGP12.phx.gbl...
pressures,[vbcol=seagreen]
they[vbcol=seagreen]
Files\MSSearch\Bin[vbcol=seagreen]
Files\Microsoft
> Ok, to reduce the number of variables, I did this on the full text index
in
> a different database that I did not touch: (i.e. it's not due to me moving
> stuff, etc...)
> Results are:
> Microsoft (R) Windows Script Host Version 5.6
> Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
> 5/10/2005 11:22:04 AM Add The gatherer has started
> 5/10/2005 11:22:12 AM Add The recovery has completed
> 5/10/2005 11:38:52 AM Add The gatherer has started
> 5/10/2005 11:39:04 AM Add The recovery has completed
> 5/10/2005 12:13:52 PM Add The gatherer has started
> 5/10/2005 12:14:02 PM Add The recovery has completed
> 5/10/2005 1:28:02 PM Add The gatherer has started
> 5/10/2005 1:28:10 PM Add The recovery has completed
> 5/10/2005 2:03:02 PM Add The gatherer has started
> 5/10/2005 2:03:12 PM Add The recovery has completed
>
> Yet, the index is still paused.
> Just tried to STOP the population on this paused index.
> The tail of the new gather file shows:
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004418 Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004c9a Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/000043e6 Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM Add Completed Incremental crawl
> And the index still shows itself as paused.
>
> Looking at a gather file on the index (which I did move) in the original
DB:[vbcol=seagreen]
> Microsoft (R) Windows Script Host Version 5.6
> Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
> 5/10/2005 11:22:04 AM Add The gatherer has started
> 5/10/2005 11:22:10 AM Add The initialization has completed
> 5/10/2005 11:38:52 AM Add The gatherer has started
> 5/10/2005 11:39:00 AM Add The initialization has completed
> 5/10/2005 12:13:52 PM Add The gatherer has started
> 5/10/2005 12:13:58 PM Add The initialization has completed
> 5/10/2005 1:28:02 PM Add The gatherer has started
> 5/10/2005 1:28:08 PM Add The initialization has completed
> 5/10/2005 2:03:02 PM Add The gatherer has started
> 5/10/2005 2:03:08 PM Add The initialization has completed
> And
> select fulltextcatalogproperty('foo_bar_1998','populatest atus') as
> populatestatus , count(*) as '1998',
> fulltextcatalogproperty('foo_bar_1998','itemcount' ) as itemcount,
> fulltextcatalogproperty('foo_bar_1998','IndexSize' ) as IndexSize,
> fulltextcatalogproperty('foo_bar_1998','UniqueKeyC ount') as
> UniqueKeycount,fulltextcatalogproperty('foo_bar_19 98','Logsize') as
> LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge') as
> PopulateCompletionAge from foo_bar_1998 (nolock)
> returns:
> Populate status 1998 itemcount, indexssize, uniquekeycount,
> logsize populatecompletionage
> 2 441 442 2
> 72004 328 482772780
> Now, this index had been fully populated before any of these problems
> happened, so it should be fine. (i.e. no reason to pause, etc.)
>
> I'm bewildered at this point.
> Any other thoughts. (I mean worse comes to worse I'll simply destroy the
> indices and rebuild them, but then I'll lose a week or two of time waiting
> for the larger ones.)
>
>
> message
believe[vbcol=seagreen]
one
> in
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OImpBB2VFHA.2196@.TK2MSFTNGP09.phx.gbl...
> I am not sure what you mean by restarted in your original post? Do you
mean
> a reboot? If not, I would reboot your server to see if this clears the
> condition. It is baffling.
Well, restarted means just that, stopping and restarting all the services in
question.
No joy.
So today I rebooted the box, and still no joy.
So I completely removed one index, including the catalog and recreated it
from scratch.
Now at least the status is 1 - population in progress.
Yet after 4 hours, nothing has been indexed.
Very strange.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:cnvge.26735$ia6.20761@.twister.nyroc.rr.com...
> pressures,
> they
> Files\MSSearch\Bin
> Files\Microsoft
> in
moving[vbcol=seagreen]
> DB:
as[vbcol=seagreen]
waiting[vbcol=seagreen]
> believe
> one
7.0).
>
|||when you run profiler does it show lots of activity from PKM, specifically
calls to sp_fulltext_getdata.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Awtie.39724$ia6.30323@.twister.nyroc.rr.com...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OImpBB2VFHA.2196@.TK2MSFTNGP09.phx.gbl...
> mean
> Well, restarted means just that, stopping and restarting all the services
in[vbcol=seagreen]
> question.
> No joy.
> So today I rebooted the box, and still no joy.
>
> So I completely removed one index, including the catalog and recreated it
> from scratch.
> Now at least the status is 1 - population in progress.
> Yet after 4 hours, nothing has been indexed.
> Very strange.
>
> message
why[vbcol=seagreen]
index[vbcol=seagreen]
> moving
user[vbcol=seagreen]
user[vbcol=seagreen]
user[vbcol=seagreen]
original[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge')[vbcol=seagreen]
> as
the[vbcol=seagreen]
> waiting
the[vbcol=seagreen]
including[vbcol=seagreen]
2.
> 7.0).
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eEZZSm0WFHA.2128@.TK2MSFTNGP14.phx.gbl...
> when you run profiler does it show lots of activity from PKM, specifically
> calls to sp_fulltext_getdata.
Well, I sorta "gave in"
I had a suspicion that a 4-5 of the full-text indices I moved may have been
the source of the problem (because I ended up having to hand edit their
registry entries.)
Removing those and the remaining ones started to work.
So now just running an incremental build on the ones that are behind.
Thanks for the help Hilary.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:Awtie.39724$ia6.30323@.twister.nyroc.rr.com...
services[vbcol=seagreen]
> in
it[vbcol=seagreen]
> why
> index
> user
> user
> user
crawl[vbcol=seagreen]
> original
> completed
> completed
> completed
> completed
> completed
> LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge')
uniquekeycount,[vbcol=seagreen]
problems[vbcol=seagreen]
> the
in[vbcol=seagreen]
> the
> including
returns
> 2.
>
Ran out of diskspace the other day. Stopped the Service, moved the indices
to another drive (had a little problem with 1-2 of them which I believe I've
fixed.)
Have restarted, and every full text index on the machine, including one in a
different DB is in a paused state.
They have data and a search on them returns data.
But select fultextcatalogproperty('foo','populatestatus') returns 2.
What's up with that?
(This is on SQL 2000. Thankfully finally I'm moving off of SQL 7.0).
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
MSSearch will pause for several reasons; low disk space; memory pressures,
or if you are running on battery power.
Use the gatherlog utility to see if it gives any more hints as to why they
are paused. You can find it in C:\Program Files\Common Files\MSSearch\Bin
and it is called gthrlog.vbs. Copy this file to %windir%\system32
Then open a command prompt and navigate to the C:\Program Files\Microsoft
SQL Server\MSSQL\FTData\SQLServer\Gatherlogs
then do a dir /od locate a recent log and do this
cscript.exe gthrlog.vbs SQL0000600005.1.gthr
Replacing SQL0000600005.1.gthr with the name of your log.
This should reveal more details as to why MSSearch is pausing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Axfge.25950$ia6.18501@.twister.nyroc.rr.com...
> Ok, why are my indices all paused?
> Ran out of diskspace the other day. Stopped the Service, moved the
indices
> to another drive (had a little problem with 1-2 of them which I believe
I've
> fixed.)
> Have restarted, and every full text index on the machine, including one in
a
> different DB is in a paused state.
> They have data and a search on them returns data.
> But select fultextcatalogproperty('foo','populatestatus') returns 2.
> What's up with that?
> (This is on SQL 2000. Thankfully finally I'm moving off of SQL 7.0).
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O0yvusjVFHA.3176@.TK2MSFTNGP12.phx.gbl...
> MSSearch will pause for several reasons; low disk space; memory pressures,
> or if you are running on battery power.
> Use the gatherlog utility to see if it gives any more hints as to why they
> are paused. You can find it in C:\Program Files\Common Files\MSSearch\Bin
> and it is called gthrlog.vbs. Copy this file to %windir%\system32
> Then open a command prompt and navigate to the C:\Program Files\Microsoft
> SQL Server\MSSQL\FTData\SQLServer\Gatherlogs
> then do a dir /od locate a recent log and do this
> cscript.exe gthrlog.vbs SQL0000600005.1.gthr
> Replacing SQL0000600005.1.gthr with the name of your log.
>
Ok, to reduce the number of variables, I did this on the full text index in
a different database that I did not touch: (i.e. it's not due to me moving
stuff, etc...)
Results are:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
5/10/2005 11:22:04 AM Add The gatherer has started
5/10/2005 11:22:12 AM Add The recovery has completed
5/10/2005 11:38:52 AM Add The gatherer has started
5/10/2005 11:39:04 AM Add The recovery has completed
5/10/2005 12:13:52 PM Add The gatherer has started
5/10/2005 12:14:02 PM Add The recovery has completed
5/10/2005 1:28:02 PM Add The gatherer has started
5/10/2005 1:28:10 PM Add The recovery has completed
5/10/2005 2:03:02 PM Add The gatherer has started
5/10/2005 2:03:12 PM Add The recovery has completed
Yet, the index is still paused.
Just tried to STOP the population on this paused index.
The tail of the new gather file shows:
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004418 Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004c9a Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/000043e6 Add
Error fe
tching URL, (80040d54 - The filtering has been aborted because of a user
action,
such as stopping the crawl. )
5/11/2005 5:59:14 PM Add Completed Incremental crawl
And the index still shows itself as paused.
Looking at a gather file on the index (which I did move) in the original DB:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
5/10/2005 11:22:04 AM Add The gatherer has started
5/10/2005 11:22:10 AM Add The initialization has completed
5/10/2005 11:38:52 AM Add The gatherer has started
5/10/2005 11:39:00 AM Add The initialization has completed
5/10/2005 12:13:52 PM Add The gatherer has started
5/10/2005 12:13:58 PM Add The initialization has completed
5/10/2005 1:28:02 PM Add The gatherer has started
5/10/2005 1:28:08 PM Add The initialization has completed
5/10/2005 2:03:02 PM Add The gatherer has started
5/10/2005 2:03:08 PM Add The initialization has completed
And
select fulltextcatalogproperty('foo_bar_1998','populatest atus') as
populatestatus , count(*) as '1998',
fulltextcatalogproperty('foo_bar_1998','itemcount' ) as itemcount,
fulltextcatalogproperty('foo_bar_1998','IndexSize' ) as IndexSize,
fulltextcatalogproperty('foo_bar_1998','UniqueKeyC ount') as
UniqueKeycount,fulltextcatalogproperty('foo_bar_19 98','Logsize') as
LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge') as
PopulateCompletionAge from foo_bar_1998 (nolock)
returns:
Populate status 1998 itemcount, indexssize, uniquekeycount,
logsize populatecompletionage
2 441 442 2
72004 328 482772780
Now, this index had been fully populated before any of these problems
happened, so it should be fine. (i.e. no reason to pause, etc.)
I'm bewildered at this point.
Any other thoughts. (I mean worse comes to worse I'll simply destroy the
indices and rebuild them, but then I'll lose a week or two of time waiting
for the larger ones.)
> This should reveal more details as to why MSSearch is pausing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:Axfge.25950$ia6.18501@.twister.nyroc.rr.com...
> indices
> I've
in
> a
>
|||I am not sure what you mean by restarted in your original post? Do you mean
a reboot? If not, I would reboot your server to see if this clears the
condition. It is baffling.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cnvge.26735$ia6.20761@.twister.nyroc.rr.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O0yvusjVFHA.3176@.TK2MSFTNGP12.phx.gbl...
pressures,[vbcol=seagreen]
they[vbcol=seagreen]
Files\MSSearch\Bin[vbcol=seagreen]
Files\Microsoft
> Ok, to reduce the number of variables, I did this on the full text index
in
> a different database that I did not touch: (i.e. it's not due to me moving
> stuff, etc...)
> Results are:
> Microsoft (R) Windows Script Host Version 5.6
> Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
> 5/10/2005 11:22:04 AM Add The gatherer has started
> 5/10/2005 11:22:12 AM Add The recovery has completed
> 5/10/2005 11:38:52 AM Add The gatherer has started
> 5/10/2005 11:39:04 AM Add The recovery has completed
> 5/10/2005 12:13:52 PM Add The gatherer has started
> 5/10/2005 12:14:02 PM Add The recovery has completed
> 5/10/2005 1:28:02 PM Add The gatherer has started
> 5/10/2005 1:28:10 PM Add The recovery has completed
> 5/10/2005 2:03:02 PM Add The gatherer has started
> 5/10/2005 2:03:12 PM Add The recovery has completed
>
> Yet, the index is still paused.
> Just tried to STOP the population on this paused index.
> The tail of the new gather file shows:
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004418 Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/00004c9a Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM mssql75://sqlserver/2dffd964/000043e6 Add
> Error fe
> tching URL, (80040d54 - The filtering has been aborted because of a user
> action,
> such as stopping the crawl. )
> 5/11/2005 5:59:14 PM Add Completed Incremental crawl
> And the index still shows itself as paused.
>
> Looking at a gather file on the index (which I did move) in the original
DB:[vbcol=seagreen]
> Microsoft (R) Windows Script Host Version 5.6
> Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
> 5/10/2005 11:22:04 AM Add The gatherer has started
> 5/10/2005 11:22:10 AM Add The initialization has completed
> 5/10/2005 11:38:52 AM Add The gatherer has started
> 5/10/2005 11:39:00 AM Add The initialization has completed
> 5/10/2005 12:13:52 PM Add The gatherer has started
> 5/10/2005 12:13:58 PM Add The initialization has completed
> 5/10/2005 1:28:02 PM Add The gatherer has started
> 5/10/2005 1:28:08 PM Add The initialization has completed
> 5/10/2005 2:03:02 PM Add The gatherer has started
> 5/10/2005 2:03:08 PM Add The initialization has completed
> And
> select fulltextcatalogproperty('foo_bar_1998','populatest atus') as
> populatestatus , count(*) as '1998',
> fulltextcatalogproperty('foo_bar_1998','itemcount' ) as itemcount,
> fulltextcatalogproperty('foo_bar_1998','IndexSize' ) as IndexSize,
> fulltextcatalogproperty('foo_bar_1998','UniqueKeyC ount') as
> UniqueKeycount,fulltextcatalogproperty('foo_bar_19 98','Logsize') as
> LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge') as
> PopulateCompletionAge from foo_bar_1998 (nolock)
> returns:
> Populate status 1998 itemcount, indexssize, uniquekeycount,
> logsize populatecompletionage
> 2 441 442 2
> 72004 328 482772780
> Now, this index had been fully populated before any of these problems
> happened, so it should be fine. (i.e. no reason to pause, etc.)
>
> I'm bewildered at this point.
> Any other thoughts. (I mean worse comes to worse I'll simply destroy the
> indices and rebuild them, but then I'll lose a week or two of time waiting
> for the larger ones.)
>
>
> message
believe[vbcol=seagreen]
one
> in
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OImpBB2VFHA.2196@.TK2MSFTNGP09.phx.gbl...
> I am not sure what you mean by restarted in your original post? Do you
mean
> a reboot? If not, I would reboot your server to see if this clears the
> condition. It is baffling.
Well, restarted means just that, stopping and restarting all the services in
question.
No joy.
So today I rebooted the box, and still no joy.
So I completely removed one index, including the catalog and recreated it
from scratch.
Now at least the status is 1 - population in progress.
Yet after 4 hours, nothing has been indexed.
Very strange.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:cnvge.26735$ia6.20761@.twister.nyroc.rr.com...
> pressures,
> they
> Files\MSSearch\Bin
> Files\Microsoft
> in
moving[vbcol=seagreen]
> DB:
as[vbcol=seagreen]
waiting[vbcol=seagreen]
> believe
> one
7.0).
>
|||when you run profiler does it show lots of activity from PKM, specifically
calls to sp_fulltext_getdata.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Awtie.39724$ia6.30323@.twister.nyroc.rr.com...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OImpBB2VFHA.2196@.TK2MSFTNGP09.phx.gbl...
> mean
> Well, restarted means just that, stopping and restarting all the services
in[vbcol=seagreen]
> question.
> No joy.
> So today I rebooted the box, and still no joy.
>
> So I completely removed one index, including the catalog and recreated it
> from scratch.
> Now at least the status is 1 - population in progress.
> Yet after 4 hours, nothing has been indexed.
> Very strange.
>
> message
why[vbcol=seagreen]
index[vbcol=seagreen]
> moving
user[vbcol=seagreen]
user[vbcol=seagreen]
user[vbcol=seagreen]
original[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
completed[vbcol=seagreen]
LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge')[vbcol=seagreen]
> as
the[vbcol=seagreen]
> waiting
the[vbcol=seagreen]
including[vbcol=seagreen]
2.
> 7.0).
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eEZZSm0WFHA.2128@.TK2MSFTNGP14.phx.gbl...
> when you run profiler does it show lots of activity from PKM, specifically
> calls to sp_fulltext_getdata.
Well, I sorta "gave in"
I had a suspicion that a 4-5 of the full-text indices I moved may have been
the source of the problem (because I ended up having to hand edit their
registry entries.)
Removing those and the remaining ones started to work.
So now just running an incremental build on the ones that are behind.
Thanks for the help Hilary.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:Awtie.39724$ia6.30323@.twister.nyroc.rr.com...
services[vbcol=seagreen]
> in
it[vbcol=seagreen]
> why
> index
> user
> user
> user
crawl[vbcol=seagreen]
> original
> completed
> completed
> completed
> completed
> completed
> LogSize,fulltextcatalogproperty('foo_bar_1998','Po pulateCompletionAge')
uniquekeycount,[vbcol=seagreen]
problems[vbcol=seagreen]
> the
in[vbcol=seagreen]
> the
> including
returns
> 2.
>
Subscribe to:
Posts (Atom)