Note:-All the samples here are run against AdventureWorks sample DB
DBCC proccache
Provides procuedure cache information
DBCC proccache
dbcc showfilestats
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
dbcc showfilestats
DBCC cachestats
show cache information cache hit ratio , number of times cache is used and pages used.
DBCC cachestats
DBCC activecursors
DBCC activecursors [SPID]
Provide activecursors information for selected DBCC Commands
DBCC activecursors (59)
DBCC showtableaffinity
DBCC showtableaffinity ('person.address')
DBCC detachdb
It will detach database.
DBCC detachdb ('db name')
sp_msforeachtable
sp_msforeachtable is will execute the command against every table in the database.
Exec sp_msforeachtable 'select convert(varchar(512),''?''),count(*) from ?'
sp_msindexspace
sp_MSindexspace tablename , index_name
Will provide information of index_id, index_name, size, additional comment for all index for selected table.
exec sp_msindexspace '[sales].[storecontact]'
sp_MStablespace
sp_MStablespace tablename
Provide information for tables space
sp_mstablespace 'person.address'
sp_msforeachdb
Will run the command for each database in the server
exec sp_msforeachdb 'sp_spaceused'
sp_MShelpindex
sp_MShelpindex tablename , indexname , flags
sp_mshelpindex 'person.address'
Returns index name, status , index id, fillfactor , columns, fulltext key, order etc...
sp_MShelpcolumns
Provide information for columns of a table
sp_MShelpcolumns 'person.address'
sp_MShelptype
Provides information about system data types and user data types.
exec sp_MShelptype
xp_fixeddrives
Provide free space information for each disk.
exec xp_fixeddrives
DBCC proccache
Provides procuedure cache information
DBCC proccache
dbcc showfilestats
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
dbcc showfilestats
DBCC cachestats
show cache information cache hit ratio , number of times cache is used and pages used.
DBCC cachestats
DBCC activecursors
DBCC activecursors [SPID]
Provide activecursors information for selected DBCC Commands
DBCC activecursors (59)
DBCC showtableaffinity
DBCC showtableaffinity ('person.address')
DBCC detachdb
It will detach database.
DBCC detachdb ('db name')
sp_msforeachtable
sp_msforeachtable is will execute the command against every table in the database.
Exec sp_msforeachtable 'select convert(varchar(512),''?''),count(*) from ?'
sp_msindexspace
sp_MSindexspace tablename , index_name
Will provide information of index_id, index_name, size, additional comment for all index for selected table.
exec sp_msindexspace '[sales].[storecontact]'
sp_MStablespace
sp_MStablespace tablename
Provide information for tables space
sp_mstablespace 'person.address'
sp_msforeachdb
Will run the command for each database in the server
exec sp_msforeachdb 'sp_spaceused'
sp_MShelpindex
sp_MShelpindex tablename , indexname , flags
sp_mshelpindex 'person.address'
Returns index name, status , index id, fillfactor , columns, fulltext key, order etc...
sp_MShelpcolumns
Provide information for columns of a table
sp_MShelpcolumns 'person.address'
sp_MShelptype
Provides information about system data types and user data types.
exec sp_MShelptype
xp_fixeddrives
Provide free space information for each disk.
exec xp_fixeddrives
3 comments:
Nice collection
Please add some dbcc commands also.
will do it in short period
The time is moving very quickly and in today’s era everyone knows the solution for solve their data handling problems and they hire the data scientist for this. So if you also want to hire the data scientist for solving your won data handling and management problems than just visit this activewizards.com/ for getting the best data solution provider for you.
Post a Comment