DBCC: DBCC TRACEON and other trace related DBCC commands in SQL Server

Details

Written by Ranjeet

For monitoring SQL Server, there are features called trace. SQL Server provide different trace flag number. Being a DBA, you may need to trace some flag for monitoring purpose. There three DBCC command available in this regards.

Trace related DBCC Command

1. DBCC TRACEON
2. DBCC TRACEOFF
3. DBCC TRACESTATUS

Sample output of TRACESTATUS

0 Comments

DBCC: DBCC LOGINFO SQL Server DBCC commands in SQL Server

Details

Written by Ranjeet

Just for information, this DBCC command is not documented. DBCC LOGINFO is a very useful DBCC command yet not often used by DBA. But when a DBA troubleshoot log file related issue, then may require to see what happening inside the log file. DBCC LOGININFO is used to see information about virtual logs (VDF) inside the log file. It gives the tabular output containing FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN columns. Each row corresponds to one VLF.

Each column is important but status column in particular gives you the information about what portions of the log are in use and which are in not. If value is 0 means VSF is not in use and ready for reuse and if it is 2 it means this portion of the log is still in use. Even when status value is 2, it is not necessarily transaction is still active. DBCC OPENTRAN may not produce any active transaction at all but status showing is 2 for many rows. Here point to be understand that, value 2 of status column might be meaning that these VLF are waiting for backup, replication or mirroring. So, it can’t be reuse until it operation is complete.

As we know, the log is being written sequential in order. The FSeqNo is also notable column to check the file sequence number. Log File sequence number is circular in nature.

DBCC LOGINFO command

-->> DBCC LOGINFO

DBCC LOGINFO

Register to read more...

0 Comments

DBCC: DBCC OPENTRAN SQL Server DBCC commands in SQL Server

Details

Written by Ranjeet

DBCC OPENTRAN is one of the frequently used command to know about the active transaction in database. I very important DBCC command which give information about active transaction as below. In run for specific database

 Display the oldest active transaction

1. Show the active transaction, which some time prevent log truncation
2. Display Oldest distributed and non- distributed replicated transaction
3. It doesn’t need any parameter

DBCC OPENTRAN Example

DBCC OPENTRAN 

OUTPUT when there is no open transaction

Register to read more...

0 Comments

DBCC: Log file related informatics and troubleshooting DBCC commands in SQL Server

Details

Written by Ranjeet

I often use DBCC SQLPERF to get information about usage of log file for all the databases. And then DBCC OPENTRAN to see if any transaction is active and in the process to troubleshoot further. If we see any open transaction, then further we run sp_who2 to find the spid. We all do these very often but you ever used DBCC LOGININFO dbcc command to get more information about inside of log file. You might be NO or very hardly. Me too the same answer. Let go through one by one about these DBCC commands.

Log File Related useful DBCC Command

1. DBCC SQLPERF
2. DBCC OPENTRAN
3. DBCC LOGINFO

Click each of the DBCC command to get the detailed description with example associated with it. 

0 Comments

DBCC: DBCC SHOWCONTIG and other SHOW related commands in SQL Server

Details

Written by Ranjeet

SQL Server has series of SHOW related DBCC commands which does same kind of work for different purposes. One of the frequent used is DBCC SHOWCONTING which returns detailed metadata about tables/objects. Similarly, we have many mores. Let’s have brief introduction of those.

SHOW DBCC commands

DBCC SHOW_STATISTICS: This DBCC command take two parameters, one is table name and other is target which use to be index or statistics name but not column name. It shows the contents of query optimization statistics table stored in the database. It’s display out is header, histogram and density vector.

DBCC SHOWCONTIG: It display the detailed metadata for table data and index which includes, fragmentation information, number of paged, number of rows etc. Its takes table name as parameter value.

DBCC PDW_SHOWEXECUTIONPLAN: This is new DBCC command which specially designed for Azure or PDW (Parallel data warehouse) data warehouse. It displays the execution plan for a query running on data warehouse or PDW compute or control node.

DBCC PDW_SHOWPARTITIONSTATS: This one is again a new DBCC command which specially designed for Azure or PDW (Parallel data warehouse) data warehouse. It displays the size and number of rows for each partitions of a table in data ware house or PDW.

DBCC PDW_SHOWSPACEUSED: This again a new DBCC command which specially designed for Azure or PDW (Parallel data warehouse) data warehouse. It is again useful DBCC commands which helps getting more inside metadata about tables. It displays the number of rows, disk space reserved, and disk space used for a table of data warehouse or PDW.

0 Comments

DBCC: DBCC CHECKDB and other CHECK related commands in SQL Server

Details

Written by Ranjeet

When we read too many DBCC commands, sometimes forget to remember. But if you group them on the basis of its work nature its becomes easy to remember and understand the different types of DBCC commands SQL Server does provides. One of those type is CHECK. Let me first list those CHECK related DBCC commands first. 

Check DBCC commands

DBCC CHECKDB: It checks the logical and physical integrity of all the objects in a particular database. It does CHECKALLOC, CHECKTABLE and CHECKCATELOG operation.

DBCC CHECKALLOC: It checks the consistency of disk space allocation structure for a particular database. CHECKDB run CHECKALLOC to check the consistency of disk space allocation within for specified database. It is used to check and fix the database corruption if any.

DBCC CHECKTABLE: It check the logical and physical integrity of a specified table or indexed view. It checks the integrity of all the pages and structures that make up the table or indexed view. CHECKDB run CHECKTABLE within for each tables.

DBCC CHECKCATELOG: This command run to check the consistency of catalog within the database. CHECKDB run CHECKCATELOG internally to check catalog integrity within for specified database.

DBCC CHECKCONSTRAINTS: It is again an important DBCC commands which used to check the constraint integrity for either particular or all the constraint for a table.

DBCC CHECKFILEGROUP: As its names states, it run on filegroup level. This DBCC command checks the allocation and structural integrity of all the tables and indexed view in a particular file group in selected database. You can say it is the combination of CHECKALLOC and CHECKTABLE which runs on filegroup level.

DBCC CHECKIDENT: It is not commonly used but it is very useful to check and reset the identity value in a table. 

0 Comments

DBCC: Check when last time DBCC CHECDB was run in SQL Server using DBCC DBINFO

Details

Written by Ranjeet

Recently, I have been asked to check when DBCC CHECKDB was run on all the database. My first thought came is, let’s find some DMV for this. But I got DBCC command itself to get this information. DBCC DBINFO is very useful DBCC commands which gives you bunch of information about the database regarding different operations.  But for particular CHECKDB last run information, search for value “dbi_dbccLastKnownGood” in Field column. See below DBCC command and its output

Check when last time DBCC CHECDB was run

-->> Check when last time DBCC CHECDB was run

DBCC DBINFO(TESTDB) WITH TABLERESULTS 

OUTPUT

Above output says it never run. You can create a report for all the databases and plan your schedule. 

0 Comments

DBCC: DBCC SQLPERF in SQL Server

Details

Written by Ranjeet

I have been using DBCC SQLPERF from long back to monitor log space for users and system databases log file. There are two more important usages of this DBCC command which DBA mostly missed it. I will be listing its three different usage here.

1. Monitor log space
2. Reset Wait Statistics
3. Reset Latch Statistics

Monitor Log Space

-->> DBCC SQLPERF

DBCC SQLPERF(LOGSPACE)

GO 

OUTPUT

Reset Wait Statistics

-->> DBCC SQLPERF, reset wait statistics

DBCC SQLPERF("sys.dm_os_wait_stats", clear)

GO 

OUTPUT

Reset Latch Statistics

-->> DBCC SQLPERF, reset wait statistics

DBCC SQLPERF("sys.dm_os_latch_stats", clear)

GO 

OUTPUT

0 Comments

DBCC: DBCC FREESHESSIONCACHE and DBCC FREESYSTEMCACHE in SQL Server

Details

Written by Ranjeet

I have written different many articles on DBCC commands. There many DBCC commands available to manage SQL Server. In this article I am going to let you know the use of DBCC FREESESSIONCACHE and DBCC FREESYSTEMCACHE.

1. DBCC FREESESSIONCACHE: As it names states about free up session cache. It is true but it actually, flushes the connection caches only which is used by distributed query.
2. DBCC FREESYSTEMCACHE: It free/remove all the unused entry from all the cache. However, SQL also does this in background periodically.

Syntax

USE TESTDB

GO

-->> DBCC FREESESSIONCACHE

DBCC FREESESSIONCACHE

GO

Register to read more...

0 Comments

TEMPDB: tempdb related useful DMVs in SQL Server

Details

Written by Ranjeet

There are few things in SQL server needs to maintained properly. One of them is tempdb. There are many myths about tempdb in DBAs. To maintain tempdb, SQL server has introduced many DMVs which help collecting us tempdb related metadata. It helps us to understand the tempdb and its property and the current state which ultimately required to troubleshoot if any. Below are those DMVs. These DMVs help us collecting metadata about space usage inside the tempdb on database/object level, session level and task level.

1. Sys.dm_db_file_space_usage: Stores metadata about disk space usage statistics by “user object”, “internal objects” and “version store”
2. Sys.dm_db_session_space_usage: Stores metadata about disk space usage, page allocation and deallocation statistics at user session level.
3. Sys.dm_db_task_space_usage: Stores metadata about disk space usage, page allocation and deallocation statistics at session task level.
4. Sys.dm_tran_active_snapshot_database_transactions: its returns a virtual table for all active transaction running in tempdb

   Register to read more...

   0 Comments