SQL Server - Error Log Information

Hi,

I can't understand why it happends in sql server logs?

When i am opened sql server logs .....in message body showing all this below messages 
    -- Migrating non-intent lock resource: PAGE: 39:1:1109741
   Migrating page intent locks from 1:1109741 to 1:1109742
   -- Migrating key resource: KEY: 39:72057594075480064 (3c01895129db)

present in sql server logs having 30 lakhs records......each second generates thousands of records.....
Apart from this......every day i am scheduled full backup databases and log backups in this server.....this backup files generates only 10 records per day......apart from this all are above messages......

Due to this in log folder errorlog file occupying 2gb where it located in C:\programfiles\microsoft sql server\log

Please give me the suggestion ....how can i prevent........because it is a production server........

Thanks advance.......

Regards,
Prathap.

It looks like the Trace flags were turned on to check or change the SQL Server's activities.

Run the following command in your query analyzer. I hope in your case it will return you a line that will indicate that the Trace 1206 is turned on. The Trace 1206 prints the information of locks. But I am not sure what else it prints. 
DBCC TRACESTATUS(-1)

Run the TraceOff command to turn it off.
DBCC TRACEOFF(1206)

Note that these hold good for a single and current connection. If you are sure, that this is OK, and you want to do it at the server level, run the same commands with a -1 flag. .like, 
DBCC TRACEOFF (1206, -1)

Needless to say, you need to be logged in with a SysAdmin role.

What is the Error Log?

SQL Server maintains its own error logs that contain messages describing informational and error events.

These are simple text files stored on disk, but it’s best to view them using the facilities provided by SQL Server to prevent any SQL operations from being blocked by opening one of these files in a text editor. Also, the error log files can become quite large – SQL Server will stream the files into the Log File Viewer whereas notepad will open the file into a single buffer and consume a great deal more memory.

A new error log file will be created when one of two things happens:

1. The SQL Server service is started
2. sp_cycle_errorlog is called

Once this happens, any pending writes to the current log file will complete and a new log file will be created. The actual error log files can be found at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n.

Viewing the Error Log

refer to the below link,

http://facility9.com/2009/10/sql-server-error-logs/

Hello

Step 1 : login using  SysAdmin

STEP 2 : The SQL Server service should started

you can direct view the error log by using this  http://msdn.microsoft.com/en-us/library/ms187885.aspx(Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n) but it's very hard to find one event entry and catch it & print. better you should go with third step.

step 3: DBCC TRACESTATUS(-1) // allow u to print the Trace status.


set using this command.. --> DBCC TRACEOFF (1206( your line nuber), -1)

u will 100% resloved u query using above steps. let me know if u need any other information.