Hey guys,

I'm new to managing SQL servers with Server Enterprise Manager. (v.8). There are a bunch of databases set up on this server, some of which are replicating to another server or db. I'm trying to set up an integrity check on the main database and I'm trying to setup the mail session of the SQL server agent so I can get emails is something is not working.

My questions are:
If I stop and start the SQL server agent so the mail session property can be turned on, what will happen with the replication that is running? and are there any other events that are going to happen if I do this?

What is the best way to set up an integrity check for the Main database? How often should it run? Will it affect the replication process? And how do I schedule it to run when I want it to?

Thanks,
JD

When you stop and restart SQL Agent, the replication agents should come back up normally. You can check this in Replication Monitor in Enterprise Manager, by looking for the Distribution Agents folder. Some will not show as running, if they are on an actual schedule, as opposed to running continuously. This is OK. You should be able to right click on each agent to check what the actual schedule is.

I usually do the integrity check (here, I am assuming you mean DBCC CHECKDB) on Sundays. Some people do theirs every day. This is because the performance can suffer while the integrity check is running. The actual impact for your database(s) will be something you will have to determine for yourself, though.

The replication agents are running constantly. I will have to wait for a down time to see if I can start/stop the SQL server agent to get the email working.

Yes I was talking about DBCC CHECKDB. I found it in a maintenance plan and it was set to run every night at 11pm but there was never a record of it happening in the log? I'm not sure why? I know a lot of different backups happen around 12am or 00:00 so maybe it wasn't getting a chance to finish?

How would I determine the impact on my database from running it nightly to once a week??

Thanks for all your help!
JD

I am not sure if the Maintenance plan logs the DBCC run, but the SQL Server errorlog should.

As for determining the impact, run it, and watch perfmon, task manager, current processes, and see if there are any adverse effects. I would wager you are not talking about 100GB+ databases.

I am assuming it does log the DBCC CHECKDB runs because I have all my other database's checkdbs showing up, just not for the 2 main ones that run close to other backup stuff.

Yeah I'm using old crappy servers and trying to get everything ported over to a new one eventually. Too many little home-made programs and scripts that I can't find them all. Not to mention the VAX programs and quel DBs haha but I won't go to far into it.

The databases I work with are smaller then 1 gig...

Thanks again! I'll see if I can monitor some stuff and see how it goes.

For databases at that size, you probably won't even notice a DBCC CHECKDB running, unless you were specifically looking for it. Run it any time. If you have doubts about the hardware, run it daily, so you can pick up errors due to hardware failures.