[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 104
  • Last Modified:

delete date from table

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
marrowyung
Asked:
marrowyung
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
DELETE FROM Events
WHERE startime < GETDATE()-90

Open in new window

Where 90 is the number of days
0
 
Barry CunneyCommented:
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
 
Scott PletcherSenior DBACommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
marrowyungAuthor Commented:
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
 
Barry CunneyCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
marrowyungAuthor Commented:
"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
 
Scott PletcherSenior DBACommented:
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
 
marrowyungAuthor Commented:
hi,

I change it to 15 days already sir.

the whole process finish in less than 1 hours.
0
 
marrowyungAuthor Commented:
tks all.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now