Thank you all for 1000+ views!

First off, I’d like to say a big thank you for visiting my blog for more than 1000 times:

I’ve had hits from 65 countries around the world, top three being, India with 347, US with 310, UK 60.

I know that other bloggers have 1,000 views per day but for me, reaching 1,000 has made me happy and at the same time this milestone has motivated me to keep updating my blog.

Here are some blog stats.

Thank you all very much…!.

Advertisements

Quickly search SQL Server Error Log

Tags

sp_cycle_errorlog, sp_readerro

As a Part of my routine DBA activity, I have to go through sp_readerrorlog to locate errors if any. In our production environment we have have Both Failed and Successful logins auditing enabled. Hence if i run sp_readerrorlog without any filters it would return me huge no of rows and go through each and every row is a time consuming and repetitive activity.
Here i would like to share how we can quickly go through Error Log with few filters.

sp_readerrorlog takes 4 parameters to narrow down our search.

1. @p1 – Log file we want to read, 0 = current error log, 1 = Archive #1, 2 = Archive #2 etc..
2. @p2 – 1 or null = SQL Server error log, 2 = SQL Agent error log.
3. @p3 – string which we want to search.
4. @p3 – string to further refine our search.

I basically filter one day error log as below.

EXEC sp_readerrorlog 0 , null , '2014-04-13 10:00' , '2014-04-14 10:00'

You can also refine your search just to check database backups like below.

EXEC sp_readerrorlog 0 , 1 ,   'Database Mirroring' , 'error'

As sp_readerrorlog is undocumented stored procedure to review error log, you will not find any official references in Books online.

Keep your current error log under control just by recycling it, so that you can quickly retrieve its output, refer sp_cycle_errorlog.

Hope this helps…!

Quick start of SSMS

Tags

SQL shortcut, SSMS, ssms.exe

Instead of starting SSMS through Start->Programs-> or through a desktop shortcut, one can start the SSMS in a much faster way by

1. Start->RUN or WINDOWS key+R
2. Type SqlWb.exe for 2005 or SSMS.exe for later versions

3. Press enter and SSMS is opened up for you.

If you want to take this further by making use of more options such as to directly connect to the server by just mentioning server name like

If you like to know what other options are available, you can do that as follows.

this gives you a screen as below.

Hope this helps…!