Tuesday, November 25, 2014

SQL SERVER – Error: SSMS Database Compatibility Level Drop Down is Empty

I recently installed SQL server 2014 on my machine; I was using the SSMS for SQL 2012 as it as all the pluggins installed for my daily use.  Now for testing an application, i have restored a database from sql server 2008,  I changed the compatibility programmatically

image

When i queried the sysdatatabases, it showed as cmplevel 120

Unfortunately when i connected to this server from my existing SQL Server management studio, in the options sections, I saw the compatibility level drop down empty. there is nothing to choose as shown below; I queried the sysdatabases and i see it is 120. After spending some time I realized that I was using the older version of SSMS.

image

Wednesday, November 19, 2014

Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command SetCDCTracked(Value = 1)

I got this  error message when I was trying to enable CDC on a SQL Server  database for

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database XXXXX is enabled for Change Data Capture.
The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404:
'Could not obtain information about Windows NT group/user 'XXXX\xxxxxxx', error code 0x5.'.
Use the action and error to determine the cause of the failure and resubmit the request.

 

The fix for this is to change the database owner to "sa" by executing the below script.

USE <Database>

GO

EXEC sp_changedbowner 'sa'

Read the below KB article for more details.

http://support.microsoft.com/kb/913423

Tuesday, November 18, 2014

How to list role members in SQL Server

 

Below is the query to list the users in a specific role.  if you are looking for the users who can manage the sql server agent jobs, use the filter ( Where rp.name = ‘SQLAgentRole’)  and run this under ‘msdb’ database. otherwise remove the filter

 

;WITH RoleMembers (member_principal_id, role_principal_id)
AS
(
SELECT
rm1.member_principal_id,
rm1.role_principal_id
FROM sys.database_role_members rm1 (NOLOCK)
UNION ALL
SELECT
d.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm (NOLOCK)
INNER JOIN RoleMembers AS d
ON rm.member_principal_id = d.role_principal_id
)
select distinct rp.name as database_role, mp.name as database_user
from RoleMembers drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
WHERE rp.name = 'SQLAgentUserRole'

Wednesday, November 5, 2014

Restrict user from connecting to the database from SSMS

The following logon trigger will restrict a user from connecting thru SQL Server management studio. As always, test this first before implementing.

This will deny all the connections made by the user ‘test’  from the application ‘ssms’

 

CREATE TRIGGER ltr_restrictUserFromSSMS
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF
(PROGRAM_NAME()
LIKE 'Microsoft SQL Server Management Studio%' AND ORIGINAL_LOGIN() ='test' )
BEGIN
ROLLBACK;
END
END


DROP TRIGGER ltr_restrictUserFromSSMS ON ALL SERVER


 



 



 



Sometimes the connections from SSMS will listed as ‘Microsoft SQL Server Management Studio –query’; so if you want to restrict all the connections made from ssms and the queries run from them, it is good to use   PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio%' 



 



 



image

DBCC PAGE to Examine SQL Server Table and Index Data

I was writing another article on the smallest dataset possible in sql server, and i came across a situation where I need to use DBCC PAGE () statement for a table. DBCC PAGE () statement, uses database name, file number, page number, and print Option as arguments. In my case, i needed to find the page number of the table in order for me to use this  statement. Then I came across the undocumented DBCC IND() statement which lists the ‘data’ and ‘index’ pages of the table.

For the test purpose, I created a database , and a table , when I ran DBCC IND() statement against it, it returned no rows.

image

This was the expected behavior, since the table was empty, sql server engine hasn’t allocated any pages for it.

In the next step, i insert a row into the table to see how many pages it has allocated.

 

image

 

What does all this data mean? For purposes of this tip, we'll focus on a couple of key columns. The columns PageFID and PagePID represent a file number where pages reside and a page number within the file where data lives, respectively. IndexID is the index' index_id as found in sys.indexes. PageType dictates the kind of page. Type = 1 is a data page, Type = 2 is an index page, and Type = 10 is the IAM page that maintains the index itself. IndexLevel is the level within the IAM structure the page falls. If level = 0, then this is a leaf level page for the index. For more detailed information on all the columns (this is an undocumented command after all), this MSDN blog by former Microsoft storage engine expert Paul Randal breaks them all down.

I hope this help you in some manner.  please let me know your thoughts and comments.

 

Aneesh

Monday, November 3, 2014

When statistics was updated ?

Statistics are very important for sql server engine,  it is  used by the SQL Server optimizer to choose the most efficient plan.  When we don’t have up to date statistics
it may end with SQL server optimizer choosing inefficient query plan. You can use below query to identify when SQL Server statistics were last updated

 

SELECT DB_ID() AS dbid ,
CASE WHEN indid IN ( 0, 1 ) THEN CONVERT (CHAR(12), rows)
ELSE ( SELECT rows
FROM dbo.sysindexes i2
WHERE i2.id=i.id
AND i2.indid IN ( 0, 1 )
)
-- ''-''
END AS rowcnt ,
CASE WHEN indid IN ( 0, 1 ) THEN rowmodctr
ELSE CONVERT (BIGINT, rowmodctr)
+( SELECT rowmodctr
FROM dbo.sysindexes i2
WHERE i2.id=i.id
AND i2.indid IN ( 0, 1 )
)
END AS row_mods ,
CASE rows
WHEN 0 THEN 0
ELSE CONVERT (BIGINT, CASE WHEN indid IN ( 0, 1 )
THEN CONVERT (BIGINT, rowmodctr)
ELSE rowmodctr
+( SELECT CONVERT (BIGINT, rowmodctr)
FROM dbo.sysindexes i2
WHERE i2.id=i.id
AND i2.indid IN ( 0, 1 )
)
END
/CONVERT (NUMERIC(20, 2), ( SELECT CASE CONVERT (BIGINT, rows)
WHEN 0 THEN 0.01
ELSE rows
END
FROM dbo.sysindexes i2
WHERE i2.id=i.id
AND i2.indid IN ( 0, 1 )
))
*100)
END AS pct_mod ,
CONVERT (NVARCHAR, u.name+'.'+o.name) AS objname ,
CASE WHEN i.status&0x800040=0x800040 THEN 'AUTOSTATS'
WHEN i.status&0x40=0x40
AND i.status&0x800000=0 THEN 'STATS'
ELSE 'INDEX'
END AS type ,
CONVERT (NVARCHAR, i.name) AS idxname ,
i.indid ,
STATS_DATE(o.id, i.indid) AS stats_updated ,
CASE i.status&0x1000000
WHEN 0 THEN 'no'
ELSE '*YES*'
END AS norecompute ,
o.id
AS objid ,
rowcnt ,
i.status
FROM dbo.sysobjects o ,
dbo.sysindexes i ,
dbo.sysusers u
WHERE o.id=i.id
AND o.uid=u.uid
AND i.indid BETWEEN 1 AND 254
AND o.type='U'
ORDER BY pct_mod DESC ,
CONVERT (NVARCHAR, u.name+'.'+o.name) ,
indid
GO