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
ORDER BY NumberOfRows DESC, SizeinKB DESC, TableName
DROP TABLE #RowCountsAndSizes
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: http://pastebin.com/u2yuexXf 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.