:::: MENU ::::

Posts Categorized / DBCC Commands

  • Mar 06 / 2009
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, Transaction log files

Check for any open transaction in transaction log

There may exist an open transaction within the transaction log. In that case if the BACKUP LOG statement is used, only the inactive part of the log can be truncated. An open transaction can prevent the log from truncating completely. To identify that open transaction use DBCC OPENTRAN in a database.
It will provide the transaction information if there is such present. If no such open transaction exists then an informative message will be displayed.

  • Nov 11 / 2008
  • 0
DBCC Commands, dbDigger, System Stored Procedures

To perform a T-SQL task for all databases in SQL Server

Sometimes we need to perform a specific task on all databases. Like to run a specific SP on all database. In that case if you have a number of databases (likely in most of cases) then an automated method is required rather than to perform the task on each database one by one. In SQL Server T-SQL we have a system stored procedure (sp) sp_msforeachdb for this purpose. For example if you want to check database integrity of all databases, then you may run the following command.

 
EXEC sp_msforeachdb 'DBCC CHECKDB()'  
 GO  

The command DBCC CHECKDB() will be executed for all databases one by one.

  • Sep 02 / 2008
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, System Stored Procedures

Check size and disk allocation for a database

Space is an important element to be considered. A DBA has to keep track of space in all aspects and it is good practice to keep an eye on all trends that your DB is going on in case of space. Here are some use full T-SQL commands that will help you in space analysis and tracking.

DBCC showfilestats
This DBCC command will show use full information regarding to data file of current database.

sp_spaceused
This command will provide use full information about total size, used size, unused size, index size and data size of current database.

sp_databases
This use full command will provide you size information of all databases. The size shown is combined size of data and log files.

DBCC CHECKALLOC
This use full DBCC command will give you a very detailed view of disk allocation for current database. You will find every information here about storage allocation.

DBCC CHECKALLOC
sp_helpdb adventureWorks
This use full command will provide value able info about mentioned database. It gives other usefull information along with size of data and log files.

helpdb

  • May 21 / 2008
  • 0
DBCC Commands, dbDigger

T-SQL Database Console Command statements

DBCC
The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for Microsoft SQL Server. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems.
Database Console Command statements are grouped into these categories.
Maintenance Statements:
  • DBCC DBREINDEX
  • DBCC DBREPAIR
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • DBCC UPDATEUSAGE
Miscellaneous Statements:
  • DBCC dllname (FREE)
  • DBCC HELP
  • DBCC PINTABLE
  • DBCC ROWLOCK
  • DBCC TRACEOFF
  • DBCC TRACEON
  • DBCC UNPINTABLE
Status Statements:
  • DBCC INPUTBUFFER
  • DBCC OPENTRAN
  • DBCC OUTPUTBUFFER
  • DBCC PROCCACHE
  • DBCC SHOWCONTIG
  • DBCC SHOW_STATISTICS
  • DBCC SQLPERF
  • DBCC TRACESTATUS
  • DBCC USEROPTIONS
Validation Statements:
  • DBCC CHECKALLOC
  • DBCC CHECKCATALOG
  • DBCC CHECKCONSTRAINTS
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKIDENT
  • DBCC CHECKTABLE
  • DBCC NEWALLOC
Pages:12
Consult us to explore the Databases. Contact us