Solved

delete date from table

Posted on 2016-08-23
10
74 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 48

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 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