Post reply

TempDB misconfiguration

  • February 9, 2023 at 11:41 pm

    #4147819

    Bit of a dilemma,  I accidentally entered the incorrect sizing of the tempdb files during the installation of a clustered instance.

    the installation completed but the tempdb partially created the files then ran out of space.  The issue is the SQL instal completes but failed on a few steps and now it will not start.

    I got around this by entering in the -f flag in the start up parameters but the tempdb only shows the one file on the c: which I think it using just so I can log in with the minimum requirements and I was hoping I could run the alter db to move the tempdb back to the correct volume and to resize correctly. 

    The issue I have is the -f flag puts the instance in single user mode and as such I am getting a error notifying me as such when it comes to opening the new query.

    I can modify the tempdb using the SSMS interface as obviously it will not simply let me move the tempdb location.

    The error when I don’t have the flag is basically not starting as it does not have the correct number of tempdb.

    I could uninstall it but I would rather see if I can get around this.

    Any help would be appreciated.

    thanks

     

     

  • February 9, 2023 at 11:55 pm

    #4147820

    You could try this:

    ssc

    Let me know if it works.

  • February 10, 2023 at 12:32 am

    #4147837

    Thanks for your reply.

    Unfortunately I have tried -m flag however when i use this flag it attempts to create the tempdb files on the volume that has insufficient space and when it can not the instance will not start.

    If there was a way to start the instance using the minimum configuration that the -f flag gives you without it putting me in the single user mode that would be great but i haven't found anything.  That or if there was a way to change the size of the tempdb files SQL is trying to create on start up that would also get me out of the bind.

     

     

  • February 10, 2023 at 12:55 am

    #4147840

    Have you tried using both the -m and -f flags?

    sqlserver.exe -m -f

  • February 10, 2023 at 1:05 am

    #4147841

    So the instance starts, however because i am using the -f flag i get the error Server is in single user mode.  Only one administrator can connect at a time (Microsoft SQL Server, Error: 18461)

  • February 10, 2023 at 1:59 am

    #4147852

    You could try using sqlcmd instead of SSMS

  • February 10, 2023 at 9:56 am

    #4147975

    Single user for clustered instances: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode?view=sql-server-ver16

  • February 13, 2023 at 1:56 pm

    #4149025

    Thanks for the assistance everyone.

    I tried SQLCMD as well as attempting ot find a solution in other articles articles to get me around in the end i had to uninstall the instance which i did not  really want to do but it was by far the quickest and easiest way to resolve.

     

     

     

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply