Running DBCC Commands On Different Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Running DBCC Commands On Different Servers

Author

Topic

heygee
Starting Member

27 Posts

Posted - 2005-05-18 : 06:42:58

Hi all, I can’t seem to find any info on this but am sure it can be done better than I have it already. I am trying to run DBCC commands such as DBCC SHOWFILESTATS and DBCC SQLPERF(LOGSPACE) on other servers from my local machine. I am running these commands as part of a stored procedure which loops through all the servers listed in master..sysservers. The code below just displays the results into one column, where I would like to have the results for DBCC SQLPERF(LOGSPACE) split into different columns so reports can be run against them etc. Any advice

DROP TABLE #SqlPerfTemp
go
CREATE TABLE #SqlPerfTemp (Results varchar(255))
INSERT INTO #SqlPerfTemp(Results) EXEC('master..xp_cmdshell ''ISQL -S MyRemoteServer -U MyRemoteUser -P MyRemotePassword -Q"DBCC SQLPERF(LOGSPACE)"''')
select * from #SqlPerfTemp

Thanks in advance.
Gee

Happiness is a good health and a bad memory!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-18 : 09:36:55

try using
myserver.master.dbo.sp_executesql N'dbcc ...'

see
http://www.nigelrivett.net/SQLTsql/sp_CreateDataLoadScript_Rem.html
for parameterising the sever/name in the command.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

heygee
Starting Member

27 Posts

Posted - 2005-05-18 : 10:11:31

Looks like this one will be a winner!!!!
I have been working on a stored procedure that will be run from one central location to then gather information from all servers attached to my server by getting the info from sysservers, returning the data back to be reported on. I'll post the script as soon as its done... or more questions if I run into another problem. Thanks mate :)

DROP TABLE #SqlPerfTemp
go
CREATE TABLE #SqlPerfTemp (dbName varchar(30), LogSizeMB decimal(18,2), LogSpacePct decimal(18,2), Status int)
INSERT INTO #SqlPerfTemp EXECUTE ('GENCODCSAPP01.master.dbo.sp_executesql N''DBCC SQLPERF(LOGSPACE)''')
select * from #SqlPerfTemp

Happiness is a good health and a bad memory!

heygee
Starting Member

27 Posts

Posted - 2005-05-31 : 10:04:33

I had a little break away from this... and now I have hit a brick wall yet again! Here is the complete code for the stored procedure:

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_spacecheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_spacecheck]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_spacecheck

AS
DECLARE @dbname nvarchar(20)
DECLARE @v_srvname nvarchar(30)
DECLARE @cmd nvarchar(1024)
DECLARE @today datetime
DECLARE @diff int
DECLARE @PARAMDEF nvarchar(200)
--DECLARE @SQLCURSORSTRING nvarchar(500)

SET @diff = datediff(d, '2005-01-01', @today)
SET @today = dateadd(d, datediff(d, '2005-01-01', current_timestamp),'2005-01-01')

DECLARE srv_cursor CURSOR FOR
SELECT srvname FROM master.dbo.sysservers
WHERE srvname NOT IN ('DE00415', 'GENCORBSAPP01')

OPEN srv_cursor
FETCH NEXT FROM srv_cursor INTO @v_srvname
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE ##SQLPERFtemp (dbName varchar(30), LogSizeMB decimal(18,2), LogSpacePct decimal(18,2), Status int)
SET @cmd = 'INSERT INTO #SqlPerfTemp EXECUTE (' + @v_srvname + '.master.dbo.sp_executesql N' + char(39) + 'DBCC SQLPERF(LOGSPACE)' + char(39) + ')'
--INSERT INTO ##SQLPERFtemp EXECUTE ('DBCC SQLPERF(LOGSPACE)')
print '@cmd = ' + @cmd + ''
EXECUTE sp_executesql @cmd
print '@cmd = ' + @cmd + ''
INSERT INTO DBAstats..SQLPERF
SELECT @today, @v_srvname, dbName, convert(decimal(18,2),LogSizeMB), convert(decimal(18,2),LogSizeMB*LogSpacePct/100), convert(decimal(18,2),LogSizeMB)-(convert(decimal(18,2),LogSizeMB*LogSpacePct/100)), convert(decimal(18,2),LogSpacePct)
FROM ##SQLPERFtemp

DROP TABLE ##SQLPERFtemp

CREATE TABLE ##ShowFileStatstemp (fid int, fgrp int, alloc decimal(18,2), used decimal(18,2), grpname varchar(30), dbfilename varchar(100))

print '@v_srvname = ' + @v_srvname + ''
DECLARE @db_cursor CURSOR
SET @cmd = 'SET @db_cursor = CURSOR FOR SELECT name FROM ' + @v_srvname + '.master.dbo.sysdatabases; OPEN @db_cursor'
print '@cmd = ' + @cmd + ''
SET @PARAMDEF = '@db_cursor CURSOR OUTPUT'
print '@PARAMDEF = ' + @PARAMDEF + ''
EXEC sp_executesql @cmd,@PARAMDEF,@db_cursor = @db_cursor OUTPUT
print '@v_srvname = ' + @v_srvname + ''
print '@dbname = ' + @dbname + ''
FETCH NEXT FROM @db_cursor INTO @dbname
--FETCH NEXT FROM @db_cursor INTO @v_srvname

print '@v_srvname = ' + @v_srvname + ''
print '@dbname = ' + @dbname + ''

WHILE @@FETCH_STATUS = 0
BEGIN
print '@v_srvname = ' + @v_srvname + ''
print '@dbname = ' + @dbname + ''

SET @cmd = 'USE ' + @dbname + '
INSERT INTO ##ShowFileStatstemp EXECUTE (''DBCC SHOWFILESTATS'')'
EXECUTE sp_executesql @cmd

FETCH NEXT FROM @db_cursor INTO @dbname
END
CLOSE @db_cursor
DEALLOCATE @db_cursor

INSERT INTO DBAstats..ShowFileStats
SELECT @today, @v_srvname, d.name, convert(decimal(18,2),alloc*64/1024), convert(decimal(18,2),used*64/1024), dbfilename
FROM ##ShowFileStatstemp f, master.dbo.sysdatabases d, master.dbo.sysaltfiles a
WHERE f.dbfilename = a.filename and a.dbid = d.dbid

SET @cmd = 'DROP TABLE ##ShowFileStatstemp'
EXECUTE sp_executesql @cmd

FETCH NEXT FROM srv_cursor INTO @v_srvname
END
CLOSE srv_cursor
DEALLOCATE srv_cursor

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I think I am having trouble with the line:
SET @cmd = 'INSERT INTO #SqlPerfTemp EXECUTE (' + @v_srvname + '.master.dbo.sp_executesql N' + char(39) + 'DBCC SQLPERF(LOGSPACE)' + char(39) + ')'

the message returned is:
@cmd = INSERT INTO #SqlPerfTemp EXECUTE (GENCODCSAPP01.master.dbo.sp_executesql N'DBCC SQLPERF(LOGSPACE)')
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'GENCODCSAPP01'.
@cmd = INSERT INTO #SqlPerfTemp EXECUTE (GENCODCSAPP01.master.dbo.sp_executesql N'DBCC SQLPERF(LOGSPACE)')

(0 row(s) affected)

@v_srvname = GENCODCSAPP01
@cmd = SET @db_cursor = CURSOR FOR SELECT name FROM GENCODCSAPP01.master.dbo.sysdatabases; OPEN @db_cursor
@PARAMDEF = @db_cursor CURSOR OUTPUT
@v_srvname = GENCODCSAPP01

@v_srvname = GENCODCSAPP01
@dbname = master
@v_srvname = GENCODCSAPP01
@dbname = master
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

@v_srvname = GENCODCSAPP01
@dbname = tempdb
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

@v_srvname = GENCODCSAPP01
@dbname = model
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

@v_srvname = GENCODCSAPP01
@dbname = msdb
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(1 row(s) affected)

@v_srvname = GENCODCSAPP01
@dbname = GPMS
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'GPMS'. No entry found with that name. Make sure that the name is entered correctly.

It doesn't seem to be looping through the servers to return the data. Any ideas would be greatly appreciated.
TIA, Gee

Gee

"Happiness is a good health and a bad memory!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources