SQL Server Log

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rubens - 25 Mar 2008 15:43 GMT

Where can I query the SQL Server Log? I'd like to see the Database Mirroring entries using T-SQL, not the log viewer.

Thank-you,
Rubens

Reply to this Message

Linchi Shea - 25 Mar 2008 16:56 GMT

If you are talking about the SQL Server errorlog, it's not exactly query-able
via T-SQL. That said, you do have an option of using an undocumented extended
stored procedure xp_readerrorlog.

I don't know exactly you meant by 'see the Database Mirroring entries', but
why not just open the errorlog file with Notepad?

Linchi

> Where can I query the SQL Server Log? I'd like to see the Database Mirroring entries using T-SQL, not the log viewer.
>
> Thank-you,
> Rubens

Reply to this Message

Rubens - 25 Mar 2008 23:57 GMT

Hmmm, ok... I don't think I want to see the SQL Server error log.

In a nutshell, I am setting up database mirroring for the first time, so I
am creating my own document with all the scripts included for how this is
done (specific to our organization). When you setup mirroring you can look
at the current SQL Server log file and see entries such as:

spid21s, Unknown, Database mirroring is active with database
'SQLTESTING_DBMIRROR' as the principal copy. This is an information message
only. No user action is required.
spid12s, Unknown, The Database Mirroring protocol transport is now listening
for connections.

I wanted to include a script in my document to pull these entries, just as a
confirmation that database mirroring is active. It's not a necessity
because it can be done through the current SQL Server log, but I just
thought it would be nice to have everything script based.

I will poke around with xp_readerrorlog and see if that has anything along
the lines of what I need.

Thank-you Linchi.

Rubens

> If you are talking about the SQL Server errorlog, it's not exactly
> query-able
[quoted text clipped - 13 lines]
>> Thank-you,
>> Rubens

Reply to this Message

Rubens - 26 Mar 2008 00:08 GMT

Bingo, xp_readerrorlog shows me those entries! That's pretty much what I
was looking for. Not a biggy, but is there any way to filter records with
xp_readerrorlog?

Thanks Linchi... as always!

Rubens

> Hmmm, ok... I don't think I want to see the SQL Server error log.
>
[quoted text clipped - 38 lines]
>>> Thank-you,
>>> Rubens

Reply to this Message

Andrew J. Kelly - 26 Mar 2008 00:45 GMT

sp_readerror log calls the up so have a look at this:

http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Bingo, xp_readerrorlog shows me those entries! That's pretty much what I
> was looking for. Not a biggy, but is there any way to filter records with
[quoted text clipped - 46 lines]
>>>> Thank-you,
>>>> Rubens

Reply to this Message

Uri Dimant - 26 Mar 2008 10:16 GMT

Rubens
DECLARE @lookfor varchar(25)

Declare @cmd Nvarchar(4000)
set @lookfor = 'SQL'

set @cmd = 'FINDSTR /I /C:"'+@lookfor+'" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG1"'

exec master..xp_cmdshell @cmd

Where can I query the SQL Server Log? I'd like to see the Database Mirroring entries using T-SQL, not the log viewer.

Thank-you,
Rubens

Reply to this Message

Rubens - 26 Mar 2008 20:44 GMT

Thanks Andrew and Uri.

Uri, this doesn't appear to work with SQL 2005. I tried searching for a bunch of different strings, but it returns null. Any idea?

Thank-you,
Rubens
Rubens
DECLARE @lookfor varchar(25)

Declare @cmd Nvarchar(4000)
set @lookfor = 'SQL'

set @cmd = 'FINDSTR /I /C:"'+@lookfor+'" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG1"'

exec master..xp_cmdshell @cmd

"Rubens" <rubensrose@hotmail.com> wrote in message news:%239DaWbojIHA.5084@TK2MSFTNGP04.phx.gbl...
Where can I query the SQL Server Log? I'd like to see the Database Mirroring entries using T-SQL, not the log viewer.

Thank-you,
Rubens

Reply to this Message

Uri Dimant - 27 Mar 2008 09:25 GMT

Rubens

The problem is (sorry for not mentioning that) SQL Server 2005 saves/creates the ERROR.LOG as unicode file, save it as ANSII and run again

Thanks Andrew and Uri.

Uri, this doesn't appear to work with SQL 2005. I tried searching for a bunch of different strings, but it returns null. Any idea?

Thank-you,
Rubens
"Uri Dimant" <urid@iscar.co.il> wrote in message news:OS2sYHyjIHA.2304@TK2MSFTNGP05.phx.gbl...
Rubens
DECLARE @lookfor varchar(25)

Declare @cmd Nvarchar(4000)
set @lookfor = 'SQL'

set @cmd = 'FINDSTR /I /C:"'+@lookfor+'" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG1"'

exec master..xp_cmdshell @cmd

"Rubens" <rubensrose@hotmail.com> wrote in message news:%239DaWbojIHA.5084@TK2MSFTNGP04.phx.gbl...
Where can I query the SQL Server Log? I'd like to see the Database Mirroring entries using T-SQL, not the log viewer.

Thank-you,
Rubens

Reply to this Message

Rubens - 28 Mar 2008 22:33 GMT

No problem Uri, will give that a shot.

Thank-you,
Rubens
Rubens

The problem is (sorry for not mentioning that) SQL Server 2005 saves/creates the ERROR.LOG as unicode file, save it as ANSII and run again

"Rubens" <rubensrose@hotmail.com> wrote in message news:eJ0lqo3jIHA.5208@TK2MSFTNGP04.phx.gbl...
Thanks Andrew and Uri.

Uri, this doesn't appear to work with SQL 2005. I tried searching for a bunch of different strings, but it returns null. Any idea?

Thank-you,
Rubens
"Uri Dimant" <urid@iscar.co.il> wrote in message news:OS2sYHyjIHA.2304@TK2MSFTNGP05.phx.gbl...
Rubens
DECLARE @lookfor varchar(25)

Declare @cmd Nvarchar(4000)
set @lookfor = 'SQL'

set @cmd = 'FINDSTR /I /C:"'+@lookfor+'" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG1"'

exec master..xp_cmdshell @cmd

"Rubens" <rubensrose@hotmail.com> wrote in message news:%239DaWbojIHA.5084@TK2MSFTNGP04.phx.gbl...
Where can I query the SQL Server Log? I'd like to see the Database Mirroring entries using T-SQL, not the log viewer.

Thank-you,
Rubens

Reply to this Message