Skip to content

Clive Roberts

SQL Server DBA

Menu
  • Setup
  • Performance
  • Health Check
  • Troubleshooting
  • T-SQL
  • Patches
  • News

DBCC SHRINKDATABASE with TRUNCATEONLY

May 28, 2012
| No Comments
DBCC SHRINKDATABASE (N'Leads', 0,TRUNCATEONLY)
SQL Server, Troubleshooting

Post navigation

← Schedule SQL Server Express Backups with sqlcmd
Fix SQL Server Error 3707 with Emergency Mode and DBCC CHECKDB →

SQL Server DBA Handbook

Setup, backup and high availability (37)
  • Backing Up and Restoring a Database
  • Restoring a Full Database Backup (SSMS)
  • Restoring a Database to a New Location
  • Backup and Restore with T-SQL
  • Disaster Recovery Planning
  • Basic vs Advanced High Availability
  • Adding a Database to an Availability Group
  • Suspending and Resuming Data Movement
  • Availability Group Health Troubleshooting
  • Quorum Failure
  • Log Shipping
  • Database Mail Setup and Troubleshooting
  • Moving TempDB
  • Database Snapshots
  • Server Build Checklist
  • Encryption
  • TDE: Backing Up the Certificate
  • Data Obfuscation and Masking
  • SQL Server Audit
  • FILESTREAM
  • Orphaned SQL Logins
  • Replicating SQL Server Logins with Their SIDs
  • Login Failures (Error 18456)
  • Random Password Generator
  • Generating the SPNs (Kerberos)
  • SQL Server Replication: Setup, Types and Pitfalls
  • Adding an Article to a Publication
  • Enabling Change Tracking: Good and Bad Points
  • Migrating a Reporting Services Installation (Native Mode)
  • Upgrading SSRS 2005 to 2012 (Quick and Dirty)
  • Azure SQL Options
  • Migrating SQL Server to Azure
  • Building an Availability Group
  • Failover Cluster Instances and Distributed Availability Groups
  • Backup to URL and Backup Encryption
  • PowerShell and dbatools
  • SQL Server Trace Flags
Performance (16)
  • Database Performance Tuning and Index Strategy
  • Data Compression
  • Forwarded Records
  • NUMA
  • Common Memory Issues
  • Average Write Performance
  • Stress Testing the I/O Subsystem with SQLIO
  • When the SQL Server Cache Needs to Be Cleared
  • Parameter Sniffing
  • Table Partitioning
  • Resource Governor
  • What Happens During a Data Change: Buffer Pool, Write-Ahead Log and Durability
  • Measuring Query Performance and Taking a Baseline
  • Reading Execution Plans
  • Plan Caching and Parameterization
  • Statistics Problem or Caching Problem?
Troubleshooting (21)
  • Database Suspect, Recovery Pending or Stuck Recovering
  • Shrinking Databases
  • Transaction Log Full
  • Data File Full and Disk Space (Error 1105)
  • Shrinking Transaction Logs
  • Stopping a Running Job in Job Activity Monitor
  • Failed SQL Agent Job Triage
  • Single-User Mode
  • Tempdb Full or Contention
  • Repairing the WMI Provider (mofcomp)
  • SQL Server Referencing the Wrong Disk Drives
  • Performance Troubleshooting Queries
  • High CPU Troubleshooting
  • SQL Locks
  • SQL Server Error Numbers
  • Monitoring Deadlocks
  • Sleeping Processes
  • DMVs and Session Monitoring
  • Collecting Data with Performance Monitor
  • Collecting a Trace with SQL Profiler
  • Schema Locks and Online Index Maintenance
T-SQL (17)
  • SQL Best Practices
  • T-SQL Coding Conventions and Programming Guidelines
  • Common Table Expressions (CTEs)
  • CROSS APPLY and OUTER APPLY
  • Exporting Data to a File
  • SET IDENTITY_INSERT
  • OPENQUERY with Linked Servers
  • Service Broker in SQL Server
  • Enabling and Resetting Service Broker
  • Change Data Capture (CDC)
  • Extended Events
  • Temporal Tables
  • DDL and DML Audit Triggers
  • Transaction Modes: Autocommit, Explicit and Implicit
  • Error Handling in Transactions: TRY/CATCH, @@TRANCOUNT and XACT_STATE()
  • Read Consistency: Read Committed, RCSI and Snapshot Isolation
  • T-SQL Performance Anti-Patterns

Question of the Day

Setup, backup and high availability (1)
  • Fixed Server Roles in SQL Server
Performance (1)
  • Disabled Indexes in SQL Server
T-SQL (10)
  • The T-SQL SQUARE Function
  • Using a Column Alias in ORDER BY
  • The T-SQL SIGN Function
  • T-SQL Reserved Keywords
  • Adding a Column with a DEFAULT
  • Trailing Spaces in SQL Server
  • SQL Server Regex Functions
  • Aggregates on Empty Sets in T-SQL
  • The SQL Server Vector Data Type
  • The T-SQL CHOOSE Function

All Question of the Day posts (12)

Categories

Sidebar

  • Handbook, news and patches (121)
  • Regain sysadmin access (1)
  • Kill SQL processes (1)
  • Configure MSDB (13)
  • TempDB in use (1)
  • Connected to SQL (1)
  • Move backups (2)
  • VLF details (1)
  • Config Manager error (1)
  • Fill factor (1)
  • Missing indexes (1)
  • Number of cores (1)
  • DBCC CHECKDB (1)
  • Wait times (7)
  • Tail-log backup (1)
  • STUFF function (6)
  • Blocking and deadlocks (2)
  • Locking hints (2)
  • Errors 823, 824, 825 (1)
  • DBCC commands (1)
  • Transaction counts (1)
  • Database details (1)
  • Replication (1)
  • Find jobs (1)
  • Backup progress (1)
  • Pivot table (1)
  • Replication objects (1)
  • Tables with no PK (1)
  • Who dropped the database (1)
  • last_value (1)
  • Parse XML (1)
  • Search a string (1)
  • DATETIME difference (1)
  • Error 3707 (1)
  • SHRINKDATABASE (1)
  • Express backup (1)
  • Enable xp_cmdshell (1)
  • Output a file (1)
  • Start of the month (1)

RSS feed: Clive Roberts Clive Roberts

  • Using a Column Alias in ORDER BY
    Applies to SQL Server 2019, 2022 and 2025. Using an ORDER BY alias is one of those things that feels inconsistent the first time you hit it. You give a calculated column a name in the SELECT list, sort by that name and it works. Try to filter on the same name in the WHERE... […]

Recent Posts

  • The T-SQL SQUARE Function
  • Using a Column Alias in ORDER BY
  • Disabled Indexes in SQL Server
  • News: Who’s Hiring Database People? November 2026 Edition
  • The T-SQL SIGN Function
  • News: My Black Friday Sale on Training Classes is Open Now!
  • T-SQL Reserved Keywords
  • Adding a Column with a DEFAULT
  • News: [Video] Office Hours: 4 Great Questions Off Canada
  • Trailing Spaces in SQL Server
  • News: Meet Hugo Kornelis, Execution Plan Expert.
  • SQL Server Regex Functions
  • News: Why You Don’t Get Parallelism When You Ask for The Whole Table
  • Fixed Server Roles in SQL Server
  • News: New Stuff in the Mastering Server Tuning Class
  • News: New Stuff in the Mastering Server Tuning Class
  • Aggregates on Empty Sets in T-SQL
  • News: This Friday’s the Deadline for My Next Live Class Rotation.
  • The SQL Server Vector Data Type
  • The T-SQL CHOOSE Function
  • News: [Video] Talking Pronouns in Alaska
  • News: Why’s This Query So Slow in Prod? Check sp_BlitzPlanCompare.
  • News: AI Stories and Thoughts
  • News: I’m Probably Not Supposed To Show You Microsoft’s Auto-Indexing Tool.
  • SQL Server Trace Flags
  • Statistics Problem or Caching Problem?
  • Plan Caching and Parameterization
  • T-SQL Performance Anti-Patterns
  • Reading Execution Plans
  • Measuring Query Performance and Taking a Baseline
  • News: Thoughts on AI After My Panel Discussion
  • Read Consistency: Read Committed, RCSI and Snapshot Isolation
  • Schema Locks and Online Index Maintenance
  • What Happens During a Data Change: Buffer Pool, Write-Ahead Log and Durability
  • Error Handling in Transactions: TRY/CATCH, @@TRANCOUNT and XACT_STATE()
  • Transaction Modes: Auto commit, Explicit and Implicit
  • PowerShell and dbatools
  • Resource Governor
  • Table Partitioning
  • Backup to URL and Backup Encryption
  • Failover Cluster Instances and Distributed Availability Groups
  • Building an Availability Group
  • Parameter Sniffing
  • DDL and DML Audit Triggers
  • Temporal Tables
  • Extended Events
  • Change Data Capture (CDC)
  • Migrating SQL Server to Azure
  • Azure SQL Options
  • News: [Video] Office Hours on the Last Alaskan Cruise of the Season
  • Upgrading SSRS 2005 to 2012 (Quick and Dirty)
  • Migrating a Reporting Services Installation (Native Mode)
  • Health Check 00: is this SQL Server running well?
  • Health Check 01: server overview
  • Health Check 02: databases
  • Health Check 03: backups
  • Health Check 04: disks and files
  • Health Check 05: SQL Agent jobs
  • Health Check 06: wait statistics
  • Health Check 07: activity and blocking
  • Health Check 08: error log
  • Health Check 09: integrity checks
  • Health Check 10: memory and CPU
  • Health Check 11: tempdb
  • Health Check 12: high availability
  • Performance 01: top queries
  • Performance 02: Query Store regressions
  • Performance 03: missing indexes
  • Performance 04: unused indexes
  • Performance 05: statistics age
  • Performance 06: index fragmentation
  • Performance 07: cardinality estimate skew
  • Performance 08: filtered statistics candidates
  • Performance 09: duplicate and overlapping indexes
  • Performance 10: statistics health
  • Performance 11: cardinality estimator audit
  • Performance 12: implicit conversions
  • Enabling Change Tracking: Good and Bad Points
  • Adding an Article to a Publication
  • SQL Server Replication: Setup, Types and Pitfalls
  • Enabling and Resetting Service Broker
  • Service Broker in SQL Server
  • OPENQUERY with Linked Servers
  • News: Breaking News: Hold Off on SSMS 22.11.0
  • News: Fact-Checking Microsoft’s “PostgreSQL to SQL Field Notes: Connections”
  • SET IDENTITY_INSERT
  • Exporting Data to a File
  • CROSS APPLY and OUTER APPLY
  • Common Table Expressions (CTEs)
  • T-SQL Coding Conventions and Programming Guidelines
  • SQL Best Practices
  • Generating the SPNs (Kerberos)
  • Random Password Generator
  • News: Free Webcast: How to Think Like the SQL Server & Postgres Engines
  • Login Failures (Error 18456)
  • Replicating SQL Server Logins with Their SIDs
  • Orphaned SQL Logins
  • FILESTREAM
  • SQL Server Audit
  • Patch: SQL Server 2017 CU31 + GDR (build 14.0.3560.2)
  • Patch: SQL Server 2017 GDR (build 14.0.2140.2)
  • Patch: SQL Server 2019 CU32 + GDR (build 15.0.4500.2)
  • Patch: SQL Server 2019 GDR (build 15.0.2200.2)
  • Patch: SQL Server 2022 CU27 + GDR (build 16.0.4305.5)
  • Patch: SQL Server 2022 GDR (build 16.0.1210.3)
  • Patch: SQL Server 2025 CU9 + GDR (build 17.0.5015.4)
  • Patch: SQL Server 2025 GDR (build 17.0.1145.4)
  • Data Obfuscation and Masking
  • TDE: Backing Up the Certificate
  • Encryption
  • Collecting a Trace with SQL Profiler
  • Collecting Data with Performance Monitor
  • DMVs and Session Monitoring
  • Sleeping Processes
  • Monitoring Deadlocks
  • SQL Server Error Numbers
  • SQL Locks
  • High CPU Troubleshooting
  • Performance Troubleshooting Queries
  • When the SQL Server Cache Needs to Be Cleared
  • Stress Testing the I/O Subsystem with SQLIO
  • Average Write Performance
  • Common Memory Issues
  • NUMA
  • Forwarded Records
  • Data Compression
  • Database Performance Tuning and Index Strategy
  • Server Build Checklist
  • SQL Server Referencing the Wrong Disk Drives
  • Repairing the WMI Provider (mofcomp)
  • Database Snapshots
  • Tempdb Full or Contention
  • Moving TempDB
  • Single-User Mode
  • Database Mail Setup and Troubleshooting
  • Failed SQL Agent Job Triage
  • Stopping a Running Job in Job Activity Monitor
  • Shrinking Transaction Logs
  • Data File Full and Disk Space (Error 1105)
  • Transaction Log Full
  • Shrinking Databases
  • Database Suspect, Recovery Pending or Stuck Recovering
  • Log Shipping
  • Quorum Failure
  • Availability Group Health Troubleshooting
  • Suspending and Resuming Data Movement
  • Adding a Database to an Availability Group
  • Basic vs Advanced High Availability
  • Disaster Recovery Planning
  • Backup and Restore with T-SQL
  • Restoring a Database to a New Location
  • Restoring a Full Database Backup (SSMS)
  • Backing Up and Restoring a Database
  • Patch: SQL Server 2025 CU10 (build 17.0.5035.5)
  • Regain sysadmin access to SQL Server (2016–2022) when no login works
  • Kill All User Sessions in a SQL Server Database
  • sp_who3: sp_who2 with SQL Text, Waits and Reads
  • Which Trace Flags to Keep on SQL Server 2016–2022
  • Weekly DBCC CHECKDB Agent Jobs (Ola Hallengren)
  • Database Integrity Check
  • Weekly Index Maintenance Agent Job (Ola Hallengren)
  • Create Backup Objects and Jobs
  • Index Optimize
  • Create Operator and Standard Alerts
  • Set Up SQL Server Agent Alert Emails (Database Mail)
  • Set Up TempDB Data Files: One per CPU, up to 8
  • SQL Server Post-Install Configuration Script
  • Cycle the SQL Server Error Log Nightly (Agent Job)
  • Configure msdb Size and Growth in SQL Server
  • Find Which Queries Are Using TempDB Right Now
  • Who Is Connected to SQL Server? (T-SQL)
  • Check VLF Counts for Every SQL Server Database
  • Move SQL Server Backup Files with xp_cmdshell
  • SQL Server VLF Details: Count, Size and Log Reuse Wait
  • Fix “Cannot Connect to WMI Provider” in Configuration Manager
  • Fill Factor
  • Find Missing Indexes in SQL Server (Top 25 by Impact)
  • Count CPU Cores and Sockets on a SQL Server
  • Run DBCC CHECKDB to Check a SQL Server Database
  • Fix SQL Server Error 1222: Lock Request Time Out
  • SQL Agent Job Schedules in Plain English (T-SQL)
  • Find Unused Indexes in SQL Server (with DROP Statements)
  • Show Wait Types for SQL Server System Tasks
  • Key SQL Server Performance Counters with T-SQL
  • Top 10 SQL Server Queries by Physical Reads
  • Top 10 SQL Server Wait Types (sys.dm_os_wait_stats)
  • Tail-Log Backup in SQL Server: When and How
  • Types of Replication
  • Index type
  • Database Engine Stored Procedures (Transact-SQL)
  • SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
  • CONCAT Function in SQL Server: Join Strings
  • STUFF Function in SQL Server: Insert or Replace Text
  • Find Tables Without a Clustered Index (Heaps) in SQL Server
  • Blocking, Timeouts and Deadlocks
  • SET XACT_ABORT ON: Stop Open Transactions After Timeouts
  • Locking hint
  • Set Up SQL Server Alerts for Errors 823, 824 and 825
  • DBCC commands
  • Measure SQL Server Transactions per Second with a DMV
  • Setup
  • Performance
  • Health Check
  • Troubleshooting
  • T-SQL
  • Patches
  • News

Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes