August 12, 2015 at 8:30 am
Hi all
Been practicing DR strategies with a test SQL instance by following the scenarios listed here:
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
> Took a backup of the Model database
> Stopped SQL Server
> Deleted model database data & log file
> Started SQL Server and it obviously wouldn't start because TempDB needs a model database present.
> Started SQL instance with trace flags 3608 & 3609
> Connected to SQL instance using command prompt.
> Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended.
Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (57:256:347). Restore from a backup of the database, or repair the database.
2015-08-12 16:21:36.88 spid51 Error: 3313, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 During redoing of a logged operation in database 'tempdb', an error occurred at log record ID (59:136:21). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Any ideas?
Thanks
August 12, 2015 at 8:47 am
What's the command line you're using to start SQL, and what's in the rest of the error log?
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
August 12, 2015 at 8:47 am
SQLSACT (8/12/2015)
Hi allBeen practicing DR strategies with a test SQL instance by following the scenarios listed here:
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
> Took a backup of the Model database
> Stopped SQL Server
> Deleted model database data & log file
> Started SQL Server and it obviously wouldn't start because TempDB needs a model database present.
> Started SQL instance with trace flags 3608 & 3609
> Connected to SQL instance using command prompt.
> Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended.
Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (57:256:347). Restore from a backup of the database, or repair the database.
2015-08-12 16:21:36.88 spid51 Error: 3313, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 During redoing of a logged operation in database 'tempdb', an error occurred at log record ID (59:136:21). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Any ideas?
Thanks
borrow the model db files from a working instance that is same version and collation.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 12, 2015 at 8:53 am
GilaMonster (8/12/2015)
What's the command line you're using to start SQL, and what's in the rest of the error log?
net start MSSQLSERVER /T3608 /T3609
I tried using the SQLServr.exe -T3608 -T3609 which yielded the same result.
This is the rest of the error log:
2015-08-12 16:12:06.72 Server Authentication mode is MIXED.
2015-08-12 16:12:06.73 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2015-08-12 16:12:06.73 Server The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
2015-08-12 16:12:06.73 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2015-08-12 16:12:06.73 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
-T 3608
-T 3609
2015-08-12 16:12:07.17 Server SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2015-08-12 16:12:07.17 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2015-08-12 16:12:07.17 Server Detected 3032 MB of RAM. This is an informational message; no user action is required.
2015-08-12 16:12:07.17 Server Using conventional memory in the memory manager.
2015-08-12 16:12:07.42 Server This instance of SQL Server last reported using a process ID of 696 at 8/12/2015 4:09:54 PM (local) 8/12/2015 2:09:54 PM (UTC). This is an informational message only; no user action is required.
2015-08-12 16:12:07.43 Server Node configuration: node 0: CPU mask: 0x00000003:0 Active CPU mask: 0x00000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2015-08-12 16:12:07.43 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2015-08-12 16:12:07.44 Server Database Mirroring Transport is disabled in the endpoint configuration.
2015-08-12 16:12:07.44 Server Software Usage Metrics is disabled.
2015-08-12 16:12:07.45 spid4s Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.
2015-08-12 16:12:07.45 spid4s Starting up database 'master'.
2015-08-12 16:12:07.55 spid4s 2 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2015-08-12 16:12:07.55 spid4s Snapshot isolation or read committed snapshot is not available in database 'master' because SQL Server was started with one or more undocumented trace flags that prevent enabling database for versioning. Transaction started with snapshot isolation will fail and a query running under read committed snapshot will succeed but will resort back to lock based read committed.
2015-08-12 16:12:07.55 spid4s Snapshot isolation or read committed snapshot is not available in database 'master' because SQL Server was started with one or more undocumented trace flags that prevent enabling database for versioning. Transaction started with snapshot isolation will fail and a query running under read committed snapshot will succeed but will resort back to lock based read committed.
2015-08-12 16:12:07.57 Server CLR version v4.0.30319 loaded.
2015-08-12 16:12:07.61 spid4s Snapshot isolation or read committed snapshot is not available in database 'master' because SQL Server was started with one or more undocumented trace flags that prevent enabling database for versioning. Transaction started with snapshot isolation will fail and a query running under read committed snapshot will succeed but will resort back to lock based read committed.
2015-08-12 16:12:07.61 spid4s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2015-08-12 16:12:07.62 spid4s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2015-08-12 16:12:07.73 Server Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework\v4.0.30319\.
2015-08-12 16:12:08.24 spid4s Resource governor reconfiguration succeeded.
2015-08-12 16:12:08.24 spid4s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2015-08-12 16:12:08.24 spid4s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2015-08-12 16:12:08.33 spid4s SQL Trace ID 1 was started by login "sa".
2015-08-12 16:12:08.35 spid4s Server name is 'namenamename'. This is an informational message only. No user action is required.
2015-08-12 16:12:09.08 spid11s A self-generated certificate was successfully loaded for encryption.
2015-08-12 16:12:09.10 spid11s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2015-08-12 16:12:09.10 spid11s Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2015-08-12 16:12:09.18 Server Server is listening on [ ::1 <ipv6> 1434].
2015-08-12 16:12:09.21 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2015-08-12 16:12:09.21 Server Dedicated admin connection support was established for listening locally on port 1434.
2015-08-12 16:12:09.23 spid11s Recovery is complete. This is an informational message only. No user action is required.
2015-08-12 16:12:09.23 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2015-08-12 16:12:09.23 spid11s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2015-08-12 16:12:10.31 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/namenamename.avinet.co.za ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2015-08-12 16:13:18.06 spid6s 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): 4284, committed (KB): 39932, memory utilization: 10%.
2015-08-12 16:18:46.96 spid6s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 11100, committed (KB): 40004, memory utilization: 27%.
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (57:256:347). Restore from a backup of the database, or repair the database.
2015-08-12 16:21:36.88 spid51 Error: 3313, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 During redoing of a logged operation in database 'tempdb', an error occurred at log record ID (59:136:21). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
2015-08-12 16:21:36.88 spid51 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I know I can get it to work by copying the data and log files from a SQL Server with the same version but I really wanna get it to work this way as well.
August 12, 2015 at 9:00 am
Copy the files from a working instance, then restore.
TempDB can't always be recovered, because it's not designed to be recovered on startup, but that's what the traceflag forces. So when you get a case where model's bad and TempDB can't be recovered, you have to use files from another instance (same version and SP) to get SQL started.
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
August 13, 2015 at 12:01 am
GilaMonster (8/12/2015)
Copy the files from a working instance, then restore.TempDB can't always be recovered, because it's not designed to be recovered on startup, but that's what the traceflag forces. So when you get a case where model's bad and TempDB can't be recovered, you have to use files from another instance (same version and SP) to get SQL started.
Thanks
Managed to sort it out albeit a bit messy.
On this particular instance, TempDB was not in the default location (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA). I remembered that I had moved it to another location and added another date file. So this is what I did....
> Started SQL with trace flag 3608
> Dropped extra TempDB file and moved tempdev and templog to the default location (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA) with alter database TempDB modify file commands
> Stopped SQL
> Started SQL with trace flags 3608 & 3609
> Restored Model database - it complained a bit and left the model database in single_user mode.
> Stopped SQL and re-started as normal with no trace flags
> Set model database to multi_user
Could you perhaps shed some clarity on why I had to follow this procedure to get model restored?
August 13, 2015 at 5:08 am
SQLSACT (8/13/2015)
Could you perhaps shed some clarity on why I had to follow this procedure to get model restored?
Because you didn't copy the files from a working instance as suggested 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2015 at 5:18 am
Perry Whittle (8/13/2015)
SQLSACT (8/13/2015)
Could you perhaps shed some clarity on why I had to follow this procedure to get model restored?Because you didn't copy the files from a working instance as suggested 😉
I want it to work THIS way :angry:
August 13, 2015 at 5:49 am
SQLSACT (8/13/2015)
Perry Whittle (8/13/2015)
SQLSACT (8/13/2015)
Could you perhaps shed some clarity on why I had to follow this procedure to get model restored?Because you didn't copy the files from a working instance as suggested 😉
I want it to work THIS way :angry:
Why?
The goal is to get a working SQL instance. Copying files achieves that. In a disaster situation you don't demand that one option works, anything that gets SQL back up and running is fine. Copying files does that.
Personally I'd use the traceflag and restore if either I didn't have another instance handy. You should be familiar with both, so you can chose the best method, and so that you can switch to an alternative method if one gives problems.
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
August 13, 2015 at 6:03 am
GilaMonster (8/13/2015)
SQLSACT (8/13/2015)
Perry Whittle (8/13/2015)
SQLSACT (8/13/2015)
Could you perhaps shed some clarity on why I had to follow this procedure to get model restored?Because you didn't copy the files from a working instance as suggested 😉
I want it to work THIS way :angry:
Why?
The goal is to get a working SQL instance. Copying files achieves that. In a disaster situation you don't demand that one option works, anything that gets SQL back up and running is fine. Copying files does that.
Personally I'd use the traceflag and restore if either I didn't have another instance handy. You should be familiar with both, so you can chose the best method, and so that you can switch to an alternative method if one gives problems.
The goal is to get a working SQL instance. Copying files achieves that. In a disaster situation you don't demand that one option works, anything that gets SQL back up and running is fine. Copying files does that.
I completely agree with this.
I've followed your article a bunch of times.
You should be familiar with both
This is why I posted in the first place. The 2nd, albeit much longer method was not working for me on my test.
August 13, 2015 at 6:08 am
Yes, and as I mentioned earlier, there may be cases where it doesn't work because TempDB is not designed to always be recoverable.
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
August 13, 2015 at 6:15 am
GilaMonster (8/13/2015)
Yes, and as I mentioned earlier, there may be cases where it doesn't work because TempDB is not designed to always be recoverable.
Cool
What also worked for me in this case was moving the TempDB files to another location (via cut & paste, not alter database TempDB modify file) and then running the restore method.
Doing that, I was met with this:
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.
MSSQLSERVER\MSSQL\DATA\tempdb.mdf". Operating system error 2: "2(The system cann
ot find the file specified.)".
Msg 5120, Level 16, State 101, Server ServerName, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL11.
MSSQLSERVER\MSSQL\DATA\model.mdf". Operating system error 2: "2(The system canno
t find the file specified.)".
File activation failure. The physical file name "C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf" may be incorrect.
Processed 264 pages for database 'model', file 'modeldev' on file 1.
Processed 3 pages for database 'model', file 'modellog' on file 1.
Msg 3165, Level 16, State 1, Server ServerName, Line 1
Database 'model' was restored, however an error was encountered while replicatio
n was being restored/removed. The database has been left offline. See the topic
MSSQL_ENG003165 in SQL Server Books Online.
Msg 3013, Level 16, State 1, Server ServerName, Line 1
RESTORE DATABASE is terminating abnormally.
I then set model to Online and all was fine.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply