Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Archive for the ‘Administration’ Category

Can’t Start SQL Server Due to Insufficient Memory

Posted by jeffprom on December 14, 2017

SQL Server loves memory and will take as much as you give it. Sometimes you may want to limit the amount of memory it can have. To do this, go into management studio, right click on the server, select properties, and select the memory tab. To limit the amount of memory it can have, set a value for Maximum Server Memory (in MB).

If you aren’t careful, you can set a value that is too low and SQL Server won’t be able to run. I recently did this on my local copy and had a hard time changing the setting again to a higher value as everything expects that SQL Server is running, and that you can simply change the configuration.

In my case, I had set the value to 128 MB and SQL Server kept showing an “insufficient memory” error in the windows event log and wouldn’t start.

If you find yourself in a similar predicament, here is how to get it working again.

Open a command prompt as an administrator. Find the install path of your version of the SQL Server engine. In my case it was “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

Change to the directory:

Cd “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”

Next, we want to start SQL Server with minimal configuration. This will start SQL Server in a minimal state and allow you to connect in management studio. To do this use the following:

Sqlservr.exe -f -s <instancename>

Open the server properties window and bump up the max memory limit.

Close the command prompt window to shut down SQL Server. Finally, go to SQL Server Configuration Manager and start the service like normal.

You should now be able to run the service as usual and connect.

Advertisements

Posted in Administration, Troubleshooting | Leave a Comment »

View Running Queries by CPU Time

Posted by jeffprom on December 9, 2012

If you have ever done performance tuning you know it can be a bit of an art and you need your detective hat on. I was recently working on a server that was performing poorly. After looking at resource monitor it was clear that the CPU usage was unusually high. As it turns out SQL Server was utilizing most of the CPU. The hunt was on. I grabbed my magnifying glass and followed the trail. While I could run sp_who2 to find some relevant info, I instead ran the query below. sys.dm_exec_requests returns information about each request that is executing within SQL Server. sys.dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle.

-- check for queries running. sort by cpu time
SELECT a.session_id, db_name(a.database_id) as db_name, a.start_time, a.command, a.status, a.cpu_time, a.total_elapsed_time, a.reads, a.writes, b.text as query
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.session_id > 50 -- filter out background tasks
and a.session_id <> @@spid -- filter out this query session
order by a.cpu_time desc

This query shows open queries sorted in descending order by CPU time. I was able to nab a few culprits in the act. I copied the queries to new windows and checked the execution plans. After some intense interrogation it was clear that several tables needed an index and the sub-queries should be turned into joins. Updates were put in place, and the CPU usage went way down. Case closed!

Posted in Administration, T-SQL, Tips, Troubleshooting | Leave a Comment »

Property Owner is not available for Database

Posted by jeffprom on April 16, 2010

If a database becomes orphaned and has no database owner, you will get the following error message when you try to view the database properties in SSMS:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)

You can use the following code to see orphaned databases.
SELECT databases.NAME AS DB_Name, server_Principals.NAME AS User_Name
FROM sys.[databases]
LEFT OUTER JOIN sys.[server_principals]
ON [databases].owner_sid = [server_principals].sid

To assign a new owner:
EXEC sp_changedbowner 'newuser'

If you get the following error message:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database
.

Open up the user’s Login Properties window under Security\Logins and uncheck the Map checkbox for the database and click OK. Basically the code won’t assign them as the new owner because it thinks they are already associated with that database. Now run the sp_changedbowner command again and it will work.

Posted in Administration, T-SQL, Tips, Troubleshooting | Leave a Comment »