July 30, 2014 at 1:43 pm
Hey Folks,
I'm new at this SQL Server stuff and all I'm trying to do is run a 'dbcc checkdb <some database> (for now) via possibly sql server agent or maybe powershell.
And so all i'm trying to do is email the summary of the out put and i'm googling this and it seems like i might have to start reading up on long sql scripts. Is there anyway
of doing this which is easy ? I don't want to run dbcc's on all databases (for now) - just on 1 or 2 . And i only have 4 db servers. I was thinking of just running a job or script locally on those servers.
Any ideas would really be welcome. And, oh, can you be as specific as you can as i'm new at this.
Thanks,
Chris
July 30, 2014 at 1:46 pm
This script works pretty well.
http://spaghettidba.com/2013/02/27/dba_runcheckdb-v2012/
Gianluca coded it to send results via email as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 2:04 pm
SQLRNNR,
This code has hundreds of lines to it. I've got no clue what it does. I'm not going to put something like this on a production server not knowing what it does.
THanks anyway.
July 30, 2014 at 2:13 pm
We wouldn't expect you to put code on your server without knowing what it does. But you want a specific ability to email results of the run of checkdb. That takes plenty of code and plenty of review effort.
The code at Gianlucas site does just that and does it rather nicely. But test it out and confirm on a test server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 4:28 am
cdm33.com (7/30/2014)
SQLRNNR,This code has hundreds of lines to it. I've got no clue what it does. I'm not going to put something like this on a production server not knowing what it does.
THanks anyway.
That is an excellent answer.
Jason is trustworthy and has probably pointed you at a good script, but I love your answer.
You can use the Maintenance Plans and SQL Agent to automate this easily. Just be extremely cautious about the Maintenance Plans. It'll look like they do everything for you short of make coffee in the morning, but they can be problematic. However, focused use, like just having it do DBCC checks, should be perfectly fine.
You'll find though, that over time, as you gain experience, the level of control with Maintenance Plans is too low. You'll eventually want to run scripts to get the job done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2014 at 8:09 am
Grant Fritchey (7/31/2014)
cdm33.com (7/30/2014)
SQLRNNR,This code has hundreds of lines to it. I've got no clue what it does. I'm not going to put something like this on a production server not knowing what it does.
THanks anyway.
That is an excellent answer.
Jason is trustworthy and has probably pointed you at a good script, but I love your answer.
You can use the Maintenance Plans and SQL Agent to automate this easily. Just be extremely cautious about the Maintenance Plans. It'll look like they do everything for you short of make coffee in the morning, but they can be problematic. However, focused use, like just having it do DBCC checks, should be perfectly fine.
You'll find though, that over time, as you gain experience, the level of control with Maintenance Plans is too low. You'll eventually want to run scripts to get the job done.
There is that option. I see many problems with maintenance plans (even with strict focused use). One big problem is the Plan reporting success but actually doing nothing at all. That would be extremely frustrating as a DBA - especially if corruption did occur.
Another simple step would be to do a foreachdb that runs checkdb for each database. Sadly that means you might run into the foreachdb problem where it occasionally skips a database.
Another alternative is to write out the command for each database, put it in a separate job step and save that as a job. Then setup an operator and notifications for job failure. That doesn't get the checkdb results/output, which means coding something else to trap the results and report the results.
IMHO, if a simple solution is wanted and elegant results needed - it will take more lines of code. The simplest and most accurate of these options of course is the last option proposed where you script the checkdb for each database and put it into individual job steps.
😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 9:22 am
Grant Fritchey (7/31/2014)
cdm33.com (7/30/2014)
SQLRNNR,This code has hundreds of lines to it. I've got no clue what it does. I'm not going to put something like this on a production server not knowing what it does.
THanks anyway.
That is an excellent answer.
Jason is trustworthy and has probably pointed you at a good script, but I love your answer.
+1000 I can't tell you how many times experienced people put things they don't understand into production. It drives some of us (myself included) crazy. To hear you describe yourself as "new at this" and say that gives me hope. Perfect attitude - keep it.
July 31, 2014 at 9:30 am
Jason,
Thanks for your input. I'm looking at the last option where i script the checkdb for each database then. I'm hoping the script will not be long - maybe a command like 'dbcc checkdb <databasename>' . I'll have to read up on 'job steps' and i hope the output will give me what i want - namely, the results of dbcc. I find it pretty surprising though that with all the elaborate tools that windows and studio manager comes with - that you can't easily send an email with output of dbcc. oh well, i'm naive. I have to get back to linux and mysql i suppose where i feel a bit more comfortable.
July 31, 2014 at 9:58 am
cdm33.com (7/31/2014)
Jason,
Thanks for your input. I'm looking at the last option where i script the checkdb for each database then. I'm hoping the script will not be long - maybe a command like 'dbcc checkdb <databasename>' . I'll have to read up on 'job steps' and i hope the output will give me what i want - namely, the results of dbcc. I find it pretty surprising though that with all the elaborate tools that windows and studio manager comes with - that you can't easily send an email with output of dbcc. oh well, i'm naive. I have to get back to linux and mysql i suppose where i feel a bit more comfortable.
dbcc checkdb (databasename) with no_infomsgs,tableresults,ALL_ERRORMSGS,DATA_PURITY
As far as job steps, it is a pretty straight forward thing. I recommend using the GUI to build the job, then hit the script button (top of the GUI). Then you will be able to see everything that is being done to create the job. Poke around in the GUI, you will see where the steps are (and schedules too).
One note about DATA_PURITY. That isn't really necessary but is recommended on the first checkdb you run against the master database. It is a default but sometimes master doesn't get that check done until after you explicitly tell it to do it the first time through.
Every database should have the checkdb run against it. Once you get a feel for it and your SQL Server, then start tweaking the schedule and the job (maybe split into multiple jobs etc).
You can also omit the tableresults option. That is a preference/styling option. I like to see results in table format.
No_infomsgs gets rid of the noise.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 10:29 am
Jason,
Not knowing too much about 'Maintenance Plans' and Jobs under 'SQL Server agent' in SSMS, does one just create a job without going through the Maintenance Plan or is the 'Job' part of the Maintenance plan ? Clearly, i need to read up. It's just that I'm stuck with production servers and no lab servers to test anything out.
Thanks for your help though.
CHris
July 31, 2014 at 10:38 am
Skip the Maintenance plan node and go straight down to SQL Server Agent. Expand that node. Right-click on the Jobs node and select New Job from the Context menu.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 1:35 pm
Jason,
Thanks for your help - i think i got it. it's a bit nerve racking testing on production.
July 31, 2014 at 2:16 pm
cdm33.com (7/31/2014)
Jason,Thanks for your help - i think i got it. it's a bit nerve racking testing on production.
Let's just up-vote that say 1 million times.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 9:14 pm
Janson, Grant and Ed,
After poking around and reading what you had to say about what I wanted to do, I think I'm off to a good start. It's just a pain trying to do stuff without a lab sometimes.
Great forum by the way.
Thanks a lot,
Chris
July 31, 2014 at 10:04 pm
cdm33.com (7/31/2014)
Great forum by the way.Thanks a lot,
Chris
Glad you like it. Stick around and you will learn quite a bit.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply