-1

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.

services

error1

I have checked the Error log for the same and it's showing below 2 messages.

  1. The transaction log for database 'master' is full due to 'CHECKPOINT'.
  2. During upgrade, database raised exception 9002, severity 17, state 1. Use the exception number to determine the cause.
10
  • Why are trying to install SSMS 2014? 18.8 came out quite recently. Download SQL Server Management Studio (SSMS) Though the errors you give are installed errors for SSMS.
    – Larnu
    Jan 27 '21 at 12:00
  • @Larnu - There are several dependency with the sharepoint 2013. Due to the same I won't be able to jump to SSMS 2018 directly
    – Mr. Roshan
    Jan 27 '21 at 12:40
  • 1
    Are you aware that Management Studio (a client application) and SQL Server and SQL Server Agent services are not related in a way that updating the Management Studio would cause a problem on those services? Also I don't think there's a service pack for SSMS, so it must be a service pack for SQL Server itself. How much free space do you have on the drive where the master database resides?
    – Ronaldo
    Jan 27 '21 at 12:41
  • 1
    @J.D. sorry for the confusion.... it's all about the SQL Server 2014.
    – Mr. Roshan
    Jan 27 '21 at 12:48
  • 1
    @Ronaldo, Right, agreed on its own SSMS is unrelated. Coincidentally when OP misspoke he referred to the last version of SSMS that was shipped with the SQL Server installer, so before we knew what he actually meant all bets were off at that point lol (e.g. a botched install breaking SQL Server that was meant to only update SSMS). But yea as you said, we have more context now and SSMS isn't even relevant to this issue lol.
    – J.D.
    Jan 27 '21 at 17:43
1

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:

  1. Restore master to another instance and copy its files
  2. Rebuild the system databases

But start with #2 (trying to start the instance in single user mode) then follow the aforementioned answer in order.

7
  • I have already extended the disk space size but it's still showing the same error. Also as I am not able to connect to the database (Services are throwing an error) then how can I shrink the databases
    – Mr. Roshan
    Jan 27 '21 at 12:47
  • @Mr.Roshan I didn't say anything about shrinking. How big is the master log file currently, how big is the drive it's on, and how much free space is left on that drive?
    – J.D.
    Jan 27 '21 at 12:50
  • Yes.... whenever we took the backup that time shrinking process also takes place right.....but we are in the situation where we won't be able to connect to the database and right now there is almost 1TB+ space is available on the drive also the size of master.mdf(4 MB) and mastlog.ldf (2 MB)
    – Mr. Roshan
    Jan 27 '21 at 12:53
  • @Mr.Roshan My answer suggests making free space on the drive first so you're able to eventually connect to the server, knowing you're currently unable to connect, as it states. It is very strange you have over 1 TB of free space where the log file is and the log file is only 2 MB big (but complaining it's full). Maybe the master database was configured to grow to a max size, which it now hit. I'll have to think how you can work around that and will update my answer when I can.
    – J.D.
    Jan 27 '21 at 13:04
  • 1
    My guess is that there was some confusion on how SQL Server works, and shrinking for the master database was performed. As an analogy: You have a full disk and you address this by making the disk smaller. So master now has an absurdly small size (4 MB data and 2 MB log) which is the root cause of the (current) problem. Jan 27 '21 at 13:56
1

Try restoring that problematic master on another instance to resize the log file:

  1. Rename the original master database .mdf and .ldf files and make copies of them (we don't wanna make things worse);

  2. 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;
    
  3. Resize the log file:

    ALTER DATABASE master2
    MODIFY FILE (NAME = mastlog, MAXSIZE = UNLIMITED, SIZE = 50MB);
    
  4. 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.

  5. Try to start SQL Server service now.

7
  • I have tried to Create database by renaming the .mdf &.ldf files on another instance but getting same error while creating the database
    – Mr. Roshan
    Jan 27 '21 at 15:03
  • 1
    @Mr.Roshan, try the option to create a new Log file: CREATE DATABASE [master2] ON (FILENAME = 'C:\BackupSQL\master2.mdf') FOR ATTACH_REBUILD_LOG; on another instance.
    – Ronaldo
    Jan 27 '21 at 15:52
  • @Mr.Roshan were you able to use the above option for creating a new log file during the attach process or it caused some error too?
    – Ronaldo
    Jan 28 '21 at 22:50
  • I am able to create it but by using little bit different approach
    – Mr. Roshan
    Jan 29 '21 at 5:49
  • 1
    @Mr.Roshan please, share that approach with us. Did it solve the problem of the service not starting? If yes, you could write it as the answer to the question.
    – Ronaldo
    Jan 29 '21 at 10:11
0

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 :-

  1. You have to setup another SQL instance running the exact same release and build.

  2. Create database master2

  3. stop the SQL Services and goto .mdf and .ldf file location "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA".

  4. Remove the master2.mdf and master2_log.ldf files.

  5. 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

  6. start the SQL Services again and connect to the database from SSMS.

  7. you can see the master2 database is showing state as recovery pending

Common steps :-

  1. Take master2 database offline

Take Offline

  1. Either remove or rename master2.ldf file from "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA"

  2. 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')

  1. Take master2 database Online

  2. Execute below command

`ALTER DATABASE master2 SET MULTI_USER`
  1. we will run the DBCC CHECKDB command below, in order to check the physical consistency of the database after rebuilding the SQL Transaction Log file
`DBCC CHECKDB (master2)`
  1. after this replace the master2.mdf & master2_Log.ldf file to your original Sql server instance (rename the files back to original name) and try to connect now.
2
  • Thanks for sharing your method. Some observations: Step 1 is not needed, you can do step 2 using any instance of SQL Server 2014 or above you already have. Steps 12 and 13 are not related to the solution of the instance not starting, WSS_Content is a SharePoint database and not a SQL Server system database.
    – Ronaldo
    Feb 2 '21 at 14:08
  • @Ronaldo - updated the answer.
    – Mr. Roshan
    Feb 3 '21 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.