Because I keep having to rewrite my scripts every time I need to have a spammy procedure to just be doing something, and because 10 minutes is 10 minutes – here’s my spamroller.

First create the table:

CREATE DATABASE [SPAMSPAMEGGSANDSPAM]
GO
USE [SPAMSPAMEGGSANDSPAM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SpamSpamSpam](
	[spam_id] [int] IDENTITY(1,1) NOT NULL,
	[Time_Entered] [datetime] NOT NULL,
	[TypeOfSpam] [varchar](100) NOT NULL
) ON [PRIMARY]
GO

Then create the job to populate it and mess it up every 10 seconds.

USE [msdb]
GO

/****** Object:  Job [LovelySpam]    Script Date: 1/07/2019 2:45:57 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 1/07/2019 2:45:58 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'LovelySpam', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'To simulate a little activity on the database this job enters Spam into a table.  The job inserts a new row, updates a random existing one and reads everything in the table every 10 seconds.  Note for future reviewers - we may need to reseed the database in about 680 years.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Bacon Eggs and Spam]    Script Date: 1/07/2019 2:46:00 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Bacon Eggs and Spam', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'declare @start as DateTime;
declare @randomnumber as int = cast(rand() * 9000 AS INT)+999
declare @randomtext as varchar(100) = cast(@randomnumber as varchar(100))+''!''
set @start = getdate()


SET @randomtext=replace(@randomtext,''1'',''spam, '')
SET @randomtext=replace(@randomtext,''0'',''sausage, '')
SET @randomtext=replace(@randomtext,''2'',''spam, '')
SET @randomtext=replace(@randomtext,''3'',''baked beans, '')
SET @randomtext=replace(@randomtext,''4'',''spam, '')
SET @randomtext=replace(@randomtext,''5'',''spam, '')
SET @randomtext=replace(@randomtext,''6'',''eggs, '')
SET @randomtext=replace(@randomtext,''7'',''spam, '')
SET @randomtext=replace(@randomtext,''8'',''bacon, '')
SET @randomtext=replace(@randomtext,''9'',''spam, '')
SET @randomtext=replace(@randomtext,'', !'','' and spam.'')

set @randomnumber=CAST(RAND() * 9 AS INT)+1

insert into dbo.SpamSpamSpam(Time_Entered,TypeOfSpam) values(@start,@randomtext)
', 
		@database_name=N'SPAMSPAMEGGSANDSPAM', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Spam Spam and Eggs]    Script Date: 1/07/2019 2:46:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Spam Spam and Eggs', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'Select * from dbo.SpamSpamSpam', 
		@database_name=N'SPAMSPAMEGGSANDSPAM', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Spam Sausage Spam Spam and Eggs with Spam]    Script Date: 1/07/2019 2:46:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Spam Sausage Spam Spam and Eggs with Spam', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--Update a random row
--Note - this step should get slower the bigger the table gets.  By design.

declare @start as DateTime;
declare @randomrow as int = cast(rand() * 9000 AS INT)+999
declare @randomtext as varchar(100) = cast(@randomrow as varchar(100))+''!''
set @start = getdate()


SET @randomtext=replace(@randomtext,''1'',''spam, '')
SET @randomtext=replace(@randomtext,''0'',''sausage, '')
SET @randomtext=replace(@randomtext,''2'',''spam, '')
SET @randomtext=replace(@randomtext,''3'',''baked beans, '')
SET @randomtext=replace(@randomtext,''4'',''spam, '')
SET @randomtext=replace(@randomtext,''5'',''spam, '')
SET @randomtext=replace(@randomtext,''6'',''eggs, '')
SET @randomtext=replace(@randomtext,''7'',''spam, '')
SET @randomtext=replace(@randomtext,''8'',''bacon, '')
SET @randomtext=replace(@randomtext,''9'',''spam, '')
SET @randomtext=replace(@randomtext,'', !'','' and spam.'')

select top 1 @randomrow=spam_id from dbo.SpamSpamSpam order by newid()

select @randomrow, @randomtext
update dbo.SpamSpamSpam set TypeOfSpam = @randomtext, Time_Entered = @start where spam_id=@randomrow', 
		@database_name=N'SPAMSPAMEGGSANDSPAM', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run it heaps', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=2, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20190701, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'ea695562-ea40-4536-8164-ea8c2afce7c4'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Once you’ve run both bits of code you’ll be the proud owner of a rather boring agent job that runs every 10 seconds, adds a row to a table and updates another random one. Add loops, more jobs and bigger fields depending on your purposes for randomly filling a table.

What’s the point? Well, this particular usecase I wanted to test the effects of statistics as rows changed. I’ve used the same type of procedure for measuring compression and testing isolation levels. It’s amazing how often I find myself rewriting this type of job just because I need to be testing against a database that pretends to have actual connections to it.

Enjoy.