Archive for December, 2008

You can stripe a SQL Server’s database backup across more than one file. Striping a SQL Server backup across more than one backup file can provide performance advantages as well as make it possible to backup a VLDB where there is no single drive available with enough free space. Striped backups are supported in SQL Server 2000 & SQL Server 2005.

— take a striped backup of the DB. Can be many more files than just two.
backup database northwind
to disk=’\\Server1\t$\northwind1.bak’,
disk=’\\Server2\h$\northwind2.bak’

— take a look at the logical files in the DB so we can move them on restore
restore filelistonly
from disk=’\\Server1\t$\northwind1.bak’,
disk=’\\Server2\h$\northwind2.bak’

— restore a DB from a striped backup
restore database northwind
from disk=’\\Server1\t$\northwind1.bak’,
disk=’\\Server2\h$\northwind2.bak’
with move ‘northwind_Data’ to ‘t:\northwind2.mdf’,
move ‘northwind_Log’ to ‘h:\northwind2.ldf’,

Advertisements

If SQL server is in trouble and you cannot connect to it then the next option is to connect using with the Dedicated Administrators Connection (DAC). Once you get connected, you can collect some information on what is happening. It would be nice to have a stored procedure to execute that displays information and can even generate a text file for future review.

Please use the below script and run on any DEV or Production server once. This script is a stored procedure you need to create under MASTER database. Reason behind this is when you connect you have access to MASTER database only when the server is extremely busy. Now open a CMD/DOS window and execute the following script

Link to enable DAC in sqlserver's http://www.mssqltips.com/tip.asp?tip=1005

USE master

GO

CREATE PROC sp_dba_DAC

AS

SELECT '*** Start of DAC Report ***'

SELECT '– Shows SQL Servers information'

EXEC ('USE MASTER')

SELECT

 CONVERT(char(20), SERVERPROPERTY('MachineName')) AS 'MACHINE NAME',

 CONVERT(char(20), SERVERPROPERTY('ServerName')) AS 'SQL SERVER NAME',

 (CASE WHEN CONVERT(char(20), SERVERPROPERTY('InstanceName')) IS NULL

 THEN 'Default Instance'

 ELSE CONVERT(char(20), SERVERPROPERTY('InstanceName'))

 END) AS 'INSTANCE NAME',

CONVERT(char(20), SERVERPROPERTY('EDITION')) AS EDITION,

 CONVERT(char(20), SERVERPROPERTY('ProductVersion')) AS 'PRODUCT VERSION',

 CONVERT(char(20), SERVERPROPERTY('ProductLevel')) AS 'PRODUCT LEVL',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 1

 THEN 'Clustered'

 WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 0

 THEN 'NOT Clustered'

 ELSE 'INVALID INPUT/ERROR'

 END) AS 'FAILOVER CLUSTERED',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 1

 THEN 'Integrated Security '

 WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 0

 THEN 'SQL Server Security '

 ELSE 'INVALID INPUT/ERROR'

 END) AS 'SECURITY',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 1

 THEN 'Single User'

 WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 0

 THEN 'Multi User'

 ELSE 'INVALID INPUT/ERROR'

 END) AS 'USER MODE',

CONVERT(char(30), SERVERPROPERTY('COLLATION')) AS COLLATION


SELECT '– Shows top 5 high cpu used statemants'

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],

 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

 ((CASE qs.statement_end_offset

 WHEN 1 THEN DATALENGTH(st.text)

 ELSE qs.statement_end_offset

 END qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;


SELECT '– Shows who so logged in'

SELECT login_name ,COUNT(session_id) AS session_count

FROM sys.dm_exec_sessions

GROUP BY login_name;


SELECT '– Shows long running cursors'

EXEC ('USE master')

SELECT creation_time ,cursor_id

 ,name ,c.session_id ,login_name

FROM sys.dm_exec_cursors(0) AS c

JOIN sys.dm_exec_sessions AS s

 ON c.session_id = s.session_id

WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

SELECT '– Shows idle sessions that have open transactions'

SELECT s.*

FROM sys.dm_exec_sessions AS s

WHERE EXISTS

 (

 SELECT *

 FROM sys.dm_tran_session_transactions AS t

 WHERE t.session_id = s.session_id

 )

 AND NOT EXISTS

 (

 SELECT *

 FROM sys.dm_exec_requests AS r

 WHERE r.session_id = s.session_id

 );

SELECT '– Shows free space in tempdb database'

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

 

SELECT '– Shows total disk allocated to tempdb database'

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

SELECT '– Show active jobs'

SELECT DB_NAME(database_id) AS [Database], COUNT(*) AS [Active Async Jobs]

FROM sys.dm_exec_background_job_queue

WHERE in_progress = 1

GROUP BY database_id;

 

SELECT '–Shows clients connected'

SELECT session_id, client_net_address, client_tcp_port

FROM sys.dm_exec_connections;

SELECT '–Shows running batch'

SELECT * FROM sys.dm_exec_requests;

 

SELECT '–Shows currently blocked requests'

SELECT session_id ,status ,blocking_session_id

 ,wait_type ,wait_time ,wait_resource

 ,transaction_id

FROM sys.dm_exec_requests

WHERE status = N'suspended'

 

SELECT '–Shows last backup dates ' as ' '

SELECT B.name as Database_Name,

 ISNULL(STR(ABS(DATEDIFF(day, GetDate(),

 MAX(Backup_finish_date)))), 'NEVER')

 as DaysSinceLastBackup,

 ISNULL(Convert(char(10),

 MAX(backup_finish_date), 101), 'NEVER')

 as LastBackupDate

 FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A

 ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

SELECT '–Shows jobs that are still executing' as ' '

exec msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

SELECT '–Shows failed MS SQL jobs report' as ' '

SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

SELECT '–Shows disabled jobs ' as ' '

SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

SELECT '–Shows avail free DB space ' as ' '

exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )

 AS int)/128.0 AS ''Available Space In MB'' FROM .SYSFILES'

SELECT '–Shows total DB size (.MDF+.LDF)' as ' '

 set nocount on

 declare @name sysname

 declare @SQL nvarchar(600)

 — Use temporary table to sum up database size w/o using group by

 create table #databases (

 DATABASE_NAME sysname NOT NULL,

 size int NOT NULL)

 declare c1 cursor for

 select name from master.dbo.sysdatabases

 — where has_dbaccess(name) = 1 — Only look at databases to which we have access

 open c1

 fetch c1 into @name

while @@fetch_status >= 0

 begin

 select @SQL = 'insert into #databases

 select N'''+ @name + ''', sum(size) from '

 + QuoteName(@name) + '.dbo.sysfiles'

 — Insert row for each database

 execute (@SQL)

 fetch c1 into @name

 end

 deallocate c1

select DATABASE_NAME, DATABASE_SIZE_MB = size*8/1000 — Convert from 8192 byte pages to K and then convert to MB

 from #databases order by 1

 

 select SUM(size*8/1000)as '–Shows disk space used – ALL DBs – MB ' from #databases

 

 drop table #databases

SELECT '–Show hard drive space available ' as ' '

EXEC master..xp_fixeddrives

SELECT '*** End of Report **** '

GO

—– Once you are done with the execution of the above script. Please open command prompt use below query to test it.


 sqlcmd -A -S<server/instance name> -dmaster -E -Q"sp_dba_DAC" -oc:dac-results.txt


Thanks & Regards
Md.S.Hassan

SQL Server 2005 Installation Step by Step

 

I found lots of queries in different forums regarding the installation of SQL Server 2005. If you don’t have any other instances of SQL server running on the machine, the usual “Next -> next … ” will do. Now, if you have already SQL Server 2000 already installed on your machine, then the above approach won’t be ideal, will just install the client tools only. So, what’s to be taken care, you need to install 2005 as a ‘Named’ instance if you have any plans for multiple sql server installations or already one instance is running. 

So, What you are waiting for ? Put the SQL Server 2005 CD/DVD on your CD/DVD drive and start Installation.

Step1 : As with every setups, the Fist Step is accepting the EULA.

clip_image001

Step 2 : Now the installation will check for the pre-requisites

clip_image002

clip_image003

Once these validations are over, it will start installation 

clip_image004

Next step is the System configuration Check

clip_image005

Once the configuration check is complete, the actual Installation starts

clip_image006

Now you need to select the components to install

clip_image007

clip_image008

here comes the most Important part of the setup

 

Once you click ‘next’, it will ask for whether to install SQL Server 2005 as a Default Instance or as a Named Instance.

This is very critical, if you have already have an SQL Server 2000 installed on your machine. If the SQL 2000 instance is the ‘default’ instance and you are trying to overwrite that installation with the 2005, it will just install the client tools and no server components will get installed. So you won’t get the new features of SQL Server 2005. As you can see in the following diagram, I have chosen a named instance “SQL2005_1”

clip_image009

 

 

That’s all, now you can continue the installation by the usual “Next->Next->Next..”.

The next screen prompts  you to select service account details,if you wish, you can select different accounts for each services.

clip_image010

Authentication Mode : this is also very important, if you are installing on a “development / QE” machine, better go for “Mixed Authentication Mode”.  Make sure that you put a complex password (something like your car registration number)

clip_image011

clip_image012

clip_image013

clip_image014

clip_image015

clip_image016

Now you can see, what are going on during the installation; what components are getting installed and what components “Failed”

clip_image017

If  you find all “Green”, your installation is successful.

clip_image018

That’s all. you can now start working on SQL Server 2005.


Thanks & Regards
Md.S.Hassan

DMV queries on a sql server 2005, It would be very useful to quickly analyze the workload and possible bottlenecks on a SQL server.


Query 1 shows which stored procedures are being called the most often

Query 2 shows the top 20 stored procedures sorted by total worker time (CPU pressure). This will tell you the most expensive stored procedures from a CPU perspective.

Query 3 shows the top 20 stored procedures sorted by total logical reads(Memory pressure). This will tell you the most expensive stored procedures from a memory perspective

Query 4 shows the top 20 stored procedures sorted by total physical reads(Read I/O pressure). This will tell you the most expensive stored procedures from a read I/O perspective.

Query 5 shows the top 20 stored procedures sorted by total logical writes(Write I/O pressure). This will tell you the most expensive stored procedures from a write I/O perspective.



Query 1

— Get Top 100 executed SP’s ordered by execution count

SELECT TOP 100 qt.text AS ‘SP Name’, qs.execution_count AS ‘Execution Count’,

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS ‘Calls/Second’,

qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,

qs.total_worker_time AS ‘TotalWorkerTime’,

qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’,

qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.dbid = db_id() — Filter by current database

ORDER BY qs.execution_count DESC

Query 2

— Get Top 20 executed SP’s ordered by total worker time (CPU pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, qs.total_worker_time AS ‘TotalWorkerTime’,

qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,

qs.execution_count AS ‘Execution Count’,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS ‘Calls/Second’,

ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS ‘AvgElapsedTime’,

qs.max_logical_reads, qs.max_logical_writes,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.dbid = db_id() — Filter by current database

ORDER BY qs.total_worker_time DESC

Query 3

— Get Top 20 executed SP’s ordered by logical reads (memory pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, total_logical_reads,

qs.execution_count AS ‘Execution Count’, total_logical_reads/qs.execution_count AS ‘AvgLogicalReads’,

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS ‘Calls/Second’,

qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,

qs.total_worker_time AS ‘TotalWorkerTime’,

qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’,

qs.total_logical_writes,

qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’, qt.dbid

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.dbid = db_id() — Filter by current database

ORDER BY total_logical_reads DESC

Query 4

— Get Top 20 executed SP’s ordered by physical reads (read I/O pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS ‘Avg Physical Reads’,

qs.execution_count AS ‘Execution Count’,

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS ‘Calls/Second’,

qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,

qs.total_worker_time AS ‘TotalWorkerTime’,

qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’,

qs.max_logical_reads, qs.max_logical_writes,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’, qt.dbid

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.dbid = db_id() — Filter by current database

ORDER BY qs.total_physical_reads DESC

Query 5

— Get Top 20 executed SP’s ordered by logical writes/minute (write I/O pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS ‘AvgLogicalWrites’,

qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Logical Writes/Min’,

qs.execution_count AS ‘Execution Count’,

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS ‘Calls/Second’,

qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,

qs.total_worker_time AS ‘TotalWorkerTime’,

qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’,

qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’,

qs.total_physical_reads/qs.execution_count AS ‘Avg Physical Reads’, qt.dbid

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.dbid = db_id() — Filter by current database

ORDER BY qs.total_logical_writes DESC

Here are a few more useful DMV queries that will give you a better idea which tables in your database are getting the most read and write activity :

— Tables with the most writes

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT TableName = object_name(s.object_id),

Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,’IsUserTable’) = 1

AND s.database_id = @dbid

GROUP BY object_name(s.object_id)

ORDER BY writes DESC

— Tables with the most reads

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT TableName = object_name(s.object_id),

Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,’IsUserTable’) = 1

AND s.database_id = @dbid

GROUP BY object_name(s.object_id)

ORDER BY reads DESC

— Script to create a database role for non db_owners to grant access to execute Stored procedures.

USE pubs
GO

EXEC sp_addrole 'db_executor'
GO

USE pubs

GO

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

— Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

— "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

— Set the return code to 0
SET @Return = 0

— Encapsulate the permissions assignment within a transaction
BEGIN TRAN

— Cycle through the rows of the cursor
— And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    — Create the SQL Statement. Since we're giving
    — access to all stored procedures, we have to 
    — use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure 
          + '] TO db_executor'

    — Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    — Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

— Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

— Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    — Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    — Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    — Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO


Thanks & Regards
Md.S.Hassan