February 14, 2011 at 7:46 am
In your SSMS, under Management you will see SQL Server Logs. By default there will be 6 of them, with the most current one being labled as such.
Review the entries of this one for any errors.
Ask your networking people to review the event logs on the server. It would be beneficial for you to be standing there watching so that you can see how as well as what it says.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 14, 2011 at 7:51 am
mw112009 (2/14/2011)
Let me make it simple.When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
Did you log off after starting the restore?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 7:57 am
mw112009 (2/14/2011)
Let me make it simple.When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
However, when I looked at the directories the restored files were there. When I started SSMS it shows the newdatabase ( but with a green arrow ) and a message saying "restoring..."
Ok, I need help on what SQL to run to check the interruptions you mentioned in your reply.
I have nto used that system view before.
It's pretty easy. Run this query:
SELECT der.start_time,
der.database_id,
der.blocking_session_id,
der.wait_type,
der.wait_time,
der.last_wait_type,
der.wait_resource,
der.percent_complete,
der.cpu_time,
der.total_elapsed_time,
der.reads,
der.writes
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.is_user_process = 1
that should be enough to get you started understanding what's going on with your system. You'll get all user connections with this. To filter down you have to look into adding other DMVs into the mix. If this doesn't show you what's going on in a way that helps, let me know and I'll show you how you could filter it down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 8:06 am
GilaMonster (2/14/2011)
mw112009 (2/14/2011)
Let me make it simple.When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
Did you log off after starting the restore?
1.) I did not logout from the server
2.) Clicked on START->WINDOWS SECURITY and then clicked on 'Lock Computer"
February 14, 2011 at 8:10 am
Here is the plan as of now..
One of our gurus uninstalled Sql server and then did a fresh installation.
We just started a new "RESTORE..." command. I wil get back with you
3 ( or 4-5 ) hours later. Thanks for now.
February 14, 2011 at 9:12 am
Grant Fritchey (2/14/2011)
mw112009 (2/14/2011)
Let me make it simple.When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
However, when I looked at the directories the restored files were there. When I started SSMS it shows the newdatabase ( but with a green arrow ) and a message saying "restoring..."
Ok, I need help on what SQL to run to check the interruptions you mentioned in your reply.
I have nto used that system view before.
It's pretty easy. Run this query:
SELECT der.start_time,der.database_id,
der.blocking_session_id,
der.wait_type,
der.wait_time,
der.last_wait_type,
der.wait_resource,
der.percent_complete,
der.cpu_time,
der.total_elapsed_time,
der.reads,
der.writes
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.is_user_process = 1
that should be enough to get you started understanding what's going on with your system. You'll get all user connections with this. To filter down you have to look into adding other DMVs into the mix. If this doesn't show you what's going on in a way that helps, let me know and I'll show you how you could filter it down.
I ran this query in the prod environment ( just to see what info it gives )
Wat I noticed was with each run the output gives a different number of rows.
OK, so you are telling me if the next restore gets stuck ( or is still running after 2-3 hours )
this query will tell us what is blocking the restore process. Is that right ?
February 14, 2011 at 9:20 am
mw112009 (2/14/2011)
Grant Fritchey (2/14/2011)
mw112009 (2/14/2011)
Let me make it simple.When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
However, when I looked at the directories the restored files were there. When I started SSMS it shows the newdatabase ( but with a green arrow ) and a message saying "restoring..."
Ok, I need help on what SQL to run to check the interruptions you mentioned in your reply.
I have nto used that system view before.
It's pretty easy. Run this query:
SELECT der.start_time,der.database_id,
der.blocking_session_id,
der.wait_type,
der.wait_time,
der.last_wait_type,
der.wait_resource,
der.percent_complete,
der.cpu_time,
der.total_elapsed_time,
der.reads,
der.writes
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.is_user_process = 1
that should be enough to get you started understanding what's going on with your system. You'll get all user connections with this. To filter down you have to look into adding other DMVs into the mix. If this doesn't show you what's going on in a way that helps, let me know and I'll show you how you could filter it down.
I ran this query in the prod environment ( just to see what info it gives )
Wat I noticed was with each run the output gives a different number of rows.
OK, so you are telling me if the next restore gets stuck ( or is still running after 2-3 hours )
this query will tell us what is blocking the restore process. Is that right ?
Yes, you'll see different values each time your run it because it is showing what is active when you run it. Things change, transactions complete, and close, etc., so yes, different rows each time.
If anything is blocking the restore or if it is waiting on CPU or whatever, it should show up here, yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 9:25 am
Our new retore attempt has taken 1 hour and 17 minutes so far. I am going to give this 2 hours at least and check again.
Question: Can we run this query while a restore is taking place ?
February 14, 2011 at 10:32 am
mw112009 (2/14/2011)
Our new retore attempt has taken 1 hour and 17 minutes so far. I am going to give this 2 hours at least and check again.Question: Can we run this query while a restore is taking place ?
Absolutely! This is a very lightweight query going against system views. It won't hurt stuff as it's currently configured.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 11:25 am
I ran the query and see the results ( attached )
Please see attached
February 14, 2011 at 12:04 pm
Do you think there is something wrong with the backup file.
Please take a look at the attached images to see the results after
running the
RESTORE HEADERONLY
and
RESTORE FILELISTONLY commands
February 14, 2011 at 12:06 pm
Grant Fritchey (2/14/2011)
mw112009 (2/14/2011)
Our new retore attempt has taken 1 hour and 17 minutes so far. I am going to give this 2 hours at least and check again.Question: Can we run this query while a restore is taking place ?
Absolutely! This is a very lightweight query going against system views. It won't hurt stuff as it's currently configured.
Looks like the backup is running, it's just taking a long time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 12:06 pm
Crud. I mean restore. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 12:18 pm
After about 2 hours the result was the same. When I logged in I did not see SSMS running.
Why did it shut down on it's own ?
February 14, 2011 at 12:25 pm
What is in the error log and the event viewer for the server? We need more information otherwise it is solely speculation.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply