Showing posts with label level. Show all posts
Showing posts with label level. Show all posts

Wednesday, October 15, 2014

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Try this Solution : 

Nothing could be found in SQL Error logs.Check the service account of SQL Server, find out whether it has enough permissions to run SQLServer. If you are not able to change the Service acccounts permissions immediately and wants to come out of this Service Pack Installtion errors.  Change Service account for SQL Server to Local System. SQL Server DB Engine Service will be started and the build number will be updated too. Can revert your SQL Servers Service account back to Original from Local System.

Tuesday, September 23, 2014

Script to list out all object level permissions in SQL Server

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) + CASE 
  WHEN cl.column_id IS NULL
   THEN SPACE(0)
  ELSE '(' + QUOTENAME(cl.NAME) + ')'
  END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Object Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
 AND cl.[object_id] = PERM.major_id
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Database Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE PERM.major_id = 0
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

Scrip to find Schema level size information in SQL Server

--Script to know number of tables, row count, data size, index size, used size
BEGIN TRY
 SELECT
  --(row_number() over(order by a3.name, a2.name))%2 as l1,
  a3.NAME AS [schemaname]
  ,count(a2.NAME) AS NumberOftables
  ,sum(a1.rows) AS row_count
  ,sum((a1.reserved + ISNULL(a4.reserved, 0)) * 8) AS reserved
  ,sum(a1.data * 8) AS data
  ,sum((
    CASE 
     WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
      THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
     ELSE 0
     END
    ) * 8) AS index_size
  ,sum((
    CASE 
     WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
      THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
     ELSE 0
     END
    ) * 8) AS unused
 FROM (
  SELECT ps.object_id
   ,SUM(CASE 
     WHEN (ps.index_id < 2)
      THEN row_count
     ELSE 0
     END) AS [rows]
   ,SUM(ps.reserved_page_count) AS reserved
   ,SUM(CASE 
     WHEN (ps.index_id < 2)
      THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
     ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
     END) AS data
   ,SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  GROUP BY ps.object_id
  ) AS a1
 LEFT JOIN (
  SELECT it.parent_id
   ,SUM(ps.reserved_page_count) AS reserved
   ,SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (
    202
    ,204
    )
  GROUP BY it.parent_id
  ) AS a4 ON (a4.parent_id = a1.object_id)
 INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id)
 INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
 WHERE a2.type <> 'S'
  AND a2.type <> 'IT'
 GROUP BY a3.NAME
 ORDER BY a3.NAME
END TRY

BEGIN CATCH
 SELECT - 100 AS l1
  ,1 AS schemaname
  ,ERROR_NUMBER() AS tablename
  ,ERROR_SEVERITY() AS row_count
  ,ERROR_STATE() AS reserved
  ,ERROR_MESSAGE() AS data
  ,1 AS index_size
  ,1 AS unused
END CATCH