Category Archives: Troubleshooting

SQL Server 2012 – How to change the collation of SQL Server.

SQL Server 2012, Troubleshooting July 6, 2016 Leave a comment

In this article we will see how we can change the collation of a SQL Server post installation. To achieve this we need to rebuild the master database. While rebuilding the master database, the process gives an option to change the collation. Let us understand the process step by step and change the collation of an already installed SQL Server.

Step 1: Take a backup of all your system databases and user databases.

Step 2: Script out all Logins.

In this example we will change the collation from ‘SQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
collate1

 

Step 3: Stop the SQL Server Service.

collate3

 

Step 4: Via Command prompt locate to the Binn directory of the SQL Server

collate2

Step 5: Run the below command

sqlservr -m -T4022 -T3659  -s”INST2012_1″  -q”SQL_Latin1_General_CP1_CI_AS”

Parameters:
[-m] Single User Mode
[-T] Trace flag turned on at startup
[-s] SQL Server Instance Name
[-q] New Collation

collate5

Step 6: Restart SQL Server and check the collation

collate6

Advertisement

How to Rebuild the MSDB database

Troubleshooting June 27, 2015 Comments: 2

I was preparing a migration plan to migrate all SQL Server databases from our existing SAN to new SAN. While doing so I thought of actually doing a small practice on my laptop SQL Server. The user databases moved from the existing location to another location very smoothly. I was very happy. Now was the turn of the system databases. I started with the MSDB database. As recommended in the Microsoft technet article of relocating System DB’s, I was following the steps prescribed. Pretty simple steps. After making the file location change in the master database, I stopped the SQL service, physically moved the files to the new location and then restarted the SQL service. The MSDB database would not come up….In recovery…..

What do I do?…I didn’t even have a backup of the MSDB database. I read few tech net articles that helped me rebuild the MSDB database from scratch. Here is what needs to be done

1) Stop all SQL server services.
msdb12) Start SQL Server from the command prompt using the trace flag T3608. The exact command for this would be : NET START [Name of the SQL service]/T3608. This is what the trace flag does as per Technet.

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require tempdb are initiated, then model is recovered and tempdb is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work.

msdb23) Now we need to detach the MSDB database from the instance. This is what needs to be done for the same. We need to run a SQLCMD command to do the same.
SQLCMD -E -S<servername> -dmaster -Q”EXEC sp_detach_db msdb”
msdb4After running this rename the existing MSDB files. Stop and restart SQL Service via the configuration manager.
msdb54) We need to locate the following .SQL file in the install folder of the SQL Server installation files.
msdb65) Open the script in SSMS and execute.

SQL Server agent was running successfully. The MSDB database was online again. The first thing I did was take a back up now.

 

 

 

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

SQL Server 2008 -How to troubleshoot error: Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856

Troubleshooting September 7, 2013 Comments: 2

A few days ago i encountered the below mentioned error message in SQL error logs in one of our production environments
Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856.

I found the following KB Article on MSDN to throw light on this issue. As per the article there was a stack dump that we found in the server.
StackWhat caused the schedulers to be non-responsive:
As per the article the cause of the issue is:
“This issue occurs because the profiling session is not closed correctly. The profiling session is used to calculate the cost of an object to be cached, and is based on memory usage and CPU usage”.

Resolution as Provided by MS in the Kb article for SQL server 2008 is:
Apply CU6 for SP 3.

Our patch level in the environment is beyond that and still this issue happened, so literally there is no solution provided by MS. We would wait and see if this error comes back too often and open a case with MS support.

SQL server 2008 – How to troubleshooting error: The fulltext filter daemon host (FDHost) process has stopped abnormally.

Full-Text Search, Troubleshooting September 5, 2013 Leave a comment

A few days ago i came across the below message in our SQL Server logs:
Error: 30089, Severity: 17, State: 1.
The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

Full-text search is a key component in our SQL environment as we support a database that consists of millions of documents. I started scanning the FT crawl log and also found the below error:
Error ‘0x80004005’ occurred during full-text index population for table or indexed view ‘[DBNAME].[dbo].[TABLENAMe]’ (table or indexed view ID ‘10045673’, database ID ‘6’), full-text key value ‘4’. Attempt will be made to reindex it.

In case you are not aware as to where to find the crwal log then go the following location:
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG folder- this is where log folder would be for SQL logs. Under this folder you will find files with following pattern:

SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]
Where DatabaseID is
The ID of a database. <dbid> is a five digit number with leading zeros.<FullTextCatalogID>
Full-text catalog ID. <catid> is a five digit number with leading zeros.
n an integer that indicates one or more crawl logs of the same full-text catalog exist.

For example, SQLFT00006000010.5 is the crawl log file for a database with database ID = 5, and full-text catalog ID = 10. The 5 at the end of the file name indicates that there are two crawl log files for this database/catalog pair.

I searched the msdn site with error message and this is what i found as the cause of the error:
This problem occurs because of a bug in the communication between the Sqlservr.exe process and the Fdhost.exe process. When batches are sent from the Sqlservr.exe process to the Fdhost.exe process, sometimes the outbound shared memory (OSM), which is the shared memory from Sqlservr.exe to Fdhost.exe, has insufficient space to fit the next column of a document. When this occurs, the Sqlservr.exe process sends a message to notify the Fdhost.exe process to process all the existing data in the OSM. While the Fdhost.exe process is processing data, the inbound shared memory (ISM), which is the shared memory from Fdhost.exe to Sqlservr.exe, may become full. In this situation, the Fdhost.exe process sends a “rewind” message to the Sqlservr.exe process. This message requests that the Sqlservr.exe process resend the last document from the first column. However, when the Sqlservr.exe process starts to send the document again, it starts from column 2 instead of column 1.

Resolution:
Apply Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2 and Cumulative update package 4 for SQL Server 2008

MS also suggests an workaround for the same which is increasing the ism size value:

What is ISM. Well ISM is the internal shared memory used by the FDHost.exe process.

To show the current value:
sp_fulltext_service ‘ism_size’

To change the value to a new value:
sp_fulltext_service ‘ism_size’,@value=NewValue

Useful tech articles of Full-text search:
http://msdn.microsoft.com/en-IN/library/ms142560%28v=sql.100%29
http://msdn.microsoft.com/en-us/library/cc721269%28SQL.100%29.aspx

SQL Server 2008 – How to troubleshoot error : Granted or revoked privilege EXECUTE is not compatible with object.

Security, Troubleshooting September 4, 2013 Leave a comment

Today while executing the following code i got this particular error message:

GRANT EXECUTE ON dbo.fn_SomeFunction_TVF TO SomeRole

Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

I started to read about this error message and this is what i concluded.

You cannot grant execute permissions on Table-valued functions. This is the way this has been designed. Though i could not find as to why this is so.

Solution:
I had to use the references keyword. The syntax for the grant statement hence becomes:
GRANT REFERENCES ON dbo.fn_SomeFunction_TVF TO SomeRole.

A snapshot of the MSDN article that details out permission levels to different securables is below.

Grant

For more details please visit the msdn link :
http://msdn.microsoft.com/en-us/library/ms191291.aspx

SQL Server 2008 – How to troubleshoot error: The operating system returned error 6(The handle is invalid……)

I\O, Troubleshooting September 3, 2013 Leave a comment

Recently i came across this error message in the sql server logs in one of our production systems.

“The operating system returned error 6(The handle is invalid.) to SQL Server during a read at offset 0x0000063931c000 in file ‘F:\MSSQL\DATA\DBFile_8.ndf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online”.

i started reading about this error message and i found this KB article that discusses the error message and provides a resolution

Link to the KB Article:
http://support.microsoft.com/kb/2015755

The cause of the error as mentioned in the article:
”
SQL Server uses Windows API’s [e.g. ReadFile, WriteFile, ReadFileScatter, WriteFileGather] to perform the I/O operations. After performing these I/O operations, SQL Server checks for any error conditions associated with these API calls. If these API calls fail with an Operating System error, then SQL Server reports Error 823.

The 823 error message contains the following information:

i)The database file against which the I/O operation was performed
ii)The offset within the file where the I/O operation was attempted
iii)Is the I/O operation a read or write request
iv)The Operating System Error code and Error Description

The 823 error message usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged“.

The resolution provided in the article was helpful for me to nail the issue. Please go through the KB article as discusses the RCA in detail

%d bloggers like this: