MS SQL useful statement

1. To find out how much space occupied by each table and index

Column names are database name, current date, row count, data size, index size.

SELECT DB_NAME() AS DatabaseName
, object_name(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.[rows] AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] in (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
end = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id

2. The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait

• sp_helpdb BizTalkDTADb
  
  ALTER DATABASE BiztalkDTADb
  SET RECOVERY SIMPLE;
  GO
  
  DBCC SHRINKFILE (BiztalkDTADb_log, 1);
  GO
  sp_helpdb BizTalkDTADb
  GO
  ALTER DATABASE BiztalkDTADb
  SET RECOVERY FULL
  GO

  Why on Earth are you posting a reply to a thread that started in 2006, and which has not been touched for over two years? On top of that you solution is a bad one for several reasons.
  1) You suggest shrinking the log file to 1 MB, which unlikely to be correct. What is correct is difficult to tell, because it depends on the system in question. For a developer database 50 MB can do. For a production database that itself is 1TB, a log file of 100 GB is not unreasonable.
  2) You suggest flipping the recovery model from full to simple and back again. This breaks the log chain, and you will not be able to do point-in-time recovery from this point and on. There are two options here:
  a) You don't care about point-in-recovery. (For instance, because it is a development database on your workstation). In this case keep the database in simple recovery.
  b) Immediately take a full backup, so that you can meet the RPO (Recovery Point Objective) in your SLA (Service Level Agreement.)
  And overall, shrinking database files is a very exceptional thing which you should only do when you know that the file has grown to a size that will not achieve again. This is particularly true for log files, since growing a log file takes time.