Error report, please help me read what is the problem

[stephanhenning]

@stephanhenning

dbo.ReportTemp

I have deleted the table dbo.“Report Temp”
I have run the Metrik script, it says it wasn’t there on database.

[Jesse]

Your hd is full or your sql server is full. It really looks like your hard drive is full.

[stephanhenning]

Not HD, how to I minimize the size safely Jesse?

[stephanhenning]

ok tried shrink DB via SQL, that didn’t work

here are my screen shots after I have shrunk DB and deleted ReportTemp

[Memo]

Please check your db settings to ensure auto growth is enabled.

Also, you have 4-times the amount of tasks as you do orders. What are you using tasks for?

[stephanhenning]

for tasks I have implemented most of the tutorials on this V5. Some I use, some I don’t, but to know what is important and what to deleted is the question… I have a normal restaurant setup with many screens as we have many departments and I use the Task Printer Screen system and 2 main departments and 5 small kitchens (bar,coffee, milkshakes,pizza,main meal,etc)

[stephanhenning]

I could not save todays work period because of that same error I shared in 1st port, had to Backup yesterdays work period unfortunately so cash up and stock take will be an nightmare.

So what I have done so far in case anyone else has same problem:

• shrink DB via SMMS (task)
• run Metrik script from this forum to eliminate old ReportTemp DB table via SMMS
• made sure Autoshrink was enabled
• made sure Autogrowth was enabled and increased it to 100mb (MSSQL Database Autogrowth Configuration)
• Increased DB allocated size (same link)

[Jesse]

You could also upgrade to full sql. Or even try the developer version although you are not supposed to use that in production.

[Memo]

Jesse has a point - you may be hitting some limitation of the express version.

I’d want to investigate the the huge tasks table.

Hey @Jesse, once a task is marked complete, can that task be restarted/enabled in the future? I know nothing of tasks but I’m wondering if OP could just delete all the completed tasks from the table without impacting current operations.

[stephanhenning]

What can I send you a screen-shot of to see the Tasks active?

[Memo]

From what I gather from your posts, you’re using tasks to route orders to various kitchen displays, no?

[Jesse]

You can delete any tasks. It shouldn’t impact it. It will only be a bandaide he should consider upgrading. Or archiving data if upgrading isn’t an option.

[stephanhenning]

yes, mostly task print screen.

[Memo]

Does the data for ELG Event Log need to persist?

[stephanhenning]

I doubt it must be important…

This i believe is the origin:

Event Logs using Tasks

I was thinking about Ticket Logs the other day and thought I should add some Logging at my Venues. Naturally, being me, my mind went straight to using Tasks for the logging. Why? We know that there is already an Add Ticket Log Action in SambaPOS that allows us to log details of what happens in a Ticket, and @emre created a very good Tutorial for this: Great Tutorial. But there is one problem with it, and I believe this could be a common scenario where operators could be putting a lot of m…

[Memo]

In the above scenario, we solved that by disabling the default cancel order rule and instead implemented automation that voids the orders but the ticket persists. Then ticket logging works without issue.

Backup your database before executing any changes.

I am assuming that the tasks have been marked as completed.

If you want to keep your the logging data, execute this script in SSMS:

BEGIN TRANSACTION;
BEGIN TRY
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    DECLARE @TaskTypeId INT;

    SELECT @TaskTypeId = Id
    FROM dbo.TaskTypes
    WHERE Name = 'ELG Event Log';

    DELETE FROM dbo.Tasks
    WHERE TaskTypeId <> @TaskTypeId
          AND Completed = 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;

        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;
END CATCH;

if you don’t care about the log data, use this script:

BEGIN TRANSACTION;
BEGIN TRY
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    DELETE FROM dbo.Tasks
    WHERE Completed = 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;

        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;
END CATCH;

[stephanhenning]

The task DB is much smaller now…

[Memo]

There’s still quite a bit of data in there. My guess is some tasks aren’t being marked complete.

If you want to completely clear the table and don’t care about anything stored in that table, this will clear the rest out for you.

Also, be sure to shrink the database - use SSMS and select the following:

BEGIN TRANSACTION;
BEGIN TRY
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    TRUNCATE TABLE dbo.Tasks;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;

        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;
END CATCH;

[stephanhenning]

Msg 50000, Level 16, State 1, Line 19
Cannot truncate table ‘dbo.Tasks’ because it is being referenced by a FOREIGN KEY constraint.

This is response from SMS after running the query

[Memo]

Ahh, sorry about that. This should work on a table with FK references:

BEGIN TRANSACTION;
BEGIN TRY
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    DELETE FROM dbo.Tasks;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;

        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END;
END CATCH;