[Attunity CDC for Oracle]my own odd Troubleshooting

Have you ever encountered “Aborted” status on “Change Data Capture Designer for Oracle by Attunity”?
or on “Oracle CDC for SSIS”? Then you dump CDC trace even used “SOURCE” but can’t find useful information.
For me, yes. I’ve encountered the same situation and I’ll describe a little about my story and how to troubleshooting.
My Environment:

• Windows 2012 R2
• SQL 2012 SP2 + CU6

[Replication]Fixed Specify the actual server name (Replication.Utilities)

I believe lots DBAs have encountered the error when you add a remote Publication or Subscription.
We do know the “SERVER NAME” should match the value of “@@SERVERNAME” or “Select * from sys.sysservers;
select * from sys.servers “
And if you find it doesn’t “MATCH”, then you have to do the step below:

SP_dropserver xxxx
SP_addserver xxx, 'local'

For my situation, we used the name instance I have not the issues above, even though I did it again. It still doesn’t FIX it.
But I’ve found 2 interesting things then I changed it, then it fixed. These 2 things you must ensure it’s available if you want to deploy SQL REPLICATION

• Open firewall UDP port 1434 for SQL Server Browser uses. I just opened TCP port 2382
• The SQL instance name CANNOT be hidden from network.

[CDC] Error 22859, 622, 2601

When you enable SQL CDC (Change Data Capture) feature on DB and table. There are two job created in SQL Agent

• cdc.dbnamexxx_capture
• cdc.dbnamexxx_cleanup

The best practice is to create another SQL “FILE GROUP” and “FILE” then assign CDC tables to the new “FILE GROUP”. I’ve done it. But the job failed and showed the message below

  1: Msg 22859, Level 16, State 2, Log Scan process failed in processing log records. 
  2: Refer to previous errors in the current session to identify the cause and correct 
  3: any associated problems. For more information, query the sys.dm_cdc_errors dynamic management view.

and

  1: The Log-Scan Process failed to construct a replicated command from 
  2: log sequence number (LSN) {000015a9:0000395a:001b}. 
  3: Back up the publication database and contact Customer Support Services. 
  4: [SQLSTATE 42000] (Error 18805)  Log Scan process failed in processing log records. 
  5: Refer to previous errors in the current session to identify the cause and correct 
  6: any associated problems. [SQLSTATE 42000] (Error 22859)  
  7: The statement has been terminated. [SQLSTATE 42000] (Error 3621)  
  8: The statement has been terminated. [SQLSTATE 01000] (Error 3621)  
  9: The call to sp_MScdc_capture_job by the Capture Job for database 'xxxxxx' failed. 
 10: Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864)

There are two system DMV you can query to see the errors rather than see the job logs.

• sys.dm_cdc_log_scan_sessions
• sys.dm_cdc_errors

The easiest way to get SQL version

Most of people suggest to use “@@VERSION” and “SERVERPROPERTY”

But when we manage SQL server, DBA or developer need to do a lots tedious/repeatable tasks to determine the SQL version in order to meet different needs. So the method above is not what I need.

The most convenient way and the easiest way to remember the syntax is

SELECT @@VERSION<br /><br />

It shows not only the detail SQL version but also the Windows version

But you have to do lots “STRING” function if you want to manipulate the info for program purpose

[CRM 2013] Error: 18456, Severity: 14, State: 11.

Message
Login failed for user ‘Domain\MachineName$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: MachineName]

It’s related to SSRS Reporting Server and run as Network Service account. It’s supposed to be good. Because when you added a computer to domain, it will register Local system and Network Service account permission to the SPN records.
That’s why sometime you don’t know WHY you put Network Service account as a service account, then everything will be fixed. (Reference 2)
I’ve been encountered the error for al long time, and I’ve tried lots methods from Google.com

Could not find database ID 0, name 'xxxx'. The database may be offline

https://connect.microsoft.com/SQLServer/feedback/details/529716/problem-with-cannot-find-database-id-0-the-database-may-be-offline

"Now, what I found out: This server has an AUDIT on a database. If the audit is enabled, I get this error. If I disable the audit, the query runs fine."

Could not allocate space for object 'dbo.SORT temporary run storage

Situation:

Could not allocate space for object 'dbo.SORT temporary run storage:  141564582166528' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

Solution:
CHECKDB (Part 6): Consistency checking options for a VLDB

Log Shipping - [SQLSTATE 42000] (Error 14421)

Once you set up SQL Log Shipping, it will produce 4 Major jobs:
I assume the Primary server is R1, the Secondary is R2

• Alerts (Both on Primary R1 and Secondary R2)
• Backup (On Primary)
• Copy (On Secondary)
• Restore (On Secondary)

I’ve set up already and Backup/Copy/Restore are all worked fine and correctly. But Alerts on Primary R1 has been sent all the time below

Get SQL Service Accounts

Recently our Domain team created an exclusive OU for DBA. I’d like to control all the SQL service accounts. But SQL server not only has SQL Engine and SQL Agent accounts but also SSIS/SSAS/SSRS/Full Text, etc.....
How do I know all the accounts in SQL Server? If you have SQL 2008+, you can use a new DMV “sys.dm_server_services”.
If you have SQL 2005, you have to use undocumented “xp_regread” function to read registry records that in Windows Server.
It’s also fit to SQL 2008+…

Automated Database Restore Script Out

Limitation:

In order to be compatible with SQL 2005 ( no default value for T-SQL variable), I changed the code script method.

A lot of posts that used “RESTORE FILELISTONLY” and “BackupSet” table to grab backup files.

Yes, I agree it would be more accurate for latest backup and correct LSN/DB logical name. But if someone drop/detach DB and left backup histories and backup files. It would be incorrect. Additional, sometimes when you use “RESTORE FILELISTONLY” to grab backup files, it will show “Access Denied” message.

In order to avoid the problem, I use “sys.Master_files” table to match “Backupset” and “BackupsetMediafamily”.