I am trying to update the SQL Server 2014 to Service Pack 3 on my Windows Server Machine. After the updates from Windows update feature I am unable to start the SQL Server service and SQL Server Agent Service.
I have checked the Error log for the same and it's showing below 2 messages.
- The transaction log for database 'master' is full due to 'CHECKPOINT'.
- During upgrade, database raised exception 9002, severity 17, state 1. Use the exception number to determine the cause.
Which version of SQL Server are you running? Does this StackExchange help resolve any off the issues?
Exception 9002 basically means your Transaction Log is full, likely regarding the master database, as related to your first error.
With the SQL services offline, the quickest solution might be to make more space available in the drive that the Transaction Log for master lives on so it can grow the file enough, as needed, so you can start the service and then troubleshoot better, like taking a backup of master so it shrinks the Transaction Log file. The amount of space you'll need to free up is hard to determine (and dependent on what the growth factor is set to for the master database), but maybe an equal amount to its existing log size (so it can double if needed) is a good start.
After the additional details you posted in the comments (you should put them in your original question, by the way) I'm wondering if you possibly have a corrupt master database. In any case, this StackExchange answer might prove very useful to you. Particularly if you can do steps #3 or #4:
- Restore master to another instance and copy its files
- Rebuild the system databases
But start with #2 (trying to start the instance in single user mode) then follow the aforementioned answer in order.
Try restoring that problematic master on another instance to resize the log file:
Rename the original master database .mdf and .ldf files and make
copies of them (we don't wanna make things worse);
Attach the copied files on a different SQL Server instance to check the state of that database:
USE [master];
CREATE DATABASE [master2] ON
( FILENAME = N'C:\BackupSQL\master2.mdf' ),
( FILENAME = N'C:\BackupSQL\mastlog2.ldf' )
FOR ATTACH;
Resize the log file:
ALTER DATABASE master2
MODIFY FILE (NAME = mastlog, MAXSIZE = UNLIMITED, SIZE = 50MB);
detach the master2 database and paste the .mdf and .ldf files back
to the original place (remember to keep the original .mdf and .ldf
files with a different name, don't delete them as they might be
needed in case this doesn't work) and rename the new ones as master.mdf and
mastlog.ldf.
Try to start SQL Server service now.
Error 9002 :-
Thanks @Ronaldo and @J.D.
I have resolved the problem by using below actions.
Perform below steps in case master file transaction log is full :-
You have to setup another SQL instance running the exact same release and build.
Create database master2
stop the SQL Services and goto .mdf and .ldf file location "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA".
Remove the master2.mdf and master2_log.ldf files.
take our master.mdf and mastlog.ldf files which is having issue related to transaction log. rename both the file as below:
a. master.mdf -> master2.mdf
b. mastlog.ldf -> master2_log.ldf
start the SQL Services again and connect to the database from SSMS.
you can see the master2 database is showing state as recovery pending
Common steps :-
Either remove or rename master2.ldf file from "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA"
Execute below command which will rebuild the transaction log file again.
ALTER DATABASE master2 REBUILD LOG ON (NAME= logicalname, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master2_log.ldf')
Take master2 database Online
Execute below command
`ALTER DATABASE master2 SET MULTI_USER`
`DBCC CHECKDB (master2)`