To get the best performance while storing Load Test Results into Database, we need to make sure that the recovery model on the database is set to “SIMPLE”. Sometimes when the DB create script, loadtestresultrepository.sql, that is in ~Program Files\Microsoft Visual Studio 10.0\Common7\IDE folder is used to create the results database, the recovery model is erroneously set to “FULL” which is not a desirable setting for Load Test Results DB.
To check if the recovery model is set to “SIMPLE” and to fix, follow these steps:
Step 1: Run “Visual Studio Command Prompt 2010” in Administrator mode
Step 2: Open “Notepad checkDB.sql”
Step 3: Enter the following query into the file and save:
CAST(DATABASEPROPERTYEX(name, 'Recovery') as nvarchar(30))
Step 4: Run the following command:
sqlcmd -S .\sqlexpress -i checkDB.sql
Step 5: Make sure the Load Tet results DB is listed as "SIMPLE" for recovery model
Step 6: If it is NOT set to SIMPLE, enter the following SQL into "fixDB.sql"
ALTER DATABASE LoadTest2010 SET RECOVERY SIMPLE
Step 7: Run command:
sqlcmd -S .\sqlexpress -i fixDB.sql
Step 8: Repeat step 5 to make sure it is now changed to "SIMPLE".
The reason why we want to set recovery model to SIMPLE is that the Load Test results are always bulk loaded into database. So we do not need to maintain database logs, which makes the DB operations slow. You can read more about the recovery models on MSDN