|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Logging SQLServer Uptime and Downtime
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Logging SQLServer Uptime and Downtime
|
|
| girish 2002-11-29, 5:23 am |
| I have to write to a flat file, the timestamp on the
server machine, whenever SQL server goes down and whenever
it is started.
| |
| Allan Mitchell 2002-11-29, 6:23 am |
| You are probably best then querying the Error Logs of SQL Server along with
the System Event logs.
--
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"girish" <girish_s@infosys.com> wrote in message
news:1eefc01c29797$d51cde50$89
f82ecf@TK2MSFTNGXA01...
> I have to write to a flat file, the timestamp on the
> server machine, whenever SQL server goes down and whenever
> it is started.
| |
| girish 2002-11-29, 8:23 am |
| How do we search for the strings in the log file and take
it to a table ?
>-----Original Message-----
>You are probably best then querying the Error Logs of SQL
Server along with
>the System Event logs.
>
>
>
>--
>
>
>
>Allan Mitchell (Microsoft SQL Server MVP)
>www.SQLDTS.com
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>
>
>"girish" <girish_s@infosys.com> wrote in message
> news:1eefc01c29797$d51cde50$89
f82ecf@TK2MSFTNGXA01...
>> I have to write to a flat file, the timestamp on the
>> server machine, whenever SQL server goes down and
whenever
>> it is started.
>
>
>.
>
| |
| Jasper Smith 2002-11-29, 11:23 am |
| You could use a procedure like the one below
I tried this as a startup procedure but it doesn't seem to
work so the best you could do is set up a job to execute
this procedure and for it's schedule set it to execute when
SQL Agent starts. This assumes you always start SQL
Agent as well as SQL Service. I will try and figure out why it
doesn't work as a startup procedure as ideally that would
work the best, the output in the file will be similar to
2002-11-29 16:14:44.21,SQLShutdown
2002-11-29 16:14:54.12,SQLStartup
/* procedure below */
use master
go
if object_id('sp_serveruptime') is not null
drop procedure sp_serveruptime
go
create procedure sp_serveruptime
as
set nocount on
declare @logfile varchar(255)
declare @shutdown varchar(50)
declare @startup varchar(50)
declare @separator char(1)
declare @writecmd varchar(500)
--change these if required
set @logfile = 'c:\sqluptime.log'
set @separator = ','
create table #errorlog(colid int identity(1,1),
logtext varchar(1000),
cont_row int)
--Get the last shutdown date
insert #errorlog(logtext,cont_row)
exec master.dbo.xp_readerrorlog 1
select @shutdown = left(logtext,22) + @separator + 'SQLShutdown'
from #errorlog where colid=(select max(colid) from #errorlog)
truncate table #errorlog
--Get the latest startup date
insert #errorlog(logtext,cont_row)
exec master.dbo.xp_readerrorlog
select @startup = left(logtext,22) + @separator + 'SQLStartup'
from #errorlog where colid=(select min(colid) from #errorlog)
drop table #errorlog
--write to the log file
set @writecmd = 'echo ' + @shutdown + ' >>' + @logfile
exec master.dbo.xp_cmdshell @writecmd,no_output
set @writecmd = 'echo ' + @startup + ' >>' + @logfile
exec master.dbo.xp_cmdshell @writecmd,no_output
return
go
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"girish" <girish_s@infosys.com> wrote in message
news:1ed2901c297af$2d96e550$8d
f82ecf@TK2MSFTNGXA02...
> How do we search for the strings in the log file and take
> it to a table ?
>
>
> >-----Original Message-----
> >You are probably best then querying the Error Logs of SQL
> Server along with
> >the System Event logs.
> >
> >
> >
> >--
> >
> >
> >
> >Allan Mitchell (Microsoft SQL Server MVP)
> >www.SQLDTS.com
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >
> >"girish" <girish_s@infosys.com> wrote in message
> > news:1eefc01c29797$d51cde50$89
f82ecf@TK2MSFTNGXA01...
> >> I have to write to a flat file, the timestamp on the
> >> server machine, whenever SQL server goes down and
> whenever
> >> it is started.
> >
> >
> >.
> >
|
|
|
|
|