Wednesday, February 22, 2012

Identify database features restricted to a specific edition of SQL Server 2008

How can we identify whether a database uses any of the features that are restricted to a specific edition of SQL Server 2008.  In this tip we will go through the steps a DBA needs to follow to identify a database which uses edition specific features.



When you implement any of the features such as Data Compression , Partitioning , Transparent Data Encryption or Change Data Capture of SQL Server 2008 Enterprise Edition on a database, these features will internally change the way the database engine stores information within the database files. If a database uses any of the above features which are specifically designed to be used in Enterprise Edition of SQL Server 2008, it cannot be moved to other editions of SQL Server 2008.

DBAs can identify all edition specific features that are enabled within a user database by using the sys.dm_db_persisted_sku_features dynamic management view. 

As an example, lets try restoring the AdventureWorks database to a SQL Server 2008 Standard Edition instance.  Unfortunately, there is a catch, one of the tables Person.Address of is using Page Level compression in SQL Server 2008 Enterprise Edition.  When you try to restore the database to a SQL Server 2008 Standard Edition instance which does not support the Data Compression feature, the restoration of the database will fail with the following error message:

RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 4
Database 'AdventureWorks' cannot be started in this edition of SQL Server because part or all of object 'Address' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

Msg 933, Level 21, State 1, Line 4
Database 'AdventureWorks' cannot be started because some of the database functionality is not available in the current edition of SQL Server.


If a DBA is asked to move a database from Enterprise Edition to any other edition of SQL Server 2008 it is a good idea to execute the T-SQL code below to identify whether the current database uses any of the features that are restricted to a specific edition of SQL Server. The sys.dm_db_persisted_sku_features dynamic management view will let you know which edition specific feature is being used by the database. One needs to have VIEW DATABASE STATE permission on the database to view the results. 
USE AdventureWorks
GO
SELECT *
FROM sys.dm_db_persisted_sku_features
GO

As you can see in the code above snippet, the dynamic management view lets you know that the AdventureWorks database uses Compression feature of SQL Server 2008 Enterprise Edition.  The sys.dm_db_persisted_sku_features dynamic management view will not return any rows if no features that are restricted by edition are used by the database. In case, you are using Data Compression , Partitioning , Transparent Data Encryption or Change Data Capture of SQL Server 2008 Enterprise Edition, you need to remove the features before you perform the full backup and restore the database. 


Wednesday, February 8, 2012

Delete Orphan users from databases on a server


Have you heard about orphan SQL Server users? If not, an orphan user is a user in a SQL Server database that is not associated with a SQL Server login. Orphan users are created when a database backup from one server is restored on another server. There are a number of articles that have been written, and lots of topics on discussion boards about reconnecting orphan users with logins, but few, if any, regarding removing orphan users. Therefore, this article deals with how to identify which databases have orphan users, and how to remove the identified orphan users.

Just because you are moving a database from one server to another does not mean you also want to move all of the users associated with the database. In some cases, you may not want to move any, and in other cases, you might want to move only a few. For this discussion, let's say you have already identified and reconnected all of the orphan users you plan to keep. For all of the other orphan users you did not reconnect, I will show you how to identify and remove them.

Identifying Orphan Users
SQL Server provides a SP to help you identify orphan users that were originally associated with SQL Server Authenticated logins. The SP is called sp_change_users_login. However, SQL Server does not provide a mechanism to identify orphan users that where originally associated with Windows authenticated users or groups. The key to removing users is being able to identify them. The following code can be run against any database to identify all of the orphan users regardless of whether they are associated with a SQL Server or Windows authenticated user and/or a Windows group.

select u.name
  from master..syslogins l
   right join sysusers u on l.sid = u.sid
where l.sid is null
    and issqlrole <> 1
    and isapprole <> 1
    and u.name not in ('INFORMATION_SCHEMA''guest', 'system_function_schema', 'sys', 'dbo''SA')

Removing Orphan Users

Once you have identified orphan users it is extremely simple to remove them. You remove them by using my script. Here is an example that removes the database users 'Orphan', from the current database in use.

exec sp_revokedbaccess 'Orphan'. It seems fairly simple to do this for a few users and databases. However, if you have a large number of orphan users and databases, I'm sure you would not like to do this by hand. At least, I didn't want to do this manually. Because I like to automate repetitive manual tasks, I developed a script to accomplish identifying and removing orphan users.

This SP first determines which databases have orphan users. For each database that has orphans, it removes them one at a time. If an orphan user is the database owner then the "sp_changedbowner" SP is used to change the owner to "SA" before the orphan user is removed. The SP does not really remove the users, or change the database owners, but instead it just generates the code to remove the users. This allows you to review the code and determine if you want to remove all users, or only a select set of orphan users.

Conclusion

You can leave the orphan database users in a database if you want. Although they are excess baggage, that comes along with a database restore. In addition, they provide a small security risk if some newly defined login is unintentionally associated with an orphan user, allowing the new login to gain unauthorized database access. It is best to remove un-necessary orphan users to provide a clean, uncluttered database environment. This script provides an easy method to identify and remove unneeded orphan users. Therefore, this script can be a valuable tool, to be used as part of your database restore process, should you desire to remove orphan users. Download my script here.

set nocount on

-- Section 1: Create temporary table to hold databases to process 

-- drop table if it already exists
if (select object_id('tempdb..##dbnames')) is not null
  drop table ##dbnames

-- Create table to hold databases to process
create table ##dbnames (dbname varchar(128))

-- Section 2: Determine what databases have orphan users
exec master.dbo.sp_MSforeachdb 'insert into ##dbnames select ''?'' from master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1 
and u.name not in (''INFORMATION_SCHEMA'', ''guest'', ''system_function_schema'', ''sys'', ''dbo'', ''SA'') 
having count(1) > 0'

-- Section 3: Create local variables needed
declare @CNT int
          , @name char(128)
          , @sid  varbinary(85)
          , @cmd nchar(4000)
          , @c int
          , @hexnum char(100)
          , @db varchar(100) 

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from ##DBNAMES
While @CNT > 0
begin

-- get the name of the top database
  select top 1 @db=dbname from ##DBNAMES

-- delete top database 
  delete from ##DBNAMES where dbname = @db

-- Build and execute command to determine if DBO is not mapped to login
  set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' + 
             rtrim(@db) + '..sysusers u on l.sid = u.sid' + 
             ' where l.sid is null and u.name = ''DBO'''
  exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
  if @cnt = 1
  begin
    print 'exec ' + @db + '..sp_changedbowner ''SA'''  
    -- exec sp_changedbowner 'SA'
  end -- if @cnt = 1


-- drop table if it already exists
if (select object_id('tempdb..##orphans')) is not null
  drop table ##orphans

-- Create table to hold orphan users
create table ##orphans (orphan varchar(128))

-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
   set @cmd = 'insert into ##orphans select u.name from master..syslogins l right join ' + 
              rtrim(@db) + '..sysusers u on l.sid = u.sid ' + 
              'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +  
              'and (u.name not in (''INFORMATION_SCHEMA'', ''guest'', ''system_function_schema'', ''sys'',  ''dbo'', ''SA'')'
   exec (@cmd)


-- Are there orphans
  select @cnt = count(1) from ##orphans
  
  WHILE @cnt > 0 
  BEGIN
  
-- get top orphan
  select top 1 @name= orphan from ##orphans

-- delete top orphan
  delete from ##orphans where orphan = @name

-- Build command to drop user from database.
    set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
    print @cmd
    --exec (@cmd)

   
-- are there orphans left
    select @cnt = count(1) from ##orphans  
  end --  WHILE @cnt > 0


-- are the still databases to process
select @cnt=count(*) from ##dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table ##dbnames, ##orphans

Wednesday, February 1, 2012

Do you use backup log with truncate only in your instance

In some cases developers are using the statement BACKUP LOG <databasename> WITH TRUNCATE_ONLY in their scripts. And this script is going to be scheduled in our production environment. If the database has not been set on simple and we make backups from the log in between for our DR. Then those backup logs are useless.

How can we determine if the log isn't being backup in a proper manner.

Here is the script I am using:


DECLARE @currentfilename nvarchar(max)
DECLARE @logvalue        nvarchar(1000)
DECLARE @oldfilename     nvarchar(max)
DECLARE @oldlogId        smallint
DECLARE @olderfilename     nvarchar(max)
DECLARE @olderlogId        smallint
DECLARE @endpos          smallint
DECLARE @startpos        smallint
DECLARE @diffpos         smallint

SELECT @currentfilename=CAST(value AS nvarchar(max))
  FROM fn_trace_getinfo(default)
 WHERE property = 2 -- Get the current trace file name

SET @endpos   = CHARINDEX('.trc',@currentfilename)
SET @startpos = CHARINDEX('log_',@currentfilename)
SET @diffpos  = @endpos-@startpos


SET @logvalue=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),1,10)
SET @oldlogId=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),5,4) - 1
SET @olderlogID=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),5,4) - 2

SET @oldfilename = 'log_'
                 + CAST(@oldlogId AS varchar(1000))
SET @olderfilename = 'log_'
                 + CAST(@olderlogId AS varchar(1000))

SET @oldfilename = REPLACE(@currentfilename,@logvalue,@oldfilename)
SET @olderfilename = REPLACE(@currentfilename,@logvalue,@olderfilename)

SELECT *
   FROM fn_trace_gettable(@currentfilename,DEFAULT) AS Trace
 WHERE Trace.ObjectType <> '21587' -- Ignoring the statistics
   AND TextData is not null
   AND (TextData like '%no_log%'
    OR TextData like 'DUMP%'
    OR TextData like '%truncate%')
   AND TextData not like 'SELECT%'

--SELECT *
--  FROM fn_trace_gettable(@oldfilename,DEFAULT) AS Trace
-- WHERE Trace.ObjectType <> '21587' -- Ignoring the statistics
--   AND TextData is not null
--   AND (TextData like '%no_log%'
--    OR TextData like 'DUMP%'
--    OR TextData like '%truncate%')
--   AND TextData not like 'SELECT%'
--
--SELECT *
--  FROM fn_trace_gettable(@olderfilename,DEFAULT) AS Trace
-- WHERE Trace.ObjectType <> '21587' -- Ignoring the statistics
--   AND (TextData like '%no_log%'
--    OR TextData like 'DUMP%'
--    OR TextData like '%truncate%')
--   AND TextData not like 'SELECT%'
--

Calculate the size of you indexes

I am using this script to see how much size my indexes are using inside my database.


SET NOCOUNT ON

CREATE TABLE #index_size
(Table_view_name varchar(200)
,Index_name varchar(200)
,Index_size_kb decimal(20,6)
,Index_size_Mb decimal(20,6)
,Index_size_Gb decimal(20,6))


DECLARE @object_name NVARCHAR(255)

DECLARE object_cur CURSOR FOR
  SELECT t2.name + '.' + t1.NAME
    FROM SYS.OBJECTS t1
  INNER JOIN dbo.sysusers t2 on t1.schema_id = t2.uid
   WHERE t1.type = 'U' --in ('U','V')
   ORDER BY t2.name, t1.name

OPEN object_cur

FETCH NEXT FROM object_cur INTO @object_name

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #index_size (Table_view_name)
SELECT @object_name

PRINT @object_name

INSERT INTO #index_size
SELECT     @object_name
     ,     i.name              AS IndexName
     ,     SUM(page_count * 8) AS IndexSizeKB
     ,     SUM(page_count * 8.00) / 1024 AS IndexSizeMB
     ,     SUM(page_count * 8.00) / 1024 / 1024 AS IndexSizeGB
  FROM sys.dm_db_index_physical_stats(db_id(), object_id(@object_name), NULL, NULL, 'DETAILED') AS s
  JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
                       AND s.index_id    = i.index_id
 GROUP BY i.name
 ORDER BY i.name

FETCH NEXT FROM object_cur INTO @object_name
END

CLOSE object_cur
DEALLOCATE object_cur

select @@SERVERNAME, db_name(), Table_view_name,
CASE
  WHEN index_name IS NULL
    THEN CASE
           WHEN Index_size_kb IS NOT NULL
             THEN 'HEAP'
             ELSE index_name
         END
    ELSE index_name
END as index_name, Index_size_kb, Index_size_MB, Index_size_Gb
     from #index_size
where Index_size_kb is not null

drop table #index_size

Top Stored Procedures by Execution Count

In this script I want to show you how you can find the procedures that have been executed the most times on your instance. I always use this script to see if I can make the most executed stored procedures perform better.


SELECT TOP 100
               CASE
                    WHEN dbid = 32767
                          THEN 'Resource'
                          ELSE DB_NAME(dbid)
               END [DB_NAME]
             , OBJECT_SCHEMA_NAME(objectid, dbid) AS [SCHEMA_NAME]
             , OBJECT_NAME(objectid, dbid) AS [OBJECT_NAME]
             , SUM(usecounts) AS [Execution Count]
   FROM    sys.dm_exec_cached_plans cp
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
 WHERE   objtype = 'Proc'
      AND CASE
                     WHEN dbid = 32767
                       THEN 'Resource'
                       ELSE DB_NAME(dbid)
               END NOT IN ('Resource','msdb','master')
     AND UPPER(
                 REPLACE(
                   REPLACE(
                     REPLACE(
                       REPLACE(
                         REPLACE(
                           REPLACE(
                             REPLACE(text , '       ', ' ') , '       ', ' '), '      ', ' '),'     ', ' '), '    ', ' '), '   ', ' '), ' ', ' '))
              NOT LIKE '%CREATE FUNC%'
GROUP BY dbid
                  , objectid
                  , cacheobjtype
ORDER BY SUM(usecounts) DESC ;

What should we use delete or truncate


There are many stories about the differences between the delete and the truncate statement.

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

Truncates:

Reset the identity value
Can’t have where clause
Can’t be run on a table which is referenced by other tables
Does not fire triggers.
Can’t be run on a table which has depending objects such as indexed views.
Can’t be run on a table which has published by merge or transactional replication publication
Needs db_owner and db_ddladmin permission.
Needs locks on the table and schema but do not need locks on rows of the tables

Internally, after a table is truncated, no data pages are left for the table whereas deletion over a heap may not release the database and deletion on a B-Tree may not release none leaf pages. Truncating command simply unhook data from metadata of the table. When table is small, unhooked data (rows) will be deallocated synchronously. When table is large, data will be removed asynchronously which is called deferred deallocation.

Some myths about the truncate statement:

Truncate cannot be rolled back. That’s not true. Truncate Table command is transactional. It can be rolled back.
Truncate generate less log records. It depends. If table is small enough, truncating table will generate more logs.

Truncate is always faster. It depends. The Truncate statement needs more operations than delete if the table is very small, thus theoretically it needs more time to process.

Deferred deallocation

While a large table is getting truncated, SQL Server merely unhooks the meta-data and IAMs then returns without resetting bits in IAM, PFS, GAM and SGAM. An internal process will set those bits and then change the allocation related tables.