Category Archives: Sql Server

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.

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

Here’s a couple of ways to get it via code.

/*

http://sqlandme.com/2013/08/20/sql-service-get-sql-server-service-account-using-t-sql/
*/
DECLARE @DBEngineLogin VARCHAR(100)
DECLARE @AgentLogin VARCHAR(100)

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = N'ObjectName',
@value = @AgentLogin OUTPUT

SELECT [DBEngineLogin] = @DBEngineLogin,
[AgentLogin] = @AgentLogin
GO

SELECT [servicename],
[service_account]
FROM [sys].[dm_server_services]
GO