SQL Database full

DarrenG

Member
Join Date
Dec 2016
Location
London
Posts
74
Hi folks,

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.

Additionally, confirm that you are able to log into the SQL Server instance using the credentials defined in the Alarm and Event Historian Database Properties dialog (accessed at the bottom of the Studio Explorer Window)

Thanks in advance for your help,

Darren
 
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
 
Last edited:
If you're going to have this much data in a short period of time, you might need to buy the standard version of SQL Server.
 
Thanks for your replies! We might have to upgrade to the standard version alright yeah. I think I might have set the datalog up on this plant to capture a bit too frequently though!

I've gotten this far and now I'm scratching my head, sorry! There are a lot of columns and this is only one of the tables that is apparently having an issue. What do I need to write instead of 'your_datetime_column_name'?

Thanks again.

sql columns.PNG
 
Note: do a backup of the database first!!!

It looks like the column you want to use is "EventTimeStamp" (the clue is that the column is a datetime format).

So...

DELETE FROM [dbo].[AllEvent] WHERE EventTimeStamp < '2019-03-30 00:00:00.000' (if you want to delete all records before March 30, 2019 at midnight)

You can modify the date and time in the single quotes as needed.
 
Also, if you do go ahead with SQL Server Standard, I would strongly suggest going ahead and putting it on a real server, with a separate server for the data.
Hardware isn't all that expensive nowadays, you can get a very nice rack mount data server PC and file server PC for under $7500.
 
Thanks again for your help folks :)

I ran that query on a different sql database on a plant that hasn't been commissioned yet but it didn't seem to reduce in size. I could be wrong though, it did say that x amount of rows were affected so I might have just been looking in the wrong place. Maybe I was looking at the size allocated for the database now that I think about it... šŸ™ƒ

The plant in question is back up and running so I've got to wait until things settle down before I can get at it again. Once I'm allowed to get on there I'll try it and let you know how I get on. I really should do a course on SQL...
 
Very often there is a requirement especially in food processes to keep data backups for a minimum specific time. It looks like the trends are logging at quite a short interval, try to find out if the intervals could be lengthened many temperature trends do not need to be in very short intervals and very often these tables are logged even when the process is not running. I would check what intervals have been set (and if under legislation of your process they can be changed), do they need to run continuously or only when process is running etc. I have done this many times even on servers with licenced SQL as the database grows, reports can become slower. We also automated the process of hiving off older data and storing it thereby keeping the database size at a reasonable level. Some Scada systems can be self cleaning i.e. delete records older than xx But just be clear on what the log rate is required, how long you need to keep the data and how often you back up.
 
I have a very similar task. The difference is that I do want to hold on to the older data but move them off the operation server. So I'm thinking that I would..

- create a new db.
- select the older data and copy them to the new db.
- delete the older data from the db.
- move new db off the server.

It's been quite a while since my SQL class. What would be the command to copy the data over?
 
Very often there is a requirement especially in food processes to keep data backups for a minimum specific time. It looks like the trends are logging at quite a short interval, try to find out if the intervals could be lengthened many temperature trends do not need to be in very short intervals and very often these tables are logged even when the process is not running.

Very sound advice - I once visited a site that had their local RSViewSE trend database "full", i.e. out of storage space...

It was looking at the water levels in 7 remote reservoirs in the locality, logging their levels every 100mS !!

I changed the logging interval to once per hour for all seven.

A phrase I use consistently "Horses for Courses"
 

Similar Topics

Hello, Does anyone have experience managing the FactoryTalk alarm & events SQL database from outside of FactoryTalk? Essentially I'd have tag...
Replies
3
Views
1,244
Hi, We are doing a project in FactoryTalk View with Database connected to SQL via Connections available within the FactoryTalk View Project. Is...
Replies
1
Views
1,564
Hi, Can someone guide me through how to use FactoryTalk SE VB script to open communication with SQL Server? which command I can use? Are there...
Replies
2
Views
2,249
Respected Members; Good day. I want to perform the following task: 1. successfully fetches the data via RSLINK / DDE to excel. 2. Now i want to...
Replies
17
Views
4,374
Hello. A semiconductor production machinery maker has inquired about a Windows application that be able to cyclically read data from an OPCUA...
Replies
9
Views
2,064
Back
Top Bottom