February 28, 2013 at 9:06 am
hello World,
I have table with date and rowcount like below.
Time stamp Row Count
2013-02-28 10:53:50.283 1211
2013-02-28 11:53:50.283 1212
2013-02-28 12:53:50.283 1213
2013-02-28 13:53:50.283 1213
2013-02-28 14:53:50.283 1213
I am running the below command to retrive the information.
select getdate(), 'Row Count' = count(*) from dba_backuplocation
Now, i have to create a table with two columns Timestamp & Row count (say the table name is Rowcount_monitor) and insert the rowcount values every hour using
a sql server job. ( If we put my requiremnt is stored procedure and get that executed through job).
Requiremnt :
I have to monitor the table and compare the last three hours of rowconut and if the rowcount is same then i have send an
database mail saying rowcount did not change from last three hours. We can use the above table as example where the last three rowcount did not change. Also we have have to delete the records if they are two days old.
Can someone help on this immediately.
February 28, 2013 at 9:33 am
Hi and welcome to SSC. Your post does not contain a whole lot of information. I cobbled together some stuff based on what you posted. I changed your column names because using reserved words as column names is far too annoying.
--create the data
if object_id('tempdb..#SomeData') is not null
drop table #SomeData
create table #SomeData
(
Time_Stamp datetime,
Row_Count int
)
insert #SomeData
select '2013-01-28 10:53:50.283', 1211 union all
select '2013-02-28 10:53:50.283', 1211 union all
select '2013-02-28 11:53:50.283', 1212 union all
select '2013-02-28 12:53:50.283', 1213 union all
select '2013-02-28 13:53:50.283', 1213 union all
select '2013-02-28 14:53:50.283', 1213
select * from #SomeData
--because your sample data is all actually ahead of my local time I had to use a variable here. You will want to use getdate()
declare @RunTime datetime = '2013-02-28 12:00:00.000'
--Now we can retrieve your information
if EXISTS
(
select count(*)
from #SomeData sd
where Time_Stamp > dateadd(hour, -3, @RunTime)--getdate())
having MAX(Row_Count) <> MIN(Row_Count)
)
select 'You should insert a row into a table to send an email'
--Now do your deletes.
Delete #SomeData
where Time_Stamp < dateadd(week, -3, getdate())
select * from #SomeData --Now the rows older than 3 weeks are gone
Notice how I posted ddl for the table and sample data. This is something you should do on future posts. It gives you a much better chance of the volunteers around here taking time to work on your problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2013 at 9:36 am
Welcome to the forum!
...
Can someone help on this immediately.
If you really want to receive help immediately (or at least close to this) here, I would suggest you to follow forum etiquette and include a bit more description in your question + something to help your helpers to help you :-).
Please follow the link at the bottom of my signature, to find out what I'm talking about.
February 28, 2013 at 9:57 am
Thanks for you quick reply.
I have inserted few records and chnged the date time column.
Time_Stamp Row_Count
2013-02-28 09:44:08.760 22
2013-02-28 10:44:20.017 22
2013-02-28 11:44:38.610 22
Intead of creating a # table in tempdb, i wold like to create the
table in a user DB as i have to insert the values into the table every hour.
below i have changed the script,
create table SomeData
(
Time_Stamp datetime,
Row_Count int
)
insert SomeData
select GETDATE(),COUNT(*) from mydb.dbo.rowcount
go
use master
go
select * from master.dbo.SomeData
declare @RunTime datetime = getdate()
if EXISTS
(
select count(*)
from SomeData sd
where Time_Stamp > dateadd(hour, -3, @RunTime)--getdate())
having MAX(Row_Count) <> MIN(Row_Count)
)
Now if only run the beloe command, it does not give me any results back.
declare @RunTime datetime = getdate()
select count(*)
from SomeData sd
where Time_Stamp > dateadd(hour, -3, @RunTime)--getdate())
having MAX(Row_Count) <> MIN(Row_Count)
Also, when you say
select 'You should insert a row into a table to send an email' -- what exactly you mean.
I was thinking like select top 3 records of the table order by the date_stamp desc and compare if the recrod count is same then send email.
let me know if you have any questions.
February 28, 2013 at 10:07 am
kk2286 (2/28/2013)
Thanks for you quick reply.I have inserted few records and chnged the date time column.
Time_Stamp Row_Count
2013-02-28 09:44:08.760 22
2013-02-28 10:44:20.017 22
2013-02-28 11:44:38.610 22
Intead of creating a # table in tempdb, i wold like to create the
table in a user DB as i have to insert the values into the table every hour.
You initially said you have a table. I don't have that table so I had to create one on my system. I have to have something to work with. The temp table was a representation of your table.
declare @RunTime datetime = getdate()
if EXISTS
(
select count(*)
from SomeData sd
where Time_Stamp > dateadd(hour, -3, @RunTime)--getdate())
having MAX(Row_Count) <> MIN(Row_Count)
)
The point here was that you should use getdate() INSTEAD of a variable. I had to use a variable because our systems are in different time zones.
Also, when you say
select 'You should insert a row into a table to send an email' -- what exactly you mean.
What I mean is, there are rows found within the last 3 hours that have the same row_count, we need to send an email. I used a select statement but that could just as easily be your send email logic.
I was thinking like select top 3 records of the table order by the date_stamp desc and compare if the recrod count is same then send email.
In your original post you said you wanted to check all rows within the last 3 hours. That is exactly what this code does.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2013 at 10:49 am
i came up with below script and it looks like its working.
My requirement was to monitor the rowcount of a particular table if it is increasing or not. I thought of scheduling the insert for every 1 hour so that the top 3 will be the rowcount for the past 3 hours. Can you modify the below script irrespective of the top 3 records and verify the rowcount for the past 3 hours did not change ( if the records count is more than 4 rows )?
CREATE TABLE MONITOR_ROW_COUNT -- This will be one time creation of table
(
TIME_STAMP DATETIME,
ROW_COUNT INT
)
INSERT MONITOR_ROW_COUNT
SELECT GETDATE(),COUNT(*) FROM MASTER.DBO.MYTABLE
SELECT TOP 3 TIME_STAMP,[ROW_COUNT] INTO #TEST FROM MASTER.DBO.MONITOR_ROW_COUNT ORDER BY TIME_STAMP DESC
SELECT * FROM #TEST
IF (
SELECT COUNT(DISTINCT ROW_COUNT) FROM #TEST
) = 1
EXEC MSDB.DBO.SP_SEND_DBMAIL
@RECIPIENTS='email@email.COM',
@SUBJECT = 'ROW COUNT OF THE TABLE IS SAME FROM PAST THREE HOURS',
@BODY = 'ROWS COUNTS ARE SAME',
@QUERY = 'SELECT * FROM #TEST
ELSE
PRINT 'ROW COUNT OF THE TABLE RE CHANGING FROM THE PAST 3 HOURS'
DROP TABLE #TEST
DELETE MONITOR_ROW_COUNT
WHERE TIME_STAMP < DATEADD(WEEK, -2, GETDATE())
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply