Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.