[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

[Indexes] And my unused index query.

Actually, not mine, but it's the one I use. All sorts of variations you can run

SELECT  @@SERVERNAME AS server_name,
            DB_NAME() AS database_name,
            o.name AS object_name,
        i.name AS index_name,
        i.type_desc,
        ISNULL(u.user_seeks, 0) user_seeks,
        ISNULL(u.user_lookups, 0) user_lookups,
        ISNULL(u.user_scans, 0) user_scans,
        ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0) user_total,
        ISNULL(u.user_updates, 0) user_updates,
        last_user_seek,
        GETDATE() AS date_inserted       
--        fill_factor
--    ,'DROP INDEX ' + i.name + ' ON dbo.' + o.name
FROM    sys.indexes i
JOIN    sys.objects o
        ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u
        ON i.object_id = u.object_id
           AND i.index_id = u.index_id
           AND u.database_id = DB_ID()
WHERE   o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')           -- No system tables!
AND (ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)) < 100
AND i.type_desc NOT IN ('HEAP','CLUSTERED')
AND i.is_primary_key = 0
--AND i.is_unique_constraint = 0
ORDER BY (ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0)),ISNULL(u.user_updates, 0) DESC, o.name, i.name
--ORDER BY ISNULL(u.user_updates, 0) desc, o.name, i.name
--ORDER BY o.name,
--        i.name

[Index] Find the size of your indexes

Something found online (originally by Alejandro Mesa). This is doubly handy now that 2005 can tell us which indexes aren't frequently used. Thanks to N8WEI for the correction on sys.allocation_units.

select
i.[object_id],
i.index_id,
i.NAME AS Index_Name,
i.type_desc,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
on (a.type = 2 AND p.partition_id = a.container_id)
OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
--where
-- i.[object_id] = object_id('dbo.thebakingdba_fanmail') --wishful thinking
-- and i.type = 1 -- clustered index
order by
i.name --p.partition_number
go