x

Sign In

Email Address


Titanium Schedule - Support Portal


back
SQL Express 'Out of Disk Space'
Ticket: 22951
Created: 6/24/2016
Modified: 11/14/2018
Rating: No one has rated this article yet
Was this helpful? Yes   No
print

The error being reported is related to the size of the database and the allocated space for it:

Our Disk usage from the TitaniumSchedule reports the following:

Space Reserved:  8.93

Space Used: 8.79

There are two directions to go with this type of issue:

Option 1 will cost some money up-front, but you'll never have to worry about this again. 

Option 2 remains free but would require regular, manual maintenance.

 

1. You can purchase the Standard SQL Server 2016 from Microsoft, which could be up to $3,800 without educational discount:

https://www.microsoft.com/en-us/server-cloud/products/sql-server/purchasing.aspx

SQL Server 2012 or 2014 are more stable and most likely less expensive; those prices can be researched on the internet or through your Microsoft software representative for your university.

Justification of going this route:

http://searchsqlserver.techtarget.com/feature/SQL-Server-Standard-Edition-Why-its-worth-the-cost

 

2.  The following items can be done manually to decrease the size of your database.  These will need to be done regularly if not just every time you receive this notification about "You're running out of disk space".

 

No matter which direction you decide to go, these immediate suggestions on decreasing your database size can be done now:

 

This is for your IT GROUP:

1. Shrink the logs, files, and data.

From SSMS expand ‘Databases’ to find the Titanium database and right click on it.  Select 'Properties' and chose ‘Options’ from the page selector.  Set your ‘Recovery model’ to “Simple” (this is also Microsoft’s most recent recommendation).

Now, to get your T-log shrunk, do a Full backup of the database.  At the end of each successful full backup the t-log will now be truncated.  After the first full backup I’d then go back in and right-click on the db, select All Tasks, Shrink, Files, and here’s the key that most people miss, change the ‘File type’ from “Data” to “Log” and hit OK.

That should shrink it down a ton and the users should notice some enhanced performance during certain transactions.

 

Now that you've shrunk the files, rebuilding the index should be done:

This script should be run on the Titanium database through the SSMS utility or some other query utilty on the SQL Server.  Make sure to run this script while Titanium is not in use.

--this will rebuild all indexes for all tables in the database

--this script may take a while to run, especially on a highly fragmented database with a lot of data

--NOTE this script should only be run when Titanium Schedule is not in use (eg after hours), as it will take the database offline

 

--this first portion of this script fixes indexes that are not trusted due to possible constraint violations
--this script should not fail, but if it does it indicates inconsistencies in your database
--if it fails, contact Titanium Software technical support
declare @notTrusted table
(
scriptToTrust NVARCHAR(2000)
)
insert into @notTrusted
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0
and i.is_disabled = 0
DECLARE @scriptToTrust NVARCHAR(max)
DECLARE trust_cursor CURSOR
FOR
select scriptToTrust from @notTrusted
OPEN trust_cursor
FETCH NEXT FROM trust_cursor INTO @scriptToTrust
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @scriptToTrust
FETCH NEXT FROM trust_cursor INTO @scriptToTrust
END
CLOSE trust_cursor;
DEALLOCATE trust_cursor
GO
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

 

 
 

2.  Search your database for large attachments.

*** If notes that contain attachments are signed and locked, this process cannot be accomplished without losing those signatures (as the notes have to be [Unlocked]). ***

Many centers make attachments to their client notes (PDF, XLS, etc.).  A common mistake is saving the PDF file in too high of a resolutions.  150 dpi is standard for documents.  Many PDF creation utilities default to 300+.  The following script will identify attachments and their size so that the center can go through Titanium, open the attachment, re-save it at a lower DPI, delete the current attachment from the note, and finally add the newly saved (smaller) PDF.

The process, step by step:

From SQL Server Management Studio, run this script to identify attachment sizes: (dbv. 150101 only.  If prior to 150101 we have a different script)

select A.ParentTable OwnerRecordType, A.ParentId OwnerRecordId, C.Id as ClientId, A.AddDate Date, DATALENGTH(A.Data)/1024 as SizeInKB from Attachment A left join CaseNote CN on A.ParentTable = 'CaseNote' and A.ParentId = CN.Id left join Client C on CN.ClientID = C.Id where DATALENGTH(A.Data) > 0 order by DATALENGTH(A.Data) desc

 

This will provide a list of attachments for the center to address which Titanium can be searched by ClientID (IT should supply these results to the center).

 

Titanium System Admin (usually the Center Director):

  • from within Titanium, verify that 'Search by Client ID' is enabled.
  • {Configure > System Configuration > {Client}, verify checks are in both 'Client ID (view only) and 'Client ID Search'}  This will then enable the 'Client ID' field in 'Open' > Client.
  • Find and open each client with large KB attachments, then open their [Client File] to identify the case note that has the attachment by the date. 
  • Double click on the attachment for that note, and locally save it as a smaller size file.
  • [Edit] the note, select [Attach], hover the mouse over the current (large) attachment and then select 'Delete'.  Save the note.
  • [Edit] the note again now saving the new (small) attachment.

The above two items are some of the manual things you can do to decrease the size of your database that I mentioned earlier that would need to be done at least yearly should you choose not to go with Option 1. 

 

If any of you have any questions or concerns with any of these suggestions, I'm happy to schedule a conference call with any involved.