Wednesday, 27 February 2013

High CPU usage caused by SQL Server

All processes that are currently running on the instance-


SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
and session_id <> @@spid


If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output)

  • RPC: Completed (Under stored procedures)


  • SQL: BatchCompleted (Under TSQL)


Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value.


1.Find disk delays for a particular database-

select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
where DB_NAME(database_id) = 'your database name'
order by avg_io_stall_ms desc


2. Find IO pending for an Instance-

select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle



3. Find memory pressure-


select * from sys.dm_os_performance_counters
where counter_name like 'page life%'


4. Find queries utilization-


SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE STATUS !='SLEEPING'
ORDER BY CPU DESC


5. Find CPU usage-


select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255


6. Find System info-
Select * from sys.dm_os_sys_info



7. There are some useful Dynamic Management Views (DMVs) to check CPU bottlenecks.

select plan_handle,
sum(total_worker_time) as total_worker_time,
sum(execution_count) as total_execution_count,
count(*) as number_of_statements
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc


SQL Server 2008 computes the hash value of every query during compilation. You can find this value in the query_hash column.
This value is shown as the QueryHash attribute in Showplan/Statistics XML too.
The plan_generation_num column shows how many times the query has been recompiled.

The SQL Server optimizer tries to choose an execution plan for the query that provides the fastest response time but this does not always mean minimal CPU utilization. Inefficient query plans that cause increased CPU consumption can also be detected using the sys.dm_exec_query_stats.


8. 

Monday, 25 February 2013

Mirrored Backup and Split File Backup

1. Conventional one-file backup-

BACKUP DATABASE [hhhh] 
TO  DISK =  N'D:\DBA-HIMALAYA-PC$SQL2008R2\AAA\hhhh.bak' 
GO

2. let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.


BACKUP DATABASE [hhhh]
TO DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak'
GO


RESTORE DATABASE [hhhh]
FROM DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak',
DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak'
GO







It's a common practice to create an exact copy of the backup and store it to several places.
There is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.




BACKUP DATABASE [hhhh_new] TO  DISK = N'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\hhh.bak'
mirror to DISK = N'C:\DBA-HIMALAYA-PC$SQL2008R2\AAA\hhh.bak'
WITH FORMAT, INIT,  NAME = N'hhhh_new', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO









Friday, 22 February 2013

SQL Performance tuning - Drilling into the problem

Example 1 – A query is slow because the response time is greater than 10 seconds and normally it’s 2 seconds
Example 2  -  Client User requests are queing up and we notice CPU usage at 100% , whereas the normal level is 25%
In Example 1, we’re using the response time  as the unit of measure for the task i.e query
In Example 2 , CPU usage is the unit of measure for the client user requests
Once the problem is quantified in this manner , break the quantities down into smaller parts. It may be session IDs (SPID), execution plan data etc. Each profile has it’s own data requirements. The key message is : attempt to gather performance data matching the problem
Creating the profile at the correct level of detail requires relevant data. SQL Server offers different methods of collecting data e.g DMV, Traces , Custom queries.   It’s important to collect data at the right range. If analysing a problem at a user level , then collect data at the user level.  Strip out  noise.
If analysing a user session resource usage , don’t use  system wide aggregates as your starting point. The main point is to collect relevant data.
At a user level, SQL Server is a collection of sessions (SPID). Nothing occurs without a session. Sessions are used to execute T-SQL statements. During execution of a  T-SQL statement , there is work on the CPU or the session goes idle – either being blocked or waiting for some activity to complete. In other words, different events are contributing to the overall time spent
Identify the SPID of the user complaint. Break the session down and analyse how the time is spent during the session. How is time spent during the session? IO, CPU, Locks , idle :  all use time and contribute to the response time.
 A chronologically ordered breakdown of how the session spends time gives clues for the delays. The delay could be on the network , application , storage etc
 So far , we’ve looked at the user level. What happens  if insufficient data is available ? At this point the DBA collects more data , waiting for a reoccurrence.
Developing a systematic and objective approach to SQL performance troubleshooting is key to fixing problems. Avoid relying on luck and focus on the facts.

#youmightbeaDBA


Your two main enemies are developers and SAN admins #youmightbeaDBA

When your girlfriend has to lean around the laptop to kiss you goodnight #youmightbeadba

You always plan an exit strategy, even when entering a McDonald's #youmightbeaDBA 

You can't explain to your family what you really do for a living #youmightbeaDBA 

You have at least one set of scripts you won't share #youmightbeaDBA 

You and the Oracle DBA would happily fight off a developer together #youmightbeaDBA 

You've sent a vendor suggestions on improving their database design or code (and been ignored) #youmightbeaDBA 

You've sent a vendor suggestions on improving their database design or code (and been ignored) #youmightbeaDBA 

You refer to clothes as "Data Abstractions" #youmightbeaDBA 

Your girlfriend knows what "ETL" means #youmightbeaDBA 

You check your server logs before you check your e-mail in the morning so you can reply "Yeah, I already fixed that." #youmightbeaDBA 

You can convince a boss that you need 16GB of RAM in your laptop #youmightbeaDBA 

You call cigarettes "work vitamins" #youmightbeaDBA 

You've tried to convince someone else to become a DBA #youmightbeaDBA 

You call texting a "queuing system" #youmightbeaDBA 

You have an e-mail rule for backup notifications #youmightbeaDBA 

You would pay money for a license plate that has the letters S-Q-L together #youmightbeaDBA 

You have actually considered making a RAID array from thumb drives #youmightbeaDBA 

You've written blog posts on technology you've never actually implemented in production #youmightbeaDBA 

Your blog would make a "best practices" or "worst practices" book #youmightbeaDBA 

The first thing you install on your netbook is SSMS #youmightbeaDBA 

You tell people that you made a database query go faster, and expect them to be happy for you #youmightbeaDBA 

You take the word "NoSQL" as a personal attack #youmightbeaDBA 

You think that something OTHER than the database might be the performance bottleneck #youmightbeaDBA 

You refer to time as a "Clustered Index" #youmightbeaDBA 

You think a millisecond is a really long time #youmightbeaDBA 

You think "Set theory" is not an verb but a noun #youmightbeaDBA

You don't like Oracle, and not just because of what she did to Neo #youmightbeaDBA 

You know when to say "sequel" and "s-q-l" #youmightbeaDBA 

Holiday == "Maintenance Window" #youmightbeaDBA

You cringe in public when the word "upgrade" is used in a sentence #youmightbeaDBA 

When someone tells you they are going to take a dump and you wonder of which database then #youmightbeaDBA 

When it's 11pm on a holiday weekend and you are working #youmightbeaDBA 

When getting milk from the fridge you check the expiry date is > getdate() #youmightbeaDBA 

Your friends don't understand why you think there's a difference between single and double quotes #youmightbeaDBA 

The thought of disrupting 500 people's work so you can do something doesn't phase you #youmightbeaDBA

Your home computer is backed up in 2 different places #youmightbeaDBA 

If you have ever set up a SQLAgent job to email your mobile phone to serve as an alarm clock #youmightbeaDBA 

The late-night security guard knows the names of your town. #youmightbeaDBA 

You have edited a data file by hand, just to see what would happen. #youmightbeaDBA 

You've built programs that access data just to keep other developers from asking you to run queries all the time. #youmightbeaDBA 

You have memorized the names of several of the AdventureWorks employees. #youmightbeaDBA 

When the waiter says he'll be your server today, you ask how many terabytes he is #youmightbeaDBA 

hmmm... #youmightbeaDBA if your recipe for dinner is "SELECT * FROM Refrigerator" 

If you're up at midnight commenting about SQL #youmightbeaDBA 

I'd noticed that. :) #youmightbeaDBA 

when people talk about "their type" you're thinking varchar, bigint, binary, etc #youmightbeadba 

Someone asks you to throw another log on the fire and you tell them not to worry about it because Autogrowth is turned on #youmightbeaDBA 

you google to trouble shoot a problem and end up at your own blog (and it fixes it) #youmightbeaDBA

You list TSQL as your native language on the 2010 census. #youmightbeaDBA 

You're sitting and typing #youmightbeaDBA when you could be outside #youmightbeaDBA 

Thursday, 21 February 2013

SQL SERVER – Get Server Version and Additional Info


It is quite common to get the SQL Server version details from following query.

SELECT @@VERSION VersionInfo
GO





Recently I have been using following SP to get version details as it also provides me few more information about the server where the SQL Server is installed.

EXEC xp_msver
GO




But what good is this command if your sql server has expired and you can't even open management studio.
For this situation, use the following option-


When you click on the link below, SQL Server will be started to discover the SQL Server features.

The Installation Center can be launched from the Start Menu, under the SQL Server 2008 entry:

Click on the “Installed SQL Server features discovery report”

Here is the report:



The SQL Server Discovery Report is saved to %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<last Setup Session>

Options:


  • You can also generate the Discovery report through the command line. Run “Setup.exe /Action=RunDiscovery” from a command prompt
  • If you add “/q” to the command line above no UI will be shown, but the report will still be created in %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\20091112_082147.



Wednesday, 20 February 2013

Recompilation Of Execution Plan after plan cache flushing


In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and cause a sudden, temporary excessive CPU usage and decrease in query performance.

The following operations cause the plan cache flushing



  • When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server.
  • ALTER DATABASE [dbName] SET ONLINE
  • ALTER DATABASE [dbName] SET OFFLINE
  • ALTER DATABASE [dbName] SET READ_ONLY
  • ALTER DATABASE [dbName] SET READ_WRITE
  • ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name]
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY
  • ALTER DATABASE [dbName] COLLATE Collation_Name
  • DROP DATABASE [db_Snapshot_Name]
  • Restore database operation
  • Detach database operation
  • DBCC FREEPROCCACHE
  • DBCC FREESYSTEMCACHE

Script To fix all Orphaned users


USE [master]
GO
CREATE PROCEDURE [dbo].[SP_AutoFixOrphanUsers]

AS
SET NOCOUNT ON
---Declare Variables

DECLARE @iDBSUCCEED           INT,
            @iDBFAIL          INT,
            @iAutoID        INT,
            @iMaxAutoID     INT,
            @iRetryAttempts TINYINT,
        @cUserName      NVARCHAR(128) --Variable to hold current UserName to process
     
DECLARE @OrphanUsers TABLE
        (AutoID     INT IDENTITY(1,1),
         UserName    NVARCHAR(128))

-- Initialize Variables
--
SET   @iDBSUCCEED = 0
SET   @iDBFAIL    = -100
SET @iAutoID    = 1
SET @iRetryAttempts = 0

BEGIN TRY

    -- Add Orphan Users into the Temp Table
 -----
    INSERT INTO @OrphanUsers(UserName)
        SELECT  name
        FROM    sysusers
        WHERE   (issqluser = 1)
            AND (sid is not null and sid <> 0*0)
            AND (suser_sname(sid) is null)
        ORDER BY name

    -- Get the Max AutoID
  -----
    SELECT  @iMaxAutoID = MAX(AutoID)
    FROM    @OrphanUsers

    -- Loop through orphan users and fix those
    ---------
    WHILE @iAutoID <= @iMaxAutoID
    BEGIN

        -- Get the UserName
        -----------
        SELECT  @cUserName = UserName
        FROM    @OrphanUsers
        WHERE   AutoID = @iAutoID

            -- Check whether if that login exists here
            -- If it exists then proceed fixing the user
            ------
            IF (SELECT  COUNT(*)
                  FROM    master..syslogins
                  WHERE name = @cUserName) = 1
            BEGIN
                  -- Re-sync the currently selected orphan user
                  -----------
                  EXEC sp_change_users_login 'auto_fix', @cUserName  
            END

        -- Get Next Orphan User
        ----
        SELECT @iAutoID = @iAutoID + 1
    END
 
    -- Return Success
  ---------
    RETURN @iDBSUCCEED

END TRY

-- Error Handling
BEGIN CATCH

    -- Return Failure
      ---------
      RETURN @iDBFAIL

END CATCH


EXEC SP_AutoFixOrphanUsers

Script to get the most expensive SQL statement in addition to their Query plan


DECLARE @MinExecutions int;
SET @MinExecutions = 5

SELECT A.total_worker_time AS TotalWorkerTime
      ,A.total_logical_reads + A.total_logical_writes AS TotalLogicalIO
      ,A.execution_count As ExeCnt
      ,A.last_execution_time AS LastUsage
      ,A.total_worker_time / A.execution_count as AvgCPUTimeMiS
      ,(A.total_logical_reads + A.total_logical_writes) / A.execution_count
       AS AvgLogicalIO
      ,DB.name AS DatabaseName
      ,SUBSTRING(B.text
                ,1 + A.statement_start_offset / 2
                ,(CASE WHEN A.statement_end_offset = -1
                       THEN LEN(convert(nvarchar(max), B.text)) * 2
                       ELSE A.statement_end_offset END
                 - A.statement_start_offset) / 2
                ) AS SqlStatement
        ,C.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_query_stats AS A
     CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B
     CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C
     LEFT JOIN sys.databases AS DB
         ON B.dbid = DB.database_id  
WHERE  A.last_execution_time > DATEDIFF(MONTH, -1, GETDATE())
ORDER BY AvgLogicalIo DESC
        ,AvgCPUTimeMiS DESC

Starting SQL server In single Mode using Command Net start



  • Launch the command line as administrator
  • Run the following command net start MSSQL$INSTanceName /m
  • Stop the SQL service InstanceName using Net Stop

Create a SQL Server event alert


In this Example we will configure SQL Server Agent to transmit an alert if an error of severity level 17 occurs and alert the operator Operator_admin when it happens

·         In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
·         In the New Alert dialog box, add a new alert with the properties in the following table.

·         Configure the alert as below :

Name: Sql Server Resource alert
Type: Sql server event alert
Database Name: MyuserDB
Severity: 017-Insufficient Resources
Raise alert when Message Contains: Cleared

·         In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
·         On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: An error occurred in MyuserDB database, and then click OK.

To test that the alert is triggered when the condition is met
·         Connect to The instance using SSMS

·         On the File menu, point to New, and then click Query with Current Connection.

·         In the query pane, type the following Transact-SQL code.

USE MyUserDB
GO
RAISERROR (‘Test Error’, 17, 1) WITH LOG

·         On the Query menu, click Execute.

·         Switch to Outlook and verify the new mail exists.

See WHO is blocking OFF


set nocount on
declare @spid varchar(10)
declare @blkby varchar(10)
declare @stmt varchar(1000)

   create table #temp ( spid integer, status_1 varchar(100), login_1 varchar(50), hostname varchar(25), blkby varchar(10),
                        dbname varchar(100), command varchar(100), cputime integer, diskio integer, lastbatch varchar(25),
                        programname varchar(255), spid2 integer, Requestid varchar(10) )


insert into #temp
exec sp_who2

declare curs cursor for
select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'

open curs

fetch next from curs into @spid, @blkby
while @@fetch_status = 0
begin
   set @stmt = 'dbcc inputbuffer(' + @blkby + ')'
   raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait
   exec (@stmt)
   raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait
   set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'
   exec (@stmt)
   fetch next from curs into @spid, @blkby
end

close curs

deallocate curs

select * from #temp

drop table #temp

List Datafiles names , Locations and Size of all instance Databases (best script ever)


create table #temp (Databasename varchar(100),name varchar(1000),fileid int , filename varchar(500),filegroup varchar(100) ,
                    size varchar(200),maxsize varchar(300),growth varchar(100),usage varchar(100))

declare @database varchar(100)
Declare c cursor for select name  from master..sysdatabases where dbid not in (13,14)
open c
fetch next from c into @database
while @@fetch_status=0
begin
exec('use '+@database +'
insert into #temp(name ,fileid , filename,filegroup ,
                    size ,maxsize ,growth ,usage )
 exec sp_helpfile')
update #temp set Databasename=@database where Databasename is null
fetch next from c into @database
end
close c
deallocate c

update #temp set size=replace(size,'KB','')
select * from #temp
select 'Sql Server',rtrim(convert(varchar(100),a.Filename))+'&'+left(replace(replace(b.filename,'d:\Program Files\Microsoft SQL Server\MSSQL\',''),'data\',''),26)
,a.DatabaseName,CONVERT(char(20), SERVERPROPERTY('servername'))
  from #temp a inner join (select * from #temp where usage<>'data only') b
on a.databasename=b.databasename
where a.usage='data only' order by 1



drop table #temp

Create a SQL Server Performance Condition alert


we will configure SQL Server Agent to transmit an alert if the number of connections is above 50 and alert the operator Operator_admin when it happens

  •  In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
  • In the New Alert dialog box, add a new alert with the properties in the following table.
  • Configure the alert as below :


Name: Sql Server Performance alert
Type: Sql server Performance Condition alert
Object: Sql server General Statistics
Counter: User connections
Alert if counter rises above: 50


  • In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
  • On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: The number of user connections has exceeded 50 on the instance NamedInstance, and then click OK.

Create a SQL Server WMI alert (CPU Workload)



         
In this example, we will create an alert in sql server to be notified when a CPU average workload is 90% within a minute

  • In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
  • In the New Alert dialog box, add a new alert with the properties in the following table.
  • Configure the alert as below :



Name: SQL Performance CPU alert
Type: WMI event Alert
Namespace: \\.\ROOT\CIMV2
Query: SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA “Win32_Processor” AND TargetInstance.LoadPercentage > 90





  • In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
  • On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: The Average CPU workload has exceeded 90% on the instance NamedInstance, and then click OK

List SQL Server Tables without clustered Indexes


SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID) FROM SYS.INDEXES WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 ORDER BY [TABLE]

Monday, 18 February 2013

The PowerShell script for execution of T-SQL batch files


<#--------------------------------------------------------------------------
.SYNOPSIS
Script for  running T-SQL files in MS SQL Server
Andy Mishechkin

.DESCRIPTION
runsql.ps1 has a next command prompt format:
.\runsql.ps1 -server MSSQLServerInstance -dbname
ExecContextDB -file MyTSQL.sql [-go] [-u SQLUser] [-p SQLPassword]

Mandatory parameters:
-server - name of Microsoft SQL Server instance
-dbname - database name for  T-SQL execution context (use the '-dbname master' for  creation of new database)
-file - name of .sql file, which contain T-SQL code for  execution

Optional parameters:
-go - parameter-switch, which must be, if  T-SQL code is contains 'GO'  statements. If you will use the -go switch for T-SQL script, which is not contains 'GO'-statements - this  script will not execute
-u - the user name if  using Microsoft SQL Server authentication
-p - the password  if  using Microsoft SQL Server authentication

Examples.

1) Execute on local SQL Server the script CreateDB.sql, which is placed in  C:\MyTSQLScripts\ and contains 'GO'  statements, using

Windows credentials of current user:
.\runsql.ps1 -server local -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go

2) Execute on remote SQL Server Express with
machine name 'SQLSrvr'  the script CreateDB.sql, which is placed in C:\MyTSQLScripts\ and
contains 'GO' statements, using SQL Server user name 'sa' and password 'S@Passw0rd':
.\runsql.ps1 -server SQLSrvr\SQLEXPRESS -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go -u sa -p S@Passw0rd

---------------------------------------------------------------------------#>
#Script parameters
param(
        #Name of MS SQL Server instance
        [parameter(Mandatory=$true,
               HelpMessage="Specify the SQL Server name where will be run a T-SQL code",Position=0)]
        [String]
        [ValidateNotNullOrEmpty()]
        $server = $(throw "sqlserver parameter is required."),

        #Database name for execution context
        [parameter(Mandatory=$true,
               HelpMessage="Specify the context database name",Position=1)]
        [String]
        [ValidateNotNullOrEmpty()]
        $dbname = $(throw "dbname parameter is required."),

        #Name of T-SQL file (.sql)
        [parameter(Mandatory=$true,
               HelpMessage="Specify the name of T-SQL file (*.sql) which will be run",Position=2)]
        [String]
        [ValidateNotNullOrEmpty()]
        $file = $(throw "sqlfile parameter is required."),

        #The GO switch. Must be specified if T-SQL code is contain the GO instructions
        [parameter(Mandatory=$false,Position=3)]
        [Switch]
        [AllowEmptyString()]
        $go,

        #MS SQL Server user name
        [parameter(Mandatory=$false,Position=4)]
        [String]
        [AllowEmptyString()]
        $u,

        #MS SQL Server password name
        [parameter(Mandatory=$false,Position=5)]
        [String]
        [AllowEmptyString()]
        $p
    )
#Connect to MS SQL Server
try
{
    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    #The MS SQL Server user and password is specified
    if($u -and $p)
    {
        $SQLConnection.ConnectionString = "Server=" + $server + ";Database="  + $dbname + ";User ID= "  + $u + ";Password="  + $p + ";"
    }
    #The MS SQL Server user and password is not specified - using the Windows user credentials
    else
    {
        $SQLConnection.ConnectionString = "Server=" + $server + ";Database="  + $dbname + ";Integrated Security=True"
    }
    $SQLConnection.Open()
}
#Error of connection
catch
{
    Write-Host $Error[0] -ForegroundColor Red
    exit 1
}
#The GO switch is specified - parsing T-SQL code with GO
if($go)
{
    $SQLCommandText = @(Get-Content -Path $file)
    foreach($SQLString in  $SQLCommandText)
    {
        if($SQLString -ne "go")
        {
            #Preparation of SQL packet
            $SQLPacket += $SQLString + "`n"
        }
        else
        {
            Write-Host "---------------------------------------------"
            Write-Host "Executed SQL packet:"
            Write-Host $SQLPacket
            $IsSQLErr = $false
            #Execution of SQL packet
            try
            {
                $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLPacket, $SQLConnection)
                $SQLCommand.ExecuteScalar()
            }
            catch
            {

                $IsSQLErr = $true
                Write-Host $Error[0] -ForegroundColor Red
                $SQLPacket | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
                $Error[0] | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
                "----------" | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
            }
            if(-not $IsSQLErr)
            {
                Write-Host "Execution succesful"
            }
            else
            {
                Write-Host "Execution failed"  -ForegroundColor Red
            }
            $SQLPacket = ""
        }
    }
}
else
{
    #Reading the T-SQL file as a whole packet
    $SQLCommandText = @([IO.File]::ReadAllText($file))
    #Execution of SQL packet
    try
    {
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLCommandText, $SQLConnection)
        $SQLCommand.ExecuteScalar()
    }
    catch
    {
        Write-Host $Error[0] -ForegroundColor Red
    }
}
#Disconnection from MS SQL Server
$SQLConnection.Close()
Write-Host "-----------------------------------------"
Write-Host $file "execution done"





Script for running T-SQL files in MS SQL Server


runsql.ps1 has a next command prompt format:
.\runsql.ps1 -server MSSQLServerInstance -dbname ExecContextDB -file MyTSQL.sql [-go] [-u SQLUser] [-p SQLPassword]

Mandatory parameters:
-server - name of Microsoft SQL Server instance

-dbname - database name for  T-SQL execution context (use the '-dbname master' for  creation of new database)

-file - name of .sql file, which contain T-SQL code for  execution

Examples.
1) Execute on local SQL Server the script CreateDB.sql, which is placed in C:\MyTSQLScripts\ and contains 'GO' statements, using

Windows credentials of current user:

.\runsql.ps1 -server local -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go

2) Execute on remote SQL Server Express with machine name 'SQLSrvr' the script CreateDB.sql, which is placed in C:\MyTSQLScripts\ and
contains 'GO' statements, using SQL Server user name 'sa' and password 'S@Passw0rd':

.\runsql.ps1 -server SQLSrvr\SQLEXPRESS -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go -u sa -p S@Passw0rd


Install SQL-DMO for Clients



SQL-DMO (Data Management Objects) is not the best choice, but has regardless been adopted in one of our little applications.

Previously, it has always (to my knowledge) installed perfectly fine on many client machines. However, after having taken over the project (for some other improvements) I come to find myself having major deployment problems with the SQLDMO.dll file. The file is used in a setup Custom Action to allow the user to configure database details on install.

Any attempts to register this file fail (via regsvr32). There are various answers to how to get it registered; I tried a few and none worked.

  • LoadLibrary (snip) failed - The specified module could not be found.



To cut a long story short, you need to install "Microsoft SQL Server 2005 Backward Compatibility Components" (which contains SQL-DMO):
http://www.microsoft.com/en-us/download/details.aspx?id=15748







Unfortunately, there are no standalone redistributables just for SQL-DMO.  However, you can specify to only install the DMO parts in the installer, but this is down to the install.

To register the SQL-DMO components on a client computer
  • From C:\Program Files\Microsoft SQL Server \80\Tools\Binn\Resources\<language> directory, execute:
\Program Files\Microsoft SQL Server \80\Tools\Binn\REGSVR32 SQLDMO.DLL


Sunday, 17 February 2013

Joke for database



One day a DBA guy called and ask advise in very critical situation.
Comunication of us as follows…
Guy called “Hi Gitesh, I am in very critical condition.”“What is happening?” I asked
“My database is down, Kindly help what to do” DBA guy told
“Explain me situation” I asked more details to advise him
” System datafile is corrupt and giving error to perform media recovery” He explained.
“Then what are you looking for? Just restore backup and enable media recovery” I told him.
“But I didn’t take backup yesterday?” He murmuring.
“Does your database is on archivelog mode?” I tried to solve his problem.
“No it is running on noarchivelog mode” He answered.
” When you took last backup?” I asked.
“One month back, after that backup gets failed due to space problem” He disclosed and asked again “Please give me advise to resolve and get rid of this critical problem”
“Update your resume and start applying” I advised

Saturday, 16 February 2013

Database Files Physical Location


SET NOCOUNT ON
declare @db_list table( row_no smallint identity (1,1), db varchar(200))

INSERT into @db_list
select name from master..sysdatabases

declare @first smallint
declare @last smallint
declare @db varchar(200)
declare @sql varchar(500)

select @first = min(row_no) from @db_list
select @last = max(row_no) from @db_list

create table #db_file_list ( db_name varchar(100), Filename varchar(200), file_location varchar(500))

while @first <= @last
BEGIN

select @db = db from @db_list where row_no = @first

SET @sql = 'INSERT INTO #db_file_list select '+ CHAR(39) + @db + CHAR(39)+' ,name,filename from '+ @db+'..sysfiles '

--print (@sql)
exec (@sql)

SET @first = @first + 1

END

--select * from #db_file_list where file_location like '%N:%'

select * from #db_file_list

drop table #db_file_list

SET NOCOUNT OFF

Ping Linked servers


I created a monitoring server and one of the things I want to know if all the servers that I look after are up and running. So I created this proc to do just that. I even have a linked server that is an Oracle box and I pinged that one to ensure I can still get a connection to it.
I wanted to only know when any of the linked servers where having issues so this is how this was created. I added parameters and logic to handle the situation where you want to know the the job was run and what it's findings were.
I will proably add the the sql to write this out to a table so we can record system uptime over time and automatically calculate database availability % and report on that SLA .
  
To execute from a SSMS
EXAMPLE USE 1
 DECLARE @return_value int
 EXEC @return_value = [dbo].[usp_lsping]
 @nf = N'Y',
 @wtn = N'E'
 SELECT 'Return Value' = @return_value
I set this up as a job to run at regular intervals ie dev/test servers every 30 min every 6 minutes for production boxes I want it to let know me if it finds an error. I added an in clause to the where clause that builds the list of the servers so that one version check the dev and test versions and another checks the production databases.
 Please feel free to update and modify as you see fit please contribute it back so we can get a good script to use and share with everyone.



Create procedure usp_lsping(@nf nchar = 'N', @wtn nchar = 'E')
WITH ENCRYPTION
as 
-- ===========================================================================
-- Description:Description to ping the linked server and see if there alive
-- ===========================================================================
-- Parameters
-- @nf - did you want to be notified Y - yes N - no 
-- @wtn - when to notofiy E - on error S - on Succes B - on Both
--
-- EXAMPLE USE
--DECLARE@return_value int
--EXEC@return_value = [dbo].[usp_lsping]
--@nf = N'Y',
--@wtn = N'E'
--SELECT'Return Value' = @return_value
-- ===========================================================================
-- 
set nocount on
BEGIN
DECLARE @LSrvrs TABLE 
(
SrvrID int IDENTITY(1,1) PRIMARY KEY, 
SrvName nvarchar(128)
)

insert into @LSrvrs
select srvname from sys.sysservers
where srvname != CONVERT(nvarchar(128), SERVERPROPERTY('servername'));
;
--
declare @maxloocnt int;
declare @loopcnt int;
declare @srvr nvarchar(128);
declare @retval int;
declare @Clr nvarchar(4)
declare @msg nvarchar(MAX)
declare @errchk int;
--
set @errchk = 0;
set @Clr = char(13)+char(10);
select @maxloocnt = count(*) from @LSrvrs;
set @loopcnt = 1;
set @msg = '';
set @msg = @msg + '<Start>'+@Clr
while @loopcnt <= @maxloocnt
begin
select @srvr = srvname from @LSrvrs where SrvrID = @loopcnt;
--select @srvr
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
set @errchk = 99;
end catch;
--if @retval =qwqw 0 
set @msg = @msg + @Clr+@srvr + ':' + cast(@retval as nvarchar(100));
set @loopcnt = @loopcnt + 1;
end;
set @msg = @msg + @Clr+'<end>'+@Clr
if @nf = 'Y'
begin
--if @wtn = 'E' or @wtn = 'S' or @wtn = 'B'
--BEGIN
--print 'WTN:'+@wtn
--print '@errchk:'+ CONVERT(nvarchar(128),@errchk)
if (@wtn = 'S' and @errchk = 0) or (@wtn = 'E' and @errchk > 0) or(@wtn = 'B' )
BEGIN
--select @msg;
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'mark.huber@bp.com',@body=@msg, 
@subject ='Linked Server Ping'--,@query ='select @msg;', 
--@attach_query_result_as_file = 1,@query_attachment_filename ='PingResults.txt'
END;
--else
-- BEGIN
--
-- END
END
--end 
--else
--BEGIN
-- '';
--END;

END
set nocount off



Restore database from a device containing multiple backups


If you have multiple backup database backups on a single SQL Server 2008 R2 backup device, this script will help you restore a database from the LATEST full and differential backups residing on that backup device.

If this script is to be used for restoring a SQL Server 2005 database, please remove "CompressedBackupSize" from the CREATE temporary table statement.


/*
Script to restore the LATEST full and differential backups from a SQL Server 2008 R2 backup device.
If this script is to be used for restoring a SQL Server 2005 database, please remove "CompressedBackupSize" from the temporary table
*/
----------------------------------------------------------------------------
--1. Create a temporary table for holding backup header information
----------------------------------------------------------------------------
IF OBJECT_ID('TempDB..#RestoreHeaderOnlyData') IS NOT NULL
DROP TABLE #RestoreHeaderOnlyData
GO
CREATE TABLE #RestoreHeaderOnlyData( 
BackupName NVARCHAR(128) 
,BackupDescription NVARCHAR(255) 
,BackupType smallint 
,ExpirationDate datetime 
,Compressed tinyint 
,Position smallint 
,DeviceType tinyint 
,UserName NVARCHAR(128) 
,ServerName NVARCHAR(128) 
,DatabaseName NVARCHAR(128) 
,DatabaseVersion INT 
,DatabaseCreationDate datetime 
,BackupSize numeric(20,0) 
,FirstLSN numeric(25,0) 
,LastLSN numeric(25,0) 
,CheckpointLSN numeric(25,0) 
,DatabaseBackupLSN numeric(25,0) 
,BackupStartDate datetime 
,BackupFinishDate datetime 
,SortOrder smallint 
,CodePage smallint 
,UnicodeLocaleId INT 
,UnicodeComparisonStyle INT 
,CompatibilityLevel tinyint 
,SoftwareVendorId INT 
,SoftwareVersionMajor INT 
,SoftwareVersionMinor INT 
,SoftwareVersionBuild INT 
,MachineName NVARCHAR(128) 
,Flags INT 
,BindingID uniqueidentifier 
,RecoveryForkID uniqueidentifier 
,Collation NVARCHAR(128) 
,FamilyGUID uniqueidentifier 
,HasBulkLoggedData INT 
,IsSnapshot INT 
,IsReadOnly INT 
,IsSingleUser INT 
,HasBackupChecksums INT 
,IsDamaged INT 
,BeginsLogChain INT 
,HasIncompleteMetaData INT 
,IsForceOffline INT 
,IsCopyOnly INT 
,FirstRecoveryForkID uniqueidentifier 
,ForkPointLSN numeric(25,0) 
,RecoveryModel NVARCHAR(128) 
,DifferentialBaseLSN numeric(25,0) 
,DifferentialBaseGUID uniqueidentifier 
,BackupTypeDescription NVARCHAR(128) 
,BackupSetGUID uniqueidentifier 
,CompressedBackupSize BIGINT 

----------------------------------------------------------------------------
--2. Collect header information FROM the backup device into a temporary table
----------------------------------------------------------------------------
INSERT INTO #RestoreHeaderOnlyData 
EXEC('RESTORE HEADERONLY FROM DISK = ''\\servername\foldername\BackupDeviceName.bak''') 

----------------------------------------------------------------------------
--3. Complete database restore from the latest FULL backup; 
----------------------------------------------------------------------------
--NORECOVERY is specified so that roll back not occur. This allows additional backups to be restored. 
DECLARE @File smallint
SELECT @File = MAX(Position) 
FROM #RestoreHeaderOnlyData 
WHERE DatabaseName = 'SomeDatabase' 
    AND BackupTypeDescription = 'Database'

RESTORE DATABASE [SomeDatabase] 
FROM DISK = N'\\servername\foldername\BackupDeviceName.bak' 
WITH FILE = @File, 
    MOVE N'SomeDatabase' TO N'E:\Data_NI1\SomeDatabase.mdf', 
    MOVE N'SomeDatabase_log' TO N'E:\Log_NI1\SomeDatabase.ldf', NOUNLOAD, REPLACE, STATS = 10, NORECOVERY
GO

----------------------------------------------------------------------------
--4. Next: Restore the latest differential database backup
----------------------------------------------------------------------------
--If log backups are to be restored, specify NORECOVERY in this step also
--Then use RESTORE LOG to restore logs in the correct sequence 
--Specify RECOVERY in the last RESTORE LOG statement

DECLARE @File smallint
SELECT @File = MAX(Position) 
FROM #RestoreHeaderOnlyData 
WHERE DatabaseName = 'SomeDatabase' 
    AND BackupTypeDescription = 'Database Differential'
    
RESTORE DATABASE [SomeDatabase] FROM DISK = N'\\servername\foldername\BackupDeviceName.bak'
    WITH FILE = @File, 
    MOVE N'SomeDatabase' TO N'E:\Data_NI1\SomeDatabase.mdf', 
    MOVE N'SomeDatabase_log' TO N'E:\Log_NI1\SomeDatabase.ldf', 
NOUNLOAD, REPLACE, STATS = 10, RECOVERY