Category: Production Issues

The transaction log for database ‘XXX’ is full due to ‘LOG_BACKUP’

Yesterday, one of my client send me email stating she is not able to run any query against database.

she was getting below error message –

The transaction log for database ‘xxx’ is full due to ‘LOG_BACKUP’

From error message it’s look like transaction log full issue hence I looked for db property and found database is in Full recovery model and growth is restricted.

Hence I thought, let’s change restricted growth to unlimited but it’s not allowing me and was getting same error message.

I thought to perform Full backup and same error message.

I tried to shrink the log file but same error message.

Finally, I tried to perform t-log backup and it went successfully and there after I am able to shrink the file and change db growth to unlimited

Still, I did not get chance to look reason behind this wired error message but if any of you know please feel free to share.

 

 

 

Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.

Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –

 

1

Solutions – On error message it’s look like some other processes was trying to use SQL default port 1433.

Hence it was not allowing to restart SQL Services with Port 1433.

On my server, we have multiple SQL Services installed ( SQL 2008 to SQL vNext ) hence I started checking if any of these SQL Server have assigned static port 1433.

I start checking assigned port for all SQL Server and I found SQL Server 2014 and SQL Server 2016 was having assigned static port 1433.

In order to resolve the issue, I removed static port entry and restart my SQL Services and it started successfully.

2

34

The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context

My Development team contacted me and said they are not able to access database  “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context”

when I looked into server server I found –

  1. Login was there and access was granted.
  2. When I looked into database I found user was disable ( Red down arrow mark )

2

Here are two solutions –

  1. Most of the people recommended to recreate use by deleting and reading into database.

But main reason why it’s disabled because user is not having connect permission to database.

  1. if you grant connect permission it will resolve your issue.

use [YourDBName]

go

grant connect to [xyz\user]

1

Replication – Snapshot is not getting generated

Replication issues and resolutions

Today I have encountered very strange issue –

Scenario – Snapshot is not getting generated for newly added articles

or

Error when setting up Transactional Replication via SQL scripts.

Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Step performed to resolved the issue-

  1. Re snap with reinitialize all subscription option no luck.
  2. Removed the newly added articles and add back and ran the snapshot but NO Luck.
  3. Drop the subscriber and add back and generate the snapshot but no luck ( Most of time it resolve the issue )
  4. Script out publication and drop the publication and when I try to add

While adding Publication i am getting error message –

Error Message –

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Resolution —

There are 2 unofficial workarounds:

(a) You can circumvent the check by specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article

(b) You could change the immediate_sync property in syspublications to 0 (see sp_changepublication).

Other more official workarounds —

Including changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only) and ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

Before —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’concurrent’,

@retention = 0,

@allow_push = N’true’,

 

After —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’native’,

@retention = 0,

@allow_push = N’true’,

 

  1. I have change the @sync_method = N’concurrent’ to @sync_method = N’native’ and ran the script to create publication.
  1. Ran the snapshot but snapshot not getting generated for newly added articles

7. Remove all article (from console) and added again … and ran the snapshot resolve my issue