SQL: Start in single user mode and run commands.

\ by

Change the SQL server service for SQL Server 2012 2014 to this
adding -m to the startup parameters.

Before you do that you really want to

  • Get everyone off the server.
  • Disable SQL Agent service changing to manual
  • Close any SSMS object explorer windows you or other administrators have open.
    • Remember it’s single user mode.
  • Stop the services, for SQL and any others which may automatically reconnect once SQL is back up.
  • Possibly leave SSMS open with a query window with the commands you want to run in single user mode.

Click and restart the SQL service. After it has restarted [re-]open a SSMS query window and run the commands.

Once done

  • Stop SQL Service
  • Change the properties removing the -m from the startup parameters
  • Change SQL Agent to autostart and or whatever else you had to change to aget SQL into single user mode.
Posted in: SQL, SQL Server

SQL: Msg 2555 Cannot move all contents of file “tempdev8” to other places to complete the emptyfile operation.

\ by

SQL Server 2014. Suddenly we can’t seem to remove extra tempdb files (which were added after we reduced CPU).

I’m getting this error:

DBCC SHRINKFILE: Page 9:505072 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 94
Cannot move all contents of file "tempdev8" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 95
The file 'tempdev8' cannot be removed because it is not empty.

Since it was a test server I could try to bounce the server. But even on restart it still occurred. The only fix was to put SQL into single user mode and run the following.

USE [tempdb]
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO
DBCC SHRINKFILE ('tempdev8' , emptyfile)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev8]
GO

 

Posted in: SQL, SQL Server

SQL Configuration: Auto Update Stats Async Enabled

\ by

Brent Ozar is the best starting place for “Auto Update Stats Async Enabled” and has the reference links for further research. He says:

We recommend setting it back to the default (disabled) unless you’ve got evidence that delays caused by statistics updates are causing you problems.

Tara Kizer warns:

Here is how to enable the option:

ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Enabling the async option affects your ability to put a database into single-user mode.  The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database.

Since I’ll probably forget changing the setting and we don’t have problems at the moment with rogue queries, and we sometimes change to SINGLE USER MODE, I’ll pass.

Posted in: Configuration, SQL Server