Customized success or failure log of the SQL job in the Windows Event Viewer.

This #JustLearned was published in Beyondrelational.com and for other #JustLearned by me check the link provided at the end of this article.

 

You can check the status of a  SQL job and log the customized success or failure message of the job for the day in the Windows Event Viewer.

IF NOT EXISTS (SELECT TOP 1 RUN_DATE FROM MSDB.DBO.SYSJOBHISTORY WHERE JOB_ID=
(SELECT JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=’S_000W’) AND
STEP_ID = 3 AND RUN_STATUS=1 AND
RUN_DATE=CONVERT(VARCHAR , GETDATE(), 112) ORDER BY RUN_DATE DESC)
BEGIN
EXEC XP_LOGEVENT 60000, ‘S_000W: DAILY JOB NOT COMPLETE’, ERROR
END
ELSE
BEGIN EXEC XP_LOGEVENT 60000, ‘S_000W: DAILY JOB COMPLETED’, INFORMATIONAL
END

Usage

–xp_logevent { error_number , ‘message’ } [ , ‘severity’ ]

For more information

http://msdn.microsoft.com/en-us/library/ms186244.aspx

Also check the uses RAISERROR

For other #JustLearned check the below links

SQL Server Tips – Identify running jobs on remote server

Checking the Job Schedule status of a SQL job

Windows Service Installation with a Service User Account

Dotnet – Its possible to generate the assembly of specific platform

Platform specific code in dotnet

Disabling/Enabling SQL Jobs or Job schedule from remote computer

Windows Service with Windows Timer control doesn’t tick

Interview Questions & Answers (.NET & SQLServer)

I have written couple of questions and answer in my blog at BeyondRelational. Here I plan to put all of them together and also in future when ever I will add any questions answers to that blog, I will add a link here. Go through the questions and answers , but don’t think these are complete, you have to read a lot using the reference material and other books. You have to practice a lot and then you will get the answers fundamentally and practically correct for your understanding. So happy reading and please provide feedback for this series. If you find anything wrong, please guide me and tell me the correct answer so that I can verify the same and will update the most appropriate answer.

Till next time Keep smiling 🙂

UG Meet by BDOTNET & BITPro on 7th April 2012

Bitpro1

Reliving TechEd 2012 with Vinod Kumar 1

bdotnet

Being a UG member I always try my best to write my learning about each and every UG meet I attend, hence I never thought of missing this one also 🙂 . I wrote about the topic and about the speaker here. This post will provide links to so many URLs where you will find more about the topics covered as well as you can go through some of the blog written by other members about the topic and about Vinod Kumar.

As decided the UG meet was on 7th April and I was there at Microsoft Building by 10AM. To my surprise already people were there and they were discussing with Vinod Kumar and Lohith, me and Sudeepta(@skganguly) joined the conversation. I met Pinal Dave (@pinaldave), Lohith(@kashyapa), VijayRaj(@msigeek) ,Sudeepta(@skganguly), Dr.Pavanaja(@pavanaja), Balmukund(@blakhani) , AmalDev (@amaldevv)  , Kamlesh Rao(@kamleshrao ), Praveen (@stpraveen1 ), Sandeep Pani(@sandippani),  Rajesh Patel (@PatelRajeshK ) , Gopal Debta and …. at #ReLiveTechEdIn

Vinod Kumar During the session. A complete DEMO based sessions.

556125_3749185171535_1333515319_33612885_191408406_n 532834_3749219412391_1333515319_33612947_1999688312_n (1)

Pinal Dave gave a small presentation on “Who is Vinod” and we all got to know about the achievements and contribution by Vinod for the community till date. Kamlesh Rao twitted saying “A presentation about SQL Server Agent Vinod Kumar by Pinal Dave” :). Vinod is more than a Brother, Friend, Philosopher, Sensei, motivator and guru for all.

305938_3749205132034_1333515319_33612916_551685971_n Pinal_pavanaja

Lohith addressing the audience

Sharing TECHED experience

532447_3749225972555_1333515319_33612965_2146055826_n 540602_3749198731874_1333515319_33612903_1663076959_n

Kaun hai udhar??

Jokes and networking

538380_3749224772525_1333515319_33612960_1593092088_n528030_3749226772575_1333515319_33612971_839973522_n 522491_3749227172585_1333515319_33612972_424511810_n529357_3749223932504_1333515319_33612958_948387244_n

Goodies

Goodies

The session was very much interactive and everyone from audience participated in the discussion. There were lots of Q&A and everyone who gave correct answer got goodies from #Telerik and #Pluralsight. Also Pinal Dave distributed SQL Poster from SQLAuthority to everyone present in the room.

There were lots of goodies and excitement but without much delay Vinod started his session on the topic Sys Internals. Here are some points from the session:

  • A full demo on sysinternal tool like PAL, LogMan, VMMap, Process Explorer, Resource monitor.
  • It was all about Tools to manage, troubleshoot and diagnose your windows systems.
  • Disadvantage of PerfMon is that its difficult to decipher, PAL is an alternative.
  • Powershell is used behind the screen to run PAL.
  • Logman can convert from one format to another.
  • Each process has 4GB and 2GB of that is taken by kernel.
  • WOW is Windows on Windows.
  • If you see an yellow color in Proc Explorer means its running a .net code.
  • Virtual memory is low because the system commit limit (RAM+ page files) has been reached.
  • Virtual allocation will happen before physical memory allocation
  • GPU Usage
  • To know about the processor family on your pc/laptop Goto Run. Type msinfo32 to know about your processor family.

image GpuusageKamlesh

 

SQL server 2012 changes on Storage, Security, Checkpoint & memory.

 

Ask Vinod on SQLServer, Intel Architecture and Memory Internals I bet you will be satisfied with the answers. Some point from the session were

  • How to change the target recovery time of a SQL Server database in SQL Server 2012 and the default target recovery time is 0
  • A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash(as told in MSDN).
  • Internal checkpoint is different from Indirect Checkpoint

QUIZ Time

  1. what is the different between Virtual Size, Private Bytes and Working Set on Process Explorer tool
  2. what is virtual address & physical address?
  3. what is cluster and sector?
  4. What is transaction log used for? Is that get corrupted?
  5. Can you have more than 8K pages in the buffer pool?
  6. Is the max. memory setting on your SQL Server accurate?
  7. What’s a 3GB switch ?
    Links
    Photographs , Vinod’s Blog, Sudeepta’s Blog, Praveen’s Blog, Nakul’s Blog, BDOTNET, BITPRO

    There was a 128bit joke came out from the session. If you have missed it …you have missed it. Don’t miss next time because….

     

    UG Meet teen cheeso se chalta hai  Share, Learn and Network  ya phir Knowledge, Goodies aur demo.

    Be helpful.When you see a person without a smile ,Give him yours.

    ~zig zigler

    http://beyondrelational.com/members/simplymanas/default.aspx

    #JustLearned : Checking the Job Schedule status of a SQL job

    We check daily and weekly jobs schedule status that has been scheduled in SQL server in many servers for different projects. I wrote some queries to get the status as below.(if you have better solution please comment)

    We can use a readymade solution SPHELP_JOBSCHEDULE store procedure which takes jobid, jobname, schedulename, scheduleid and schedule descirption. But this will return multiple columns and what if we have to query only the enabled status of a schedule.

    So the other approach may be to query the MSDB table DBO.SYSSCHEDULES which will give us the ‘enabled’ column from which we can get the Schedule status of the job. For more information we can join the dbo.sysjobschedules and dbo.SYSJOBS.

    SELECT enabled as JobScheuduledStatus FROM DBO.SYSSCHEDULES WHERE [NAME]=’JSCH_HR600d’

    *this one is already published in BeyondRelational.com

    Reliving TechEd India 2012 with Vinod Kumar

    bdotnetBitpro1  together are presenting a UG Meet where you will undergo the experience of TECHED2012 again(Two of the great sessions). Also its a chance for those who missed it during TechEdIn. You can have a chance to meet MVPs, UG Leads and community members. Chances to win some cool #goodies and #educational materials.

    Reliving TechEd 2012 with Vinod Kumar 1

    1. Understanding Windows

    with SysInternals Tools

     

    2. Peeling the Onion:

    SQL Server Internals Demystified.

     Key-points of discussion

    • Memory is one of the key parameters for having a healthy SQL Server performance.
    • Recovery time is important and not your backup time.
    • Disk, Security, Checkpoint and memory are the key for SQL Server internals.
    • Performance, scalability and more worst how does OS work.
    • Where is my memory gone, why does Windows behave in this way?
    • On performance monitoring and insights into performance tuning.
    • The platform of Windows has many hidden gems, lesser known and lesser understood.
    • Simple tools and identify performance tuning gems.
    • Take your experience on Windows to the next level.

    Date: Saturday, April 7, 2012 (10:30am until 1:30pm)

    Venue: Microsoft, Krishna Hall, Embassy Building, 3rd Floor, Domlur.

    Event Details: https://www.facebook.com/events/139444029517882/

    PS: Session is FREE for all.

     

    Teched31 026

    Holds 26+ Microsoft Certification on various technologies till date

    Was Microsoft MVP – SQL Server for more than 3 years.

    Speaker at Technical conferences like Microsoft TechEd, MSDN, TechNet, GIDS.

    More than 13+ years in computers and database systems

    Site and blog at: http://blogs.ExtremeExperts.com

    Twitter – @vinodk_sql

    Author – SQL Server Interview Questions and Answers

    http://beyondrelational.com/members/simplymanas/default.aspx