DBCC Showcontig

Question

Post reply

DBCC Showcontig

Rkoganti

SSC Journeyman

Points: 79
More actions
October 17, 2005 at 3:32 pm

#110432

I am planning to run a DBCC ShowContig on a Linked Server. I have linked a server using sp_addlinkedserver script, now i have to run the DBCC command from my local machine on the linked server. Any help is appreciated.

Kewl Guy

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 1

Why a linked server ? You can just execute it from QA ... am I missing someting here ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

barsuk SSChampion Points: 12280 More actions · Answer 2

If you have an access to the linked server, why don't you created a job, run it, save results to the table and analyze it....

Rkoganti SSC Journeyman Points: 79 More actions · Answer 3

Thanks for taking a look Guys.

1. Why linked server?

I have more than 2000 remote servers that i need this performed on and then generate a report. I can do it in QA but saving the results in a local table is hard.(seems Doable)

2. If you have an access to the linked server, why don't you created a job, run it, save results to the table and analyze it....?

I cannot run a DBCC Command on a Linked server and save the reults to my local database table.

Kewl Guy

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

You can run sp_executeSQL remotely.

exec <linkedserver>.master.dbo.sp_executesql N'use <database>; dbcc showcontig (<table&gt with tableresults, all_indexes'

This requires at least ddladmin rights for the linked server login.

I would be tempted to decide what kind of information I wanted to collect and set up scheduled jobs on each remote server to track it to local tables. Then your master server only needs to query the tables for reporting. Or possibly create stored procs on each server to generate the data, and call the stored proc remotely.

I would think it would be better to have a scheduled job on each server that started at 6AM (for example) and collected all the showcontig results, then your master server could start at around 7AM to quickly collect all the interesting results in one query (per server). Running a script to generate hundreds of SHOWCONTIG commands for each of 2000 servers in turn would take forever.

Rkoganti SSC Journeyman Points: 79 More actions · Answer 5

we are not allowed to leave the script on any of the linked server. Idea was to Link Server, Run the script, Collect data to our local server, Remove link to server, DTS the analyzed data to a reporting database(non-SQL). Looks like i am getting a handle on this the hardway.

Thanks anyways Scott!!!

Kewl Guy