Feeds:
Posts
Comments

Archive for the ‘DBA Interview’ Category

Quick one today:

Occasionally, we find a need to retrieve all the IDENTITY columns in all our environments to perform analysis on some architectural tickets. Since there could be many databases across different environments, we need a single script that could retrieve necessary data — so we could point to to a multi-script and BAM !! All data retrieved in one shot !!

--
--  Retrieve IDENTITY columns for all tables in a database
--
SELECT	  	  @@SERVERNAME				AS [ServerName]
		, DB_NAME()				AS [DatabaseName]
		, O.name				AS [TableName]
		, C.name				AS [ColumnName]
		, T.name 				AS [DataType]
		, I.seed_value
		, I.increment_value
		, I.last_value
		, IDENT_CURRENT(O.name) AS [IdentCurrent]
		, I.is_not_for_replication
		, I.is_nullable
		, I.column_id
		, I.user_type_id
FROM sys.objects AS O
INNER JOIN sys.columns AS C
	ON O.object_id = C.object_id
	AND O.is_ms_shipped = 0
INNER JOIN sys.identity_columns AS I
	ON C.name = I.name
	AND C.object_id = I.object_id
	AND I.is_identity = 1
INNER JOIN sys.types AS T
	ON T.user_type_id = I.user_type_id
ORDER BY @@SERVERNAME, DB_NAME(), O.name, C.name
GO

 

Result

Output will look something like this:

IDENTITY_Properties

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »

Quick one today:

Occasionally, we also have a need to retrieve all the server roles a login is associated with. Earlier we covered a similar post where covered a script to retrieve all database users & their database role assignments; In this we’ll  cover similar task, but for Server Roles & Logins.

--
-- List all Server roles a login is part of
--
SELECT DB_NAME(),  RP.type_desc AS [Role Type], RP.name, MP.name, MP.type_desc AS [LoginType]
FROM sys.server_role_members AS R
INNER JOIN sys.server_principals AS RP
	ON RP.principal_id = R.role_principal_id
	AND RP.type_desc = 'SERVER_ROLE'
INNER JOIN sys.server_principals AS MP
	ON MP.principal_id = R.member_principal_id
GO

Just like last time, if you run this from multi-script window, we could compare between two different environments.

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

Surprisingly, this need comes up frequently to list all the Sql Agent jobs in all instances in our lab machines. Sometimes we need to cross check to be sure all environments have the same jobs.

This below query lists out all jobs with their steps:

--
-- List all Sql Agent Jobs with their steps and other relevant information
--
SELECT
	  J.originating_server
	, L.loginname
	, S.database_name
	, J.job_id
	, J.name
	, J.enabled
	, J.description
	, S.step_id
	, S.step_name
	, S.command

FROM dbo.sysjobs_view AS J
INNER JOIN dbo.sysjobsteps AS S
	ON J.job_id = S.job_id
INNER JOIN sys.syslogins AS L
	ON L.sid = J.owner_sid
ORDER BY J.enabled ASC
	, J.originating_server ASC
	, S.database_name ASC
	, J.name ASC
	, S.step_id ASC
GO

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

Earlier in our lab environment, ran into this interesting error:

2019-06-20 00:18:46.67 Connecting to Distributor ''
2019-06-20 00:18:46.78 The replication agent had encountered an exception.
2019-06-20 00:18:46.78 Source: Replication
2019-06-20 00:18:46.78 Exception Type: 
Microsoft.SqlServer.Replication.ReplicationAgentException
2019-06-20 00:18:46.78 Exception Message: The snapshot could not be 
generated because the publisher is inactive.
2019-06-20 00:18:46.78 Message Code: 54057
2019-06-20 00:18:46.78

Replication from publisher to subscriber was set up correctly, but when the agent runs, the SQL Agent job stops with this error.

Resolution:

Go to Distributor and run this query to check the status of the publisher instance.


EXEC sp_helpdistpublisher

PublisherInvalid

As we can see, one of the publisher instance is set to Inactive. So now we need to reset it.

Run the script below to change the publisher status at distributor:


EXEC sp_changedistpublisher  @publisher = 'Publisher',  @property = 'active', @value = 'true'
GO

The result looks something like this:

Publisher_Active

Now  run the previous ‘sp_helpdistpublisher’ again and now the status is active:

PublisherValid

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Quick one today:

Intellisense is a great feature. Quite often, we run into situations where we don’t see the drop-down showing new tables, columns, objects, etc names. While this is not frequent, it does happen often enough that it causes some good discussions in conference calls.

Problem Definition:

When we make schema changes in the current session, they are not always visible within intellisense immediately (similarly other people making schema changes to the the database schema are not immediately visible in your session either)

As you can imagine, Intellisense has its own cache and it needs to be refreshed to update with recent schema changes.

Resolution

Two options to fix this:

  1. Ctrl + Shift + R
  2. Edit menu >> Intellisense >> Refresh Local Cache
IntellisenseCache
Hope this helps,
_Sqltimes

 

Read Full Post »

Quick  one today:

With regular frequency, there is a need sometimes to change the password of a login. Using SSMS, its an easy step. But when we have a lot of such changes, its gets easier to use T-SQL script.

--
-- Change Login Password
--
ALTER LOGIN LoginName
WITH PASSWORD = 'NewPassword'
GO

For more  info

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

In our lab environemnt, one of the SQL cluster environment ran into this error.

 

 

Error Message:

Clustered role 'Cluster Group' has exceeded its failover threshold.  
It has exhausted the configured number of failover attempts within 
the failover period of time allotted to it and will be left in a 
failed state.  
No additional attempts will be made to bring the role online or fail 
it over to another node in the cluster.  Please check the events 
associated with the failure.  After the issues causing the failure are 
resolved the role can be brought online manually or the cluster may 
attempt to bring it online again after the restart delay period.

The Cluster service failed to bring clustered role 'Cluster Group' 
completely online or offline. One or more resources may be in a 
failed state. This may impact the availability of the clustered role.

Resolution:

Errors like this are more common in Lab environments than in production environment. In any case, if you encounter the same error in production environment, then take extra caution before you follow these steps.

Possible Root Cause:

In lab sometimes, as part of some other effort, we inadvertently end up failing over the cluster several times within a short period of time. There is a setting in Cluster that measures the failover count.

  • If that count hits a particular threshold, it flags the Resource Group as ‘Failed’ state
  • And creates an entry in the the Cluster Events, that Cluster Resource Group failed after reaching the threshold (see the error message : Clustered role ‘Cluster Group’ has exceeded its failover threshold)

Resolution Steps:

According to this MSDN post, we could alter that failover count threshold to allow the resource group to come back up in a healthy state.

 

Step 1:

Go to Failover Cluster Manager >> Roles >> right click on the Resource Group and to go Properties:

  • Change the Maximum failures in a specified period to a larger number to account for the repeated failovers in recent hour.

ClusterFailoverThreshold_1

 

 

Step 2:

Go to Failover Cluster Manager >> Roles >>

In the bottom portion of the window where we the individual resources are listed, right click on the Resource that is in failed state and go Properties:

  • Increase the Maximum restarts in the specified period setting to a larger number to account for recent restarts.

 

ClusterFailoverThreshold_2

 

NOTE: This is not a standard solution for production environments.

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »