Summary

It has come to our attention that there are App-V customers who have migrated their App-V SQL Server Databases but did not transfer the 4 SQL Jobs, which are required to keep the App-V Database (DB) and App-V Management Server in optimal running condition. These 4 App-V DB jobs are:

- Check Usage History
- Close Orphaned Sessions
- Enforce Size Limit
- Monitor Alert/Job Status

Without these jobs in place, running correctly and on schedule the database will become bloated and could experience the problems outlined in the blog post below, if you have license enforcement enable it will not work correctly, failing to close orphaned sessions:

Troubleshooting App-V/SoftGrid Database Growth Issues
http://blogs.technet.com/b/appv/archive/2008/08/04/troubleshooting-softgrid-database-growth-issues.aspx

This sql script creates these jobs, it is part of the Creating a New Application Virtualization 4.5 Database Using SQL Scripting process outline in the white paper with the same name located here:

Creating a New Application Virtualization 4.5 Database Using SQL Scripting
http://download.microsoft.com/download/1/6/1/161042F3-9CDE-45F7-BC20-4FBDA8888890/Creating_new_db_using_sql_scripting.docx

The files referenced in the article above can be found on your MDOP disk in the following location:

MDOP\App-V\Installers\4.5_SP2\Server\Management\Support\SQLServer\CreateDB

How to run the script

SQL server Sysadmin rights are required to run this script, specifically rights to modify the App-V Database (APPVIRT by default). Open Microsoft SQL Server Management Studio on the SQL 2008 server hosting the App-V Database , navigate to and highlight the App-V database, click on New Query and either copy and paste the script into the Query window, or drag the <label>ALERTS_JOBS.SQL</label> file into the window. Click on the check icon (Parse) to verify all is well, and then click on ! Execute, if all goes well you should see “Command(s) completed successfully” in the messages window.

Alternately you can run this from an associated batch file CREATE_JOBS_MESSAGES.BAT with 2 other SQL scripts, 2645392_UPDATE.SQL and DATA_MESSAGES.SQL run from a common directory with appropriate rights to both the SQL server and master and App-V databases. Please see the description section of the aforementioned batch file for more information. Also see KB 2644495 for additional information.

SQL
Edit|Remove
/* ------------------------------------------------------------------------- *\ 
    Copyright (c) Microsoft Corporation.  All rights reserved. 
 
    Description: 
 
        This script creates the database alerts and jobs required to perform 
        database cleanup activities. 
 
        NOTE:  The database jobs MUST be created before the database alerts 
               because the alerts make reference to the jobs. 
\* ------------------------------------------------------------------------- */ 
 
EXEC sp_SFTrefreshjobs 
GO 
 
EXEC sp_SFTrefreshalerts 
GO