Fix : Msg 4928, Level 16, State 1, Line 9 Cannot alter column 'XXXXXX' because it is 'REPLICATED'

 Msg 4928, Level 16, State 1, Line 9

Cannot alter column 'xxxxxxxxxx' because it is 'REPLICATED'.  

I got this error when I tried to increase table column data type length from 150 to 300. I have removed the article from publication but no luck still error coming.

 

Finally the below procedure helped me to fix the issue :

This article had been previously removed from Two publications. So the above error should not have occurred...   We have a consistency issue. 

 Diagnostic  1: Check syscolumns.colstat.

 

Select * from syscolumns where id = object_id(<table name>)

The value of the colstat field for the particular column was 8192. I've run into this before on SQL 2k. The fix at that time was to update this field to 0 for the offending column. If you attempt that now, you get the following error :

Msg 259, Level 16, State 1, Line 2

Ad hoc updates to system catalogs are not allowed.

Either way... the 8192 is going to be an issue. I don't have the luxury of putting my production server into single user mode to modify system catalogs... so my fix needs to be real time and online.

Diagnostic 2: check sys.columns

Select is_non_sql_subscribed ,* from sys.columns where object_id = object_id(<table name>)

If the is_non_sql_subscribed field = 1, you have an issue. This can happen on servers with any type of replication including SQL Server only.  I'm currently running SQL 2k5 replication in my environment. No third party replication. Either way , the value returned for me was "1" which identifies an inconsistency issue. 

When the snapshot agent creates a new snapshot for your publication it sets this field to 1, once the snapshot is finished the agent resets the value back to 0. Our solution will use this snapshot agent behavior.

 

Workaround:

Create a new publication with your questionable article. Snapshot it. Check the data. SQL will attempt to fix your inconsistencies and properly flag the is_non_sql_subscribed field. Drop the article from your new publication, make your table edits and add the article back to your original publications.

Alter database set multi_user not working

Alter database set multi_user not working and causing deadlock with same session id even if I kill all sessions against to the desired database.

In order to resolve this first you need to set dead lock priority and then change database to multi user mode.

USE [master] SET DEADLOCK_PRIORITY HIGH EXEC sp_dboption '[StuckDB] ' ,'single user' ,'FALSE'; ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer… (error code 3417)

When you get the above error please verify the event viewer once and below would be possible errors from event viewer.


Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
                                                                          (or)
Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 942, state 4, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.


Possibilities :


1. Check if any system patches or SQL Patches applied. For me SQL 2012 SP2 is applied the day when I have faced this issue.




Resolution :




NET START MSSQL$instancename /T902

Cluster resource 'SQL Server' in clustered service or application 'SQL Group' failed.

We got this error while doing fail over or fail back in cluster environment. Unfortunately we didn't find any accurate information in cluster event log or in window event log.

I have used the below approach to find out the root cause of failure.

Start SQL Service via Net Start NOT via Failover cluster Manager.

Ex : net start MSSQL$SQL2014

How to fix log cannot be rebuilt the database because the database was not cleanly shut down

Requirement : I got few mdf and ldf files from client and requested to restore the databases on production environment. I got the below error for one of the database during mdf and ldf attachment.

"File activation failure. The physical file name "G:\MS SQL Server 2005 Data Files\MSSQL.1\MSSQL\DATA\TEST.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down."

Resolution :

I have used the below command to fix the issue.
CREATE DATABASE TEST ON (FILENAME = 'R:\SQL Data\TEST.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

Note : Make sure service account or your user has the proper access to place the files in respective drive.

How to recover access to a SQL Server instance without downtime ?

Issue : 

Starting with SQL Server 2008, the local Administrators group is no longer added by default during SQL Server setup or forgotten the sa password then you can be completely locked out of your own SQL Server instance.

Situation :

 I've seen cases where an employee has moved on, but their Windows account, being the only one with Administrator privileges for SQL Server, had been completely obliterated from the system. Of course that person was the only one who knew the sa password as well, and being a local admin or even a domain admin might not help you.

Solution : 

you can use PSExec to gain access without restarting the SQL server instance.

Step 1 :  Check whether execution policy is enabled in power shell or not ? If execution policy is not enabled then enable it as shown in the below screen.

Step 2: Now new window will open then click on Agree

Step 3: Now SSMS window will open .

Step 4 : Connect to SQL Server instance and then add admin group or you domain account. 

Step 5 : Now you should be the administrator for that instance . Close SSMS and reopen and you can continue your DBA activities.

SSIS - Connecting to the Integration Services service on the computer "" failed with the following error: "Access is denied."

Problem :

Connecting to the Integration Services service on the computer "Server Name" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

Resolution :

1. WIN + R --> Type  "dcomcnfg".

2.On the left-hand tree, navigate to Computers | My Computer | DCOM Config.

3.Find "Microsoft SQL Server Integration Services 12.0". This is for 2014; I believe earlier versions leave out "Microsoft," so check under "S" if you don't see it under "M".

4.Open Properties | Security and for each type of permission hit Edit and add an appropriate AD group or user. Be sure to check the specific permissions required, such as Remote Launch when editing Launch and Activation Permission. If anyone knows exactly which permissions are necessary for running SSIS, please share.

5.Launch Services, and restart the SQL Server Integration Services service.

You should now be able to connect to Integration Services from within SQL Studio or dtexec.exe

SQL DBA : How to fix "Primary Key Violation " error in SQL Server transaction replication .

Error messages:

Violation of PRIMARY KEY constraint 'PK_tablename'. Cannot insert duplicate key in object 'dbo.tablename'. (Source: MSSQLServer, Error number: 2627)

Impact: 

This type of error generally appears in Transaction replication type.

Replication sync gets fail if this error reported. data\transaction sends from publisher to distributor. But from distributor to subscriber it fails and report the error in transactional replication monitor.

  

Where to find:

> Go to replication monitor > All subscriptions column  > Status column > double click > check the information from publisher to distributor and dist> sub

>  You can see error of primary key violation in dist to sub history.

From the error note down Transaction sequence no & Command ID

Solution:

1. First find what transaction is inserted into subscriber which is causing this error by using below commands and execute only on distribution database.

EXEC Distribution. Sp_browsereplcmds

 @xact_seqno_start = ‘0x00000018000000A1000300000000’,

@xact_seqno_end = '0x00000018000000A1000300000000',

@command_id =1, @publisher_database_id = 1

2. COMMAND with transaction: {CALL [sp_MSins_dboetab] (N'3    ', N’c      ', N’ap        ')}

3. Delete manually from subscriber and monitor for some time.

4. Now replication should work with excellent performance with no latency value.

SQL DBA : SQLServer.exe memory consumption is 100% then how do you find it and fix ?

If Memory Utilization is 100% then identify

Memory\Available Bytes :If this counter is greater than 10% of the actual RAM in your machine then you probably have more than enough RAM and don't need to worry.

Disk Related:

Physical Disk (instance)\Disk Transfers/sec counter for each physical disk and if it goes above 25 disk I/Os per second then you've got poor response time for your disk.

Avg Disk Sec/Read- Look for <8Msec or less as optimal.

Avg Disk Sec/Write - Look for <8Msec(Non Cached) <1Msec(Cached)

Average Disk Queue Length can vary based on the activities (Typically 30 is a red flag)

Note: PerfMon is far less useful against a SAN. Check the vendor for monitoring disk performance tools specific to SAN.


PLE: Page Life Expectancy

The time in seconds the page stays in memory pool without being referenced before it is flush. should be >= 300. A lower or declining value may indicate memory pressure.

SQL Compiles/sec & Recompiles/sec

<2/sec negligible, 2-20/sec could be investigated, 20-100 poor, >100 potentially serious, I would really like a means of assessing the cost of compiles, as simple statement compile cost is low, a complex query could take 1min to compile.

Page Splits/Sec

Occurs when a 8KB page fills and must be split into two new 8K pages.

Buffer Cache Hit Ratio

Indicates how often SQL Server can get data from the buffer rather than disk (since the last restart of instance). >90% for OLAP, >95% for OLTP system . If buffer cache hit ratio is less than the bench marks then you need to increase the amount of memory available to SQL Server so that buffer cache hit ratio will in crease.

Lazy Writes/Sec

The number of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space <20

Page Reads/Sec and Page Writes/Sec

Number of physical database page reads and writes issued respectively <90

Note: If any of the above mentioned counter values will fall down less than the bench marks then check with developer or application team about the recent code changes or heavy transactions in the project and then suggest to increase the available RAM to SQL Server.

SQL DBA : My CPU utilization is 100% then what you will do ?

If CPU Utilization is 100%, then identify

1) If SQL Server is consuming CPU or some other process.

2) If SQL Server is not consuming then assign the incident to Windows team / Wintel Team to investigate.

3) If SQL Server is consuming 100% CPU then

Monitor % Processor Time (_Total) to confirm the utilization of all processors is high or less.

  

Investigating High Processor Utilization:

High processor utilization is to break it down into Processor(_Total)\% Privileged Time and Processor(_Total)\% User Time

System\Processor Queue Length counter gives an indication of how many threads are waiting for execution.

If multiple instances are present in the server, check which instance is busy with Processor in Task Manager and identify instance using PID in Configuration Manager.

4) Verify the session count to SQL Server instance.

select count(*) from sys.dm_exec_sessions

Validate with the benchmark value and find the load on the system.

5) Verify if any blocking's are there in SQL Server instance.

6) If blocking's are not identified, then run Profiler (with approval) to understand the load on the system.

7) Identify long running queries and inform application team about the longest and oldest transactions which are taking maximum CPU utilization.

select * from sys.dm_exec_requests order by cpu desc

Note: In our environment , We faced this issue at the time of  Antivirus is scanning the .mdf and .ldf files so to fix this we have excluded the database files from antivirus scan.

SQL DBA : My SQL Server Agent is not started after MSDB rebuild so how you will fix this issue ?

Execute the below script to fix this issue permanently .

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Agent XPs', 0;

RECONFIGURE;

SQL DBA : My MSDB database is corrupted then how you will handle this scenario ?

MSDB Corrupt:

1) Verify the reason of failure in the error logs and troubleshoot accordingly. If database is really corrupt then look out for a available valid backup. If backup is available restore MSDB as a normal user database and it would be restored.

2) If backup is not available, then stop the instance and start the instance in /m and /t3608 startup parameters.

net stop "SQL Server (MSSQLSERVER)"

net start "SQL Server (instance name)" /t3608 /m

3) Connect to the Query window and detach MSDB database and delete the OS level files.

sp_detach_db 'MSDB'

NOTE:  Remove MSDB data/log files manually from the path.

4) Execute the script in %Root Directory%\Install\instMSDB.sql file.

SQL DBA : My Query or Application is running slow , How you will trouble shoot it.

Query / Application is running slow:

In General we will face this issues due to poor hardware and poorly / badly written SQL Code . In order to find the solution we need to follow the below mentioned methods to trouble shoot slow running query / application in our day to day activities.

1. Check any open transactions are there by using

DBCC OPENTRAN

2. Check whether any lock types are in the tables

 SP_LOCK or select * from sys.dm_tran_lock[DMV]

3. Check for any blockings are there?

If yes then according to your project process we find blocking's and speak to apps team to confirm which SPID required to kill with approvals via email.

KILL SPID

If no blocking's then?

3. Check for any deadlocks

NOTE: By default SQL Server db engine will not capture any deadlock information DBA team need to enable trace flags.

DBCC Traceon(1222 or 1204, -1)

4. Check for any query execution plan or cost based plan.

Execution plan display:

1.Physical operation (Scan type)

2.Logical Operation

3.Estimated I\O COST

4. Estimated CPU COST

5.Estimated number of executions

6.Estimated number of rows.

7.Cache Size 

6.Check whether any indexes are created on the table or not. If no then inform your dev\apps team to suggest create index which improves performance of query.

7.If Indexes presents then check for FRAGMENTATION LEVEL on the tables.

Select * from sys.dm_db_index_physical_stats (From SQL Server 2005)

or DBCC SHOWCONTIG (Upto 2000 version of SQL Server)

If We FRAGMENTATION level

1. <5 : No action and indexes are good.

2. If >5 and <30 : Index need to reorganize

Alter index <Index Name > on table name reorganize

3. If >30 :  Index need to Rebuild

Alter index <Index Name > on table name Rebuild

If no FRAGMENTATION

8. Check for any missing indexes by using

select * from sys.dm_db_missing_index_details

> Check for unused indexes

select * from sys.dm_db_index_usage_status

9. Check CPU and memory utilisation

10. Run profiler or perfmon tool to capture events or counters depending on type of parameters.

11. Check any disk or I\O or Network related issues.

12. Check for statistics update in case if the result is varying only for few where conditions hence Statistics might be outdated due to bulk loads so due to this optimizer is not able to make the best optimum execution plan .

13. If you are not able to justify then run the DTA (Database Tuning Advisory ) to get estimated performance improvement suggestions.

SQL DBA : Unable to Start SQL Server Agent (Agent XP is disabled )

I got this error after Windows server reboot .

Route Causes :

1.Check the SQL Server Agent running status from SQL Server configuration manager .
2. Password might be changed due to company security policies.

3. The SQL Server Agent service security principal (SID) is removed or missing from the SQL Server 2008 installation.
4. Check if MSDB rebuild recently.

Possible Solutions :

1. Changes the SQL Server Agent services statues to Automatic instead of Manual.

2. Provide the updated password to SQL Server Agent services account.

3.   You can follow the below mentioned procedure for SID fix.

  1.      Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2.      In the Connect to Server dialog box, verify the default settings, and then click Connect.

3.      In Object Explorer, right-click the server, and then click New Query to open a new Database Engine Query Editor window.

4.      In the Query Editor window, type the following Transact-SQL statement.

EXEC sp_addsrvrolemember '<Domain>\<Account>', 'sysadmin';

5.      Note <Domain> represents the domain name, and <Account> represents the domain user account that is assigned to the SQL Server 2008 Agent service.

6.      Press F5 to execute the query.

7.      Close the SQL Server Management Studio window.

8.      Try to restart the SQL Server Agent service.

4. If "MSDB" database rebuild recently then execute the below scripts

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Agent XPs', 0;

RECONFIGURE;