Important T-SQL commands to know LDF and MDF file name and shrinking File

Important T-SQL commands to know LDF and MDF file name and shrinking 

file

---To know the size of Database and free space in MB------------

SELECT DB_NAME(database_id) AS DatabaseName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)[Free Space in MB]
FROM sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)

sp_helpdb 'northwind'

SELECT 12480/1024 as GB


--------------To know the logical file name like MDF and LDF file----------------------

select * from sys.databases
where name='northwind'

SELECT * FROM sys.database_files

sp_helpfile 'northwind'


----------------------------To take the backup of db and log backup---------------------

BACKUP DATABASE northwind
TO DISK = 'F:\northwind_bk.bak'
--
BACKUP LOG northwind
TO DISK = 'F:\northwind_Log.bak'

----------To alter the Database Recovery model/switch the recovery model---
ALTER DATABASE northwind
SET RECOVERY SIMPLE;
GO
 --Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Northwind_log, 1);
GO
 --Reset the database recovery model.
ALTER DATABASE northwind
SET RECOVERY FULL;

-------------------------------Shrinking whole DB -------------------

DBCC SHRINKDATABASE (northwind)

------------To know db status----------------------------


select name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases 
where name='northwind'
--where log_reuse_wait_desc<>'NOTHING'
--where log_reuse_wait_desc='REPLICATION'
----where log_reuse_wait_desc='LOG_BACKUP'
--where log_reuse_wait_desc='CHECKPOINT'