RSS

Category Archives: Administration

sys.foreign_keys does not have matching row in sys.indexes

Running DBCC CHECKDB you are getting following error message:

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes

This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179).  This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK.  If attempted should get following error message:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.

So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!

Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options.  Also supported by the KB2787112.

So question is how do you fix it?

First, identify the child table name from sys.foreign_keys:

SELECT object_name(parent_object_id) AS TableName
  FROM sys.foreign_keys
WHERE name = ‘FK_b_a’

Second, script our constraint definition:

  1. Find the table, we got in SQL Statement above.
  2. Go to Keys.
  3. Right click on FK constraint name.
  4. Script Key As.
  5. Create To.
  6. New Query Window.

Third, drop the FK constraint:

ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]

Fourth, Re-create the constraint, with script generated in Step 2.

If it was issue of someone playing around in system tables, this should resolve it.  However, if you get error similar to below:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.

This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created.  Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed.  If that is an issue, you will have to rely on your backups for recovery.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on November 20, 2015 in Administration

 

Tags: , , , , ,

Change DB Owner on all Databases one time

this one of wonderful scripts and very important 

—Change DB Owner on All Databases
EXEC sp_MSforeachdb ‘IF ”?” NOT IN (”Master”,”tempDB”,”model”,”msdb”)
BEGIN
EXEC [?]..sp_changedbowner ”User Name”
END

To know More about new feature in SQL Server 2016 you can check it from HERE

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS ,Facebook WHO WE ARE

 
Leave a comment

Posted by on November 16, 2015 in Administration

 

Tags: , ,

Check and Enable Automatic Statistic Update on Database Level

AS SQL Server Best Practices: Auto-Create and Auto-Update Statistics Should Be On – Most of the Time in general they are a very good thing for performance. You could try to figure out which columns need statistics, but it’s often better to let SQL Server do that for you.You can turn on the AUTO_CREATE_STATISTICS database option and SQL Server will automatically determine when it needs the Statistics and create them for you.

Check And Enable Automatic Statistic Update on all databases have this option is disable

Select ‘ALTER DATABASE [‘+Name+’] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [‘+Name+’] SET AUTO_UPDATE_STATISTICS ON;
GO
‘ from Sys.Databases where is_auto_update_stats_on = 0 or is_auto_create_stats_on = 0

Take the Result and Execute it in another session

Update Statistics

To check all my posts you godirect to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Slideshare ,Youtube Channel.My posts in MSDN

 

Tags: , , ,

How to build , Manage and monitor your SQL Server replication

Hello my friends this my small post in SQL Server replication it is not everything about replication but at least it is the what any DBA should know it 11 Posts in SQL Server replication technology english and arabic posts

  • What is SQL Replication?
  • Types of SQL Replication
  • Replication Overview
  •  How to create Transaction Replication
  • How to Add New Publisher Server to the Distributor Server
  •  Disable Publishing and Distribution Servers
  • Publication Status
  • Subscription Status
  • Replication information for the Complete setup
  • Find publication article
  • How we can Check Distributor agent status
  • How we can Check log reader agent status

downloadreplica

All this previous point you can know it easily from the below Posts :

  1. Replication in SQL Server Part 1
  2. Replication in SQL Server Part 2
  3. Replication in SQL Server Part 3
  4. Replication in SQL Server Part 4
  5. SQL Server DMV Replication Monitoring Part 1
  6. SQL Server DMV Replication Monitoring Part 2
  7. SQL Server DMV Replication Monitoring Part 3
  8. SQL Server DMV Replication Monitoring Part 4
  9. الأكواد الديناميكية لمراقبة ومتابعة حلول تزامن البيانات 1/ 3
  10. الأكواد الديناميكية لمراقبة ومتابعة حلول تزامن البيانات 2/ 3
  11. الأكواد الديناميكية لمراقبة ومتابعة حلول تزامن البيانات 3/ 3

Follow me because i will do one online session in replication and i will enhance all this posts by adding lot of other information in replication .

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.

 
Leave a comment

Posted by on April 14, 2015 in Administration

 

Tags: , , , , ,

Where is index location on Database?

Where is index location on Database?

Where is index location on Database? 

Hi guys today I will show smoothing is easy but more important to know about it {Where is the index live} by the below DMV we can return all the index with table name and with File group hosted on it , another thing you can use this DMV to know the heap tables , Clustered index , non-Clustered index

Index Location:

WITH C AS
(

SELECT ps.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
JOIN sys.destination_data_spaces dds ON dds.data_space_id = f.data_space_id
JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id

UNION

SELECT f.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
)
–SELECT * FROM c
SELECT [ObjectName] = OBJECT_NAME(i.[object_id])
, [IndexID] = i.[index_id]
, [IndexName] = i.[name]
, [IndexType] = i.[type_desc]
, [Partitioned] = CASE WHEN ps.data_space_id IS NULL THEN ‘No’
ELSE ‘Yes’
END
, [StorageName] = ISNULL(ps.name, f.name)
, [FileGroupPaths] = CAST(( SELECT name AS “FileGroup”
, physical_name AS “DatabaseFile”
FROM C

WHERE i.data_space_id = c.data_space_id
FOR
XML PATH(”)
) AS XML)
FROM [sys].[indexes] i
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(i.[object_id], ‘IsUserTable’) = 1
ORDER BY [ObjectName], [IndexName]

index

Follow me because next post I will explain everything about the index and How you can build you index model How you can Enhance your expensive query by index more Secrets in the index …ETC

 

 
Leave a comment

Posted by on January 22, 2015 in Administration

 

Tags: , , ,

How to Grant Show Plan Privilege

How to Grant Show Plan Privilege

Showplan Privilege it’s granted for any one need to see the execution plane for SQL Server query to check the performance of the index or doing index analysis.

Grant Showplan for one user in one database  :

GRANT Showplan TO [DominName\username]

Grant Showplan for one user in All databases in one SQL instance:

 EXEC sp_MSforeachdb N’IF EXISTS
(
SELECT 1 FROM sys.databases WHERE name = ”?”
AND Is_read_only <> 1
)
BEGIN
print ”Use [?]; GRANT Showplan TO [DominName\username]”
END’;

after the execution take the Print scripts and run it in another session.

 
1 Comment

Posted by on November 16, 2014 in Administration

 

Tags: , ,

How to Check the last restart for DB Server

How to Check the last restart for DB Server

Hi Dears i received request from the developers by the Staging DB was down yesterday evening, between 6:00 PM to 9:00 PM, can you please advice at this time to do Scientific investigation and troubleshooting so at this time you should check 2 point :

  1. Last SQL Server instance restart.
  2. Last DB server (windows services ) restart.

Last SQL Server Restart :

we can check it easily and by the Easiest ways (SQL Query)

SELECT sqlserver_start_time FROM sys.dm_os_sys_info
SELECT ‘Statistics since: ‘ + CAST(sqlserver_start_time AS VARCHAR) FROM sys.dm_os_sys_info
Server Restart

Read the rest of this entry »

 
Leave a comment

Posted by on November 15, 2014 in Administration

 

Tags: , , , , , , ,