Solved

dbcc on one or several databases

Posted on 2012-12-27
5
261 Views
Last Modified: 2013-01-22
Hello,

How can I modify this script for executing dbcc on one or several databases?


Open in new window

function main()
{
      if ($verbose) {$VerbosePreference = "Continue"}
      if ($debug) {$DebugPreference = "Continue"}
      dbcc_checkdb $I $D
}

function dbcc_checkdb ($I, $D)
{
      $cn = new-object system.data.SqlClient.SqlConnection( `
            "Data Source=$I;Integrated Security=SSPI;Initial Catalog=$D");
      $ds = new-object System.Data.DataSet "dsCheckDB"
      $query = "DBCC CHECKDB($D) WITH TABLERESULTS"
      $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $cn)
      $da.Fill($ds)

      $dtCheckDB = new-object "System.Data.DataTable" "dsCheckDB"
      $dtCheckDB = $ds.Tables[0]
      $dtCheckDB | Format-Table -autosize `
      -property Error, Level, State, MessageText, `
                                    RepairLevel, Status, DbId, ObjectId, `
                                    IndexId, PartitionId, AllocUnitId, File, `
                                    Page, Slot, RefFile, RefPage, RefSlot, Allocation
}

Thanks

bibi
0
Comment
Question by:bibi92
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:sachitjain
ID: 38726112
If you run following logic on master database, it would run dbcc checkdb for all the databases

declare @table table (SNO int identity(1,1), [name] sysname)
declare @i int, @cnt int, @dbname sysname, @query nvarchar(100)

insert into @table ([name])
select [name] from sys.sysdatabases

set @i = 1
while @i <= @cnt
begin
      select @dbname = [name] from @table where SNO = @i
      set @query = ';
                              DBCC CHECKDB;'
      set @query = 'use ' + CAST(@dbname as varchar(30)) + ' ' + @query
      print @query
      exec sp_executesql @query
      set @i = @i + 1
end
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38727424
Well, the script appears to be OK with a single database - right ?

So, the question is how to make it loop around for multiple $D given the instance of $I

Ideally, create a procedure on your SQL Server and simply pass the $D as a parameter to the stored procedure and the parameter can have multiple database names seperated by a delimiter (like a comma).

Or, create an arrary of database names and then use that to loop around and build up your query. Or, if you need to process each database seperately (because of results) then you will also need to include keeping the results / processing before moving onto next.

Or, you could build up a query to go through each one and collect the results (and I have cheated a little by simply referring to column names as col1 col2 etc). Note that the #checkdb temp table MUST match the correct number of columns, so if doing a different checkdb then that table will be different....

if object_id('tempdb..#checkdb','U') is not null drop table #checkdb
create table #checkdb (id int identity,[error] varchar(100), [level] varchar(100), [state] varchar(100), [MessageText] varchar(555), [repairlevel] varchar(100), [status] varchar(100), col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 varchar(100), col5 varchar(100), col6 varchar(100), col7 varchar(100), col8 varchar(100), col9 varchar(100), col10 varchar(100),col11 varchar(100), col12 varchar(100), col13 varchar(100), col14 varchar(100), col15 varchar(100), col16 varchar(100), col17 varchar(100))

insert #checkdb (MessageText) values ('dbcc checkdb(my_db) with tableresults')
insert #checkdb
exec ('dbcc checkdb(my_db) with tableresults')

insert #checkdb (MessageText) values ('dbcc checkdb(adventureworks2012) with tableresults')
insert #checkdb
exec ('dbcc checkdb(adventureworks2012) with tableresults')

select * from #checkdb

Open in new window


And the above could be made into a SP on the server and pass a delimited list of DB names
0
 

Author Comment

by:bibi92
ID: 38745298
Thanks but I have to use powershell.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 38745959
OK...

Then have a look at : http://cornasdf.blogspot.com.au/2012/06/powershell-to-run-dbcc-checkdb-loop-for.html

Thats doing a checkdb, with no info messages, and you want with tableresults, so, the output will be slightly different.

But the important part is it shows how to loop around (I wouldnt kill the connection each time either).
0
 

Author Closing Comment

by:bibi92
ID: 38804936
Thanks bibi
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 and SQL 2014 in memory database 11 31
Record extraction 3 14
Extract XML Data from using TSQL 5 30
Sql query 34 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now