Still on SQL Server 2000? A couple of handy queries

Recently, I published a short list of handy queries I’m using on Microsoft SQL Server when performing simple housekeeping tasks. Unfortunately, they are not working on old versions of SQL Server.

Queries for old SQL Servers are not that complex, because the ability to obtain the required data is also limited, but here are two I used recently.

List of tables and space used by them

CREATE TABLE #SpaceUsed (TableName sysname, NumRows BIGINT, 
  ReservedSpace VARCHAR(50), DataSpace VARCHAR(50), 
  IndexSize VARCHAR(50), UnusedSpace VARCHAR(50))

DECLARE @str VARCHAR(500) 

SET @str = 'exec sp_spaceused ''?''' INSERT INTO #SpaceUsed 

EXEC sp_msforeachtable @command1=@str 

SELECT * FROM #SpaceUsed 
ORDER BY NumRows DESC

DROP TABLE #SpaceUsed

Truncate transaction log

Attention! This query should be executed once the database is backed up. It is removing recovery data from the transaction log.

USE master; 
go 

DUMP transaction <YourDBName> with no_log;
go

USE <YourDBName>;
go 

DBCC SHRINKFILE (<YourDBNameLogFileName>, 123);

Please note that in two places you should use the database name, but in the third place – the logical name of the database log file. You will find it in the database permissions or using select * from sysfiles. The number at the end (eg “123”) is the desired size of the log after truncation. It will grow again, this number is the initial size.