Thursday, August 18, 2016

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) scrip

  • Total number of physical CPUs
  • Total number of physical cores per CPUs
  • Total number of physical cores
  • Total number of virtual CPUs
  • Processor type (x86 or x64)
As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:

exec xp_msver 'ProcessorCount'



Query Starts Here



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DECLARE @xp_msver TABLE (
    [idx] [int] NULL
    ,[c_name] [varchar](100) NULL
    ,[int_val] [float] NULL
    ,[c_val] [varchar](128) NULL
    )
INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;
WITH [ProcessorInfo]
AS (
    SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
        ,CASE
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            END AS [number_of_cores_per_cpu]
        ,CASE
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            END AS [total_number_of_cores]
        ,[cpu_count] AS [number_of_virtual_cpus]
        ,(
            SELECT [c_val]
            FROM @xp_msver
            WHERE [c_name] = 'Platform'
            ) AS [cpu_category]
    FROM [sys].[dm_os_sys_info]
    )
SELECT [number_of_physical_cpus]
    ,[number_of_cores_per_cpu]
    ,[total_number_of_cores]
    ,[number_of_virtual_cpus]
    ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]
The code of this script is tested on SQL Server 2005 and above versions.



Reference: https://basitaalishan.com/2014/01/22/get-sql-server-physical-cores-physical-and-virtual-cpus-and-processor-type-information-using-t-sql-script/

Monday, September 21, 2015

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Database cannot be opened due to inaccessible files or insufficient memory or disk space


Msg 945, Level 14, State 2, Line 1
Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


Solution:
Check Microsoft SQL Server error log and determine reason for the cause.
If it is because of a persistent I/O error related to Application Programming Interface, a torn page, or other hardware issues, resolved it and restore it with help of backup. But if there is no availability of backups then try DBCC CHECKDB repair option.
 If possible add some disk space to files drive or cleanup some disk space.
Verify the permissions of user account.
Verify if the database is property is set to AutoGrow On.
.mdf and .ldf files should not be marked as Read Only on windows level.


If none of the above solved Issue: Try this


1. Change the database to offline to clear the db status

use master
alter database dbname set offline

2. Now change the database to online, at this step log file and data files will be verified by sql server


use master
alter database dbname set online


Friday, April 17, 2015

SQL Server SSRS: List out all Data Sources and all their dependency objects/Reports

 This Script will list all the data sources by their actual name, and all their dependent items:

SELECT C2.NAME AS Data_Source_Name
 ,C.NAME AS Dependent_Item_Name
 ,C.Path AS Dependent_Item_Path
FROM ReportServer.dbo.DataSource AS DS
INNER JOIN ReportServer.dbo.CATALOG AS C ON DS.ItemID = C.ItemID
 AND DS.Link IN (
  SELECT ItemID
  FROM ReportServer.dbo.CATALOG
  WHERE Type = 5
  ) --Type 5 identifies data sources
FULL JOIN ReportServer.dbo.CATALOG C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5
ORDER BY C2.NAME ASC
 ,C.NAME ASC;

How to find when was a login deleted in SQL Server

SELECT TE.NAME AS [EventName]
 ,v.subclass_name
 ,T.DatabaseName
 ,t.DatabaseID
 ,t.NTDomainName
 ,t.ApplicationName
 ,t.LoginName
 ,t.SPID
 ,t.StartTime
 ,t.RoleName
 ,t.TargetUserName
 ,t.TargetLoginName
 ,t.SessionLoginName
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (
    SELECT TOP 1 f.[value]
    FROM sys.fn_trace_getinfo(NULL) f
    WHERE f.property = 2
    )), DEFAULT) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
INNER JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
 AND v.subclass_value = t.EventSubClass
WHERE te.NAME IN (
  'Audit Addlogin Event'
  ,'Audit Add DB User Event'
  ,'Audit Add Member to DB Role Event'
  )
 AND v.subclass_name IN (
  'add'
  ,'Grant database access'
  ,'drop'
  ,'Revoke database access'
  )