KickstarterForum.org

Hi all,

I noticed yesterday that the trends on one of our plants (using FactoryTalk View SE) weren't working and in development I had the following error:

A database error occurred: SQL State: 47511124 Native State: 1105 Msg: [MicroSoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object <object> in database <databasename> because the 'PRIMARY' filegroup is full.

I had a look and the database is indeed maxed out at 10gb. 10gb is the max I can go to using the Express version of SQL. In the settings of the database in FTView I have it configured to purge old records after 12 months but the plant isn't 12 months old yet for it to start purging. I'm going to have to reduce the amount of data points taken so my question for now is how do I clean the database up? My knowledge of SQL is limited, I've managed to set up databases for our last few projects using FTView but I don't know how to query or manage them really after that. I'd like to avoid having to create a new database if possible so is there a way to clear the first 3 months worth of data or something? I could do this in FTView but Rockwell warned me previously that purging too much data in one go may cause problems, whether that is true or not I don't know. Is there a better way of doing it in SQL Management Studio maybe?

Rockwells Knowledgebase wasn't hugely helpful this time around:

Solution

The database is already full.

Create a new database with free space, or clean up the database you are using.

If you want to empty the database quickly, do the following:

1) Install MS SQL Management Studio
2) Connect to the instance\database in question
3) Backup the database (right-click the database, select Tasks -> Backup...)
4) Drill down in the Object Explorer (tree on the right) to the table in question
5) Right-click on the table, choose Script Table as -> Delete To -> New Query Window
6) Change the query to: DELETE FROM [dbo].[your_table] WHERE your_datetime_column_name < '2019-03-30 00:00:00.000' (if you want to delete all records before March 30, 2019 at midnight)
7) Right-click the database, select Tasks -> Shrink -> Database

Your database is overfilled with data, and can't accept new information. This was the main reason why the commands you tried to put didn't work.
You wrote that you have to create a new database or clean the existing one. There are methods to buy/create more space for your database. If you decide to buy more space, simply write to SQL customer support for guidance. They will explain how to add more space, perform lead enrichment, and introduce the commands.

It looks like you're dealing with a full SQL database in FactoryTalk View SE. Given that you're using the Express version with a 10GB limit, you'll need to free up some space to get things running smoothly again. Here’s a step-by-step approach you can try:

Backup Your Database: Before making any changes, ensure you back up your database. This way, you can restore it if something goes wrong.

Connect to SQL Server Management Studio (SSMS):

Open SSMS and connect to your SQL Server instance.
Identify Tables with Old Data:

In the Object Explorer, expand your database and then expand the "Tables" folder.
Look for tables related to your trends or data storage.
Run Queries to Remove Old Data:

You can use SQL queries to delete data from these tables. For example:
sql
Copy code
DELETE FROM [YourTableName]
WHERE [YourDateColumn] < DATEADD(MONTH, -3, GETDATE())
Replace [YourTableName] with the name of your table and [YourDateColumn] with the column that holds the date information. This query will delete records older than 3 months.
Rebuild Indexes:

After deleting a significant amount of data, it’s a good idea to rebuild indexes to reclaim space and optimize performance. You can do this using:
sql
Copy code
ALTER INDEX ALL ON [YourTableName] REBUILD
Shrink the Database:

If you find that your database size hasn’t decreased much after deleting data, you might need to shrink it. Right-click on the database in SSMS, go to “Tasks” > “Shrink” > “Database”. Be cautious with this step, as frequent shrinking can lead to fragmentation.
Verify Database Configuration:

Double-check your database settings in FTView to ensure that purging records after 12 months is set up correctly. Since your plant isn't 12 months old, the purging might not have kicked in yet.
By following these steps, you should be able to free up some space in your existing database. If you’re unsure about executing SQL commands, consider getting assistance from someone with more SQL experience to avoid accidental data loss.

Hope this helps!

It sounds like you're running into a classic issue with SQL Express's 10GB limit, which can definitely be a headache. Since you're not quite at the 12-month mark for purging in FactoryTalk View SE, and Rockwell's own advice about bulk purging could cause performance issues, here's a more cautious approach you could try in SQL Management Studio (SSMS).

You can run a query to delete older data gradually, rather than purging a large chunk all at once. For instance, you could delete data in smaller time increments, say week by week, to avoid any system strain. Here’s a basic idea of what the SQL might look like:

sql
Copy code
DELETE FROM [YourTableName]
WHERE [YourDateColumn] < '2023-01-01'
Make sure to replace [YourTableName] with the actual table name, and [YourDateColumn] with the date field. You could adjust the date to target records older than a few months, and run the command multiple times for smaller sets of data.

It’s also worth considering adjusting your data retention settings if the volume of data is too high for 12 months. You could reduce the time frame or lower the number of data points you're collecting, which will help manage the size more efficiently.

Lastly, it might be a good idea to back up your database before performing any deletions—just to be safe!

Hope this helps!