Calculating Max and Min Memory Settings

Here is the T-SQL we use to calculate the Max and Min memory settings for our SQL Server instances

DECLARE @curMem BIGINT,
 @maxMem BIGINT,
 @optMem BIGINT,
 @minMem fLOAT;
IF OBJECT_ID('tempdb..#msver') IS NOT NULL
DROP TABLE #msver
CREATE TABLE #msver
(
[index] int,
[name] varchar(200),
[intval] bigint,
[charval] varchar(2000)
)

IF OBJECT_ID('tempdb..#report') IS NOT NULL
DROP TABLE #report
CREATE TABLE #report
(
[name] varchar(200),
[value] bigint,
[description] varchar(2000)
)

INSERT INTO [#msver] ([Index], [Name], [IntVal], [CharVal]) EXEC [master].dbo.xp_msver;

INSERT INTO [#report] ([Name], [Value], [Description])
SELECT 'total memory', [IntVal], 'Total amount of physical memory in server'
FROM [#msver]
WHERE [Name] = 'PhysicalMemory';

INSERT INTO [#report] ([Name], [Value], [Description])
SELECT [Name], CONVERT(BIGINT,[value_in_use]), [description]
FROM [master].sys.[configurations]
WHERE [name] IN ('max server memory (MB)', 'min server memory (MB)');

INSERT INTO [#report] ([Name], [Value], [Description])
SELECT [Name], CONVERT(BIGINT,[value_in_use]), [description]
FROM [master].sys.[configurations]
WHERE [name] = 'optimize for ad hoc workloads';

SELECT @curMem = CONVERT(BIGINT, [Value]) FROM [#report] WHERE [Name] = 'max server memory (MB)';

SELECT @maxMem = CONVERT(BIGINT, [Value]) FROM [#report] WHERE [Name] = 'total memory';
PRINT 'Current Max Memory Setting: ' + CAST(@curMem AS VARCHAR(20))
PRINT 'MAX Server Memory: ' + CAST(@maxMem AS VARCHAR(20))
SET @optMem = @maxMem - (CASE WHEN @maxMem <= 16384 THEN (@maxMem / 4) ELSE 4096 + (@maxMem - 16384)/8 END);
PRINT 'Suggested Max Memory: ' + CAST(@optMem AS VARCHAR(20))
SET @minMem = @optMem * .60
PRINT 'Suggested Min Memory: ' + CAST(@minMem AS VARCHAR(20))

The Script will output the suggested values for Max and Min memory settings based on the current memory of the server.

Advertisements

bcp to CSV 0x00 character issues – NUL in Notepad++

I tried every bcp option under the sun trying to get rid of the NUL characters that kept cropping up in my CSV file from a bcp export. Finally stumbled on the solution here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131426

I wrapped the columns causing the problem with a NULLIF(column, ”) as suggested in the forum post and that worked like a champ.


bcp "select * from ##MessageRights" queryout c:\Scripts\MessageRights.csv -w -t, -T -E -k -S SERVERNAME

sp_cycle_errorlog Msg 17049 Unable to Cycle Error Log file

Today I had an ERRORLOG file going buck wild! It was up over 82GB. This was discovered because of a hard drive monitor we have on the server telling us when the hard drive space is running low. Knowing that I had little chance of opening the ERRORLOG in SSMS or in a text editor I used the xp_readerrorlog stored procedure to parse the last few log entries to see what was up.

xp_readerrorlog 0,1,NULL,NULL,'MM/DD/YY HH:MI:SS','MM/DD/YY HH:MI:SS','desc'

--Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
--Log file type: 1 or NULL = error log, 2 = SQL Agent log
--Search string 1: String one you want to search for
--Search string 2: String two you want to search for to further refine the results
--Search from start time
--Search to end time
--Sort order for results: N'asc' = ascending, N'desc' = descending

We had DEADLOCKS going crazy. 100K+ entries into ERRORLOG every minute. I checked which TRACE Flags we had enabled and disabled the DEADLOCK related flags.


--See which TRACE FLAGS Are ENABLED
DBCC TRACESTATUS

--DISABLE TRACEFLAGS
DBCC TRACEOFF (1204,-1)
DBCC TRACEOFF (1222,-1)

Then I tried to cycle the log to give me a fresh log file. I received the following error when I tried to cycle the log.

EXEC sp_cycle_errorlog

Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
Unable to cycle error log file from ‘X:\Path to ERRORLOG\ERRORLOG’ to ‘X:\PATH to ERRORLOG\ERRORLOG.1′ due to OS error ’32(The process cannot access the file because it is being used by another process.)’. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access.”
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Ran this query to see what was using xp_readerrorlog and found 2 sessions. Killed the Sessions and re-ran the sp_cycle_errorlog.

SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
and session_id <> @@spid AND( text = 'xp_readerrorlog' OR text = 'sp_cycle_errorlog')
ORDER BYstart_time</em>

--Killed the SPIDs
KILL SPID

Once I had the log cycled I just deleted it. It was more important to get the disk space under control then researching the deadlock. After some research we discovered the Deadlocks were a result of a missing index in our replicated environment, causing the DEADLOCK flood of ’13.