turn on verbose logging and SQL in SQL Server 2005

Question

Post reply

turn on verbose logging and SQL in SQL Server 2005

gary1

SSC-Insane

Points: 21152
More actions
November 8, 2010 at 1:56 pm

#143977

Hi,
We have SQL Server 2005 EE with SP3 and I want to turn on verbose logging and SQL. From where I can enable this?
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

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

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

It's in the NSService.exe.config under sql server installation folder under notificationservices folder

For each of the items in the <switches> node, set the logging level by changing the value attribute.

To apply the logging changes, save the file and restart the instance of Notification Services.

---------------------------------------------------------------------------------------
It begins by taking the first step.

gary1 SSC-Insane Points: 21152 More actions · Answer 2

It's in the NSService.exe.config under sql server installation folder under notificationservices folder
For each of the items in the <switches> node, set the logging level by changing the value attribute.
To apply the logging changes, save the file and restart the instance of Notification Services.

Thank you,

But we do not have Notification Services installed.

We have only Database Engine & Integration Services.

In this case, how to enable Verbose logging?

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

What do you mean by 'verbose logging' Logging of what to where?

SQL itself has no logging levels. Most of the replication agents do, but I can't think of much else.

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

gary1 SSC-Insane Points: 21152 More actions · Answer 4

We are analyzing Blocking and MS recommended to enable Verbose logging and I did NOT find any option for that?

So I just wondering whether we have that option in Database engine?

Thanks

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 5

So is this logging related to SQL Server agent( like jobs etc)?

---------------------------------------------------------------------------------------
It begins by taking the first step.

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 6

When any critical error / warning is reported by SQL Server Agent, it is written to a log file MSSQL\Log\SQLAgent.out. By default only critical errors and warnings are reported to this log file. If you would like to see additional information messages, here are the steps you could follow to enable verbose tracing

1) Right click on SQL Server Agent --> Porperties

2) Turn on “Include execution trace messages”; Click Ok

3) Restart SQL Agent and View Current Agent Error Log

---------------------------------------------------------------------------------------
It begins by taking the first step.

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

gmamata7 (11/8/2010)
We are analyzing Blocking and MS recommended to enable Verbose logging and I did NOT find any option for that?

Ask whoever recommended it what he's talking about and where to find it.

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

gmamata7 (11/8/2010)
We are analyzing Blocking and MS recommended to enable Verbose logging and I did NOT find any option for that?

If the blocking is caused by replication jobs, you can add following parameters for verbose logging

[-Output output_path_and_file_name]

[-OutputVerboseLevel [0|1|2] ]

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 9

Try adding appropriate Trace Flags in SQLServer Service, you can then check the log file for detailed log.

Vishal Gajjar
http://SqlAndMe.com

forsqlserver SSCoach Points: 18902 More actions · Answer 10

forsqlserver

SSCoach

Points: 18902

April 19, 2011 at 3:27 am

#1313223

Thanx...:-)

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 11

After enabling it sql server agent is not starting..

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 12

My sql server agent service is not starting after doing this..It is a cluster..

Thanks

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

Please post new questions in a new thread and include some details of what you've done.

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

forsqlserver SSCoach Points: 18902 More actions · Answer 14

Thanx Gila,

The no. of input of posts in sql server central is very high and my post is old within 1 hour.Due to which I comment on older posts and also wants the suggestion of those affected persons also.

Thanks