Tag Archives: Error message

How to troubleshoot error – Msg 195, Level 15, State 10, Line 1 ‘TRY_CONVERT’ is not a recognized built-in function name

SQL Server 2012, Troubleshooting February 19, 2014 Leave a comment

While running the following code on a database on my SQL Server 2012 instance I got the following error message

USE BB
go
SELECT TRY_CONVERT(xml, 'MyClub')

TCerr1The reason for this error is that this function TRY_CONVERT is 110 compatible. Which means it will only run on a database with 110 compatibility. Upon checking the compatibility level, I found the database to be on 90 or SQL Server 2005 compatible.
TCerr2Resolution:
I had to change the compatibility level to 110 and then ran the code with success.

TCerr3

Advertisement

How to increase the number of SQL Server logs

SQL Server Logs October 21, 2013 Comments: 2

In this article I would like to demonstrate how we can increase the number of logs in SQL Server for the engine. The SQL Server error log contains events and errors related to SQL Server engine and services. You can use this error log to troubleshoot problems related to SQL Server. The default value for the maximum number of log files is 6. However we can increase that. The maximum number of logs can be up to 99.

Let us now see how we can increase the number of SQL Server logs via SSMS:
Step 1: Connect to the SQL Server where you want to perform this
Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
EL5Step 5: When you click on configure the ‘Configure SQL Server Error Logs’  box opens. Change the Maximum number of error logs to the desired number.
Step 6: Click on OK.
EL6We can also run the below query to attain the same:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs',
REG_DWORD, 14
GO
Related articles

How to Recycle SQL Server Agent Error Logs

SQL Server Agent October 18, 2013 Leave a comment

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
EL1In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
go
EXEC dbo.sp_cycle_agent_errorlog
go

This will re-initialize the current log and start a new log with the current date time stamp
EL2
2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:
EL3
b) Click on OK
EL4

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.

%d bloggers like this: