So what exactly does this thing do anyway other than keep growing HUGE?!? Let's take a look! A little background…I have been running SharePoint 2010 RTM since it was released and since then the Logging database has grown to 500MB. The database itself seems to be dynamic in that you will start off with a small number of tables, but as your increase your feature usage, more tables will be added.
Question #1: Just how many tables does your system have? Here's my current list (but you may have even more!):
- AnalysisServicesConnections
- AnalysisServicesLoads
- AnalysisServicesRequests
- AnalysisServicesUnloads
- Configuration
- ExportUsage
- FeatureUsage
- ImportUsage
- MonthlyPartitions
- NTEventLog
- PerformanceCounters
- RequestUsage
- Search_CrawlDocumentStats
- Search_CrawlProgress
- Search_CrawlWorkerTimings
- Search_PerMinuteFTQueryLatency
- Search_PerMinuteTotalOMQueryLatency
- Search_PerMinuteTotalQueryLatency
- Search_PerMinuteTotalUIQueryLatency
- Search_QueryErrors
- Search_VerboseFTQueryLatency
- Search_VerboseOMQueryLatency
- Search_VerboseQueryProcessorLatency
- Search_VerboseUIQueryLatency
- Search_VerboseWebPartQueryLatency
- SiteInventory
- SQLDMVQueries
- SQLMemoryQueries
- TimerJobUsage
- TraceDiagnosticsDummy
- ULSTraceLog
- Versions
All of those of course have their partitions to them.
Question #2: What the hell is a partition and why do I have 32 of them?
You will see that there are a max number of 31 partitions. But you will also notice that there is a partition number 0. That is 32 days of partitions? I think this is a bug in the stored procedures when the table partitions get created. But the idea is to keep the set of daily information broken apart via a rolling schedule (this means that day 9 may not map to the calendar day 9). You can look at the Configuration table to find what the current partition is. As the Timer Job is run to process the log data, it will move to the next partition and recycle as it goes.
Question #3: How do I turn the freakin thing off?
This can be accomplished by going into Central Administration->Monitoring->Configure usage and health data collection. Uncheck the "Enable usage data collection" checkbox and the "Enable health data collection" checkbox
Question #4: I like the data, but there is too much, how do I shrink it?
If you explore the Stored procedures in the database, you will see that it simply truncates when it moves to a new partition. You can do the same by looping through all the tables and truncating the tables. You can also just run the function called "fn_TruncateUnusedPartitionsHelper"
Question #5: I like the data that is generated, can I please have some more?
Hell yeah you can, if you explore the timer jobs titled "Diagnostic Data Provider*" you will see they are disabled! Turn them on and you will get even more data around:
- Event Log
- Performance Counters – Database servers
- Performance Counters – Web Front Ends
- SQL Blocking Queries
- SQL DMV
- SQL Memory DMV
- Trace Log
Ayman has a nice post on how you can use .NET Reflector to get in and see how these monsters do there dirty work here
Question #6: Can you have too much data?
Oh yeah! You can easily hit the 4GB limit on this database, for those of you running SQL Express (why?), you will be truncating tables quite frequently! For those of you with limited disk space, get ready to also be truncating tables. Those of you with HUGE disk arrays, well…you should be fine.
Question #7: Where is this documented schema thing I have seen so many marketing slides about?
Who the heck knows! It seems that anyone and any application is able to write to this thing. Even you could write into it with your own application. I'm not sure how you get a new set of partition tables setup and the attributes created, but eventually I will find some time to build something for it 🙂
Enjoy!
Chris