SQL Express: Necessary optimizations?

Post Reply
Connor
Posts: 7
Joined: 05 Nov 19 6:59

SQL Express: Necessary optimizations?

Post by Connor » 10 Nov 19 13:48

Dear,

I am currently evaluating Photo Supreme and do like it so far. The installation with SQL Express 2017 went well and quite flawless when sticking to the installation how-to.
At the current time, I am importing all of my photo files (at the moment around 170k) into Photo Supreme. While the first imports went without any bigger issues, I am now facing several error messages that all seem to be SQL related.
When checking the SQL-logs, I can see a lot of errors in it, that can mostly be tracked down to these ones:

Code: Select all

Datum,Quelle,Schweregrad,Meldung
11/10/2019 13:37:50,spid68,Unbekannt,Could not allocate space for object 'dbo.idThumbs'.'PK__idThumbs__15B69B8EED529461' in database 'photosupreme_thumbs' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files<c/> dropping objects in the filegroup<c/> adding additional files to the filegroup<c/> or setting autogrowth on for existing files in the filegroup.
11/10/2019 13:37:50,spid68,Unbekannt,Error: 1105<c/> Severity: 17<c/> State: 2.
11/10/2019 13:37:50,spid68,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:37:50,spid68,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:37:50,spid68,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:37:50,spid68,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:37:41,spid59,Unbekannt,Could not allocate space for object 'dbo.idThumbs'.'PK__idThumbs__15B69B8EED529461' in database 'photosupreme_thumbs' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files<c/> dropping objects in the filegroup<c/> adding additional files to the filegroup<c/> or setting autogrowth on for existing files in the filegroup.
11/10/2019 13:37:41,spid59,Unbekannt,Error: 1105<c/> Severity: 17<c/> State: 2.
11/10/2019 13:37:41,spid59,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:37:41,spid59,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:37:41,spid59,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:37:41,spid59,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:36:42,spid63,Unbekannt,Could not allocate space for object 'dbo.idThumbs'.'PK__idThumbs__15B69B8EED529461' in database 'photosupreme_thumbs' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files<c/> dropping objects in the filegroup<c/> adding additional files to the filegroup<c/> or setting autogrowth on for existing files in the filegroup.
11/10/2019 13:36:42,spid63,Unbekannt,Error: 1105<c/> Severity: 17<c/> State: 2.
11/10/2019 13:36:42,spid63,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:36:42,spid63,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:36:42,spid63,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:36:42,spid63,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:36:21,spid38s,Unbekannt,Transaction (Process ID 38) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
11/10/2019 13:36:21,spid38s,Unbekannt,Error: 1205<c/> Severity: 13<c/> State: 56.
11/10/2019 13:36:17,spid60,Unbekannt,Could not allocate space for object 'dbo.idThumbs'.'PK__idThumbs__15B69B8EED529461' in database 'photosupreme_thumbs' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files<c/> dropping objects in the filegroup<c/> adding additional files to the filegroup<c/> or setting autogrowth on for existing files in the filegroup.
11/10/2019 13:36:17,spid60,Unbekannt,Error: 1105<c/> Severity: 17<c/> State: 2.
11/10/2019 13:36:17,spid60,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:36:17,spid60,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:36:17,spid60,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:36:17,spid60,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:36:17,spid62,Unbekannt,Could not allocate space for object 'dbo.idThumbs'.'PK__idThumbs__15B69B8EED529461' in database 'photosupreme_thumbs' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files<c/> dropping objects in the filegroup<c/> adding additional files to the filegroup<c/> or setting autogrowth on for existing files in the filegroup.
11/10/2019 13:36:17,spid62,Unbekannt,Error: 1105<c/> Severity: 17<c/> State: 2.
11/10/2019 13:36:17,spid62,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
11/10/2019 13:36:17,spid62,Unbekannt,Error: 1827<c/> Severity: 16<c/> State: 1.
11/10/2019 13:36:17,spid62,Unbekannt,CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
What do I have to do to optimize the database usage? There is still plenty of space (roughly 800 GB) on the disk, so this should not be the issue.
Or would it be better to switch to PostGreSQL?

Thank you for your feedback,
best regards,

Connor

EDIT 1: added number of files

Hert
Posts: 5877
Joined: 13 Sep 03 7:24

Re: SQL Express: Necessary optimizations?

Post by Hert » 10 Nov 19 13:58

Sqlserver Express has a few major restrictions. For example; diskspace limits and also (maybe ever worse), it uses only one processor core. That makes it nice for small databases but anything serious should quickly push you towards the full SQLserver version. If you don’t have a sqlserver license then certainly have a look at Postgresql. It’s also very powerful.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

Connor
Posts: 7
Joined: 05 Nov 19 6:59

Re: SQL Express: Necessary optimizations?

Post by Connor » 10 Nov 19 15:08

@Hert: Thank you for your swift feedback. I just installed the PostGreSQL server and installed a new instance of Photo Supreme.
And even the picture import worked like a charm - far beyond the issues I had with the SQL version.
Now I just need to find a backup solution, since I am unfamiliar with PostGreSQL.

Have a great day,
regards,

Connor

Post Reply