SQLDBATaskwithKumar: How to know or list status for all available resources in cluster server in command prompt?

Hi Kumar,

I have one doubt..where we can find whether cluster installed or not at windows level.

Thanks
AS Reddy

Finding the CPU usage in SQL Server

SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers using below query

SQL Server 2005

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%%') AS t

) AS t

ORDER BY record_id desc

SQL Server 2008

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%%') AS t

) AS t

ORDER BY record_id desc

Finding the Collations setting at database level and Server level

Finding the Collations Setting at Database Level.

Select name as database,

databasepropertyex(name,'collation')

from sys.sysdatabases

finding the collation Setting at instance level

Select Serverproperty('Collations')

The T-SQL script below will provide all of the details about a particular collation.

select * from fn_helpcollations where name='SQL_Latin1_General_CP1_CI_AS'

Thanks

A.S.Reddy

Backup & Restores of the SQL Server Databases by using Lite Speed

Here are the some of Queries for taking the SQL Server Databases backups & Restore  by using the Lite Speed

Full & Diffencial backup

================

EXEC master.dbo.xp_backup_database @database='DBname'

,@filename='C:\DR_DIFF.BAK'

, @init= 1

, @with = 'DIFFERENTIAL'

T-log backup

========

EXEC master.dbo.xp_backup_log @database = 'DBname'

,@filename='C:\DR_LOG.BAK'

,@init = 1

Restore the database
----------
exec master.dbo.xp_restore_database @database = N'test' ,
@filename = N'Q:\Backup\Test_20130308.bak',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''Test'' TO N''M:\data\Test.mdf''',
@with = N'MOVE N''Test_log'' TO N''H:\logs\Test_log.ldf''',
@affinity = 0,
@logging = 0
GO

Thanks&regards

As Reddy

SQL Server Service Pack Version numbers

SQL Server 2000

----------------

SQL Server 2005

--------------------

-----------------

SQL 2008

----------

finding HIGH CPU using Query in SQL Server

finding the how CPU usage in SQL Server.which Query using high CPU usage..

SELECT st.text, r.cpu_time FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

ORDER BY cpu_time DESC

Change the Owner of the Maintenance plan in SQL Server

SQL server 2008

------------------

update msdb.dbo.sysssispackages

set [ownersid] = suser_sid('sa')

where [name] = 'Maintence paln name'

SQL Server 2005

--------------------

--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers

UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

SQL Server 2000 Maintenance Plan

--change the owner of a SQL Server 2000 Maintenance Plan UPDATE [msdb].[dbo].[sysdbmaintplans] SET [owner] = 'sa' WHERE [plan_name] = 'YOUR_MAINT_PLAN'