SQL Server Agent Jobs duration Report

Hi folks. I would like to share with you the following simple script to show us jobs duration report in SQL Server 2005. I manipulated with INTERGER values stored by SQL Server to convert them into DATETIME/CHAR(8) datatypes to represent the data.
Thanks to SQL Server MVP Peter Ward provided me with StartTime calculation.

WITH job_duration_view
AS
(
SELECT name,
StartTime = CONVERT(DATETIME, RTRIM(last_run_date)) +
(last_run_time * 9 + last_run_time % 10000 * 6 + last_run_time % 100 * 10 + 25 * last_run_duration) / 216e4 ,
CONVERT(CHAR(8),DATEADD(ss,last_run_duration,CAST(last_run_date AS CHAR(8))),114)
AS duration
FROM msdb.dbo.sysjobservers js
JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE last_run_date >0 AND last_run_time >0
) SELECT name AS job_name,StartTime,
StartTime -'19000101'+Duration AS EndDate ,Duration
FROM job_duration_view

Change collation in tempdb

What's happening if you installed SQL Server instance with a collation that is different from a database collation? We just started testing our production application and everything seem to work well, however one of our stored procedure inserts hebrew characters into temporary table and then after some operations the data get insertded into a real table. Guess what wee have seen in the database? Right,we have seen '????' symbols. Sure, if you do not use temporary table and insert the data directly into a permanent table you will see the right characters. Someone said that we should decline of using temporary table and insert the data into 'temporary' permanent table. Another guy said that we should run ALTER DATABASE tempdb command to change COLLATION, but as we know you cannot run this statement on system databases.
The error is
Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'tempdb' because it is a system database.

So what is the solution? Well , use the REBUILDDATABASE option in Setup.exe or re-install the instance. Fortunately, the whole story happened on developing machine and we did not forget to install PRODUCTION server with right collation:-).

Just wanted to note you how important is to choose the 'right' collation while installing production server.

Scheduling database backup in SQL 2005 Express Edition

I have been recently visited client who uses SQL Server 2005 Express Edition for his business. As you know that edition is supplied without SQL Server Agent (which was in MSDE). So,the question is how to schedule backup of the database in that case? I would like to thank to Jasper Smith, who wrote these greate articles

Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27

Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29


CREATE PROCEDURE usp_BackupDatabases
@dbname SYSNAME,
@backup_path VARCHAR(100)
AS
DECLARE @dt VARCHAR(20) ---date of db backup
DECLARE @FileName VARCHAR(50) ---build path and dbname
DECLARE @sql VARCHAR(100) ---build final sql statement

SET @dt =CONVERT(VARCHAR(15),GETDATE(),112)
SELECT @FileName = @backup_path+@dbname + @dt+'.bak'

SET @sql = N'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @FileName + ''''
EXEC(@sql)


The above is simple stored procedure that accepts two parameters and performs BACKUP of the database.Now that, we have created stored procedure lets put the call of the sp into a text file ( I use userdbfullbackup.sql) and place the file on filesystem.
usp_BackupDatabases @dbname='$(DB)',@backup_path='$(BACKUPFOLDER)'


SQLCMD utility 'knows' to accept parameters (-v),so we can easily provide parameters.

--Usage
EXEC master..xp_cmdshell 'SQLCMD -S URID\SQLSERVERDEV2005 -i"c:\userdbfullbackup.sql" -v DB="Northwind" -v BACKUPFOLDER="c:\"'


But wait , till now we just written a stored procedure that backups our databases and tested it , so what is about scheduling?

Windows Servers/XP have Scheduled Tasks Wizard
You can refernce to below KB
http://support.microsoft.com/default.aspx?scid=kb;en-us;308569&sd=tech

Please see Jasper's explanation ( do not want repetition).
http://www.sqldbatips.com/showarticle.asp?ID=27