Sql Server Error : An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

Interesting one today:

Occasionally, when we restore databases from backups, we need to fix orphan users. Recently, a few years ago, we discussed about fixing orphan users using script. Here we’ll look at a scenario, where the regular orphan users script will not work. 

Error Message:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214 [Batch Start Line 61]
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.

The script “EXEC sp_change_users_login 'Auto_Fix', 'user'” only works for Sql Authentication based logins and not for Windows-level principals or non-login based users. See MSDN article for more details.

A user without login would look like this, in SSMS:

Sql Users Without Login

In such scenarios,

• sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
• sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : View or function has more column names specified than columns defined

Interesting one today:

In our development environment, sometimes we run into this error that’s a bit nebulous.

Error Message:
SQLException for SQL []; SQL state [S0001]; error code [4502]; View or function has more
column names specified than columns defined.;
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
View or function has more column names specified than columns defined.

Occasionally, we make changes to the tables schema and when you have Views that depend on these tables, they become stale. While the definition is still intact, the meta-data becomes stale, so they need to be refreshed.

This refresh uncovers if there are any conflicts between view definitions and the underlying tables. 

--
-- Refresh View metadata 
--
EXEC sp_refreshview 'dbo.View_SomeTable'
GO 

Once the underlying conflicts are resolved, if any, the view will execute correctly from application. Just refreshing the meta-data helps sometimes.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : When was the last time a table was updated

Quick one today:

Sometimes we need to find out the last time a table was touched, either for INSERT/UPDATE or for READ. This is not a regular use case.

In any given second, a busy database processes thousands of transactions; so it a rare scenario where we’ll stop the activity to find the time when a table was touched.

While it is rare, the need arises, mostly in DEV & TEST environments, where we need to query the last time a table was updated.

The below T-SQL helps us uncover the details:

--
-- Create temporary table for the test
--
CREATE TABLE dbo.TempUpdate (ID INT, Place VARCHAR(20))
GO

--
-- Perform INSERT and check the timestamp
--
INSERT INTO dbo.TempUpdate (ID, Place) VALUES (1, 'Test')
GO
SELECT OBJECT_NAME(object_id), last_user_update, last_user_lookup, last_user_scan, last_user_seek
FROM sys.dm_db_index_usage_stats
WHERE database_id = 23
AND OBJECT_NAME(object_id) = 'TempUpdate'

 

INSERT Timestamp

--
-- Perform UPDATE and check the timestamp
--
UPDATE dbo.TempUpdate SET Place = 'Test2'
GO
SELECT OBJECT_NAME(object_id), last_user_update, last_user_lookup, last_user_scan, last_user_seek
FROM sys.dm_db_index_usage_stats
WHERE database_id = 23
AND OBJECT_NAME(object_id) = 'TempUpdate'
GO

--
-- Clean up
--
DROP TABLE dbo.TempUpdate
GO

Updated Timestamp

Side Note: As notice, table scan was performed while performing UPDATE

--
-- Clean up
--
DROP TABLE dbo.TempUpdate
GO

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : A connection was successfully established with the server, but then an error occurred during the login process. The certificate chain was issued by an authority that is not trusted

Interesting one today:

Earlier this week, while playing one of our cloud database servers, ran into this confusing error.

Error Message:

A connection was successfully established with the server, but then an error occurred during the 
login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority 
that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The certificate chain was issued by an authority that is not trusted

Connection Certificate Error

Details:

This is one of those Sql Server errors that are clear and actionable. It clearly indicates that a connection to Sql Server was successfully established, but the encryption certificate (for secure communications) was the problem.

Seems like SSMS received the certificate from the server but it does not seem to be from one of the Trusted Certificate Authorities (CA) — hence the failure in going forward with maintaining the connection.

My guess is that this certificate is used both for identification (authentication) and encryption.

• You are who you say you are (for both parties)
• If I send you something, only you can decrypt it and vice versa

Usually these certificates are issued by CAs, Certificate Authorities, that verify you before giving you a public and private key. But Windows allows us to generate our own certificates for free, but these do not come with seal of approval from CAs.

In our case, its the latter. The certificate sent to SSMS was a generated one and not a CA issues certificate. Hence the error message.

Resolution:

Go to “Connect to Server” pop-up window >> Options >> Check “Trust server certificate“.

Trust Connection Certificate

Once selected, SSMS will accept the remote certificate and complete connection.

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server Trace Flags 1211 and 1224

Interesting one today:

A while ago, we looked at the lock escalation patterns within Sql Server. Today, we’ll cover a couple of trace flags that impact that lock escalation. Thanks to my friend Bob for this idea.

• Trace Flag 1211
• Trace Flag 1224

Trace Flag 1211: 

In short, this flag prevents Sql Server from escalating locks under any circumstances.

Within Sql Server, as we saw in the previous article, Sql Server prefers to carry out the work with least number of locks at the least level of locks. If work could be carried out with Row Locks, then Sql Server would issue necessary row locks on the records and carry out the task. When the number of row locks goes beyond a threshold, then it escalates to page-lock or table lock.

This default behavior could be altered with the trace flag 1211. When we enable this trace flag, Sql Server will not escalate locks. If it has row locks, it will carry out the work with row-locks, even if more and more row locks are needed (and even if its efficient to carry out the operation with fewer page locks or a single table lock).

More locks causes more memory pressure — as each lock takes certain amount of memory space. So even under memory pressure, it will work within current lock levels. If and when it runs out of memory, it issue an error (1204). 

This is a powerful trace flag, so its better to not use it. There is a better alternative. Trace Flag 1224.

Trace Flag 1224: 

In short, Sql Server prefers to complete the operation under current lock-level, but if that incurs memory pressure, then it escalates locks. This is a much better way to handle lock escalation than trace flag 1211.

Note: If both the flags are enabled, then trace flag 1211 takes precedence over 1224. So there will not be any lock escalation even under memory pressure.

Note: Use this script to enable or disable a trace flag or to check trace flags status. 

Hat tip to Bob for the idea.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Enable a Sql Agent Job using T-SQL query

Quick one today:

Recently, we saw a time-saving technique in starting job using T-sql query.

Today, we’ll look at another one. This comes in handy when dealing with multiple jobs — where handling them is more efficient using T-SQL than UI. Even during deployments, where we need to disable jobs at the beginning of the deployment and then enable them at the end.

--
--	Enable a Sql Agent Job
--
USE msdb
GO
EXEC dbo.sp_update_job    @job_name = N'SomeJobName'
						, @enabled = 1
GO  

 

This is simple and elegant way to enable/disable one or many jobs at the same time, making interaction with SQL Server more efficient. Just query job names from

--
--    Get list of job names
--
SELECT * FROM msdb..sysjobs
GO

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Change Login Password using TSQL Query

Quick one today:

Occasionally, we need to change the password of a login within SQL Server. Following T-SQL template is handy query to carry out that operation.

--
-- Change login password using T-SQL
--
ALTER LOGIN [Login] 
WITH PASSWORD = 'new password'
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Replication Error: Specify all articles when subscribing to a publication using concurrent snapshot processing

Interesting one today:

In our lab environment, while setting up replication on a few nodes, we ran into this interesting error message.

Error:

 

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 15
Specify all articles when subscribing to a publication using concurrent snapshot processing.

 

At first glance, it did not make much sense — as we did not make any changes to the code. But upon further digging, it became obvious the minor change that was made. @sync_method parameters.

The section where we define the publication, looks like this:

Replication Add Publication

While defining the publication, along with all other parameters, we also mention the way in which the articles & data from Publisher is sent to each Subscriber. @sync_method defines, which approach to take.

The associated ‘add_subscription’ (which threw the error) looks like this:

Replication Add Subscription

Solution:

 

Bingo !!

When a Publication is defined with @sync_method as ‘concurrent‘, then it creates all articles/tables as one chunk of data (not as individual tables) in native mode. So we need to be sure to define @article parameter in ‘sp_addSubscription‘ with ‘all’ as value (and not individual table names) — since the articles are not available individually.

Replication Add Subscription

 

Viola !! Now the add subscription works !!

 

Side note:

 

If you want to add individual tables/articles in the ‘sp_addsubscription‘, then you want to consider using ‘native‘ for @sync_method.

Replication Add Publication

Replication Add Subscription

Hope this helps,

_Sqltimes

Read Full Post »

DBCC CHECKDB Error Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation.

Interesting one today:

On a lab machine, when CHECKDB was executed, this seemingly tricky error popped up. This was a lab machine, and there was no traffic or any one connected to it.

Code that we ran:

--
-- CHECKDB error
--
DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
	, TABLOCK
GO

Error Message:

Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to 
perform the operation.

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot 
could not be created and the database or table could not be locked. 
See Books Online for details of when this behavior is expected and what workarounds 
exist. Also see previous errors for more details.

After checking that there was no traffic or any connections to the database, I re-ran the same CHECKDB a few more times and still ran into the same error.

Reading this MSDB article, some ideas came to mind. The two conditions mentioned in the article are not the root of the problem.

• At least one other connection is using the database against which you run the DBCC CHECK command.
• The database contains at least one file group that is marked as read-only.

Once the TABLOCK is removed, the command worked.

DBCC CHECKDB ('DB')
WITH ALL_ERRORMSGS
	, EXTENDED_LOGICAL_CHECKS
GO

 

Hope this helps,
_Sqltimes

 

 

Read Full Post »

Sql Server : CPU TIME vs. Elapsed Time

Interesting one today:

When we run a query with SET STATISTICS TIME ON, we see CPU Time and Elapsed Time statistics for a query. What is the difference?

SET STATISTICS ON

 

CPU Time is amount of CPU cycles (in time) spent by the workload thread on a particular processor/CPU. This is not an indication of the duration since the query began running on that CPU (there are some nuances — we’ll cover below)

Elapsed Time is the total duration of that thread, the time it took from start to end.

Simple Example:

Let’s say there is a workload thread for a simple query. The query runs for a bit (let’s say 2 seconds) and gets put on hold for a bit (let’s say 5 seconds), and then runs again (let’s say for 1 second) all the way to completion. The time the thread actually spent on CPU is counted towards CPU time (the two times, 2 & 1 seconds — 3 seconds total). Elapsed time is the total duration from when the query was issued to be executed to the time the result was generated — total 8 seconds.

Parallel Processing:

Let’s say there are 4 thread carrying out the workload of a query. Each run on a processor for 1 second and complete all the work in one shot.

Total CPU Time = 4 * 1 = 4 seconds (4 CPU’s with 1 second each)

Elapsed Time = 1 second (from start to finish)

Summary

CPU Time and Elapsed Time measure different time metrics for the same query. One measure the CPU cycles spent (in time) and the other measures the duration. The difference is important in figuring out what problem we are trying to solve.

1. If a query has high Elapsed Time, but low CPU Time, that means, there is some CPU contention or other resource contention (waiting for all resources to be available before the query could be given a CPU).
   1. We want to look for ways to optimize the the way query is written — is it requesting for more resources (rows) than it needs. End user will feel the pain in waiting — if this happens to majority of the queries. 
   2. Adding to this, if the CPUs are all busy all the time, that definitely means we need bigger CPUs for the workload or optimized queries.
   3. Conversely, if the CPU’s are not too busy at this time, that means, the thread is just waiting for other resources (concurrency — locked tables) to be released for this query to progress. Optimize the queries.
2. If CPU Time is higher than Elapsed Time, then there is good amount of parallelism in play.
   1. As long as the CPUs are optimally utilized, this is not a problem, but if you notice more than 80% utilization all the time (the threshold is even lower if they are virtual CPUs), then we need to add extra CPUs to alleviate the situation.
   2. This may not be a problem right now, but it will evolve into a problem soon. One extra long query will snowball everything into high delays — to the point where a user will notice the delays. 

 

Hope this helps,

_Sqltimes

Read Full Post »