Solved

delete date from table

Posted on 2016-08-23
10
66 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 46

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41767041
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
ID: 41767054
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
ID: 41767423
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
ID: 41769737
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
ID: 41769917
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41770335
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
ID: 41771488
"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
ID: 41771970
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
ID: 41775708
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
ID: 41775710
tks all.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

910 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

21 Experts available now in Live!

Get 1:1 Help Now