SQL Database Full

NOTICE

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, please contact Product Support Services prior to attempting the procedure below.

Issue

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

Product Line

SmartStruxure Solution

Environment

StruxureWare Building Operation site with Reports installed

Cause

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

Resolution

Before continuing, make sure there is a good backup of the database.

Using SQL Server Management Studio, highlight the StruxureWareReportsDB and select New Query

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

Select Execute to run that Query as shown in the screen capture below:

The Results show each of the tables and the size of those tables as shown in the screen capture below:

Purely for example purposes, a script to reduce the number of Events would be
delete from tbEvents where DATEDIFF(day, datetimestamp, getdate()) > 180

To gain back the space after deleting rows
Right click on StruxureWareReportsdb>Tasks>Shrink>Database

casefiles/16310/1NewQuery.png