February 8, 2011 at 7:05 am
Hello,
I am checking at my new job, maintence plan and I don't see that they do integrity, is there reason you don't need to do it? if not, if I check option to check database integrity,should I include indexes
Attempt to repair any minor problems or
Exclude indexes
or
Perform these tests before backing up the database or transaction log.
Thank you
February 8, 2011 at 7:24 am
Yes you can skip the checkdb it if you don't care about that database and its data.
For reindex see this script : http://sqlfool.com/2010/04/index-defrag-script-v4-0/[/url]
I'd never run checkdb unattended with auto repair. It's better to assess that situation manually.
For the log, I usually take the tlog backup right after the reindex. However we have fast servers and faster san so we don't really care when we do it as long as it's daily. It might be different for you.
February 8, 2011 at 7:31 am
I care about these database and data, it is a production data, so I will check database integrity with include indexes right? Any specific time I should run? I am assuming evening on the weekend when enviroment is not busy, right?
February 8, 2011 at 7:38 am
The indexing job I sent out can be run in prod if most of the indexes are really small. But it's better to run in downtime of course.
Here's the script I use to run checkdb.
USE [msdb]
GO
/****** Objet : Table [dbo].[DBA_CheckDB] Date de génération du script : 02/08/2011 09:35:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBA_CheckDB](
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [nvarchar](2048) NULL,
[RepairLevel] [nvarchar](22) NULL,
[Status] [int] NULL,
[DbId] [smallint] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [smallint] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [smallint] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [smallint] NULL,
[DbName] [sysname] NULL
) ON [PRIMARY]
GO
--This goes in a scheduled job
INSERT INTO msdb.dbo.DBA_CheckDB
EXEC ('DBCC CHECKDB(''PROD-FORDIA'') WITH TABLERESULTS, NO_INFOMSGS')
UPDATE msdb.dbo.DBA_CheckDB SET DbName = 'PROD-FORDIA' WHERE DbName IS NULL
IF @@ROWCOUNT <> 0 --corruption in DB, disable all backup jobs, warn admins
BEGIN
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA (DATA)', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA (DIFFERENTIAL)', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA (LOGS 15)', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA (LOGS 30)', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA (LOGS 45)', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Reorganize indexes PROD-FORDIA', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA NEW', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Delete old backups', @enabled=0
EXEC msdb.dbo.sp_update_job @job_name=N'Backup PROD-FORDIA LOG NEW', @enabled=0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'JOB SQL',
@recipients = '<list hidden for forum>',
@query = 'SET NOCOUNT ON; SELECT * FROM msdb.dbo.DBA_CheckDB' ,
@subject = 'ERREUR FATALE, CORRUPTION DE LA BD PROD-FORDIA',
@attach_query_result_as_file = 1,
@body = 'Voici la query qui réaffiche les résultats de CHECKDB : SELECT * FROM msdb.dbo.DBA_CheckDB',
@importance = 'HIGH',
@query_result_separator = '|',
@file_attachments = '\\Fordiavcenter\Informatique\Navision backup\Help, my database is corrupt_ Now what - SQLServerCentral.mht' -- nvarchar(max);
END
February 8, 2011 at 9:50 am
Thank you very much, 2 questions:
1.Can I do this for all db including system db
2. Why don't you use maintance plan for that?
February 8, 2011 at 9:58 am
Yes, tho I remember reading that some systems tables are not checked and that it can cause bugs later when you upgrade to 2k5+.
I preffer to script everything manually. That way I have full 100% control of what's going on and how things are happenning.
If you look at the sqlfool script you'll see that you can include or exclude precise indexes, time limits with this base script. Then you can even add more rules than that. You can never get that level of control with the maintenance plans.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply