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.