Oliee D
asked on
MSSQL Error: 9002, Severity: 17, State: 2. MSQLSERVER Process shuts off
Hi our server was rebooted yesterday and when it came back online i saw the sqlserver wasn't connecting when i tried to restart the process its starts briefly then stops after a few seconds in the logs tail i see these statements. When i try to go into sql studio to try and look at the properties setting or to try and backup and truncate i cant because it will not connect to the instance since msqlserver service keeps stopping. Ive tried to put in single user mode the T flags i still cant get it to work and keep the process up so i can see whats going on. Can someone explain if they ever seen this
Error: 17053, Severity: 16, State: 1.
2015-07-20 00:08:51.34 spid9s D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 00:08:51.34 spid9s Error: 9002, Severity: 17, State: 2.
2015-07-20 00:08:51.34 spid9s The transaction log for database 'model' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2015-07-20 00:08:51.34 spid9s Could not write a checkpoint record in database ID 3 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
2015-07-20 00:08:51.34 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2015-07-20 00:08:51.34 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2015-07-20 00:08:51.45 Server The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
Error: 17053, Severity: 16, State: 1.
2015-07-20 00:08:51.34 spid9s D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 00:08:51.34 spid9s Error: 9002, Severity: 17, State: 2.
2015-07-20 00:08:51.34 spid9s The transaction log for database 'model' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2015-07-20 00:08:51.34 spid9s Could not write a checkpoint record in database ID 3 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
2015-07-20 00:08:51.34 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2015-07-20 00:08:51.34 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2015-07-20 00:08:51.45 Server The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
ASKER
I disabled the server agent and added the switch and it still wont start . It shuts down after a few seconds. There is about 45gb of free space on the drive the databses take up 225gb its a 300gb drive.
this is the full file error i get
15-07-20 07:25:52.26 Server (c) 2005 Microsoft Corporation.
2015-07-20 07:25:52.26 Server All rights reserved.
2015-07-20 07:25:52.26 Server Server process ID is 4548.
2015-07-20 07:25:52.26 Server Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\E
2015-07-20 07:25:52.28 Server This instance of SQL Server last reported using a process ID of 5576 at 7/20/2015 7:15:25 AM (local) 7/20/2015 11:15:25 AM (UTC). This is an informational message only; no user action is required.
2015-07-20 07:25:52.28 Server Registry startup parameters:
2015-07-20 07:25:52.28 Server -m
2015-07-20 07:25:52.28 Server -d D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 07:25:52.28 Server -e D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\E
2015-07-20 07:25:52.28 Server -l D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 07:25:52.28 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2015-07-20 07:25:52.28 Server Detected 4 CPUs. This is an informational message; no user action is required.
2015-07-20 07:25:52.29 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2015-07-20 07:25:52.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-07-20 07:25:52.45 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2015-07-20 07:25:54.46 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2015-07-20 07:25:54.46 Server Database Mirroring Transport is disabled in the endpoint configuration.
2015-07-20 07:25:54.46 spid5s Warning ******************
2015-07-20 07:25:54.46 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2015-07-20 07:25:54.46 spid5s Starting up database 'master'.
2015-07-20 07:25:54.62 spid5s CHECKDB for database 'master' finished without errors on 2015-07-18 00:00:03.050 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:54.63 spid5s SQL Trace ID 1 was started by login "sa".
2015-07-20 07:25:54.63 spid5s Starting up database 'mssqlsystemresource'.
2015-07-20 07:25:54.71 spid7s Starting up database 'model'.
2015-07-20 07:25:54.71 spid5s Server name is 'ICCAS2'. This is an informational message only. No user action is required.
2015-07-20 07:25:55.04 Server A self-generated certificate was successfully loaded for encryption.
2015-07-20 07:25:55.04 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSER
2015-07-20 07:25:55.04 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2015-07-20 07:25:55.04 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2015-07-20 07:25:55.04 Server Dedicated admin connection support was established for listening locally on port 1434.
2015-07-20 07:25:55.04 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2015-07-20 07:25:55.07 spid10s Starting up database 'BSRes2'.
2015-07-20 07:25:55.07 spid13s Starting up database 'Reports'.
2015-07-20 07:25:55.07 spid11s Starting up database 'msdb'.
2015-07-20 07:25:55.07 spid14s Starting up database 'SBill'.
2015-07-20 07:25:55.07 spid15s Starting up database 'IC'.
2015-07-20 07:25:55.07 spid12s Starting up database 'CDRs'.
2015-07-20 07:25:55.40 spid14s CHECKDB for database 'SBill' finished without errors on 2015-07-14 00:15:29.287 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:55.45 spid13s CHECKDB for database 'Reports' finished without errors on 2015-07-14 00:15:28.393 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:55.45 spid11s CHECKDB for database 'msdb' finished without errors on 2015-07-14 00:14:52.300 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:55.64 spid10s CHECKDB for database 'BSRes2' finished without errors on 2015-07-14 00:15:06.037 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:56.89 spid12s CHECKDB for database 'CDRs' finished without errors on 2015-07-14 00:15:16.160 (local time). This is an informational message only; no user action is required.
2015-07-20 07:26:02.70 spid15s CHECKDB for database 'IC' finished without errors on 2015-07-09 00:16:23.563 (local time). This is an informational message only; no user action is required.
2015-07-20 07:26:02.70 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2015-07-20 07:26:02.85 spid7s CHECKDB for database 'model' finished without errors on 2015-07-18 00:00:23.440 (local time). This is an informational message only; no user action is required.
2015-07-20 07:26:02.87 spid7s Clearing tempdb database.
2015-07-20 07:26:21.59 spid7s Error: 17053, Severity: 16, State: 1.
2015-07-20 07:26:21.59 spid7s D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 07:26:21.59 spid7s Error: 9002, Severity: 17, State: 2.
2015-07-20 07:26:21.59 spid7s The transaction log for database 'model' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2015-07-20 07:26:21.59 spid7s Could not write a checkpoint record in database ID 3 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
2015-07-20 07:26:21.59 spid7s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2015-07-20 07:26:21.59 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
this is the full file error i get
15-07-20 07:25:52.26 Server (c) 2005 Microsoft Corporation.
2015-07-20 07:25:52.26 Server All rights reserved.
2015-07-20 07:25:52.26 Server Server process ID is 4548.
2015-07-20 07:25:52.26 Server Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\E
2015-07-20 07:25:52.28 Server This instance of SQL Server last reported using a process ID of 5576 at 7/20/2015 7:15:25 AM (local) 7/20/2015 11:15:25 AM (UTC). This is an informational message only; no user action is required.
2015-07-20 07:25:52.28 Server Registry startup parameters:
2015-07-20 07:25:52.28 Server -m
2015-07-20 07:25:52.28 Server -d D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 07:25:52.28 Server -e D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\E
2015-07-20 07:25:52.28 Server -l D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 07:25:52.28 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2015-07-20 07:25:52.28 Server Detected 4 CPUs. This is an informational message; no user action is required.
2015-07-20 07:25:52.29 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2015-07-20 07:25:52.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-07-20 07:25:52.45 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2015-07-20 07:25:54.46 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2015-07-20 07:25:54.46 Server Database Mirroring Transport is disabled in the endpoint configuration.
2015-07-20 07:25:54.46 spid5s Warning ******************
2015-07-20 07:25:54.46 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2015-07-20 07:25:54.46 spid5s Starting up database 'master'.
2015-07-20 07:25:54.62 spid5s CHECKDB for database 'master' finished without errors on 2015-07-18 00:00:03.050 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:54.63 spid5s SQL Trace ID 1 was started by login "sa".
2015-07-20 07:25:54.63 spid5s Starting up database 'mssqlsystemresource'.
2015-07-20 07:25:54.71 spid7s Starting up database 'model'.
2015-07-20 07:25:54.71 spid5s Server name is 'ICCAS2'. This is an informational message only. No user action is required.
2015-07-20 07:25:55.04 Server A self-generated certificate was successfully loaded for encryption.
2015-07-20 07:25:55.04 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSER
2015-07-20 07:25:55.04 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2015-07-20 07:25:55.04 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2015-07-20 07:25:55.04 Server Dedicated admin connection support was established for listening locally on port 1434.
2015-07-20 07:25:55.04 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2015-07-20 07:25:55.07 spid10s Starting up database 'BSRes2'.
2015-07-20 07:25:55.07 spid13s Starting up database 'Reports'.
2015-07-20 07:25:55.07 spid11s Starting up database 'msdb'.
2015-07-20 07:25:55.07 spid14s Starting up database 'SBill'.
2015-07-20 07:25:55.07 spid15s Starting up database 'IC'.
2015-07-20 07:25:55.07 spid12s Starting up database 'CDRs'.
2015-07-20 07:25:55.40 spid14s CHECKDB for database 'SBill' finished without errors on 2015-07-14 00:15:29.287 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:55.45 spid13s CHECKDB for database 'Reports' finished without errors on 2015-07-14 00:15:28.393 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:55.45 spid11s CHECKDB for database 'msdb' finished without errors on 2015-07-14 00:14:52.300 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:55.64 spid10s CHECKDB for database 'BSRes2' finished without errors on 2015-07-14 00:15:06.037 (local time). This is an informational message only; no user action is required.
2015-07-20 07:25:56.89 spid12s CHECKDB for database 'CDRs' finished without errors on 2015-07-14 00:15:16.160 (local time). This is an informational message only; no user action is required.
2015-07-20 07:26:02.70 spid15s CHECKDB for database 'IC' finished without errors on 2015-07-09 00:16:23.563 (local time). This is an informational message only; no user action is required.
2015-07-20 07:26:02.70 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2015-07-20 07:26:02.85 spid7s CHECKDB for database 'model' finished without errors on 2015-07-18 00:00:23.440 (local time). This is an informational message only; no user action is required.
2015-07-20 07:26:02.87 spid7s Clearing tempdb database.
2015-07-20 07:26:21.59 spid7s Error: 17053, Severity: 16, State: 1.
2015-07-20 07:26:21.59 spid7s D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
2015-07-20 07:26:21.59 spid7s Error: 9002, Severity: 17, State: 2.
2015-07-20 07:26:21.59 spid7s The transaction log for database 'model' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2015-07-20 07:26:21.59 spid7s Could not write a checkpoint record in database ID 3 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
2015-07-20 07:26:21.59 spid7s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2015-07-20 07:26:21.59 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Problem 1:
How much space you have on this drive?
Problem 2:
On which drive is tempdb located? How much space do you have there?
2015-07-20 07:26:21.59 spid7s D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.l df: Operating system error 1450(Insufficient system resources exist to complete the requested service.) encountered.
2015-07-20 07:26:21.59 spid7s Error: 9002, Severity: 17, State: 2.
2015-07-20 07:26:21.59 spid7s The transaction log for database 'model' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2015-07-20 07:26:21.59 spid7s Could not write a checkpoint record in database ID 3 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
How much space you have on this drive?
Problem 2:
2015-07-20 07:26:21.59 spid7s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
On which drive is tempdb located? How much space do you have there?
ASKER
There is a total for 40gb free on that drive and the tempdb is on that drive
Sounds like model is defect. Something must happened.
Do you have a backup?
Then use trace flag 3608 to start SQL Server. This will only recover master. So you can use sqlcmd to restore model ( sqlcmd -E and RESTORE DATABASE model FROM DISK='pathToBackup' WITH RECOVERY; ).
Do you have a backup?
Then use trace flag 3608 to start SQL Server. This will only recover master. So you can use sqlcmd to restore model ( sqlcmd -E and RESTORE DATABASE model FROM DISK='pathToBackup' WITH RECOVERY; ).
Please have a look on the following URL The SQL Server Instance that will not start
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am not sure whether you have gone through the link that was provided by me. Am pasting one paragraph from that article for your reference, and this is the solution that worked for you like a charm -
"Nevertheless, a simpler approach might be to copy the model files from another SQL instance of the same version (preferably service pack too), and use those in place of the missing or damaged files. This lets SQL start normally and then we can simply restore model from backup. If we have backups of the model database files, we can equally well use those."
That article covers a few scenarios and hence I thought better provide a link than pasting and copying contents.
"Nevertheless, a simpler approach might be to copy the model files from another SQL instance of the same version (preferably service pack too), and use those in place of the missing or damaged files. This lets SQL start normally and then we can simply restore model from backup. If we have backups of the model database files, we can equally well use those."
That article covers a few scenarios and hence I thought better provide a link than pasting and copying contents.
ASKER
Only Solution that worked after trying multiple trace flag options and recovery mode. If you have a backup of the ldf and mdf this is probably the easiest solution in production quick recovery
The error messages reads like your disk is full. But SQL Server needs space for its system database. Cause it could not be acquired, it fails during startup.
For recovery:
Disable SQL Server Agent. Start SQL Server in Single User Mode by adding the -m switch to the startup parameters. Connect with SSMS as ADMIN:serverNamerOrIP[\ins
Backup your databases and especially the log files.