Building Operation Report Server SQL Database Full

[Product_Support]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.

Issue

SQL Database is full, or the partition the SQL is installed is full

Product Line

EcoStruxure Building Operation

Environment

Building Operation Report Server

Cause

If a site is using SQL Express 2008, the limit is 10 GB. If using the full version(s), it is unlimited. If the database or the partition where the Reports Server resides is full, here are some steps to reduce the SQL table volume and offer a short-term solution.

Resolution

1. Before continuing, make sure there is a good backup of the database.
2. Using SQL Server Management Studio, highlight the StruxureWareReportsDB and select New Query
   
3. Use the following Script in that Query:
   

   SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, 
   SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, 
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 
   FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id 
   INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
   INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
   LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id 
   WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
   GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name

4. Select Execute to run that Query as shown in the screen capture below:
   
5. The Results show each of the tables and the size of those tables as shown in the screen capture below:
   
6. Purely for example purposes, a script to reduce the number of Events would be
   

   delete from tbEvents where DATEDIFF(day, datetimestamp, getdate()) > 180

7. To gain back the space after deleting rows, right-click on StruxureWareReportsdb > Tasks > Shrink > Database