Search

Saturday, May 12, 2012

Releasing unused memory in SQL server

As SQL Server does automatic Memory management , it means that SQL Server will ask for memory from the OS as and when it requires, and will keep it as long as it can. This is intended and a default behavior of SQL Server. The memory component (Buffer Pool) does this. So, when does SQL Server release memory back to the OS? It releases memory back to the OS when any other process will ask for memory from the OS. Despite this automatic memory management, there are 2 commands that can be used to free up memory in SQL Server.


DBCC FREEPROCCACHE :- To flush out execution plans from the procedure cache &


DBCC DROPCLEANBUFFERS :- To flush out data pages from the memory.

Friday, May 11, 2012

How to make Like case sensitive

I have one column (Type: nVarChar) in a table which has data something like this:
A1
A2
A3
a4
a5
A6
B1
b2
CA
CB
c9


I want to show all the rows which have all the upper case letters in the above mentioned column.


The following query works:


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[ABC]%' Collate Latin1_General_CS_AI  


And Surprisingly this one does not:


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[A-C]%' Collate Latin1_General_CS_AI  --Would actually be [A-Z]


Is it like the case sensitivity does not work with ranges i.e. [A-C] or I am missing something here?


Here is my answer:


Can you try


select * from test WHERE LTRIM(RTRIM(A)) LIKE '%[A-C]%' Collate Latin1_General_BIN


  this should give you case sensitive result.


The reason is that  [A-C] means include all characters which is bigger or equal than A, and smaller than C,  so lowercase a is also in the range even according to linguistic order for Latin1_General_CA_A. In other case, for case sensitive collation,  A is not equal to a, not  the order of the characters might still be  a <A <b < B <c <C, etc.

Thursday, May 10, 2012

Is My Computer/Server Running 32-bit or 64-bit?

Windows 7
Click Start
Click Control Panel
Click System
The operating system is displayed as follows:
For a 64-bit version operating system: 64-bit Operating System appears for the System Type under System
For a 32-bit version operating system: 32-bit Operating System appears for the System Type under System


Windows Server 2008
Click Start
Click Control Panel
Click System
The operating system is displayed as follows:
For a 64-bit version operating system: 64-bit Operating System appears for the System Type under System
For a 32-bit version operating system: 32-bit Operating System appears for the System Type under System


Windows Vista
Click Start
Click Control Panel
Click System
The operating system is displayed as follows:
For a 64-bit version operating system: 64-bit Operating System appears for the System Type under System
For a 32-bit version operating system: 32-bit Operating System appears for the System Type under System


Windows XP
Click Start, and then click Run
Type sysdm.cpl, and then click OK.
Click the General tab. The operating system is displayed as follows:
For a 64-bit version operating system: Windows XP Professional x64 Edition Version Year appears under System.
For a 32-bit version operating system: Windows XP Professional Version Year appears under System.


Windows Server 2003
Click Start, and then click Run
Type sysdm.cpl, and then click OK.
Click the General tab. The operating system is displayed as follows:
For a 64-bit version operating system: Windows Server 2003 Enterprise x64 Edition appears under System.
For a 32-bit version operating system: Windows Server 2003 Enterprise Edition appears under System.

Wednesday, May 9, 2012

CPU usage by each database

Below script shows all CPU usage of all session for all database at a certain time:


DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid


SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc

Tuesday, May 8, 2012

Error 3023

Today I got below error while executing a backup query 
BACKUP LOG MyDB WITH TRUNCATE_ONLY


I got the following error:


Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.


I checked the sysprocesses and I found a backup background process launched from someone else. so I waited for the process to end and above query worked.