February 3, 2010 at 7:51 pm
Can you provide the full T-SQL command you used when restoring the databse that is now stuck?
February 3, 2010 at 7:52 pm
If you don't hear from me right away, it's because I have to leave now and get my kid from church. I'll check back when I get home.
February 3, 2010 at 7:53 pm
I can not as this went thru SSMS =[ sorry but it was pretty straight forward.
-D-
I think this is a lost casue and i may have to do the hardcore ..stop sql and remove the mdf/ldf files and then start it back and see what happens =[
DHeath
February 3, 2010 at 8:31 pm
So any thoughts on this solution..its a bad one i know already but it may be all i have...
1. Shut down mssql server
2. Delete all corresponding mdf /ldf files
3. Start mssql server
4. Remove entries from sysdatabase
5. Hope it works... =] and MSSQL runs smoothly afterwards
Not sure if i am forgetting any cleanup if i do this manually and against all rules out there in this world... lol
-D-
DHeath
February 3, 2010 at 8:57 pm
DHeath (2/3/2010)
So any thoughts on this solution..its a bad one i know already but it may be all i have...1. Shut down mssql server
2. Delete all corresponding mdf /ldf files
3. Start mssql server
4. Remove entries from sysdatabase
5. Hope it works... =] and MSSQL runs smoothly afterwards
Not sure if i am forgetting any cleanup if i do this manually and against all rules out there in this world... lol
-D-
Here's a problem: in SQL Server 2005 you can't update system tables. You won't be able to accomplish #4.
I may be wrong, there may actually be a way around this limitation, but I sure wouldn't try it.
So, again I will ask, can you show us what you were doing when the database got stuck? This will help us figure out a solution that hopefully will work better than the sledge hammer you are looking at wielding at the moment.
February 3, 2010 at 9:19 pm
Well hmmmmmm not sure i know how to show you what i was doing because i used SSMS to do the database restore and with this all being done thru GUI there is not query to recall, sooo with that being said....
@#$#$#$##$% and @#$#%@# ok i will keep those words to myself =]
i dont know what to say at this point especially since i have no more to give you =[
-D-
DHeath
February 3, 2010 at 9:23 pm
DHeath (2/3/2010)
Well hmmmmmm not sure i know how to show you what i was doing because i used SSMS to do the database restore and with this all being done thru GUI there is not query to recall, sooo with that being said....@#$#$#$##$% and @#$#%@# ok i will keep those words to myself =]
i dont know what to say at this point especially since i have no more to give you =[
-D-
That's is one reason I may start things in the GUI, but then I script it and run it manually in SSMS.
That said, can you recreate what you did step by step in the GUI when you had the problem?
February 3, 2010 at 9:50 pm
Recreated it with the script that would have ran. Hope this helps
RESTORE DATABASE [WCC_1] FROM DISK = N'F:\Frontier_1_data\WSS_C_F_1' WITH FILE = 1,
MOVE N'WSS_C_F_1' TO N'F:\Frontier_1_data\WCC.mdf',
MOVE N'WSS_C_F_1_Data1' TO N'F:\Frontier_1_data\WCC_1.ndf',
MOVE N'WSS_C_F_1_log' TO N'F:\Frontier_1_data\WCC_2.ldf',
MOVE N'WSS_C_F_1_log1' TO N'F:\Frontier_1_data\WCC_3.ldf',
NOUNLOAD, STATS = 10
GO
-D-
DHeath
February 3, 2010 at 9:53 pm
Anything in the logs, SQL or Error?
February 3, 2010 at 10:03 pm
Here is part of the logs.. hope i give you a part that you are able to understand and tie in with the conversation..If not my apologies
Starting up database 'WSS_Frontier_Content_1'.
2010-02-02 18:20:00.84 Backup Database was restored: Database: WSS_Frontier_Content_1, creation date(time): 2010/01/13(12:01:54), first LSN: 30890:215:79, last LSN: 30890:276:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\Frontier_1_data\WSS_Content_Frontier_1'}). Informational message. No user action required.
2010-02-02 18:20:59.57 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 180004, committed (KB): 370680, memory utilization: 48%.
2010-02-02 18:24:19.61 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 189408, committed (KB): 371464, memory utilization: 50%.
2010-02-02 18:24:37.13 spid54 Starting up database 'WSS_Frontier_Content_1'.
2010-02-02 18:24:37.14 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:24:37.14 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_log\WSS_Frontier_Content_1_2.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:24:37.14 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:24:37.14 spid54 FCB::Open failed: Could not open file F:\Frontier_1_log\WSS_Frontier_Content_1_2.ldf for file number 2. OS error: 5(Access is denied.).
2010-02-02 18:24:37.17 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:24:37.17 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_log\WSS_Frontier_Content_1_3.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:24:37.17 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:24:37.17 spid54 FCB::Open failed: Could not open file F:\Frontier_1_log\WSS_Frontier_Content_1_3.ldf for file number 4. OS error: 5(Access is denied.).
2010-02-02 18:24:58.97 spid54 Starting up database 'WSS_Frontier_Content_1'.
2010-02-02 18:24:58.98 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:24:58.98 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_log\WSS_Frontier_Content_1_2.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:24:58.98 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:24:58.98 spid54 FCB::Open failed: Could not open file F:\Frontier_1_log\WSS_Frontier_Content_1_2.ldf for file number 2. OS error: 5(Access is denied.).
2010-02-02 18:24:58.98 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:24:58.98 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_log\WSS_Frontier_Content_1_3.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:24:58.98 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:24:58.98 spid54 FCB::Open failed: Could not open file F:\Frontier_1_log\WSS_Frontier_Content_1_3.ldf for file number 4. OS error: 5(Access is denied.).
2010-02-02 18:25:26.86 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 189200, committed (KB): 371048, memory utilization: 50%.
2010-02-02 18:27:14.44 spid54 Starting up database 'WSS_Frontier_Content_1'.
2010-02-02 18:27:14.45 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:27:14.45 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_data\WSS_Frontier_Content_1_2.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:27:14.45 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:27:14.45 spid54 FCB::Open failed: Could not open file F:\Frontier_1_data\WSS_Frontier_Content_1_2.ldf for file number 2. OS error: 5(Access is denied.).
2010-02-02 18:27:14.45 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:27:14.45 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_data\WSS_Frontier_Content_1_3.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:27:14.45 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:27:14.45 spid54 FCB::Open failed: Could not open file F:\Frontier_1_data\WSS_Frontier_Content_1_3.ldf for file number 4. OS error: 5(Access is denied.).
2010-02-02 18:27:14.45 spid54 SQL Server has encountered 6 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:27:14.45 spid54 SQL Server has encountered 6 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:27:14.45 spid54 SQL Server has encountered 6 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:28:57.15 spid54 Starting up database 'WSS_Frontier_Content_1'.
2010-02-02 18:28:57.16 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:28:57.16 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_data\WSS_Frontier_Content_1_2.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:28:57.16 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:28:57.16 spid54 FCB::Open failed: Could not open file F:\Frontier_1_data\WSS_Frontier_Content_1_2.ldf for file number 2. OS error: 5(Access is denied.).
2010-02-02 18:28:57.16 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:28:57.16 spid54 FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\Frontier_1_data\WSS_Frontier_Content_1_3.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:28:57.16 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:28:57.16 spid54 FCB::Open failed: Could not open file F:\Frontier_1_data\WSS_Frontier_Content_1_3.ldf for file number 4. OS error: 5(Access is denied.).
2010-02-02 18:32:38.16 spid54 Starting up database 'WSS_Content_Frontier_1'.
2010-02-02 18:32:38.17 spid54 The database 'WSS_Content_Frontier_1' is marked RESTORING and is in a state that does not allow recovery to be run.
2010-02-02 18:32:39.11 spid54 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:32:39.11 spid54 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:32:39.11 spid54 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:32:39.14 spid54 Starting up database 'WSS_Content_Frontier_1'.
2010-02-02 18:32:39.63 Backup Database was restored: Database: WSS_Content_Frontier_1, creation date(time): 2010/01/13(12:01:54), first LSN: 30890:215:79, last LSN: 30890:276:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\Frontier_1_data\WSS_Content_Frontier_1'}). Informational message. No user action required.
2010-02-02 18:37:45.87 spid54 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:37:45.87 spid54 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:37:45.87 spid54 SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-02-02 18:52:13.67 spid54 Error: 17207, Severity: 16, State: 1.
2010-02-02 18:52:13.67 spid54 FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'F:\Frontier_1_data\WSS_Content_Frontier_1.ndf'. Diagnose and correct the operating system error, and retry the operation.
2010-02-02 18:52:13.67 spid54 Error: 17204, Severity: 16, State: 1.
2010-02-02 18:52:13.67 spid54 FCB::Open failed: Could not open file F:\Frontier_1_data\WSS_Content_Frontier_1.ndf for file number 0. OS error: 2(The system cannot find the file specified.).
2010-02-02 18:53:14.41 spid54 Starting up database 'WSS_Content_Frontier_1'.
DHeath
February 3, 2010 at 10:18 pm
That is telling me the SQL Server can't find the files on disk. What exactly is the F: drive, direct attach storage or a SAN?
February 3, 2010 at 10:21 pm
SAN Storage.... but like i said..remember i redid the EXACT same thing and all of it worked well and the second database is up and functional with NO changes made. That is what throws me
-D-
DHeath
February 3, 2010 at 10:27 pm
Is the other DB on the same drive? If so, hate to ask this, but, have you tried rebooting the server?? <ducking quickly to avoid the pork chops from a far (inside joke, you need to hang around longer to learn that one)>
February 3, 2010 at 10:30 pm
Yes i have and when i do i reboot both of the server and can do it now if you like..its in a cluster... yea let me add that in there... now let me (duck and watch for the books and chairs that might be tossed =] ) Will reboot again and let see what the results are
-D- will inform you in a few then i am thinking about calling it a night... but til then brb
DHeath
February 3, 2010 at 10:37 pm
Just a tip, the more you tell us, the better help you will get. I am not working in a clustered environment. We are, finally, setting up a HA system for our Finance and HR systems but we are going to be using database mirroring.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply