[Maint] Quick and Dirty maintenance

Is this suitable most places? No. Is it fast and easy? Yes.

Reindex all tables in a database (2000 & 2005)

exec sp_MSforeachtable "DBCC DBREINDEX ('?')"

or

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX ('?', ' ', 85)"

Update statistics in a database
For 2000 (since sp_updatestats can break certain things)

EXEC sp_MSforeachtable "update STATISTICS ?"

For 2005:

sp_updatestats

[Objects] dropping objects via code

The following code works in both SQL 2000 and SQL 2005.

Tables

if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

Stored Procedures

IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'[dbo].[the_procedure_name]') 
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
 )
DROP PROCEDURE the_procedure_name

Alternatively, the following code will CREATE a dummy SP if it doesn't exist, then ALTER it. This way you will only CREATE, not DROP, which can come in handy in certain circumstances, since it will save permissions. Note that this uses INFORMATION_SCHEMA, which is more portable than sysobjects.

IF NOT EXISTS 
 (
 SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_NAME = 'the_routine_name' 
 and routine_type = 'PROCEDURE' --could also be 'FUNCTION' for a function
 )
        EXEC ('CREATE PROC dbo.the_procedure_name AS SELECT 1')
GO
ALTER PROCEDURE dbo.the_procedure_name
AS
SELECT  *
FROM    myTable

[Jobs] Starting a job on a foreign server

Man, I'm lazy right now, copying useful code from other people.

This comes from "SQLAdmin" on the SQL Server Mag forums. Set this as a job step, and it'll run a job on a different server. Check your perms. Note that all this does is kick off the job and tell you if it kicked off successfully.

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=83712&enterthread=y

declare @retcode int
declare @job_name varchar(300)
declare @server_name varchar(200)
declare @query varchar(8000)
declare @cmd varchar(8000)

set @job_name = 'My Test Job' ------------------Job name goes here.
set @server_name = 'MyRemoteServer' ------------------Server name goes here.

set @query = 'exec msdb.dbo.sp_start_job @job_name = ''' + @job_name + ''''
set @cmd = 'osql -E -S ' + @server_name + ' -Q "' + @query + '"'

print ' @job_name = ' +isnull(@job_name,'NULL @job_name')
print ' @server_name = ' +isnull(@server_name,'NULL @server_name')
print ' @query = ' +isnull(@query,'NULL @query')
print ' @cmd = ' +isnull(@cmd,'NULL @cmd')

exec @retcode = master.dbo.xp_cmdshell @cmd

if @retcode <> 0 or @retcode is null
begin
print 'xp_cmdshell @retcode = '+isnull(convert(varchar(20),@retcode),'NULL @retcode')
end

[Free Space] SIMPLE mode yet TLOG still growing?

Had to track down an issue today - a log file had gone from 37gb to 55gb in about 6 hours. Yup, database was in simple mode.

Make sure the log file is actually growing.
http://thebakingdba.blogspot.com/2008/03/maint-show-free-space-within-database.html

Find out _why_ it's still growing.

SELECT name, log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
ORDER BY name

Our result was ACTIVE TRANSACTION. This could be either a transaction, or replication.


Why does this matter?
If the oldest transaction is still open, everything since then has to go in a new part of the data file - think of it like something blocking the entry to your cube. It doesn't have to be big, there's plenty of room inside the cube, but you need to get rid of the item to get in.


Fortunately, finding the errand SPID is easy.

DBCC OPENTRAN ()

It gives you the SPID of the errant process. In our case, it was a user process people had forgotten about. Kill the spid (or get the person to stop it) and rerun your free-space-within-database again.


There are other ways to find the open transactions.

SELECT * FROM sys.dm_tran_session_transactions

, but that's a bit more vague. It'll give you the SPID (session_id) of all open transactions, but for what I was doing it didn't seem to give me the SPID I needed to kill. You could also select from sys.processes, but honestly OPENTRAN is simpler.

-TBD