3

A production database was moved to a SQL Server 2005 cluster, I only have dbo rights to the DB and do not have access to SQL Agent. I do have access to the older SQl Server 2005 system which I could use SQL agent.

On the old system there was a job that reindexed all the tables at night. Is there a way to do the same thing using a link server or a remote sql agent service?

Thanks Dwight

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
Dwight T
  • 209
  • 2
  • 7

2 Answers2

2

Easiest thing to do is to ask the DBA to add you as a user to the msdb database & assign the SQLAgentOperatorRole fixed role to you. THen you can create the job locally for your database.

If you want to go down the painful path of using a linked server & calling from SQL Server Agent from another server then create a stored procedure in your database you'd like to re-index & call it via a linked server would be the way to do it.

Are you a local admin in windows on the sql server database? If so, you can use windows scheduled tasks to call sqlcmd that can also start your re-index sproc.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
0

Send an email to your technical support / DBA to get the agent job moved too?

Guy
  • 2,668
  • 2
  • 20
  • 24
  • Or if you know the names of the tables / indexes, script some SQL to do a ALTER INDEX REBUILD on the indexes. – Guy Oct 16 '09 at 15:08
  • Yeah, you can do the reindex manually but there's no way (I've ever seen anyway) to do it from a different instance. – squillman Oct 16 '09 at 15:10
  • Funny, I am the DBA. Just don't have full sysadmin access to the cluster. It's a temp solution until a new cluster is created. – Dwight T Oct 16 '09 at 15:16
  • Why can't you be given sysadmin access to the clustered instance? – SuperCoolMoss Oct 16 '09 at 19:20