This article is a walk through on the process of automating the SQL server commands with Intrust Server.
At time, Intrust Server gathering, import or reporting jobs execution may result in seemingly unmanaged and rapid increase of the TEMPDB database volume.
However, the common method of resetting the TEMPDB (server reboot) is usually unacceptable. SQL Server does provide means for truncating the TEMPDB with SQL statements without server restart. Still, it poses a problem as the database may be affected by multiple Intrust jobs scheduled to run at multiple times, therefore one can never tell when exactly the TEMPDB shrink statement must be executed.
1) Create a simple Transact-SQL script file by using Notepad, follow these steps:
- Click Start | All Programs | Accessories, and then click Notepad.
- Copy and paste the following Transact-SQL code into Notepad:
DBCC SHRINKFILE (TEMPDEV,100)
- Save the file as tempdb_shrink.sql in the C drive (on the SQL Server computer).
2) Schedule the execution of the SQLCMD Utility with the Task Scheduler on the computer with SQL Server installed:
sqlcmd -S myServer\instanceName -i C:\tempdb_shrink.sql
3) Open Intrust manager and locate a job (gathering, import, reporting) that allegedly leads to TEMPDB size increase.
4) Add a new job belonging to the same task as the job on step (3). As the 'Job Type' select 'WIndows Scheduled Task' option. On the next page of the Wizard specify the rest of the parameters.
5) Configure the job from the step 3 to be as a predecessor of job on step 4.
With the above steps, once jobs that potentially violate TEMPDB executed successfully, it will be followed by the SQL script job that will purge the TEMPDB.
Please consider carefully the use of the SQL script that is shrinking the TEMPDB. Under some circumstances it can impact the SQL Server performance in a negative fashion.