Shrink database SQL Server

Shrinking database is one of best option to reduce log space after taking Full Backup. To do that we requires to change your database in simple recovery mode than shrink the database.

ALTER DATABASE <database-name> SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (<database-log-name>, 5)

GO

ALTER DATABASE <database-name> SET RECOVERY FULL

GO

Database Change Log as Crucial part in database development life cycle

IT industries are keep growing so do Database size in terms of design and data. In mean time the database changes also take place in the industry. As a database developer and ETL developer, I found there is a lot miscommunication and discrepancies going inside the industry.

Due to this miscommunication a lots of production failure occurs and which causes IT industries hundreds of thousands of dollars. As a large database project, it is now necessary to implement Database Change Management. A simple table design that keep tracks of all database changes. May be it is in terms of reports or manage single spread sheet or table and retrieve using SQL queries.

ETL production failure I had seen in most cases due to table schema changes.  You may check a log but it’s not 100% accurate most of time and solution to this problem is to go for each table and compare the schema with the source which cost lots of time effort. Sometimes one more issue I came across is that some developers define their own schema design for the table which is sometimes more dangerous.

If DCM is implemented at right place than it resolved so many issues than compare each schema between source and destination. Like Event log or failure log DCM or I can say Database Change Log is also become part of IT database development strategy.

0

You can also contact me for spread sheet or taking it to database level.

Shrinking database in SQL Server

Shrinking database requires to change recovery model from Full to simple.

ALTER DATABASE <database> SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (<log database>, 5)
GO

ALTER DATABASE <database> SET RECOVERY FULL
GO

Server Login and server roles using matrix in SQL Server

WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role] ,
Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT * FROM sys.server_principals  WHERE type_desc='SERVER_ROLE') srvrole
ON srvrole.Principal_id= membership.Role_principal_id
RIGHT JOIN sys.server_principals  PRN
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0

UNION ALL

SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
  AND sp.[type] = 'CL'
  AND state = 'G' )

SELECT
name,
Type_Desc ,
'Y'    AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE '' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE '' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE '' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE '' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE '' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE '' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE '' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE '' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE '' END AS 'ControlServer'
FROM CTE_Role
PIVOT(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])
) AS pvtWHERE
WHERE name not like '%#%'
AND name not in ('_expressProvisioning','public','sysadmin','securityadmin','serveradmin','setupadmin','processadmin','diskadmin','dbcreator','bulkadmin')
Order by name;

You can edit exception list to remove account that you do not want.

To get list of Server logins and database with default schema per database

Before getting entire list first get all server login list



SELECT name
        FROM SYS.SERVER_PRINCIPALS
        WHERE name NOT LIKE '#%'
        AND name NOT IN ('sa','public','sysadmin','securityadmin',
        'serveradmin','setupadmin','processadmin','diskadmin','dbcreator',
        'bulkadmin','NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
        ORDER BY name

In code below we have to add database name instead <database-name>  and union with all databases same way right now its union with 2 databases.


DECLARE @name SYSNAME

CREATE TABLE #list (databaseName NVARCHAR(100)
        ,loginname NVARCHAR(100)
        ,default_schema_name NVARCHAR(100))

DECLARE    refreshTableCursor CURSOR FOR
        SELECT name
        FROM SYS.SERVER_PRINCIPALS
        WHERE name NOT LIKE '#%'
        AND name NOT IN ('sa','public','sysadmin','securityadmin',
        'serveradmin','setupadmin','processadmin','diskadmin','dbcreator',
        'bulkadmin','NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
        ORDER BY name

    OPEN    refreshTableCursor
    FETCH NEXT FROM refreshTableCursor INTO @name
    WHILE    @@FETCH_STATUS = 0
    BEGIN
        SELECT N'<database-Name>',
                p.name,
                p.default_schema_name
        FROM sys.server_principals AS sp
        LEFT OUTER JOIN <database-name>.sys.database_principals AS p
        ON sp.sid = p.sid
        WHERE sp.name = @name
        UNION ALL
        SELECT N'<database-Name>',
                p.name,
                p.default_schema_name
        FROM sys.server_principals AS sp
        LEFT OUTER JOIN <database-name>.sys.database_principals AS p
        ON sp.sid = p.sid
        WHERE sp.name = @name
    FETCH NEXT FROM refreshTableCursor INTO @name
    END
    CLOSE        refreshTableCursor
    DEALLOCATE    refreshTableCursor   
   
SELECT    LoginName,
        DatabaseName,
        Default_Schema_Name,
        CASE WHEN is_disabled = 0 THEN 1 ELSE 0 END AS Active
FROM #list INNER JOIN sys.server_principals sp
    ON #list.LoginName = sp.name
WHERE loginname IS NOT NULL
    AND default_schema_name IS NOT NULL
ORDER BY loginname

DROP TABLE #list