Shulou(Shulou.com)06/01 Report--

SQL DBCC commonly used statements and repair database methods are what, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this to learn, I hope you can gain something.

maintenance statement

DBCC DBREINDEX rebuilds one or more indexes of tables in the specified database

DBCC DBREPAIR removes corrupted databases

DBCC INDEXDEFRAG defragments clustered and secondary indexes for the specified table or view

DBCC SHRINKDATABASE shrinks the size of data files in the specified database

DBCC SHRINKFILE shrinks the specified data file or log file size of the related database

DBCC UPDATEUSAGE reports and corrects incorrect contents of sysindexes table

This content may result in sp-spaceused

System stored procedures produce incorrect space usage reports

status statement

DBCC INPUTBUFFER displays the last statement sent from the client to MS SQL Server

DBCC OPENTRAN If the oldest active transaction and the oldest distributed and non-distributed replicated transactions exist in the specified database

information associated therewith is displayed. only if there are active transactions or if the database contains replication information

The results are displayed. If there are no active transactions, an informational message is displayed

DBCC OUTPUTBUFFER Returns the current output buffer for the specified system process ID (SPID) in hexadecimal or ASCII format

DBCC PROCECACHE displays information about the procedure cache as a report

DBCC SHOWCONTIG Displays data and index fragmentation information for the specified table

DBCC SHOW_STATISTICS Displays current distribution statistics for the specified target on the specified table

DBCC SQLPERF provides statistics about transaction log space usage in all databases

DBCC TRACESTATUS Displays the status of trace marks

DBCC USEROPTIONS Returns SET options for the activity (settings) of the current connection

authentication statement

DBCC CHECKALLOC checks the consistency of the disk space allocation structure for a specified database

DBCC CHECKCATALOG checks consistency within and between system tables in a specified database

DBCC CHECKCONSTRAINTS Checks the integrity of a specified constraint or all constraints on a specified table

DBCC CHECKDB checks the allocation and structural integrity of all objects in a specified database

DBCC CHECKFILEGROUP checks the allocation and structural integrity of all tables (in the current database) in the specified filegroup

DBCC CHECKIDENT checks the current identity value of the specified table and corrects it if necessary

DBCC CHECKTABLE checks the integrity of the data, indexes, and text, ntext, and image pages of a specified table or indexed view

DBCC NEWALLOC checks the allocation of data and index pages for each table within the extended structure of the database

other statements

DBCC dllname (FREE) Unloads the specified extended stored procedure dynamic link library (DLL) from memory

DBCC HELP Returns syntax information for the specified DBCC statement

DBCC PINTABLE marks a table as resident, which means MS SQL Server does not flush table pages from memory

DBCC ROWLOCK used in MS SQL Server version 6.5 to enable insert row locking (IRL) operations on tables

DBCC TRACEOFF disables the specified trace mark

DBCC TRACEON turns on (enabled) the specified trace mark

DBCC UNPINTABLE marks a table as not resident in memory. After marking the table as not resident in memory,

Table pages in cache can be emptied

Common script methods for repairing databases

--testdb for database name, change all testdb to corresponding database name

--'D:\data\testdb_log.ldf' is the path to the database log file, which should also be changed to the corresponding

use master

go

sp_configure 'allow updates',1

go

reconfigure with override

go

update sysdatabases set status=-32768 where dbid=DB_ID('testdb')

go

--First execute all the above departments of the statement. After execution, change the testdb.ldf file to a name in the storage path of the database.

--and then execute the following sentence.

dbcc rebuild_log('testdb','D:\data\testdb_log.ldf')

go

--After executing the above sentence successfully, execute all the last sentences

sp_dboption 'D:\data\testdb_log.ldf'','dbo use only','false'

go

sp_configure 'allow updates',0

go

reconfigure with override

go

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.