SQL Server downtimes

Question

SQL Server downtimes

jcarroyo

Grasshopper

Points: 22
More actions
September 24, 2013 at 6:59 pm

#277484

Hello team,
Sorry if duplicated, is it possible to get ride of how many times an instance has been restarted? I need this for an Up-time report, so in a month I need to validate how many days or hours an instance was up.
I know is very easy to get the time when the instance was last restarted, I can also check with the Xp_readerrorlog the details when an instance was shutdown, but I can't figure out how to get the report, is probably tricky.
Any help or comments will be really appreciated.
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

I did a similar thing some time ago and while I don't have all the scripts available right now, just let me explain my approach.

First I created a table to store the data. Just 3 columns Servername, Shutdowntime and StartTime should be enough.

Then I create a stored procedure which would read from the previous errorlog for the time of the shutdown and insert this together with the current time (GETDATE()) into the table.

Then use sp_procoption to make your procedure a startup proc, which executes automatically everytime the server starts. Alternatively you could create a SQLAgent job which executes on startup, but then you need to add some code in your procedure to check if the sql server was stopped and started or just the SQL Agent.

Once you have your data collected you can easily calculate the downtime and create a nice report for availability.

Hope this helps

[font="Verdana"]Markus Bohse[/font]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

First of all, this proc is NOT mine but ran across it here on SSC

CREATE PROC [dbo].[SQL_Last_Started]

/* Brian K. McDonald, MCDBA, MCSD

Business Intelligence Consultant Pragmatic Works

http://www.sqlservercentral.com/blogs/briankmcdonald/archive/2010/10/16/when-was-sql-server-restarted_3F00_.aspx */

AS

INSERT INTO dbo.StartHistory

SELECT create_date AS SQL_Last_Started FROM sys.databases WHERE name = 'tempdb'

GO

EXEC sp_procoption N'[dbo].[SQL_Last_Started]', 'startup', '1'

USE [master]

GO

/****** Object: Table [dbo].[StartHistory] Script Date: 9/25/2013 8:17:34 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[StartHistory](

[Started] [datetime] NULL,

[ID] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

jcarroyo Grasshopper Points: 22 More actions · Answer 3

Thank you friends, really appreciate your help.

Let me give it a try.

Best regards,