Sample Header Ad - 728x90

Setting up Database Integrity Checks

2 votes
0 answers
456 views
I'm relatively new to SQL and, unfortunately, am definitely not at the level of a DBA - However, it's become my job to look into some SQL issues... There's a Database Integrity Check set to run for one of our customers every evening from 21:30 - This usually runs for approximately 2-5 hours (give or take) however in recent times has increased in duration reaching up to 8-9 hours - I'm not too clear on causation here, but I believe the databases are quite large and this could potentially just be down to how large those databases are. From reading through https://social.technet.microsoft.com/wiki/contents/articles/54312.sql-server-database-integrity-check-best-practices.aspx - It suggests separating the Integrity checks into phases - My question is this: **Would I be setting up separate scheduled jobs on separate times/days to space these checks out?** Presumably running the below would be no different than me simply running CHECKDB, correct? EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'ALL_DATABASES', @CheckCommands = 'CHECKALLOC,CHECKTABLE,CHECKCATALOG' So, in theory I would guess that I want three separate scheduled jobs at different times like so: EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'ALL_DATABASES', @CheckCommands = 'CHECKALLOC' EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'ALL_DATABASES', @CheckCommands = 'CHECKTABLE' EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'ALL_DATABASES', @CheckCommands = 'CHECKCATALOG' Would we say that this is accurate? Thanks for any time spent on answering this query. 1) Microsoft SQL Server 2012, exact version would be 11.0.5058.0 (for MSSMS) 2) There's one single Step in the job named "Integrity Check" of the type "T-SQL" and within that is the script I list above (dbo.DatabseIntegrityCheck across ALL_DATABASES) 3) Total size of all DB's from what I can see would be 1.1 TB
Asked by Yeldur (21 rep)
Dec 14, 2022, 11:37 AM
Last activity: Jan 6, 2023, 06:59 AM