Solved

delete date from table

Posted on 2016-08-23
10
78 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 50

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:Scott Pletcher
Scott Pletcher 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
LVL 69

Expert Comment

by:Scott Pletcher
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:Scott Pletcher
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

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)

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 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