You can use below script to find out data file (*.mdf) and Log file (.ldf) size

exec sp_helpdb [dbName]

use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2) + 'MB'
from dbo.sysfiles


Why anyone needs to take the database offline?
1. May be user don’t want to use database for time being
2. To restore the database which is used by multiple users. Yes you can restore the database eventhough it is offline


EXEC sp_dboption N'DBName', N'offline', N'true'

ALTER DATABASE [DBName] SET OFFLINE WITH
ROLLBACK IMMEDIATE


You can use the below script to make the database read only so that no user can edit the database.


USE [master]
GO
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
OR
GO
ALTER DATABASE [DBName] SET READ_ONLY
GO


Orphan User:

An orphan user is a user in a SQL Server database that is not associated with a SQL Server login.


-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixOrphanusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixOrphanusers
FETCH NEXT FROM fixOrphanusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixOrphanusers
INTO @username
END
CLOSE fixOrphanusers
DEALLOCATE fixOrphanusers