Tell me 10 DBCC commands and it uses in SQL Server?

-

 DATA BASE CONSOLE COMANDS are grouped into 4 categories-


1 Informational Statements (tasks that gather and display various type of infomation)

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC OUTPUTBUFER

DBCC PROCCACHE

DBCC SHOW_STATICS

DBCC SQLPERF

DBCC TRANCESTATUS

DBCC USEROPTIONS


Validation Statements (on a database, table, index, catlog, filegroup, allocation of database pages)

DBCC CHECKALLOC

DBCC CHECKCATLOG

DBCC CHECKCONSTRAINTS

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKIDENT

DBCC CHECKTABLE


Maintenance Statements ( task on database, index, filegroup)

DBCC CLEANTABLE

DBCC DEBREINDEX

DBCC DROPCLEANBUFFERS

DBCC FREPROCCACHE

DBCC INDEXDEFRAG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

DBCC UPDATEUSAGE


Miscellaneous Statements ( Task such as enabling trace flags, removing a DLL from memory)

DBCC dllname(FREE)

DBCC HELP

DBCC FLUSHAUTHCACHE

DBCC TRACEOFF

DBCC FREESESSIONCACHE

DBCC TRACEON

DBCC FREESYSTEMCACHE

DBCC CLONEDATABASE





Comments

Post a Comment

Popular posts from this blog

SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling)

-
  Runbook: SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling) Environment Primary Server: ServerA Secondary Server: ServerB SQL Server: AlwaysOn Availability Group (AAG) with Automatic Failover Task Breakdown 1. Before Change Start Tasks ✅ 1.1 Raise Change Ticket Open a Change Request (CR) in ITSM Jira tool. Add details: SQL Service account password rotation. Update SPNs. Application validation. Attach: Impact Analysis Backout Plan 1.2 Get CAB Approval Submit CR for CAB review and approval. Get approvals from: CAB QA Application owners 1.3 Create New AD Service Account. Request AD team to create: Username: SQLService_ServerA Password: Strong and non-expiring. Permissions: Add to Domain Users Add to Local Administrators Group Command to Add Account to Local Admin Group: net localgroup administrators "DOMAIN\SQLService_ServerA" /add 1.4 Grant SQL Server Access Connect to SQL Server as sys...
Read more

How to use Azure Automatic performance tuning ?

-
  Automatic performance tuning in Azure SQL Database is a powerful feature that helps optimize query performance without manual intervention. It includes capabilities like automatic plan correction , index tuning , and query regression detection . Here's a step-by-step guide to enable and manage it, especially focused on query performance tuning : Step-by-Step: Enable Automatic Performance Tuning in Azure SQL Step 1: Open Azure Portal Go to https://portal.azure.com Sign in with your Azure account. Step 2: Go to Your SQL Database Navigate to "SQL databases" Click on the target database you want to enable tuning for. Step 3: Enable Automatic Tuning In the left-side menu, find and click on "Automatic tuning" You will see the following options: Force Last Good Plan – Automatically reverts to a known good query plan if performance regresses. Create Index – Azure creates recommended indexes automatically. Drop Index – Azure drops unused or dupl...
Read more

How to Upgrade SQL Server from 2014 to 2022, Apply the Latest Patches Post-Upgrade, and Install the Latest SSMS Tool?

-
Firstly, ensure that you have completed this on in a non-production environment. Assume infrastructure is set up for SQL Server mirroring: - Principal A - Primary - Mirror B - Secondary - Commit mode - Synchronous commit. change ticket. Before change steps: 1. Create a change ticket. 2. Conduct a peer review of the change ticket. 3. Obtain approval from the Change Advisory Board (CAB). 4. Get QA approval for application testing following the SQL Server version upgrade, installation of SSMS, and the latest cumulative update (CU) patch. 5. Download the latest patch executable from the Microsoft website and transfer the CU to both servers. 6. Move the existing SQL Server 2022 installation media to both servers. 7. Download the latest version of SSMS from the Microsoft website and transfer it to both servers. 8. Schedule stop of alerts for unnecessary tickets from the monitoring tool. During the change window steps: 1. Create backups of the system databases. 2. Take a snapshot of the opera...
Read more