Views:

How to Find Large Tables in MS SQL Database

If your database appears to be larger than you expect it to be, this article will show you how to display a list of your MS SQL Database tables in size order, and list the associate size of the table.

These steps require you to have Microsoft SQL Server Management Studio installed on your computer. 

SQL Server Management Studio

  1. Open and log in to Microsoft SQL Server Management Studio

  2.  Right-click your database

  3. Hover over Reports 

  4. Hover over Standard Reports
  5. Select Disk Usage by Top Tables

SQL Query

  1. Open and log in to Microsoft SQL Server Management Studio

  2. Click the New Query button

  3. Copy the following script into the New Query page replacing [DatabaseName] with the name of your database

    USE [DatabaseName]
    GO
    CREATE TABLE #temp (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    SET NOCOUNT ON
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    SELECT a.table_name,
    a.row_count,
    COUNT(*) AS col_count,
    a.data_size
    FROM #temp a
    INNER JOIN information_schema.columns b
    ON a.table_name collate database_default
    = b.table_name collate database_default
    GROUP BY a.table_name, a.row_count, a.data_size
    ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
    DROP TABLE #temp
  4. Click the Execute button
The above will help to diagnose where the storage issues are and what tables are causing the "Bloat"

The Below script removes the Aync Jobs from the AsyncOperationBase (run this script in SQL Server Management Studio):
  
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

while(1=1)
begin
declare @DeleteRowCount int = 10000
declare @rowsAffected int
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where 
  OperationType in (1, 9, 12, 25, 27, 10) 
  AND StateCode = 3 
  AND StatusCode in (30, 32)

 select @rowsAffected = @@rowcount 
 delete poa from PrincipalObjectAccess poa 
   join WorkflowLogBase wlb on
    poa.ObjectId = wlb.WorkflowLogId
   join @DeletedAsyncRowsTable dart on
    wlb.AsyncOperationId = dart.AsyncOperationId
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where 
  W.AsyncOperationId = d.AsyncOperationId             
 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where 
  B.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d
where 
  O.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where 
  WS.AsyncOperationId = d.AsyncOperationID 
 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where 
  A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
  return
else
  WAITFOR DELAY '00:00:02.000'
end
Once the above steps have been completed. The audit logs are something that can be removed and squashed down quite simply in the front end of Dynamics, Navigate to Data Management -> Audit Logs -> Select the oldest log and delete (only the oldest audit log can be deleted).

If you are unable to delete the audit logs in Dynamics please refer to the below:
 

Method 1: Add the OLEDBTimeout and the ExtendedTimeout registry subkeys to increase the time-out values

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

  1. Click Start, click Run, type regedit, and then click OK.

  2. Locate the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM

  3. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

  4. Rename the DWORD value to the following value:

    OLEDBTimeout

    NOTE: This value is case sensitive

  5. Right-click the DWORD value, and then click Modify.

  6. In the Edit DWORD Value dialog box, type 86400 in the Value data box, click Decimal in the Base option, and then click OK.


    Note According to the requirement of the computer that is running SQL server and the number of customization files, the value can be larger than 86400. The value of 86400 is equivalent to 24 hours. This should only be done temporarily or as a troubleshooting step. The recommended value of this is between 30 and 300. Leaving this setting too high can result in performance issues.

  7. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

  8. Rename the DWORD value to the following value:

    ExtendedTimeout

  9. Right-click the DWORD value, and then click Modify.

  10. In the Edit DWORD Value dialog box, type 1000000 in the Value data box, and then click OK.

    Notes

    • In the Value data box, you can type a value that is larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. This is hexadecimal 0x7FFFFFFF.

    • Important: If this key already exists, notice the current value. After you have completed the import or the upgrade for Microsoft Dynamics CRM, set the value of this key back to the original value or delete the key if it did not previously exist. The default OLEDBTimeout value is 30 seconds.
       

Shrink the Database

Once the audit logs have been removed and all the above steps are complete, you will need to reduce the size of the database's allocated storage space to remove the unused space and free it up on the storage drive:
  1. For this operation use SSMS
  2. Select your org CRM
  3. Right-click on the database (for this case it was RINATEST_MSCRM)
  4. Select Tasks
  5. Select Shrink
  6. Select Database
  7. From here you can assess the database size and the amount of available free space
  8. Once you are happy with the values to be freed select OK to run the job (please note this should be done outside of business hours as it can have a large impact on the performance of the system)
  9. Now the Job is finished the space on the storage drive should be free and usable.
Keywords: SQL Database Reduction Storage