Need help on TSQL script

Hi,

Please help me on creating cursor on below script. I need to execute the below script against all DB's in partcualr server & need to store output in local drive.

EXEC sp_Msforeachdb "DBCC checkdb ('?') with physical_only"

Thanks
sg05121983Asked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

I've just checked and the usual way of getting results from system stored procedures doesn't work.

insert #SomeTempTable
execute sp_SomeStoredProcedre

I know that the SQL Agent job history captures the results, but it has a limited length, and its just one line ...

I don't think that running the above on 300-400 databases during business hours is that good an idea - likely to create all kinds of blocking while checkdb completes. This is normally run out of hours - hence me harping on about SQL Agent jobs.

Here is what I got to work:
Call SQLCmd and specify an output file.
sqlcmd -S romeo\sql2008r2 -E -Q "dbcc checkdb( 'ManagementDW' ) with physical_only" -o c:\data\tmp\output.txt

Unfortunately the output file get overwritten each execution.

SQLCmd is a command-line query interface, starting I think with SQL 2005, and takes over from osql and isql.

HTH
  David

ps code is example job with one job step for one database. I suggest thinking about managling in the database name and datetime into the filename ...
 
USE [msdb]
GO

/****** Object:  Job [DBCC]    Script Date: 12/22/2011 09:57:57 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBCC')
EXEC msdb.dbo.sp_delete_job @job_id=N'38edb555-7770-482d-a8d9-25a0c5838878', @delete_unused_schedule=1
GO

USE [msdb]
GO

/****** Object:  Job [DBCC]    Script Date: 12/22/2011 09:57:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/22/2011 09:57:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBCC', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'DTCCONSULTANTS\digit', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DBCC CheckDB ManagementDW]    Script Date: 12/22/2011 09:57:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBCC CheckDB ManagementDW', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'sqlcmd -S romeo\sql2008r2 -E -Q "dbcc checkdb( ''ManagementDW'' ) with physical_only" -o c:\data\tmp\output.txt', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

I have to ask - what is the desired result?

The simple way to do this is that you have a scheduled job that runs dbcc checkdb, and in that case put a notification on it to send an email alert when the job fails.

If the sp_MSForEachDB is causing issues by consuming the error, then one way is to put each database in their own job step, assuming that a) your server is in good enough nick and b) only one database will show errors at any one time ...

HTH
  David

PS you could use sp_MSForEachDB to help generate a script which runs checkdb on all databases for one job step
sp_MSForEachDB 'print ''dbcc checkdb( ''''?'''' ) with physical_only'''
0
 
JestersGrindCommented:
If you're running this from SSMS, you can select query --> Results to --> Results to file.  This will save the output to a file and will ask you for the file name when you execute it.

If you are running this from a job, you can select an output file in the advanced tab of the step definition.

Either way, the command that you listed should work.

Greg

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
sg05121983Author Commented:
Thanks Dtood\JestersGrind.

I am running above commend from SSMS (Query Analyzer). we need to analyze the output of CheckDB. we have server and that server contains around 300-400 user databases.

Instaed of running single DB (it will take long time), we have to run checkdb command against all DB's on particular server and need output (it may sucessfull\failure).

Please share the script, that will suitable for my requirement (With physical_only option)
0
 
JestersGrindCommented:
The command that you have listed should work.  sp_Msforeachdb will run DBCC CHECKDB against every database in that particular instance.  If you configure SSMS to save the results as a file, you will get the results in a single file.  Is that not what you require?  Are you receiving an error message?

Greg

0
 
sg05121983Author Commented:
--
0
 
David ToddSenior DBACommented:
Hi,

Can I ask why the grade of B? With no further comment for almost a month, no request for follow-up or where the suggested script didn't meet your requirements ...

David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.