[Replication] Replication status

Rough first attempt. Doesn't include everything you might need, but gives you a quick view as to how replication is doing.

The Replication Monitor has a nasty habit of not showing you broken replication - it's usually trying to reapply the command rather than actually being in a failed status. I've watched the replication monitor show everything as fine, but when you go look at a subscription you see that it's between failed retry attempts to reapply a row. If your network is slow you can see the subscriptions flash the error icon then go back to showing everything as being okay.

I'll come back to this and see about adding more information to it. Thanks to Hilary Cotter for pointing out the source table (MSDistribution_Status) in an article - but any mistakes in the code are mine.

use distribution
go
SELECT SUM(UndelivCmdsInDistDB),
  MSdistribution_agents.NAME,
  MSdistribution_agents.publication,
  subscriber_id,
  subscriber_db FROM MSDistribution_Status
INNER JOIN MSdistribution_agents
ON MSDistribution_Status.agent_id = MSdistribution_agents.id
WHERE UndelivCmdsInDistDB > 0  --show only those that are backed up
AND subscriber_id > 0 --negative subscriber IDs are for those that
   --always have a snapshot ready
GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
 subscriber_id, subscriber_db
ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
 subscriber_id, subscriber_db

[Index] Statistics age

This is pretty handy. Lets you know if your stats are out of date.

Found at http://furrukhbaig.wordpress.com/2007/08/17/index-statistics-age/

SELECT
           'Index Name' = ind.name,
           'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM
           SYS.INDEXES ind
WHERE
           OBJECT_NAME(ind.object_id) = ''

[Tools] Some handy commands for Litespeed

Some notes of mine on using Quest Software's Litespeed Backup. Really handy software, but with some quirks.

To backup without having it compressed, when using NCS (Native Command Substitution)

/* ads_translator_deactivate */
BACKUP DATABASE dynamics TO DISK ='e:\dynamics_native.bak'

(the code in the comments is actually read by the parser and turns off the NCS)

To see if Litespeed is installed on a machine

use master
go
exec xp_sqllitespeed_version
go

Basic Restore(needs to be on one line - split here for readability)

exec master.dbo.xp_restore_database
@database = 'datbase',
@filename = '\\path\datbase.bak',
@filenumber = 1,
@with = 'RECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10'

To restore a database with a new name

--First, get a list of all logical files within the backup--
xp_restore_filelistonly @filename= '\\path\datbase.BAK'

--Now do a restore with MOVE.  @database is the new db name
exec master..xp_restore_database @database='datbase2'
  , @filename= '\\path\datbase.BAK'
  , @with = 'MOVE "datbase_Data" TO "e:\SQL\datbase2.MDF"'
  , @with = 'MOVE "datbase_Log" TO "e:\SQL\datbase2.LDF"' 

Extractor
The extractor.exe application will take a compressed backup file and decompress a standard MS SQL backup file. Useful if you don't have Litespeed on your other server, or if you need to send a file to someone who doesn't have it.

To call it:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

where -F is the original compressed file and -E is the name for the tape files that will be generated (typically 7 files per backup). These can be restored via SSMS by adding each of the .bak[0-7] files to a restore.

Object-level Restores
OR.exe is the application used to do object-level restores from Litespeed. It uses BCP to pull the data out of one table and to place it in the next. It can only do it on a FULL backup - not a Filegroup, Diff, or TLOG. Irritating, that - maybe they've fixed that in Version 5.

Sample command to restore the "route" table from a backup on ServerA to ServerB. This needs to be on one line, I've split it up to illustrate the parameters.

"C:\Program Files (x86)\Imceda\LiteSpeed\SQL Server\Engine\or.exe"
 -F\\path\datbase.BAK
 -Odbo.route
-R1
-EServerB
-Sexisting_database
-Tdbo.TBD_test_restore

• -F: database backup filename
  
• -O: table name - must include schema
  
• -R: connection type - 1 is trusted
  
• -E: target server
  
• -S: target database
  
• -T: target table name - must include schema.

It can be done within SQL Server as well.

exec master..xp_objectrecovery
    @status_filename='{178FA185-ABC7-4183-910A-4DDE775BB614}',
    @FileName='E:\datbase.BAK',
    @ObjectName='dbo.tbd_test_restore',
    @DestinationServer='qa_database',
    @DestinationDatabase='new_copy_of_datbase',
    @DestinationTable='TBD_newtable_temp',
    @TempDirectory='E:\temp\'

[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

[Index] Easily find the missing indexes in a query

(run in Grid Mode in SSMS)

SET STATISTICS XML ON
[put your query here]
SET STATISTICS XML OFF 

Once you run that, in the results pane there's a "Microsoft SQL Server 2005 XML Showplan" with XML. Double-click on it - it'll open a new tab in SSMS with the XML broken out. Search for "Missing" - there will be a block entitled "Missing Indexes". It has the INEQUALITY columns, the EQUALITY columns, and the INCLUDEs that it wants.

It may be old news to some of you, but it's one of those things I hadn't played with until recently, and I'm really impressed with.

[Setup] Setting up Database Mail on 2005 servers

Just a little script to set up Database Mail on 2005 boxes. I used to use XP_SMTP_MAIL, since the IMAP mail in SQL Server 2000 was a POS. This is considerably better.

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Database_Email',
    @description = 'Mail account for use by all database users.',
    @email_address = 'Database_Email@yourcompanyname.com',
    @replyto_address = 'Database_Email@yourcompanyname.com',
    @display_name = 'Database_Email',
    @mailserver_name = 'mail.yourcompanyname.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database_Email',
    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database_Email',
    @account_name = 'Database_Email',
    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database_Email',
    @principal_name = 'public',
    @is_default = 1 ;

go
--Enable advanced options
sp_configure 'show advanced options',1
go
RECONFIGURE
go
--Now enable the server to send mail
sp_configure 'Database Mail XPs',1
go
reconfigure
go

--test mail
declare @test varchar(50)
select @test = 'Test Email from ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database_Email',
    @recipients = 'you@yourcompanyname.com',
    @subject = @test

[Backups] Alter jobs to change your backup server

Something I had to whip up on short notice, so you can see what code I cribbed from SSMS.

Here's what we do:

1. Find any jobs with "Backup" in the name
   
2. Get the job details via sp_help_jobstep
   
3. Step through each job step
   
   
   
   1. Create a different statement, with the new server's name
      
   2. If the step has BACKUP or SQLMAINT, and the old server's name, execute SP_UPDATE_JOBSTEP with the new code.
      
   
   

create table #tmp_sp_help_jobstep
(
step_id int null,
step_name nvarchar(128) null,
subsystem nvarchar(128) collate Latin1_General_CI_AS null,
command nvarchar(max) null,
flags int null,
cmdexec_success_code int null,
on_success_action tinyint null,
on_success_step_id int null,
on_fail_action tinyint null,
on_fail_step_id int null,
server nvarchar(128) null,
database_name sysname null,
database_user_name sysname null,
retry_attempts int null,
retry_interval int null,
os_run_priority int null,
output_file_name nvarchar(300) null,
last_run_outcome int null,
last_run_duration int null,
last_run_retries int null,
last_run_date int null,
last_run_time int null,
proxy_id int null,
job_id uniqueidentifier null)

declare @job_id UNIQUEIDENTIFIER
DECLARE @minid SMALLINT, @maxid SMALLINT
DECLARE @oldservername sysname, @newservername sysname
DECLARE @jobcode NVARCHAR(MAX)
declare crs cursor local fast_forward
for ( SELECT sv.job_id AS [JobID]
 FROM msdb.dbo.sysjobs_view AS sv
 WHERE NAME LIKE '%backup%' )

SELECT @oldservername = 'ServerA' 
SELECT @newservername = 'ServerB'
open crs
fetch crs into @job_id
while @@fetch_status >= 0
begin
TRUNCATE TABLE #tmp_sp_help_jobstep
insert into #tmp_sp_help_jobstep(step_id, step_name, subsystem, command, flags, cmdexec_success_code, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id)
 exec msdb.dbo.sp_help_jobstep @job_id = @job_id
update #tmp_sp_help_jobstep set job_id = @job_id where job_id is NULL
--change to job step occurs here.
SELECT @minid = NULL, @maxid = NULL
SELECT @minid = MIN(step_id), @maxid = MAX(step_id) FROM #tmp_sp_help_jobstep
WHILE @minid <= @maxid   BEGIN    SELECT @jobcode = REPLACE(command, @oldservername, @newservername)      FROM #tmp_sp_help_jobstep      WHERE step_id = @minid    IF EXISTS (SELECT * FROM #tmp_sp_help_jobstep        WHERE step_id = @minid        AND (command LIKE '%backup%' OR command LIKE '%sqlmaint%')       AND command LIKE '%'+@oldservername+'%') --   EXEC msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=@minid,  --    @command= @jobcode     SELECT @jobcode    SET @minid = @minid + 1    END  --end change   fetch crs into @job_id end  close crs deallocate crs  DROP table #tmp_sp_help_jobstep