Find the host name

Question

Find the host name

shohelr2003

Hall of Fame

Points: 3535
More actions
July 17, 2013 at 1:46 am

#280181

Dear,
I require to know the host name from which my database has been accessed I mean performed DDL,DML operations within a daterange. Say, I want to know the host name within the range of 16/07/2013 13:00:00 to 16/07/2013 14:00:00
I don't have any trace file.
Please help me to find out this. I am in stack.
Regards,
Akbar

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

For that you would have needed to have a trace running over the time period you're interested in. SQL does not record DML statements by default. For DDL, you might get some out of the default trace, providing the time you're looking for is not very long ago.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

You could read a little bit of information out of the default trace (if the timeframe hasn't been overwritten yet).

-- declare variable

declare @value sql_variant

-- get current tracefile of the default trace (SQL 2005+)

SELECT

@value = value

FROM

fn_trace_getinfo(default)

WHERE

traceid = 1

and property = 2;

--read tracefile

SELECT

*

FROM

fn_trace_gettable(cast(@value as NVARCHAR(200)),1)

WHERE

EndTime BETWEEN @startdate AND @enddate

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

Thank GilaMonster and Hanshi for your comments.

So, in future what should I do to trace DML operations performed by the host name?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Read up on SQLAudit, Change data capture, change tracking, see if any of them do what you want.

If you're considering a trace for all DDL operations, that can get huge. I have a client's server that generates over 1GB of trace data every hour, and that's tracing at the procedure level, not the statement level that you're after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 5

GilaMonster (7/17/2013)
Read up on SQLAudit, Change data capture, change tracking, see if any of them do what you want.

Thanks I have no idea of SQLAudit, Change data capture, change tracking. I must go through these.