Need help on TSQL script


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"

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:

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 ...


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'''
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.


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)
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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?


David ToddSenior Database AdministratorCommented:

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.


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]

/****** 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

USE [msdb]

/****** Object:  Job [DBCC]    Script Date: 12/22/2011 09:57:58 ******/
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)
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


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBCC', 
		@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', 
		@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', 
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
GOTO EndSave


Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sg05121983Author Commented:
David ToddSenior Database AdministratorCommented:

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 ...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.