Your Daily MSSQL Tips - Display Table Size and Disable Maintenance Plans for MSSQL 2005, MSSQL 2008 and MSSQL 2012

Yeeesh posts have been MSSQL-heavy. I've just been working on this stuff for quite a bit lately - by no means is this my favorite database. Anyway, you're not here for that. You're here because you need to fix something that's broken and Google says I have it. Here you go:

Find Database Table Size

Your database is growing and you need to know why. First step would be to find out which table is responsible for the growth - but how? Right click the database in question and run one of the following queries:

Displays the size of every table - exec sp_Msforeachtable 'exec sp_spaceused ''?'''
Displays a specific table - exec sp_spaceused TableName

Disable a Maintenance Plan

Let's say you want to temporarily disable a maintenance plan. You are trying to troubleshoot it, but you don't want to delete the entire thing and re-create it. You may have noticed SQL Management Studio's GUI has not straight forward way of doing this. First, you need to find the Job ID for the maintenance plan. This can be retrieved from the SQL Server Agent Job Properties window. Alternatively you can run the following query:

SELECT

job_id,name,description,enabled

FROM

msdb.dbo.sysjobs

ORDER BY

name ASC

Select the correct job ID and add it to this query,replacing @PutYourJobIDHere with the ID of your plan : 

DECLARE @my_job_id UNIQUEIDENTIFIER

SET @my_job_id = 'PutYourJobIDHere'

EXEC msdb.dbo.sp_update_job @job_id = @my_job_id , @enabled = 0

So a correct query would look like this:

DECLARE @my_job_id UNIQUEIDENTIFIER

SET @my_job_id = '8AE8F423-C40F-453B-9B7D-9F632FBAC2FC'

EXEC msdb.dbo.sp_update_job @job_id = @my_job_id , @enabled = 0

When you are ready to enable your plan again, use the same query, changing @enabled = 0 to @enabled = 1