Feeds:
Posts
Comments

Archive for the ‘DBA’ Category

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:

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 »

Interesting one today:

Quite often, we need a quick way to check if correct permissions are granted on the database objects. Since there could be thousands of such objects with hundreds of different possible permissions, this gets a bit daunting.

In the past, we’ve seen some dynamic between Roles, Logins, Users & Permissions. Today, we’ll look into a quick way to a query to retrieve permissions on all objects for all principals; Then we can filter further and focus on the objects/logins we need.

--
-- Detailed Permissions<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>
--
SELECT    P.class_desc                          AS [Permission_Category]
		, P.grantee_principal_id				AS [Principal_ID]
		, L.name                                AS [Principal_Name]
		, L.type_desc							AS [Principal_Type]
		, P.major_id							AS [ID_of_Resource]
		, CASE P.class_desc
			WHEN 'CERTIFICATE' THEN C.name
			WHEN 'SYMMETRIC_KEYS' THEN S.name
			WHEN 'TYPE' THEN T.name
			ELSE O.name
		  END									AS [ObjectName]
		, O.type_desc							AS [Detailed_Object_Type]
		, P.state_desc                          AS [Permission]
		, G.name                                AS [Who_Gave_This_Permission]

FROM sys.database_permissions AS P
INNER JOIN sys.database_principals AS L
       ON L.principal_id = P.grantee_principal_id
LEFT OUTER JOIN sys.database_principals AS G
       ON G.principal_id = P.grantor_principal_id
LEFT OUTER JOIN sys.objects AS O
       ON O.object_id = P.major_id
LEFT OUTER JOIN sys.symmetric_keys AS S
       ON S.symmetric_key_id = P.major_id
LEFT OUTER JOIN sys.Certificates AS C
       ON C.certificate_id = P.major_id
LEFT OUTER JOIN sys.types AS T
	ON T.user_type_id = P.major_id
WHERE P.major_id &gt;= 0      --     Ignore system objects
ORDER BY P.class_desc, L.name ASC
GO

DetailedPermissions

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In our lab, quite frequently, we need to cross check the replication distribution agent profile settings between each environment to be sure they are set correctly. Replication Monitor provides a good way to check each agent and its profiles, but when you have many, many distribution agents in each environment, manual check is not fun.

This could be done by collecting all agent profile parameters using T-SQL

--
-- Run this query pointed to the Distributor
--
SELECT    D.id
		, D.name
		, D.publisher_database_id
		, D.publisher_id
		, D.publisher_db
		, D.publication
		, D.subscriber_id
		, D.subscriber_db
		, D.subscription_type
		, D.profile_id
		, D.creation_date
		, D.subscriber_security_mode
		, D.subscriptionstreams

		, CASE	WHEN P.agent_type = 1 THEN 'Snapshot Agent'
				WHEN P.agent_type = 2 THEN 'Log Reader Agent'
				WHEN P.agent_type = 3 THEN 'Distribution Agent'
				WHEN P.agent_type = 4 THEN 'Merge Agent'
				WHEN P.agent_type = 9 THEN 'Queue Reader Agent'
				ELSE 'Unknown'
		  END AS [AgentType]
		, P.profile_name
		, CASE	WHEN P.type = 0 THEN 'System Profile'
				WHEN P.type = 1 THEN 'User Defined Profile'
				ELSE 'UNKNOWN'
		  END AS [ProfileType]

		, PML.parameter_name
		, PM.value AS [ParameterValue_InUse]

		, PML.default_value
		, PML.min_value
		, PML.max_value

FROM dbo.MSdistribution_agents AS D
INNER JOIN msdb.dbo.MSagent_profiles AS P
	ON D.profile_id = P.profile_id
	AND P.agent_type = 3 -- Distribution agent

INNER JOIN msdb.dbo.MSagent_parameters AS PM
	ON PM.profile_id = D.profile_id

INNER JOIN msdb.dbo.MSagentParameterList AS PML
	ON PML.agent_type = P.agent_type -- Distributor
	AND PML.parameter_name = SUBSTRING(PM.parameter_name, 2, 50)
GO

Result looks something like this:

Distributor_AgentProfiles.PNG

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

This may be common knowledge, but upon running some tests its evident that Snapshot Replication does both the things:

  1. Deliver a complete snapshot (of all articles chosen) to the Subscribers
  2. Also delivers, incremental changes to those articles a.k.a INSERTS/UPDATES / DELETES

Or let’s state it differently:

  1. Snapshot and Transactional replication are two different replication types
  2. When we set up replication, we create:
    1. LogReader Agent : Reads TRN Log at Publisher and writes changes to Distributor
    2. Snapshot Agent: Creates a complete snapshot/backup/data of Publisher database to be created at Subscriber (but does not deliver it to Subscriber)
    3. Distributor Agent: This agent delivers data created in the above two steps to the Subscriber.
      1. Delivers the continuous changes that LogReader brings
      2. Delivers snapshot that Snapshot agent creates
  3. Transactional Replication:
    1. When we create Transactional Replication, the continuous activity at Publisher is replicated to Subscriber. This is carried out by LogReader Agent & Distributor agent.
      1. Distributor has replication procedures for INSERT, DELETE & UPDATE that take each DML activity at Publisher and re-play it as Subscriber
    2. But Transaction replication expects database & tables to already exist at the Subscriber
      1. And that the object structure matches — as the replication procedures (INSERT & UPDATE) depend on the Table & Column names and column order
    3. Creating Snapshot Agent is an optional step while establishing Transactional Replication. Its not needed to create Snapshot Agent, if the database objects already exist at Subscriber;
    4. And all we care about is replicating data from this point onward (and not the old data)
  4. Snapshot Replication:
    1. As part of creating Snapshot replication we create Snapshot Agent & Distributor
    2. We create all replication procedures (for each article) to replicate incremental changes on top of initial snapshot/backup/data

Summary

  • Yes, when we set up Snapshot replication, not only is the initial data from Publisher is replicated to the Subscribers, it also replicates incremental changes happening at Publisher to all Subscribers.
  • Snapshot Agent is different Snapshot Replication

 

Hope this helps,
_Sqltimes

 

Read Full Post »

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

 

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 »

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).

Example:

From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.

Resolution:

First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.

 

SETSPN -L <SQL Server Instance Service Account>

 

If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:

 

Kerberos Tool Output

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

Valid SPN

 

Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA

 

 

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 »

Older Posts »