...this got me thinking. It would be useful to see the number of records in
each of Remedy's data forms.
I just created the attached SQL Server View that lists row counts of just the
data tables (T, H, B) and their corresponding form names. To see this inside
of Remedy, create a Remedy View form of this SQL View.
HTH
Stephen
Remedy Skilled Professional
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Heider, Stephen
Sent: Thursday, January 10, 2008 8:59 AM
To: [email protected]
Subject: Re: Delete Records of Alert Events
Tadeu,
The form on my server had 500,000 records. However, with the TRUNCATE TABLE
command the RDBMS handles the deletions differently than a DELETE FROM command
(which is what the Remedy clients use).
The DELETE FROM command updates the Transaction Log so in the event of an error
it can Rollback the deletions. When you want to completely empty a table then
you certainly would not want to use the DELETE FROM. You could end up with a
7GB increase in the size of the Transaction Log and it would take forever
because, in your situation, it would need to re-write all 35 million records to
the log.
The TRUNCATE TABLE command wipes out every record in a table instantly and
discards all the data in one command. The Transaction Log is not updated which
is why it runs so quickly.
I have no reservations at all with running this command in a production
environment - so long as you get the table names correct :) However, this is
your environment not mine. If you are a little unsure of performing this
operation during regular hours (although it should not matter with this command
on this particular Remedy form) then you could clear out these entries
off-hours after performing a backup of the database.
HTH
Stephen
Remedy Skilled Professional
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Tadeu Augusto Dutra Pinto
Sent: Thursday, January 10, 2008 8:46 AM
To: [email protected]
Subject: RES: Delete Records of Alert Events
Hi Stephen,
Thank you very much for your post...
I did the test on my 'test server'... that I have only 2000 records .... it's
runs very well...
But, it's safe to execute this command in my 'production server'?? Alert Events
on this server has 35.626.325 records...
How many records did you delete from your system when you did it?
Around 35 million, too??
Thankx-in-advance, again...
Regards,
Tadeu Augusto Dutra Pinto
-----------------------------------------------------------------
IT Web Services ATM
Cinq Technologies
http://www.cinq.com.br
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Fone: 41 3018-2833 - Cinq
Fone: 41 2107-5736 - HSBC Outsourcing
-----------------------------------------------------------------
Confiabilidade, Inovação e Qualidade em T.I.
________________________________
De: Action Request System discussion list(ARSList) em nome de Heider, Stephen
Enviada: qui 10/1/2008 11:17
Para: [email protected]
Assunto: Re: Delete Records of Alert Events
**
Tadeu,
I dealt with this very thing yesterday. Here is what I did, and it did not
affect the server.
Deleting all these entries from a Remedy client will take forever and may not
even complete. Use the TRUNCATE TABLE command (SQL Server) to instantly clear
out all entries for this form.
To do so, find out the table names that comprise the Remedy form. Run the
following command to get the schema number.
SELECT schemaId
FROM ARSchema
WHERE name = 'Alert Events'
On my system it is 6. Then run these three commands to clear out all entries
(substitute the number 6 for the number on your system).
TRUNCATE TABLE T6
TRUNCATE TABLE H6
TRUNCATE TABLE B6
Now that the form is clear add workflow to automatically maintain the form
size. Add a display-only radio button field to the form named something like
'Delete This Record'. Add a filter that runs on Modify when this field = 0.
The filter will use a Run Process with the command Application-Delete-Entry
"$SCHEMA$" $1$ to delete the record.
Create an escalation that runs once a night which sets the value of 0 to the
'Delete This Record' field when 'Create Date' < ($TIMESTAMP$ - (86400 * 21))
This will keep only that last 21 days of entries in this form.
Stephen
Remedy Skilled Professional
________________________________
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Tadeu Augusto Dutra Pinto
Sent: Thursday, January 10, 2008 8:00 AM
To: [email protected]
Subject: Delete Records of Alert Events
Hello for all,
How can I clean up records from a table that growths abnormaly??
I'm facin' a serious problem with "Alert Events" table.
Since 2004 it's been growing without control... Nowadays, this table has around
7 GB...
I want to know if have a way that I can delete all records of this table with a
security mode (not crashing the server)...
Could it be with Escalations?
If Yes, how? With Application-Delete-Entry? Application-Query-Delete-Entry??
I thought in a way to delete records based in a range of date... Like taking
the date of First record saved in this table and going to deleting month by
month (in a range of a month/day)...
Is it possible??
Gimme your suggestions, please!
Thankx-in-advance...
Regards...
P.S.1: I have knowledge tha exists the Escalation "CleanUpAlertEvents" but I
think that this way will crash my ARS server if I runs it...
P.S.2: I have another tables like that... but Alert Events is the greater...
Tadeu Augusto Dutra Pinto
-----------------------------------------------------------------
IT Web Services ATM
Cinq Technologies
http://www.cinq.com.br
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Fone: 41 3018-2833 - Cinq
Fone: 41 2107-5736 - HSBC Outsourcing
-----------------------------------------------------------------
Confiabilidade, Inovação e Qualidade em T.I.
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___
__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
CREATE VIEW dbo.udv_ARS_FormRowCounts
AS
SELECT a.schemaId, b.Name FormName, CONVERT(VARCHAR,a.TableName) TableName,
a.TotalRows, CHECKSUM(a.TableName) RequestID
FROM (SELECT so.Name TableName, MAX(si.rows) TotalRows,
CASE ISNUMERIC(SUBSTRING(so.Name, 2, 99))
WHEN 1 THEN SUBSTRING(so.Name, 2, 99)
ELSE
CASE ISNUMERIC(SUBSTRING(so.Name, 3,
1))
WHEN 0 THEN SUBSTRING(so.Name, 2, 1)
ELSE
CASE
ISNUMERIC(SUBSTRING(so.Name, 4, 1))
WHEN 0 THEN SUBSTRING(so.Name,
2, 2)
ELSE
CASE
ISNUMERIC(SUBSTRING(so.Name, 5, 1))
WHEN 0 THEN
SUBSTRING(so.Name, 2, 3)
ELSE
CASE
ISNUMERIC(SUBSTRING(so.Name, 6, 1))
WHEN 0 THEN
SUBSTRING(so.Name, 2, 4)
ELSE
CASE
ISNUMERIC(SUBSTRING(so.Name, 7, 1))
WHEN 0
THEN SUBSTRING(so.Name, 2, 5)
ELSE
CASE ISNUMERIC(SUBSTRING(so.Name, 8, 1))
WHEN 0 THEN SUBSTRING(so.Name, 2, 6)
END
END
END
END
END
END
END schemaId
FROM sysobjects so
JOIN sysindexes si
ON si.id = OBJECT_ID(so.name)
WHERE so.xtype = 'U'
AND LEFT(so.Name,1) IN ('T','B','H')
AND ISNUMERIC(SUBSTRING(so.Name,2,1)) = 1
GROUP BY so.Name) a
JOIN arSchema b
ON a.schemaId = b.schemaId