Pausing

Question

Pausing

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
September 16, 2014 at 11:23 pm

#74501

Comments posted to this topic are about the item Pausing
September 17, 2014 at 3:59 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

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

karthik babu Hall of Fame Points: 3335 More actions · Answer 1

Few days back I read this in the same SSC I guess. And it was the one from where I came to know the answer. SSC Rocks!! 🙂

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 2

Hmm. Good one...

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

karthik babu (9/16/2014)
Few days back I read this in the same SSC I guess. And it was the one from where I came to know the answer. SSC Rocks!! 🙂

+1, Nice question. Thanks for sharing

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

Interesting question, thanks.

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 5

Nice one, thank you, and the article is prety strighforward

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

I should have went with my 1st guess :angry: but then decided to try all options one by one on my testing server:

1. No query was paused, all working fine.

2. The server didn`t continue operating as normal (as I tried to do some GUI staff in ssms & it was crashing because of the service being Paused) only T-SQL was working fine.

3. Couldn`t connect even with DAC.

4. Tried to backup some db, found it working fine.

So picked the wrong answer, should read the options carefully & should take my coffee before answering 🙁

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SqlMel SSCrazy Points: 2901 More actions · Answer 7

This was a good question, Steve. Thanks.

---------------
Mel. 😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

That was a really good question. I had to research it to find the right answer, so thanks for teaching me something new today.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

daniel.mallott SSCommitted Points: 1768 More actions · Answer 9

daniel.mallott

SSCommitted

Points: 1768

September 17, 2014 at 6:14 am

#1746024

Thanks for teaching me something new!

doug.davidson SSCommitted Points: 1662 More actions · Answer 10

Please, clean up the question wording.

It was a little confusing differentiating between the "database" which cannot be paused; and the "database engine" which can be paused. The pause of the database engine is the same act as pausing the SQL Server (MSSQLSERVER) service via Services.msc.

FYI, I got it wrong because I do not believe everything is normal when the service is paused. If the service paused, everything using this service will "break".

sestell1 SSChampion Points: 10230 More actions · Answer 11

Hany Helmy (9/17/2014)
I should have went with my 1st guess :angry: but then decided to try all options one by one on my testing server:
1. No query was paused, all working fine.
2. The server didn`t continue operating as normal (as I tried to do some GUI staff in ssms & it was crashing because of the service being Paused) only T-SQL was working fine.
3. Couldn`t connect even with DAC.
4. Tried to backup some db, found it working fine.
So picked the wrong answer, should read the options carefully & should take my coffee before answering 🙁

Interesting to know you can't start a new DAC session when the service is paused.

I was wondering about that. Thanks Hany Helmy!

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 12

Nice question, Steve, thanks.

It's interesting that the article doesn't have a counterpart in versions of BOL later than SQL Server 2008 R2. The "Other Versions" drop-down only shows "SQL Server 2008" and "SQL Server 2005".

timwell SSCertifiable Points: 5315 More actions · Answer 13

stephen.long.1 (9/17/2014)
It's interesting that the article doesn't have a counterpart in versions of BOL later than SQL Server 2008 R2. The "Other Versions" drop-down only shows "SQL Server 2008" and "SQL Server 2005".

My search of MSDN found this article http://msdn.microsoft.com/en-us/library/hh403394(v=sql.120).aspx which only shows 2012 and 2014 in under "Other Versions."

It does include start and stop which may be the difference. Also it has the same explanation of what happens when you pause:

"Pausing the Database Engine service prevents new users from connecting to the Database Engine, but users who are already connected can continue to work until their connections are broken."

Thanks for the question. I have learned a lot from the QOTD even if I don't get it right all the time...