Friday, 8 January 2016

Finding out how much memory SQL Server is utilizing?


Many time for a new DBA  hard to find how much My Server utilising memory? Here is explained a number of ways to find the memory utilized by SQL server.
By Using Performance monitor:
 Go to Start–>Run and type perfmon . You will find the following screen where you can see the total memory used(the pages/sec) by your box.

By Using DMV:
You can Query to sys.dm_os_memory_clerks DMV to find out Memory utilization by SQL server. To see how see the following query
select name,type, 
sum(single_pages_kb+multi_pages_kb) as MemoryKB
 from sys.dm_os_memory_clerks
group by name,type
order by memorykb desc
By using SQL Reports:
Right Click on SQL Server–>reports–>Standard Reports–> Memory Consumption
See the Following image.



Microsoft SQL Server, Error: 5120 (Access Denied)

Problem:
I am trying to attach a database into MS SQL server 2012/ Altering from Read only true to Read only fals;
The following error is occurred:
Unable to open the physical file “, filename”, Operating system error 5: “(Access Denied)”. (Microsoft SQL Server, Error: 5120)

Cause:
The associated file/s (.mdf or .ldf) read only or SQL (user) do not have permission to access file in the location it saved.
Solution:
Browse the location of file/s (.mdf or .ldf) right click on file –>Properties


Click On security Tab –> Select the user (for me it is Everyone) –>click on Edit
And check the full control/ Modify option in Allow column–> Apply –>OK
Do the same for all other associated files, if any.
Now it’s completed. Try what are you doing.