Solved

delete date from table

Posted on 2016-08-23
10
60 Views
Last Modified: 2016-08-29
hi,

if I want to delete data in a table of schema:

CREATE TABLE [dbo].[Events](
	[startTime] [datetime] NOT NULL,
	[checksum] [int] NOT NULL,
	[eventId] [int] NOT NULL,
	[eventType] [int] NOT NULL,
	[eventClass] [int] NOT NULL,
	[eventSubclass] [int] NULL,
	[spid] [int] NULL,
	[applicationName] [nvarchar](128) NULL,
	[hostName] [nvarchar](128) NULL,
	[serverName] [nvarchar](128) NULL,
	[loginName] [nvarchar](128) NULL,
	[success] [int] NULL,
	[databaseName] [nvarchar](128) NULL,
	[databaseId] [int] NULL,
	[dbUserName] [nvarchar](128) NULL,
	[objectType] [int] NULL,
	[objectName] [nvarchar](512) NULL,
	[objectId] [int] NULL,
	[permissions] [int] NULL,
	[columnPermissions] [int] NULL,
	[targetLoginName] [nvarchar](128) NULL,
	[targetUserName] [nvarchar](128) NULL,
	[roleName] [nvarchar](128) NULL,
	[ownerName] [nvarchar](128) NULL,
	[targetObject] [nvarchar](512) NULL,
	[details] [nvarchar](512) NULL,
	[eventCategory] [int] NOT NULL,
	[hash] [int] NOT NULL,
	[alertLevel] [int] NOT NULL,
	[privilegedUser] [int] NOT NULL,
	[fileName] [nvarchar](128) NULL,
	[linkedServerName] [nvarchar](128) NULL,
	[parentName] [nvarchar](128) NULL,
	[isSystem] [int] NULL,
	[sessionLoginName] [nvarchar](128) NULL,
	[providerName] [nvarchar](128) NULL,
	[appNameId] [int] NULL,
	[hostId] [int] NULL,
	[loginId] [int] NULL,
	[endTime] [datetime] NULL,
	[startSequence] [bigint] NULL,
	[endSequence] [bigint] NULL
) ON [PRIMARY]

GO

Open in new window



delete as according to the starttime when startime is older than 3 months ago. what should be the full statement of the where cause ?
0
Comment
Question by:marrowyung
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
DELETE FROM Events
WHERE startime < GETDATE()-90

Open in new window

Where 90 is the number of days
0
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 125 total points
Comment Utility
Hi
Please consider the following overall approach:

-- ** Ensure you have good database backup and/or table backup
-- Wrap delete in a transaction

BEGIN TRAN

   DELETE
   --SELECT *
   FROM 
   dbo.Events e
   WHERE e.[startTime] < DATEADD(MONTH, -3, GETDATE())

   -- Only commit when fully happy delete worked as required
   -- COMMIT TRAN

-- or undo if not sure
-- ROLLBACK TRAN

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
You need to strip the time off GETDATE(), otherwise you would delete partial days, which would be very confusing.

DELETE FROM dbo.Events
WHERE startTime < DATEADD(MONTH, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

Btw, if you (almost) query this table by startTime, the table should be clustered on startTime first.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
Barry Cunney,

why that approach ? the BEGIN TRAN seems don't have commit tran statement.

ScottPletcher,

"Btw, if you (almost) query this table by startTime, the table should be clustered on startTime first."

why concern on this ?

"DELETE FROM dbo.Events
WHERE startTime < DATEADD(MONTH, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))"

what is the diff between this and this:

DELETE FROM Events
WHERE startime < GETDATE()-90

Open in new window

?
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
Hi Marrowyung,
Yes, please check my code sample that I posted - you will see a COMMIT TRAN statement commented out.
The idea is that you run this one COMMIT TRAN line manually on its own separately, but only when you are fully happy that your DELETE has only deleted the required records and has not accidentally deleted too many records.
Please let me know if this explains better for you.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
what is the diff between this:

"DELETE FROM dbo.Events
WHERE startTime < DATEADD(MONTH, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))"

and  this:

DELETE FROM Events
WHERE startime < GETDATE()-90

I strip the time from the current date in the first code.  That ensures it doesn't delete partial days.  The second DELETE will delete only part of a day, before whatever the current time is when it happens to run.  That can be confusing if you're looking at data later: why do I see records from 1:23pm but not from 2:11pm for that day in the log?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"I strip the time from the current date in the first code.  That ensures it doesn't delete partial days"

yeah, but it can make t log very full, I prefer to separate it.

"The second DELETE will delete only part of a day, before whatever the current time is when it happens to run.  That can be confusing if you're looking at data later: why do I see records from 1:23pm but not from 2:11pm for that day in the log?

I can't see where is second delete
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You're doing ninety days at one time, and you claim a few more hours' worth will make the log too big?  That doesn't really make any sense.

Good luck with this.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
hi,

I change it to 15 days already sir.

the whole process finish in less than 1 hours.
0
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
tks all.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now