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'

Import the Data from Excel Sheet to SQL server table

1) Fisrt I created Table in SQL

create table com(fname varchar(20),lname varchar(20))

2) I created Excel Sheet with name com.xls

3)given data below

4)i executed below script

INSERT INTO com ( fname, lname )

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Documents and Settings\All Users\Documents\com.xls',

'SELECT * FROM [Sheet1$]')

Tested

======

I got error while opening database properties

Cause

=====

for this errors two reasons

1)one is Database Owner is null

2)Original DBO Login was removed recently

Resolution

----------

first i checked the database owners

this is for all db's in a instace

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

SELECT name,SUSER_SNAME(owner_sid) as DB_Owner

FROM sys.databases

out put

--------

I found probably Database Owner is NUll..

Then i changed Database Owner to SA

USE DB_Name

go

EXEC sp_changedbowner 'sa'

after that i am able to open the Database Properties

Query for finding the which Service Pack installed on SQL Server

select serverproperty('productlevel')

Query for finding the number of pages in all databases

SELECT

DB_NAME(database_id) as DB_NAME,

COUNT(page_id)as number_pages

FROM sys.dm_os_buffer_descriptors

WHERE database_id !=32767

GROUP BY database_id

ORDER BY database_id

Finding the how pages Dirty pages and how many free pages in all Databases

SELECT

(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',

(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME(database_id) END) AS 'Database Name',

COUNT (*) AS 'Page Count'

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id], [is_modified]

ORDER BY [database_id], [is_modified];

GO

OUTPUT

====

Start and Stop the jobs by using T-SQL Query

Start

EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'

Stop
EXEC MSDB.dbo.sp_stop_job @Job_Name = 'JobName'

Default Trace in SQL Server 2005

Problem
I have been running SQL Server 2005 server side traces to address some SQL Server 2005 performance issues. I have noticed an unusual trace session that is running. I know I have not been running this trace and know I have been diligent about running only a single trace to not impact performance. So where did this trace session coming from?

Solution
SQL Server 2005 is running a default trace. You can think of this as a replacement to the black box mode trace that could have been run in SQL Server 2000 where the last 5 MB of data is captured. The trace's impact should be minimal to the server, but is valuable to be aware of as a DBA or Developer responsible for the server.

How can I find out if the trace is running on my SQL Server?

The simplest means to determine if the trace is running is to execute the following command:

SELECT * FROM fn_trace_getinfo(default); GO

What does the output indicate?

Result Set	Description
Traceid	Unique identifier for the trace
Property = 1	Configured trace options
Property = 2	Trace file name
Property = 3	Max file size for the *.trc file
Property = 4	Stop time for the trace session
Property = 5	Current trace status (1 = On and 0 = Off)
Value	Current value for the traceid\property combination

Where is this trace file stored by default?

The trace is stored in the LOG directory for your SQL Server instance (i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) at the root of where your SQL Server is installed.

Another alternative to determine if the trace is running is to review sp_configure.

To determine if the trace is configured to run, execute sp_configure and review the 'default trace enabled' option. When the config_value and run_value are equal to 1, then this trace is running.

How can I disable this default trace?

To disable the default trace from running, issue the following commands:

EXEC master.dbo.sp_configure 'allow updates', 1; GO EXEC master.dbo.sp_configure 'show advanced options', 1; GO EXEC master.dbo.sp_configure 'default trace enabled', 0; GO RECONFIGURE WITH OVERRIDE; GO EXEC master.dbo.sp_configure 'show advanced options', 0; GO EXEC master.dbo.sp_configure 'allow updates', 0; GO

*** NOTE *** - When you issue these commands, the trace stops executing immediately.

Should I disable this trace?

If you were not aware of this trace running and you were not having related or suspected performance issues from this trace, I would say maybe not. The final answer should come after reviewing the output from the trace files to determine if the data is valuable to you. Invaluable information like login creations and drops are captured in these files. You might also find other jewels that may answer some recent outstanding questions. If the value of the information exceeds the potential issue from running this trace, then it should remain enabled. I also encourage you to consider this trace as a source when troubleshooting an issue, so consider reviewing this file the next time a question goes unanswered.

How can I review the data captured in the trace files?

Let's end on an easy question. Just navigate to the directory where the files are located i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ and double click on the files. Profiler should load and permit you to browse the contents interactively.

Script to Get Available and Free Disk Space for SQL Server

Problem

Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure XP_FixedDrives is unable to help us in this scenario, so we have to log into the machine to check the total and free disk space for each physical/LUN/mount drive. In this tip, I show how this can be done using PowerShell.

Solution

Before we proceed with the script, let's discuss what a LUN/Mount drive is. A LUN/Mount drive is a logical unit of a SAN created by the SAN administrator and presented to an attached server's operating system. The server is unaware of the physical makeup of the disks involved in the LUN and sees it as a single locally attached disk. That's why XP_FixedDrives is unable to query the LUN/Mount drive.

We can check the disk space by executing a PowerShell script using XP_CMDShell from SSMS. In order to do this, you need to make sure that XP_CMDSHELL is enabled on the SQL instance.

You can execute the below script to check and enable XP_Cmdshell. To enable XP_Cmdshell you must have at least the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

declare @chkCMDShell as sql_variant select @chkCMDShell = value from sys.configurations where name = 'xp_cmdshell' if @chkCMDShell = 0 begin  EXEC sp_configure 'xp_cmdshell', 1  RECONFIGURE; end else begin  Print 'xp_cmdshell is already enabled' end 

---

SQL Script to check total and free disk space of physical drive/LUN/mount drive

This is the script that can be used to get the information we are looking for. This uses PowerShell and xp_cmdshell so it can be run from a SSMS query window. You could also do this just using PowerShell, but I wanted to show how this can be done directly from SSMS.

declare @svrName varchar(255) declare @sql varchar(400) --by default it will take the current server name, we can the set the server name as well set @svrName = @@SERVERNAME set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"' --creating a temporary table CREATE TABLE #output (line varchar(255)) --inserting disk name, total space and free space value in to temporary table insert #output EXEC xp_cmdshell @sql --script to retrieve the values in MB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,       (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,       (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)' from #output where line like '[A-Z][:]%' order by drivename --script to retrieve the values in GB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,       (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,       (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)' from #output where line like '[A-Z][:]%' order by drivename --script to drop the temporary table drop table #output 

Sample Output

I executed the XP_Fixeddrives extended stored procedure and the above script on the same machine. You can see that XP_Fixeddrives does not show the total space or the LUN/mount drive information.

As you can see in the below image XP_FIXEDDRIVES is able to retrieve the information for the L:\ and F:\ physical disks, but is not able to retrieve the information of the LUNs or Mount Drives for example L:\UserDBData1 andF:\UserDBLog1.

How to create and find the indexes in a table

these is for finding the indexes whether cluster and non cluster indexes on particular table...

first i created one table on user database

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

create table asr (eno int,ename varchar(20),dep varchar(30))

then insert the values

insert into asr values(10,'asr','comp')

insert into asr values(12,'dsr','seci')

select * from asr

Then i created cluster index on table

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

create clustered index asr_cluster on asr(eno)

creating non cluster index

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

create index asr_noncluster on asr(ename)

create index asr_noncluster1 on asr(dep)

Query for finding the what type indexes in a table

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

sp_helpindex 'asr(table_name)'

select object_name(object_id) object, * from sys.indexes where object_id = object_id ('asr')

How to Find the SQL Server Port Number for Particular Instance

These are the Commands for finding the version of the SQL Server

EXEC sp_server_info

xp_msver

select serverproperty('ProductVersion')

select @@version

How to find the Error Log file Location for Particular SQL Instance

select serverproperty('ErrorLogFileName')

above Query doesn't work on SQL 2000

Recovery model and status of all Databases

SELECT name,
DATABASEPROPERTYEX(name, 'Recovery') as [Recovery Model],
DATABASEPROPERTYEX(name, 'Status') as Status
FROM master.dbo.sysdatabases
ORDER BY 1