After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If two successive full database backups are the same size, there's no way to tell how much changed - and of course, you need to take a full backup to be able to tell whether the size changed.

A while ago I wrote a script that would enable SQL database DBAs to tell how big the next differential backup will be. A differential backup contains everything that's changed since the last full database backup, so every new and everything changed. Although this still doesn't show whether a single piece of existing content changed multiple times, it can still show whether existing content changed at least once. Better still, you don't need to take any kind of backup to run this script.

So, to get an idea of the churn rate of your content databases, check out this script - see New script: How much of the database has changed since the last full backup?.

Enjoy!

PS If you're using SharePoint and find this useful, please let me know and if there's any other scripts that would be useful - Kimberly and I are both starting to get more into SharePoint admin from a SQL perspective.

As you may know, Kimberly and I teach (and wrote) the first week of the 3-week Microsoft Certified Master - Database qualification. From next week onwards, we'll each also be teaching a day of the Microsoft Certified Master - Sharepoint certification too, as SQL experts. As such, we'll be learning a lot about how SQL Server behaves underneath Sharepoint and we're both going to start posting on SQL-Sharepoint issues (such as that it doesn't support multiple filegroups, so partial database availability isn't an option during disaster recovery).

Watch this space!

Both 2005 SP3 CU2 and 2005 SP2 CU12 contain fixes for two bugs that cause corruption in LOB data.

The first bug may happen when a column type is converted to varbinary(max), change the large-value-types-out-of-row option to true, insert a row, and then try to update it - described in KB 961648. The second bug may happen when you update an out-of-row LOB value in a database with snapshot isolation enabled - described in KB 962209. Either of these problems will result in a 7105 error:

Msg 7105, Level 22, State 11, Line 1
The Database ID Database ID, Page (N:N), slot N for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

You can get the fixes at 2005 SP3 CU2 and 2005 SP2 CU12.