Monday, 8 February 2016

Using KEEP_CDC option to keep CDC metadata

Having a database with Change Data Capture (CDC) enabled, we will need to take this recommendation into consideration during the restore process. There is one option to be used as part of RESTORE syntax. This is KEEP_CDC option which allows us to restore the CDC metadata as well when the database is restored to another SQL Server instance (or restore it in the same instance with a different a name, indeed). So, here is the example:

While verifying that CDC metadata was restored as well, we will see that not only the database and tables keep CDC option enabled, but also all data inside captured by CDC is still there.
-- Checking if CDC is enabled for database TESTDB2.

select is_cdc_enabled,name from sys.databases where name='TESTDB2'

-- Checking if CDC is enabled for table MyTable.

select is_replicated, is_tracked_by_cdc, * from sys.tables

select * from [cdc].[change_tables]  

-- checking the data tracked for table MyTable.

select * from cdc.dbo_MyTable_CT 

This is not all. Now we must create the CDC jobs by executing:

EXEC sys.sp_cdc_add_job 'capture'

EXEC sys.sp_cdc_add_job 'cleanup' 

Finally, you also can verify the jobs were created for CDC

EXEC [sys].[sp_cdc_help_jobs] 

Having done that, the restore process of database (with CDC included) has been completed successfully. Thanks for reading!

Thursday, 28 January 2016

Did you get this "AuthorizationManager check failed" error working with SQL Jobs and PowerShell?

Taking of PowerShell, while working on implementing SQL Jobs which execute PowerShell scripts, unexpectedly, they may begin failing without any apparent reason. So, we get this error:

AuthorizationManager check failed At line:1 char:2  + & <<<<  ‘S:myfolderscript.ps1’      + CategoryInfo          : NotSpecified: (:) [], PSSecurityException      + FullyQualifiedErrorId : RuntimeException.  Process Exit Code 1.  The step failed.

What we should do is to check PowerShell so as to make sure the ExecutionPolicy is not set to “Restricted” by executing the following command:
If it is then set it to “RemoteSigned” or “Unrestricted” depending on your security policy.
Set-ExecutionPolicy RemoteSigned
Not only do we have to make sure that Windows Management Instrumentation service (WMI) service is enabled and running, but also we have to restart it. Only after successfully doing that will your job and script run again with no error. I do believe that this would work without a shadow of a doubt for the vast majority of cases. I hope you find this post useful. Thanks for reading.

Monday, 25 January 2016

How to fix: The In-row data RSVD page count for object ‘SchemaName.ObjectName’, index ID ‘x’, partition ID ‘xxxxxxxxxxxxxx’

Undoubtedly, we all know that SQL Server 2000 is now out of compliance with Microsoft. Surprisingly, not all companies have their databases migrated, so in that sense, many of them are planning to upgrade to one of the latest version of SQL Server (SQL Server 2014/2016). This post pretends to give a recommendation that you may take it as a good practice in order to avoid inconsistency errors of object metadata for the upgraded databases. The inconsistency errors what I am talking about can be found when you run DBCC CHECKDB command to check the logical and physical integrity:

And here the following inconsistency error is likely to appear:
The In-row data RSVD page count for object ‘SchemaName.ObjectName’, index ID 0, partition ID 75863107960832, alloc unit ID 75863107960832 (type In-row data) is incorrect.


CHECKDB found 0 allocation errors and 1 consistency errors in table ‘SchemaName.ObjectName’ (object ID 1157579162). CHECKDB found 0 allocation errors and 1 consistency errors in database ‘MyDatabase’.
The output message above tells us that there is one LOGICAL consistency error inside the database "MyDatabase" because there are pages and row count inaccuracies for the table which ID is 1157579162. Luckily, errors of this sort can be fixed by running DBCC UPDATEUSAGE command which reports and corrects the inaccuracies. Only after successfully running DBCC UPDATEUSAGE(0) command in the database context will you be able to see the following messages indicating we got all inaccuracies fixed.
DBCC UPDATEUSAGE: Usage counts updated for table 'SchemaName.ObjectName' (index 'ObjectName' , partition 1):

        RSVD pages (In-row Data): changed from (-275) to (145) pages.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Finally, we are now able to run again DBCC CHECKDB without any error. The recommendation is that so rapidly have you upgraded your databases (from SQL Server 2000 to superior version) that you must execute DBCC UPDATEUSAGE so as to avoid those errors (or more critical issues). Thanks for reading!

Saturday, 16 January 2016

Error: “The local node is not able to communicate with the WSFC cluster” – AlwaysOn Availabiliy Group

At times after having an AlwaysOn solution implemented, not only may the WSFC Cluster service run down, but also it may be corrupted for whatever reason. As a result of this, we will need to reinstall everything. The big question would be now how to uninstall and delete the Availability Group correctly if WFC service is not running, and to worsen the situation, we do not have any option than deleting forcibly the Availability Groups and, therefore, ending up making a dirty and incomplete uninstallation, indeed.
After deleting the Availability Group, we will need to remove the nodes from the WSFC Cluster and, surely, it can be done by uninstalling WSFC Failover Service (instead of destroying the Windows Cluster which cannot be possible in this case). Having done that, we have to create again the Windows Cluster putting each node inside. Finally, we are going to be able to set up the AlwaysOn Availability Group. Ideally, it is the normal procedure for it.

Nevertheless, we cannot play by the procedure as each case is particular (and this case really is). It might not be a great surprise if we get this error while trying to set up the AlwaysOn Availability Group:

Obviously, this error is due to incorrect uninstallation we had done above and, luckily, to fix it we will have to disable AlwaysOn Availibility Groups feature at database engine service level.

Now we have to enable it again and then restart the database engine service

Not until you have done these all steps will you be able to create again the Availability Groups you want to. Thanks for reading again!


Wednesday, 4 February 2015

SQL Server Index Report with Included Columns, Storage and more for all Tables in a Database

Today my new tip about “SQL Server Index Report with Included Columns, Storage and more for all Tables in a Database" has been published at, I cannot wait to invite you to read it here
Let me know if you have any question or comment. Thanks for reading!

Wednesday, 28 January 2015

Using the SQL Server Default Trace to Audit Events

My new tip about “Using the SQL Server Default Trace to Audit Events” has been published, you can read it at .
Let me know if you have any comment or question. Thanks for reading!

Thursday, 8 January 2015

Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL

Yesterday my latest tip about “Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL” has been published, you can read it at
Let me know if you have any comment or question. Thanks for reading!

Friday, 19 December 2014

Script out all SQL Server Indexes in a Database using T-SQL

Today my latest tip has been published about “Script out all SQL Server Indexes in a Database using T-SQL” and you can read it at
Please let me know if you have any comments or questions. Thanks for reading!