i have a database which is 500GB in size. there has never been a integrity check done on this DB till date. this is a production DB. could you let me know if it is a good practice to run a DB integrity check? any pre requisites i must know before scheduling the maintenance task?
its sql server 2008 standard edition, 8CPU, 24GB RAM.
The best practice is to do the checkdb on a test system. Copy the prod backup to a test server, restore it and run checkdb there. This should be automated. Many people don't have the storage or servers to automate this, but this is the only way to protect production. Checkdb is a very IO intensive task. If your system is 24x7 like mine, it will be noticed when checkdb is running unless you have very fast disks. Our SAN admins can pinpoint exactly when all of our checkdb jobs run. We do have some of them automated for the backup/restore, but we have too many databases to do that for all of them.