When you say other SP's, are these linked through a linled server or do they reside in the SAME Database/Diff Databases..?

you can use GRANT EXECUTE on {SP Name} to {username}

Or...

When you try to connect to a SQL Server using SQL Server Management Studio, you get a connect to server window.

In that window there is another button on the Bottom...

Does it mean:: you can see the SQL Agent folder but nothing underneath OR You cannot see the SQL Server Agent Folder even.

In the "connect to Server" Dialog Box Click on Options and choose "Named Pipes" from Network Protocols.. click connect.

This is using Windows Authen.

Try this

Check your SQL service account running under.

Run it as a network user and not the Local System account.Then it will have access to the network.

see if this is the issue.

Try this Query..

select hostname,loginame,DB_NAME(dbid),* from sys.sysprocesses

where blocked<>0

and spid >51

order by last_batch desc

The TRUNCATE command maintains the structure and its columns, constraints, indexes etc. While if there are Foreign Key constraints, use DELETE. This will also help you retain any identity column...

Lot of info about Query Tunings etc..

Avoid too many joins

Your Query optimization in overall is dependant on your Database design.

Index play a IMP part.

Get Covering indexes, on mostly used columns

Avoid...

with that in mind, it is obvious that you have index fragmentation. and considering your Fillfactor, it is again obvious the page spits are happening Quick....

It goes slike this:

Let the growth stay at 10% for now, when the Database is smaller, and you should try to reduce this when the DB grows bigger.i.e. lets say...

Question: did yoyu specify the @local = 'local' while using the sp_addserver.

you can run a profiler and check for hostname,application name etc

Or

select qs.SPID,user_name(qs.spid),qs.BLOCKED,qs.WAITTYPE,qs.DBID,qs.CPU,qs.PHYSICAL_IO,

qs.LOGIN_TIME,

qs.LAST_BATCH,

qs.STATUS,

qs.HOSTNAME,PROGRAM_NAME,

qs.HOSTPROCESS,CMD,

qs.NT_DOMAIN,

qs.NT_USERNAME,

qs.NET_LIBRARY,

qs.LOGINAME,object_name(ec.objectid,qs.dbid) as objectid,ec.text as SQLTEXT

from master.sys.sysprocesses AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) ec

What kind of Data, How many rows do you have.. and how many tables.

Some times it might be easier to Just Migrate the Database rather than the Data itself, and...

Did you cross check, that those are not HEAP Tables.?

There is no Direct way to take care of fragmentation on a HEAP Table.

but before going ahead, make sure they are...

Adding to the above::

1. send out a outage notice. (IMP)

2. restart your SQL service/SSIS/SSAS, to get rid of any erreneous connections.

3. Log onto the remote server as a Administrator.

4. Start...