In theory, you should never need to manually check the integrity of your database, because nothing should ever go wrong with the database, the memory, or the I/O subsystems. However, in practice, checking the integrity of your database will give you confidence and ensure that your database files are in good shape, as well as detect any problems early enough to resolve them with minimal pain.
PAGE_VERIFY OptionsEach database has an option that you can configure to determine how much integrity checking is happening in real time with your database pages. You can set the option to OFF, TORN_PAGE_DETECTION, or CHECKSUM. In general, you should set the option to CHECKSUM. Each time that the SQL Server database page is written to or read from the disk, a checksum is computed from the page. If that checksum does not match what's been previously recorded on the page, SQL Server knows that the page should no longer be trusted. Enabling the CHECKSUM option does cause some additional CPU workload so that the checksum can be computed. If your SQL Server workload became such that you needed to reduce your CPU workload, you could consider turning this feature off, but at the risk of failing to detect database page corruption.
DBCC CHECKDBDBCC CHECKDB is the primary tool you will use to check the integrity of your database outside of the page verification options. You run this Transact-SQL command just like you run any other Transact-SQL statement. To run a DBCC CHECKDB command, you must be either a system administrator (a member of the sysadmin role) or a member of the DB_Owner fixed database role (or be the dbo user). DBCC CHECKDB does the following: * It checks the integrity of your database physically, verifying that the pages of your database are allocated correctly. This check can be run separately as a DBCC CHECKALLOC command if you want only a physical integrity check, rather than a full CHECKDB.
* It runs a DBCC CHECKTABLE command on all the tables and views in your database, verifying that the pages that belong to that table or view are actually allocated to the table. Additionally, it verifies the other internal structures of the objects.
* It runs a DBCC CHECKCATALOG command, verifying the integrity of the system tables.
* It validates indexes, varbinary(max) links with FILESTREAM objects, and service broker data. You do not need to run each of these separate commands. DBCC CHECKDB will take care of them all for you. If DBCC CHECKDB finds any problems, you can repair the database by using one of the REPAIR options, or you can restore the database from a backup. Unless you are very confident in what you are doing and in exactly what the REPAIR option is going to accomplish, you should not use the command without Microsoft or your Dassault Systèmes support team on the telephone with you. Read more: Dassault Systèmes ENOVIA V6 on SQL Server
PAGE_VERIFY OptionsEach database has an option that you can configure to determine how much integrity checking is happening in real time with your database pages. You can set the option to OFF, TORN_PAGE_DETECTION, or CHECKSUM. In general, you should set the option to CHECKSUM. Each time that the SQL Server database page is written to or read from the disk, a checksum is computed from the page. If that checksum does not match what's been previously recorded on the page, SQL Server knows that the page should no longer be trusted. Enabling the CHECKSUM option does cause some additional CPU workload so that the checksum can be computed. If your SQL Server workload became such that you needed to reduce your CPU workload, you could consider turning this feature off, but at the risk of failing to detect database page corruption.
DBCC CHECKDBDBCC CHECKDB is the primary tool you will use to check the integrity of your database outside of the page verification options. You run this Transact-SQL command just like you run any other Transact-SQL statement. To run a DBCC CHECKDB command, you must be either a system administrator (a member of the sysadmin role) or a member of the DB_Owner fixed database role (or be the dbo user). DBCC CHECKDB does the following: * It checks the integrity of your database physically, verifying that the pages of your database are allocated correctly. This check can be run separately as a DBCC CHECKALLOC command if you want only a physical integrity check, rather than a full CHECKDB.
* It runs a DBCC CHECKTABLE command on all the tables and views in your database, verifying that the pages that belong to that table or view are actually allocated to the table. Additionally, it verifies the other internal structures of the objects.
* It runs a DBCC CHECKCATALOG command, verifying the integrity of the system tables.
* It validates indexes, varbinary(max) links with FILESTREAM objects, and service broker data. You do not need to run each of these separate commands. DBCC CHECKDB will take care of them all for you. If DBCC CHECKDB finds any problems, you can repair the database by using one of the REPAIR options, or you can restore the database from a backup. Unless you are very confident in what you are doing and in exactly what the REPAIR option is going to accomplish, you should not use the command without Microsoft or your Dassault Systèmes support team on the telephone with you. Read more: Dassault Systèmes ENOVIA V6 on SQL Server
Posts
0 comments:
Post a Comment