Would like to add data in table when sql m/c starts or shutdown

Question

Would like to add data in table when sql m/c starts or shutdown

harsimranjeetsinghwasson

Ten Centuries

Points: 1231
More actions
August 29, 2013 at 5:04 am

#281741

I would like to add data like time in a table in sqlserver when sqlserver machine is started or shutdown.

Viewing 12 posts - 1 through 11 (of 11 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

To write data when the instance is started you can create a job with a schedule to be executed "Start automatically when SQL Agent starts". Maybe you should include some logic to read the SQL log and read the time the SQL Engine was started. This prevents from false entries when only the SQL Agent service was restarted.

There is no way of writing data when an instance is shutting down. When an instance is shutting down all activities are aborted. Look it at this way: when a power failure occurs the server is shut down immediately, there is no time to do anything.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

HanShi (8/29/2013)
To write data when the instance is started you can create a job with a schedule to be executed "Start automatically when SQL Agent starts". Maybe you should include some logic to read the SQL log and read the time the SQL Engine was started. This prevents from false entries when only the SQL Agent service was restarted.
There is no way of writing data when an instance is shutting down. When an instance is shutting down all activities are aborted. Look it at this way: when a power failure occurs the server is shut down immediately, there is no time to do anything.

You could also execute a stored procedure on startup.

sp_procoption (Transact-SQL)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

If you want logging of the startup and shutdown time of an instance, consider the following:

You can setup another server/instance and poll the connectivity to the required instance(s) every minute or so. Write the results of this polling to a table. This way you will have logging of the availability of the required instance(s). You can then create queries/reports to report about the percentage uptime over a specific period of time.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

Koen Verbeeck (8/29/2013)
You could also execute a stored procedure on startup.
sp_procoption (Transact-SQL)

That's new to me. Thanks, I learned something 😉

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

harsimranjeetsinghwasson (8/29/2013)
I would like to add data like time in a table in sqlserver when sqlserver machine is started or shutdown.

This information is recorded in the SQL Server logs and you can get the information pretty easily using sp_ReadErrorLog. I wouldn't add what you're taking about to the startup because if someone screws up and deletes the proc or the related table, it could cause SQL Server to fail. Further, adding a startup procedure won't record when the server shuts down.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 6

HI Koen,

Do i need to schedule the same as i tried to add my procedure created in master database but logging the time in table of other user defined database on same server , not reflecting any data. :doze:

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

harsimranjeetsinghwasson (8/30/2013)
HI Koen,
Do i need to schedule the same as i tried to add my procedure created in master database but logging the time in table of other user defined database on same server , not reflecting any data. :doze:

What are you doing inside the stored proc?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 8

:unsure: I guess table to log data should also be created in master only , because when i did so i started logging data.

One thing i would like to ask is about "OFF" value for option "startup" in sp_procoption. Is that related to service stop event as when i did the proc called has not update row with timestamp.

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 9

I am loggin my checkin and checkout time from office in a table , checkin is working fine but checkout depends when i shutdown my system any clue for same and last question i ask about "off" , is cleared to me and seems like it is basically related to procedure on and off on startup event .

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

harsimranjeetsinghwasson (8/30/2013)
:unsure: I guess table to log data should also be created in master only , because when i did so i started logging data.
One thing i would like to ask is about "OFF" value for option "startup" in sp_procoption. Is that related to service stop event as when i did the proc called has not update row with timestamp.

No. Like I said, I believe the only way you'll be able to record the "OFF" is to get it from the SQL Logs using sp_ReadErrorLog.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 11

So when you finish for the day you shut down SQL? If this is just to record that then how are you shutting down SQL?

If just through the services applet, command window or SSMS, replace this with a .bat file to run which uses sqlcmd to log an entry in your table, and then does a net stop on the SQL service. Use this .bat file to stop SQL.

this could also be done via powershell.

---------------------------------------------------------------------