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.
