Category Archives: SQL

List table sizes in SQL

I needed to get a list of table sizes from SQL to find out why a database was excessively large.  I used the following:

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128), rows CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

EXEC sp_MSForEachTable ‘INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ”?” ‘

SELECT TableName, CONVERT(bigint, rows) AS NumberOfRows, CONVERT(bigint, left(reserved, len(reserved)-3)) AS SizeinKB

FROM #RowCountsAndSizes

ORDER BY NumberOfRows DESC, SizeinKB DESC, TableName

DROP TABLE #RowCountsAndSizes

WSUS Cleanup Server 2012

I’ve inherited a customer with a badly running WSUS server.  It is running on Server 2012 with SQL 2012.  Maintenance had never been run, and attempts to do so failed with the cleanup wizard timing out.

I’ve created a powershell script, available here: that I have been able to use to clean it up.  It also requires the WSUSDBMaintenace.sql file from here, and SQL Management Studio if not already installed, available here.

The script performs the following:
Run the cleanup wizard;
Reindex the database;
Decline Itanium updates;
Shrink the database;
Optionally send an email.