In this tutorial, you will gain access to some handy SQL snippets that work with the Episerver CMS database. Sometimes for maintenance, or, troubleshooting reasons, it can be useful to query the Episerver database directly to get extra information. For example, checking the contents of the audit table can be quicker in SQL. If you want access to my most useful Episerver CMS SQL scripts, this is the tutorial for you 🔥🔥🔥
WARNING: Before going too far into this tutorial, I should give a warning. Reading data from the Episerver database is fine. Writing data via a custom SQL script is not. The Episerver team build the APIs assuming the database shape. The upgrade wizard works based on known schemas. My advice is to never modify or change the data or the schema of your Episerver database. Writing custom SQL to change the Episerver database will likely break the CMS, so be warned ❌
What Do The Database Tables Do?: I tend to only look within an Episerver database once in a blue moon. I can work months at a time without needing to look at an Episerver database, so spending time memorizing the Episerver database structure seems a waste of brain cells. This is why I tend to run the script below whenever I'm in SQL. This script lists all the tables within a database and all the table properties.
This script can help me figure out which table I need to query next 😊
Check Database Table Size: Over a period of months and years, your website's database can grow exponentially, so much so your website can start to slow down due to the size. The following script will list the total number of rows in each table within your Episerver database and list the amount of size it takes up. If you have site performance issues, this is the first script to run:
Running this script will give you this data:
TBLPAGETYPE: As the name implies, this table is used to store all the page types that have been registered within your site. You can use the below snippet to get a list of all the page types created within your website.
For a more advanced view, including all the properties defined for each page-type you can use this script (thanks Henrik Fransas) :
This script will give you this view:
If you are running Episerver 6 or below, this script will do the same thing:
tblChangeLog: Every time a content editor performs certain actions within the CMS, like saving, publishing or creating a new page or block, an entry is made within tblChangeLog. Often it can be handy to view the contents of this table for debugging:
Having an audit log in a database causes a problem. This table can get really big. When the table gets too large, site performance can be impacted. This is why Episerver ships with a Change Log Auto Truncate scheduled task. You can access this task from within the Episerver admin UI. It is good practice to have this running once a month on your project:
If you don't want to enable this scheduled task, you may need to look in tblChangeLog periodically and clean it. There is a stored procedure to clean this log called netActivityLogTruncate.
tblScheduledItemLog: This table keeps a log of all the scheduled tasks run on your site. If you need to figure out what scheduled jobs have been run a week or a month ago, you can use , this script:
Happy Coding 🤘
