VS 2010 Load Test Results Database Recovery Model

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:

SELECT name,

CAST(DATABASEPROPERTYEX(name, 'Recovery') as nvarchar(30))

FROM master.dbo.sysdatabases;

Go

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

master

                                                 SIMPLE

tempdb

                                                 SIMPLE

model

                                                 SIMPLE

msdb

                                                 SIMPLE

LoadTest2010

                                                 SIMPLE

Step 6: If it is NOT set to SIMPLE, enter the following SQL into "fixDB.sql"

ALTER DATABASE LoadTest2010 SET RECOVERY SIMPLE

GO

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