Way to delete/add entries to Microsoft® SQL Server™ log file

Question

Post reply

Way to delete/add entries to Microsoft® SQL Server™ log file

ankur_libra

Hall of Fame

Points: 3999
More actions
June 18, 2009 at 7:49 am

#133245

Hi All,
Is there is any way to add/delete entries from SQL Server log files?
This information helps in knowing whether any user intervention can cause addition or deletion of entries in SQL Server log files.
Thanks,
Ankur

Viewing 13 posts - 1 through 12 (of 12 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Do you mean the transaction log? or one of the error logs or maybe something else?

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

You can use RAISERROR ... WITH LOG if you're a sysadmin user or have ALTER TRACE permissions

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

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 3

with RAISE ERROR we can the error message to SQL Server Error Log,

But how can you delete the logs from SQL Server Error Log.

Rajesh Kasturi

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Rajesh kasturi (6/18/2009)
with RAISE ERROR we can the error message to SQL Server Error Log,
But how can you delete the logs from SQL Server Error Log.

you can use sp_cycle_errorlog to start a new log, though i am not sure how to remove individual log entries.

ankur_libra Hall of Fame Points: 3999 More actions · Answer 5

Thanks Gila,

Is there any way to delete an individual entry from the error log file as well?

Your valuable responses are always appreciable, commendable and trustworthy.

We all are thankful to you Gila, for valuable knowledge sharing....

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

ankur_libra (6/18/2009)
Is there any way to delete an individual entry from the error log file as well?

Short of cycling the log, opening the old log file in a text editor, deleting a line and saving it, no.

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

ankur_libra Hall of Fame Points: 3999 More actions · Answer 7

Thanks Gail,

It means that by using the text editors, one can even delete an entry from error logs.

Is the use of Text editor requires some special privileges?

Can you please name some of text editors which are available free or can be downloaded from the internet?

Thanks,:-)

Ankur

ankur_libra Hall of Fame Points: 3999 More actions · Answer 8

Hi,

I checked and found that text pad - notepad can be used for the same, but it doesnot reflect the chnages until the SQL services are stopped and restarted.

Is there is any way to avoid the restart of SQL Server services, to make the changes in error log using text editor?

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

You wanna maybe tell me why you want to remove an entry from an error log? The main use I could see for that is to hide evidence of something that was done (usually something that shouldn't be done). So can you maybe tell me what legit use you have for this?

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

Lowell SSC Guru Points: 323518 More actions · Answer 10

GilaMonster (6/19/2009)
You wanna maybe tell me why you want to remove an entry from an error log? The main use I could see for that is to hide evidence of something that was done (usually something that shouldn't be done). So can you maybe tell me what legit use you have for this?

sounds like a fingerpointing session; we had an incident like that once....developer1 who hated developer2, claimed that developer2 changed developer1 source code and introduced bad logic/errors on developer1's machine, and also tweaked the timestamps of the files so there was no "proof" that the files was changed.

professional misconduct accusations are pretty serious in my opinion.

on the face of it, how do you prove you didn't change something if they also accuse you of changing the audit of how things are changed?

That whole level of lack of trust means the business is out of whack. can you say witchhunt?

In our instance,by simply checking SourceSafe and comparing the source, we could see the bad logic was already in place...whereupon developer1 claimed that developer2 also changed everything in sourcesafe as well in order to cover his tracks....pu---lease!

we realized which developer had to move on to other opportunities at that point.

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!

ankur_libra Hall of Fame Points: 3999 More actions · Answer 11

Hi Gail,

This information is required just for knowledge purpose.

I came across the article about xp_logevent extended procedure, which is used to log a user-defined message in the Microsoft® SQL Server™ log file.

After reading it, a notion strikes to know about other methods to add and even delete entries to error log file, so asked from the experts....

Ankur

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

remember however that you need SA privileges to execute xp_logevent, so the mechanism isn't exactly wide open to "editing the logs". Essentially - those who have the access to manually edit the logs still do with this proc.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?