Database is Recovery Pending in sql server

If the Database LOG file is  missing in SQL Server then Database is going to be RECOVERY PENDING mode.

First run the sql statement below check to find the name of the database with status

ALTER DATABASE [AdventureWorks] SET MULTI_USER

Msg 5120, Level 16, State 101, Line 24

Unable to open the physical file "D:\MSSQL\DATA\AdventureWorks_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5181, Level 16, State 5, Line 24

Could not restart database "AdventureWorks". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 24

ALTER DATABASE statement failed.

-- Database LOG file is  missing

AdventureWorks_log D:\MSSQL\DATA\AdventureWorks_log.ldf

Use master

go

ALTER DATABASE [AdventureWorks] REBUILD LOG ON(

NAME ='AdventureWorks_log' ,

FILENAME='D:\MSSQL\DATA\AdventureWorks_log.LDF' )

Warning: The log for database 'AdventureWorks' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

-- Now Database is in Restricted_user mode change to Multiuser

ALTER DATABASE [AdventureWorks] SET MULTI_USER

sp_helpdb [AdventureWorks]

Generate User Name and Role Name from all databases

 SET NOCOUNT ON

DECLARE @DBName VARCHAR(255)

DECLARE @sql NVARCHAR(500)

CREATE TABLE  #dbTable

(DBName  varchar(50) null ,

UserName varchar(100) null,

RoleName varchar(100) null)

DECLARE dbCursor CURSOR FOR

SELECT name AS DbName

FROM sys.databases

WHERE name not in( 'master', 'msdb' , 'tempdb', 'model'  )

        AND state=0 

 

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'USE ' + @DBName  

--Print @DBName

EXEC (@sql)

INSERT INTO  #dbTable (DBName, [UserName], RoleName )

SELECT @DBName as DBNames, u.[name] AS [UserName], r.[name] AS RoleName 

FROM sys.database_principals u 

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id 

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id

AND u.[name] NOT IN('dbo')

ORDER BY u.[name]

FETCH NEXT FROM dbCursor INTO @DBName

END

CLOSE dbCursor

DEALLOCATE dbCursor

SELECT * FROM #dbTable

--Drop Table #dbTable

GO

Partitioning FACT Table with 664 Million Records

I had recently completed Creating Partitioning  and adding new index for table with 664 million records to improve query performance for Dashboard usage 

 --PART1

-- Creating a Partition Function

CREATE PARTITION FUNCTION IntegerPartitionFunction (INT)

AS RANGE LEFT FOR VALUES (2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029 ); 

--The result for this RANGE LEFT assignment is

CREATE PARTITION SCHEME IntegerPartitionScheme

AS PARTITION  IntegerPartitionFunction

ALL TO ([PRIMARY]) -- Because all data currently in One File group

GO

--CREATE PARTITION SCHEME IntegerPartitionScheme

--AS PARTITION IntegerPartitionFunction

--TO (

--[PARTITION_FG1], [PARTITION_FG2], [PARTITION_FG3], [PARTITION_FG4],

--[PARTITION_FG5], [PARTITION_FG6], [PARTITION_FG7], [PARTITION_FG9],

--[PARTITION_FG9], [PARTITION_FG10], [PARTITION_FG11] ,

--[PRIMARY])

 

-- check Partition schemas

SELECT ps.name, pf.name, boundary_id,value

FROM sys.partition_schemes ps

INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id

INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

GO

 

--PART2

--Index #1

CREATE NONCLUSTERED INDEX IX_FACT_1

ON dbo.FACT_SALES_DATA 

(

[YearId]

)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON IntegerPartitionScheme(YearId)

GO

 

--Index #2

CREATE NONCLUSTERED INDEX IX_FACT_2

ON dbo.FACT_SALES_DATA 

(

 [SId] ASC , 

 [YearId] ASC , 

 [MonthId] ASC 

)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON IntegerPartitionScheme(YearId)

GO

 

Database Security is most important to every organisation with any elevated access should be disabled to product the database. 

Below script is quick check to understand the User and its database roles in specific database. Make sure to grant or revoke  appropriate database level access to end users/Group. Keep checking/auditing the role regularly.

USE [Your Database];

SELECT u.[name] AS [UserName], r.[name] AS RoleName 

FROM sys.database_principals u 

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id 

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id

where  u.[name] = 'dbo'  OR  u.[name] = 'db_owner'

Image from Microsoft

Data Read Access to All the users in the specific database after refresh

 After you refresh database from Production to UAT environments make sure to run below scripts to drop production users first then grant data read access to UAT users.

use [Database_Name]
go

select  'DROP USER' , '['+ name+']'  

from dbo.sysusers

where name LIKE '%PRD-%' 

GO

use [Database_Name]
go
select 'EXEC sys.sp_addrolemember '   ,   ''' db_datareader '''   ,   '['+ name+']'
from master.dbo.syslogins

GO

Generate Foreign Key constraints for all table in Database

SELECT 'ALTER TABLE [dbo].[' +''+  object_name(fk.parent_object_id)+']' AS  ParentTableName, 

   'WITH NOCHECK ADD  CONSTRAINT'+' ['+  fk.[name] +']' ,    

   'FOREIGN KEY([' +''+  COL_NAME(fc.parent_object_id,fc.parent_column_id) +'])' AS  ParentColName ,

   'REFERENCES [dbo].[' +''+  object_name(fk.referenced_object_id) +']' AS  RefTableName,

   '([' +''+    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) +'])' AS  RerfColName ,

   ' NOT FOR REPLICATION'

FROM sys.foreign_keys fk

 INNER JOIN   sys.foreign_key_columns AS fc        

        ON fk.OBJECT_ID = fc.constraint_object_id

 INNER JOIN   sys.tables t 

      ON t.OBJECT_ID = fc.referenced_object_id

--WHERE fc.parent_object_id = object_id('Table_name')

Check DATABASEPROPERTYEX ()

If you are managing more than 'n' of SQL Server database estate in your organisation then you may required to check some times  a specified database current setting in SQL Server to understand.

This below function returns the current setting of the specified database option or property.

DATABASEPROPERTYEX ( database , property )

-- You should defined database name for each function to retrieve the property details

SELECT 
DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation, 
DATABASEPROPERTYEX( 'AdventureWorks2022' , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX('AdventureWorks2022', 'Recovery') AS Recovery_State

--Alternatively you can use db_name function for the current database to  retrieve the property details

USE [db_name]
GO
SELECT   
DATABASEPROPERTYEX( db_name() , 'Collation') AS Collation,
DATABASEPROPERTYEX( db_name() , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX( db_name() , 'Recovery') AS Recovery_State

-- Check with Sys.databases to see all the database related property values

SELECT * FROM sys.databases;