problem in witing a query

Question

problem in witing a query

nazaninahmady_sh

Default port

Points: 1465
More actions
December 25, 2008 at 1:54 am

#133301

Hello
I want to write a query to understand how many records are added to the Table from (for example) 12:10am to 12:20am,
thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

IF your table has a a datetime column with a default value of getdate(), you whould simply use a WHERE MYDateColumn BETWEEN date1 AND Date2; If there is not column like that, you really can't do it.

there's no default tracking of stuff like number of records like that; if your database option for "Recovery Model" is set to "Full" and not "Simple" , you can use a 3rd party utility to read the log.

In SQL 2005, the default trace exists, but at the best, it can help you find DML statements made, or DDL statemetns that were executed, but NOT the number of records affected.

so you could get statemetns executed between two datetimes, but not the records affected.

declare @tracefile varchar(256)

SELECT @tracefile=convert(varchar(256),value)

from(

SELECT *

FROM fn_trace_getinfo(default)) X

where property=2

print @tracefile

SELECT * from fn_trace_gettable(@tracefile, default)

select

ev.name

,tr.StartTime

,tr.DatabaseID

,tr.DatabaseName

,tr.ObjectID

,tr.ObjectName as 'Trace ObjectName'

,o.name as 'Sysobjects Name'

,o.type_desc

,tr.ObjectType

/* see 'ObjectType Trace Event Column' in BOL to get a translation of these type codes */

,sv.subclass_name as 'ObjectAbbrv'

,tr.LoginName

,tr.HostName

,tr.ApplicationName

from fn_trace_gettable(@tracefile, default) tr

join sys.trace_events ev

on tr.eventclass = ev.trace_event_id

join sys.trace_subclass_values sv

on tr.eventclass = sv.trace_event_id

and tr.ObjectType = sv.subclass_value

and sv.trace_column_id = 28

left join sys.objects o

on tr.ObjectID = o.object_id

where starttime > '2008-06-30 11:00'

-- and databaseID = db_id(@dbname)

and eventclass in (12) -- SQL Completed

and eventsubclass = 1 -- only committed act

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Miron Berlin SSC-Addicted Points: 414 More actions · Answer 2

if the database is in fully logged mode you can query database log using dbcc log command.