Troubleshooting SQL Server 2014 System Stored Procedures
If you have problems with SQL Server 2014 system stored procedures, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many system stored procedures bugs were fixed in SQL Server 2014 service packs,
you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. Executing the sp_add_log_shipping_database system stored procedure may cause
error 14412.
This error indicates that the destination database is already part of a log shipping plan.
To work around this problem, check the existing log shipping plan and correct it if necessary.
3. Executing the sp_remove_log_shipping_monitor system stored procedure may cause
error 14417.
This error indicates that the monitor server cannot be removed while databases are participating
in log shipping. To work around this problem, you should execute the sp_remove_log_shipping_monitor
system stored procedure only after all log shipping databases and log shipping plans have been
removed.
4. Adding a linked server using the sp_addlinkedserver system stored procedure
may cause error 15028.
This error indicates that the server with the name you specify already exists. For example, if
you add a new subscriber for replication, SQL Server adds a linked server. Adding a server with
the same name as an existing subscriber may cause error 15028 to occur. To work around this
problem, specify another name for the adding server.
5. Executing the sp_addtype stored procedure may cause error 15108.
This error indicates that the sp_addtype stored procedure cannot be used to define user-defined
data types for varchar(max), nvarchar(max) or varbinary(max) data types. You should use the
CREATE TYPE statement for this purpose.
6. You can encounter error 15250 during execution the SQL Server catalog procedures.
This error indicates that the database name component of the object qualifier must be the name
of the current database. To work around this problem, you should specify the current database
name as the table qualifier parameter.
7. Executing the sp_indexoption stored procedure may cause error 15389.
This error indicates that the sp_indexoption stored procedure is not supported for XML index.
In this case, you should use the ALTER INDEX statement instead of the sp_indexoption stored
procedure.
8. Executing the sp_mergecompletecleanup stored procedure may cause error 21506.
This error indicates that the sp_mergecompletecleanup stored procedure cannot be executed before
the sp_mergepreparecleanup procedure is executed. You should use the sp_mergepreparecleanup
stored procedure to initiate the first phase of merge metadata cleanup.
9. If you cannot add a linked server using the sp_addlinkedserver system stored
procedure, check the following:
– check that you have appropriate permissions to create the linked servers (executing the
sp_addlinkedserver procedure requires ALTER ANY LINKED SERVER permission)
– check that local and remote servers can communicate with one another by name, not just by
IP address
– check that SELECT @@SERVERNAME statement return value matches the computer name of each server
(otherwise, you should rename the server).
10. When you set up a Transactional replication and Initialize a transactional subscription
from a backup in SQL Server 2014, the Distribution Agent skips applying sp_MSins_,
sp_MSupd_, sp_MSdel_ stored procedures on a Subscriber.
In this case, you receive the following errors: “42000 Could not find stored procedure
‘sp_MSins_’. 2812″. This bug was first fixed in Cumulative Update package 1 for
SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693
11. When you call the sp_MScreatemergedynamicsnapshot stored procedure to create a
dynamic snapshot, you receive the error 20628.
This is the error message text: “Failed to generate dynamic snapshot.” This problem
occurs when the agent_id value exceeds 99,999 in the MSdynamicsnapshotjobs table.
This bug was first fixed in Cumulative Update package 3 for SQL Server 2014. You can
download the Cumulative Update package 3 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2984923
12. The sp_send_dbmail stored procedure truncates email attachments to 64 KB.
This problem occurs when you use the sp_send_dbmail system stored procedure with the
@attach_query_result_as_file option and the query has a large result-set. This bug was
first fixed in Cumulative Update package 4 for SQL Server 2014. You can download the
Cumulative Update package 4 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197
13. When you execute sp_sequence_get_range stored procedures and NEXT VALUE FOR
function in parallel on the same sequence object, duplicate sequence value
is generated.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 6
for SQL Server 2014. You can download the Cumulative Update package 6 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047
14. The error 11502 may occur when you execute sp_describe_undeclared_parameters
system stored procedure.
This error indicates that parameter type cannot be deduced. This problem occurs when
the sp_describe_undeclared_parameters stored procedure includes an always FALSE
statement in the statement for @tsql parameter. This bug was first fixed in Cumulative
Update package 7 for SQL Server 2014. You can download the Cumulative Update package 7
for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3046038
15. If you enable the C2 audit tracing by setting the “C2 Audit mode” option to 1
with sp_configure system stored procedure and then restart SQL Server 2014, the
“C2 Audit mode” option cannot be enabled.
This bug was first fixed in Cumulative Update package 8 for SQL Server 2014. You can
download the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
To resolve this problem, you should restart SQL Server 2014 one more time.
16. Deadlock may occur when you execute the sp_estimate_data_compression_savings
system stored procedure.
This problem occurs when the sp_estimate_data_compression_savings stored procedure is
executed on a table that previously had a “text in row” table option set but no longer
has a text, ntext, or image column type. This bug was first fixed in Cumulative Update
package 9 for SQL Server 2014. You can download the Cumulative Update package 9 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3075949