Category Archives: SQL Server Database Administration

How to add article in Transactional Replication

Let’s consider a scenario we have a database in which we have 3 tables 2 of them are already added in replication and we want to add a third table in publication.

This is more important when we have a larger database and we don’t want to reinitialize the replication.

To avoid complete snapshot of the Publisher database and to avoid re-initialization we need to make some changes in the existing replication. We need to set 2 properties to False as shown below

  1. allow_anonymous
  2. Immediate_sync

Execute below commands

Now add table T3 again from Publisher properties and press ok.

  1. USE
  2. GO
  3. EXEC sp_changepublication @publication = ‘PubDB’,
  4. @property = ‘allow_anonymous’,
  5. @value = ‘false’
  6. GO
  7. USE
  8. GO
  9. EXEC sp_changepublication @publication = ‘PubDB’,
  10. @property = ‘immediate_sync’,
  11. @value = ‘false’
  12. GO

addtable3

Now add the new articles in existing publication

addtable3_pub

Now start Snapshot Agent

startsnapshot

Now you can notice that it only creates a snapshot of one article instead of all articles, now start log reader agent if it’s in the stopped state.

snapshot

Now as you can see a snapshot was generated but only for one article, so this article will be replicated to all subscriber without impacting existing replication.

Don’t forgot to execute below command at last to enable the disabled properties.

  1. Use
  2. GO
  3. EXEC sp_changepublication @publication = ‘PubDB’,
  4. @property = ‘allow_anonymous’ ,
  5. @value = ‘True’
  6. GO
  7. Use
  8. GO
  9. EXEC sp_changepublication @publication = ‘PubDB’,
  10. @property = ‘immediate_sync’ ,
  11. @value = ‘True’
  12. GO

replsyncoption

Now you can verify the article on all your subscribers

If you want to know more about immediate sync command follow below link

  1. Immediate_sync

 

 

SQL Performance Analyzer and Health Check

Sara Performance Analyzer

Sara Performance Analyzer (SQL Administration and Reporting Analysis) is a windows based application which helps you in troubleshooting SQL Server issues. The application perform health check on various parameters and provide a full report for your SQL Server. The tool provide detailed information on below parameters

  • Hardware
  • Disk Status
  • Services
  • Instance
  • DB Configuration
  • Backup Status
  • Temp DB Status
  • Performance
  • Job related information

SARA1SARA2SARA3SARA4

Download the zip file and extract all the file in folder and run setup.exe. This will install application with name SARA your machine.

>>Download Sara Performance Analyzer<<

How to run this tool and pre-requisites for this application

  • You must be SYSADMIN on SQL Server and Server Admin on Machine to run this application
  • Application enables XP_Cmdshell to execute some command and then disable it accordingly so test this in your DEV and Test environment before executing it on production
  • Use SQL Authentication OR Integrated option
  • Enter Server Name
  • Select Authentication
  • Click Process

This application will take around 1-3 minutes to finish all test and then displays result in Data Grid.

How many SQL Instance can I host efficiently on existing server?

It’s a very open ended questions and before answering this question you need to understand what kind of application you are using and how much transaction they makes on database. You need to understand size of databases and how much CPU\MEM is required to run those databases on SQL server also it more depends on your performance capacity of Physical Server\VM, I mean if you are having a server with multiple cores 100 Gigs of mem and super-fast SAN you can host multiple instances.

This kind of consolidation of instance has its own Pros and Cons as this could be a beneficial but it can also be a headache for a DBA or Architecture team.

Why Would You Use Multiple SQL Server Instances?

  • You can save lot of cost for hardware
  • License costs for SQL Server currently are by CPU socket by machine, not by instance on a machine. Doubling up the instances on a machine can save lot of cost
  • Fully utilize the hardware performance to its limit

Performance Bench-marking is very important for consolidation (running multiple instances on server), this bench-marking will provide you information like how much MIN\MAX CPU and how much MIN\MAX memory is required to run this SQL Instance, also how much storage capacity is required. Apart from these things network bandwidth and I/O plays an important role here before making decision to host multiple instances on one Server.

What Important counters I must collect for defining my benchmark of SQL Instance?

Identify the Physical configuration of your server on which you are hosting this SQL Instance

ServerConfig

You need to focus on various trends for below performance counters to define minimum, average and maximum value required for your SQL Instance to run.

  1. Identify CPU utilization trend over a period of time to identify CPU required to run SQL Instance: This will let you decide how much computing power\processors are required to host this SQL instance.

SysUCPUU

2. Identify CPU utilization of database: You can easily get this information from T-SQL what is the average utilization of CPU for your database, a job can be configured on a server and you can draw a trend and actually see which database is consuming most CPU

 WITH db_cpu_stats 
 AS (SELECT databaseid, 
 db_name(databaseid) AS [database name], 
 sum(total_worker_time) AS [cpu_time_ms] 
 FROM sys.dm_exec_query_stats AS qs 
 CROSS apply (SELECT CONVERT(int, value) AS [databaseid] 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle) 
 WHERE attribute = N'dbid') AS f_db 
 GROUP BY databaseid) 
 
 SELECT 
 
 GETDATE() AS CAPTURE_DATE, 
 [database name] AS 'Name', 
 [cpu_time_ms] AS 'CPU Time [MS]', 
 cast([cpu_time_ms] * 1.0 / sum([cpu_time_ms]) 
 OVER() * 100.0 AS decimal(5, 2)) AS 'CPU [%]' 
 
 FROM db_cpu_stats 
 WHERE databaseid <> 32767 -- ResourceDB 
 ORDER BY cast([cpu_time_ms] * 1.0 / sum([cpu_time_ms]) 
 OVER() * 100.0 AS decimal(5, 2)) desc 
 OPTION (recompile)

 

DBCpu

3. Identify Memory utilization of database

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';
;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

DBmem4. Identify below counters usage to define MIN\MAX memory and other memory configurations.

These counters will help to understand about the load on the SQL Server. Monitor these counters over a period of time and analyze the trend that will actually give you min max and average utilization of these parameters

BP.png

PLE

PageL3

5. Identify the database usage trends to define capacity management

DBTrend

Apart from all this you need to focus on the of I/O sub system, Your I/O subsystem must be fast and further separation your Logs, Data and TEMPDB data files, will give you an additional advantage

Once you define you bench-marking on the basis of above counters which will help you in defining cpu, memory, storage and bandwidth requirement for a SQL Instance.

Who Detach database on SQL Server

Recently we are facing some complaint from a customer that one of their database gets detached automatically once a week and they have no idea why this is happening.

The other problem is that when they try to attach the SQL Servers says the file is already use even though the datafile is not attached to any database.

Technically speaking SQL database can be detached only when user has sysadmin rights and can only be detached or attached with manual intervention  i.e, either by script (Job,.Net Application or by Powershell etc.) or through SQL SSMS. When a database is

We can track this event from default trace as shown below.

DECLARE @path NVARCHAR(260)
 SELECT @path=path FROM sys.traces WHERE is_default = 1
 SELECT TE.name AS EventNamem, DT.TextData,DT.HostName,dt.StartTime,DT.ApplicationName,
 DT.LoginName
 FROM dbo.fn_trace_gettable (@path, DEFAULT) DT
 INNER JOIN sys.trace_events TE
 ON DT.EventClass = TE.trace_event_id
 where TextData LIKE 'DBCC DETACHDB%'

DetachDB

It has been seen in SQL Server when you detach a database SQL service account lost permission on file so I would suggest you take the database offline and then detach database, this will not reset permission on data\log files, else you have to remove un-necessary permission and have to add SQL  Service Account again.even because of this reason you will get error like file already in use even though it’s not in use. In that case remove un-necessary permission and add SQL Service Account Permission.

MDFPer

 

How to handle Memory Pages Per Second is too High?

Some time when you configure SCOM (System Center Operation Manager – Monitoring Tool)  in your environment and configure a SQL Management Pack to monitor “Memory Pages Per Second” then you will get the below alert as mentioned ” Memory Pages Per Second is too High. – The threshold for the Memory\Pages\sec performance counter has been exceeded

Let’s first discuss how this alert is raised by per System Center Operation Manager

Here this counter Pages/sec means it’s the rate at which pages are read from or written to disk to resolve hard page faults. In more detail it identifies the rate of pages read from and written to disk for resolving memory requests for pages that are not in memory. In other words, Pages/Sec represents pages input/sec and pages output/sec. (Pages output represents the pages removed from memory heading to disk.).  If you talk about page fault it happens when the memory manager does not find the block of memory that it needs in the cache. That memory has been swapped out to disk, and this will degrade your Server \SQL server performance.

This rule will generate an alert when the pages/sec exceeds the defined threshold for a sustained period of time and Default is Memory\Pages/Sec >50).

When this performance threshold has been exceeded it often means that the system is low on physical memory because the operating system is reading from the hard disk to recover information from the virtual memory. When the system is in this state the overall system performance is significantly diminished and result in poor operating system and application performance.

Hence please make sure that this rule must be tuned accordingly to the server configuration keeping in mind about the performance capacity of server before it can be used. So before enabling this rule create a baseline Pages/Sec performance signature for each system. Once you create a baseline, set a default threshold that will work for the largest percentage of managed nodes and then use the override feature to define threshold for the exception cases.

From the SQL Server perspective when you receive this kind of alert it’s a primary indicator suggesting that there could be an issue of memory pressure\bottleneck, but that depends on several factors how frequent you are getting this alert from same server and how it is configured, allocated memory to SQL, no. of instances running on server etc.

Note: A high number in the Pages/Sec counter does not necessarily mean that you have a memory problem, it could be a temporary issue but if the same server is generating same kind of alert then please monitor the below mentioned counter over a period of time to confirm if there is a need to add more memory to your system to reduce the number of page faults and improve performance.

Memory – Available M Bytes

SQL Server: Buffer Manager – Page Life Expectancy (Higher is good)

SQL Server: Memory Manager – Memory Grants Pending (Lower is good)

SQL Server: Buffer Manager, Buffer Cache Hit Ratio (higher is better)

SQL Server: Memory Manager – Target Server Memory

SQL Server: Memory Manager – Total Server Memory

SQL Server: SQL Statistics – Batch Requests/sec

SQL Server: SQL Statistics – Compilations/sec

Hence from the Capacity Planning point of view from the performance perspective ,keep watch for an upward trend in the value of Memory\Pages/Sec and add the memory when paging operation absorb more than 20-50 % of your total disk I/O bandwidth

Once a while this kind of alert from SQL is ok you can monitor for some time and resolve if all goes fine, but if the alert is frequent then you have to investigate on the above counters because if could an issue of memory crunch\bottleneck and I would say the trend for these counter values is more important than their absolute value at any given time and If you notice that the average Page Life Expectancy is dropping over time, that is quite significant indication(and not in a good condition).

Conclusion: Don’t ignore this alert if it’s coming frequently from certain servers and during that time on focus on trend obtained from above counters (You can ignore this alert if you are aware that during certain time some heavy activity is going on server and because of that this alert appears and off Couse if customer is not complaining). Also If you are sure that for some server you can’t do anything because their hardware lifecycle is over and you don’t want to spend too much on them then define your threshold again or override this alert with the help of SCOM team in your environment, otherwise if you feel that it’s actually a serious issue on server on the basis of above counters then ask customer to add more memory on their dedicated server but make sure before asking him to add more memory although these days the memory chips are quite cheap but still there is a cost involve.

garmin

Error: SQL Server 2008 Installation Error: “Invoke or Begin Invoke cannot be called on a control until the window handle has been created”

Error: SQL Server 2008 Installation Error: “Invoke or Begin Invoke cannot be called on a control until the window handle has been created”

While installing SQL Server 2008 I received above error and the same error could be encountered if you install SQL Server 2008 on Windows 7. Later I came to know that this issue is happening because of a behavior change in .NET that is exposing an issue with SQL Setup User Interface (UI).

Resolution:

1.In the Installation Disc you will find a folder, x86 and x64

2.Select as per your OS and go inside the folder

3.Click on the LandingPage.exe

LandingPage.exe

4. Click on the second Option on the left hand menu “Installation”

 PandingPage2

5. Start Standalone installation from the menu at the right side

 6. .It will ask for the location files on the disc

 7. Show the location, e.g. /SQL Server Enterprise 2008/x86 or x64 depending on OS

 It will complete the installation successfully

 Work Around:-

 You can also try to install SQL Server Slipstream setup with SP1 CPU4 as MS confirmed this is a problem and is corrected in Cumulative Update 4 for SQL Server 2008 Service Pack 1.

Please refer below link for more detail

http://support.microsoft.com/kb/975055

How to extract historical deadlock from extended events

DECLARE@ringAS[xml];

 

SET@ring= (SELECT     cast(xet.[target_data]AS[xml])

                        FROM       sys.dm_xe_session_targetsxet

                        INNERJOINsys.dm_xe_sessionsxe

                                        ONxe.[address]=xet.[event_session_address]

                        WHERE      xe.[name]=’system_health’);

 

SELECT                                                                                                                AS’DEADLOCKS’,

                        row_number()

                                OVER (

                                        ORDERBYsyshealth.xevent.value(‘(@timestamp)’,’DATETIME’))           AS’Sequence’,

                        syshealth.xevent.value(‘(@timestamp)’,

                                                                   ‘DATETIME’)                                    AS’Deadlock time’,

                        –SysHealth.XEvent.query(‘.’) AS [DeadlockEvent],

 

                        cast(syshealth.xevent.value(‘data[1]’,

                                                                                ‘NVARCHAR(MAX)’)ASxml)         AS’Deadlock graph’

–SysHealth.XEvent.value(‘data[1]’,’NVARCHAR(MAX)’) AS DeadlockGraph

FROM        (SELECT@ringASring)ASbuffer

CROSSapplyring.nodes (‘//RingBufferTarget/event’)ASsyshealth (xevent)

WHERE       syshealth.xevent.value(‘(@name)[1]’,

                                                                   ‘varchar (100)’)=’xml_deadlock_report’

ORDER       BY[deadlock time]DESC;

When was the last time DBCC CHECKDB executed on my databases

CREATE TABLE #Temp

(
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
)
INSERT INTO #temp
EXECUTE SP_MSFOREACHDB’DBCC DBINFO ( ”?”) WITH TABLERESULTS’;
;WITH CHECKDB1 AS
(
SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN (‘dbi_dbname’))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN (‘dbi_dbccLastKnownGood’)
)    
SELECT CHECKDB1.Value AS DatabaseName, replace(CHECKDB2.Value,’1900-01-01 00:00:00.000′,’Never Run’) AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2

DROP TABLE #temp

Script to get detail all detail of SQL Instance Health in Last 7 Days

SET NOCOUNT ON

use master

go

 

print ‘***************************************************************’

print ‘  MANUAL ACTIVITIES  ‘

print ‘    ‘

print ‘ A. See database startup parameters  ‘

print ‘ B. See SQL Server Error Log and NT Event Viewer ‘

print ‘ C. See authentication mode ( NATIVE or MIXED ) ‘

print ‘ D. See SQL Server and SQL Agent services account startup ‘

print ‘ E. See SQL Mail configuration                  ‘

print ‘ F. See backup politic ( full and transaction ) ‘

print ‘***************************************************************’

 

print ”

print ‘1. General Info’

print ‘*********************’

print ”

 

print ‘Server Name……………: ‘ + convert(varchar(30),@@SERVERNAME)

print ‘Instance………………: ‘ + convert(varchar(30),@@SERVICENAME)

print ‘Current Date Time………: ‘ + convert(varchar(30),getdate(),113)

print ‘User………………….: ‘ + USER_NAME()

go

 

print ”

print ‘1.1 Database and Operational System versions.’

print ‘———————————————-‘

print ”

 

select @@version

go

 

exec master..xp_msver

go

 

print ”

print ‘1.2 Miscelaneous’

print ‘—————————‘

print ”

 

select convert(varchar(30),login_time,109) as ‘Servidor inicializado em ‘ from master..sysprocesses where spid = 1

 

print ‘Number of connections..: ‘ + convert(varchar(30),@@connections)

print ‘Language……………: ‘ + convert(varchar(30),@@language)

print ‘Language Id…………: ‘ + convert(varchar(30),@@langid)

print ‘Lock Timeout………..: ‘ + convert(varchar(30),@@LOCK_TIMEOUT)

print ‘Maximum of connections.: ‘ + convert(varchar(30),@@MAX_CONNECTIONS)

print ‘Server Name…………: ‘ + convert(varchar(30),@@SERVERNAME)

print ‘Instance……………: ‘ + convert(varchar(30),@@SERVICENAME)

print ”

print ‘CPU Busy………..: ‘ + convert(varchar(30),@@CPU_BUSY/1000)

print ‘CPU Idle………..: ‘ + convert(varchar(30),@@IDLE/1000)

print ‘IO Busy…………: ‘ + convert(varchar(30),@@IO_BUSY/1000)

print ‘Packets received…: ‘ + convert(varchar(30),@@PACK_RECEIVED)

print ‘Packets sent…….: ‘ + convert(varchar(30),@@PACK_SENT)

print ‘Packets w errors…: ‘ + convert(varchar(30),@@PACKET_ERRORS)

print ‘TimeTicks……….: ‘ + convert(varchar(30),@@TIMETICKS)

print ‘IO Errors……….: ‘ + convert(varchar(30),@@TOTAL_ERRORS)

print ‘Total Read………: ‘ + convert(varchar(30),@@TOTAL_READ)

print ‘Total Write………: ‘ + convert(varchar(30),@@TOTAL_WRITE)

go

 

———————————————————————————————————-

print ”

print ‘2. Server Parameters’

print ‘*************************’

print ”

 

–exec sp_configure ‘show advanced options’,1

exec sp_configure

go

———————————————————————————————————-

print ”

print ‘3. Databases parameters’

print ‘***************************’

print ”

 

exec sp_helpdb

go

 

SELECT LEFT(name,30) AS DB,

 SUBSTRING(CASE status & 1 WHEN 0 THEN ” ELSE ‘,autoclose’ END +

 CASE status & 4 WHEN 0 THEN ” ELSE ‘,select into/bulk copy’ END +

 CASE status & 8 WHEN 0 THEN ” ELSE ‘,trunc. log on chkpt’ END +

 CASE status & 16 WHEN 0 THEN ” ELSE ‘,torn page detection’ END +

 CASE status & 32 WHEN 0 THEN ” ELSE ‘,loading’ END +

 CASE status & 64 WHEN 0 THEN ” ELSE ‘,pre-recovery’ END +

 CASE status & 128 WHEN 0 THEN ” ELSE ‘,recovering’ END +

 CASE status & 256 WHEN 0 THEN ” ELSE ‘,not recovered’ END +

 CASE status & 512 WHEN 0 THEN ” ELSE ‘,offline’ END +

 CASE status & 1024 WHEN 0 THEN ” ELSE ‘,read only’ END +

 CASE status & 2048 WHEN 0 THEN ” ELSE ‘,dbo USE only’ END +

 CASE status & 4096 WHEN 0 THEN ” ELSE ‘,single user’ END +

 CASE status & 32768 WHEN 0 THEN ” ELSE ‘,emergency mode’ END +

 CASE status & 4194304 WHEN 0 THEN ” ELSE ‘,autoshrink’ END +

 CASE status & 1073741824 WHEN 0 THEN ” ELSE ‘,cleanly shutdown’ END +

 CASE status2 & 16384 WHEN 0 THEN ” ELSE ‘,ANSI NULL default’ END +

 CASE status2 & 65536 WHEN 0 THEN ” ELSE ‘,concat NULL yields NULL’ END +

 CASE status2 & 131072 WHEN 0 THEN ” ELSE ‘,recursive triggers’ END +

 CASE status2 & 1048576 WHEN 0 THEN ” ELSE ‘,default TO local cursor’ END +

 CASE status2 & 8388608 WHEN 0 THEN ” ELSE ‘,quoted identifier’ END +

 CASE status2 & 33554432 WHEN 0 THEN ” ELSE ‘,cursor CLOSE on commit’ END +

 CASE status2 & 67108864 WHEN 0 THEN ” ELSE ‘,ANSI NULLs’ END +

 CASE status2 & 268435456 WHEN 0 THEN ” ELSE ‘,ANSI warnings’ END +

 CASE status2 & 536870912 WHEN 0 THEN ” ELSE ‘,full text enabled’ END,

2,8000) AS Descr

FROM master..sysdatabases

go

———————————————————————————————————-

print ”

print ‘4. LOG utilization’

print ‘****************************’

print ”

 

dbcc sqlperf(logspace)

go

———————————————————————————————————-

print ”

print ‘5. Datafiles list’

print ‘***********************’

print ”

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#TempForFileStats ‘))

DROP TABLE #TempForFileStats

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#TempForDataFile’))

DROP TABLE #TempForDataFile

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#TempForLogFile’))

DROP TABLE #TempForLogFile

 

DECLARE @DBName nvarchar(100)

DECLARE @SQLString nvarchar (4000)

DECLARE c_db CURSOR FOR

 SELECT name

 FROM master.dbo.sysdatabases

 WHERE status&512 = 0

 

CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),

  [Database Name] nvarchar(100),

  [File Name] nvarchar(128),

  [Usage Type] varchar (6),

  [Size (MB)] real,

  [Space Used (MB)] real,

  [MaxSize (MB)] real,

  [Next Allocation (MB)] real,

  [Growth Type] varchar (12),

  [File Id] smallint,

  [Group Id] smallint,

  [Physical File] nvarchar (260),

  [Date Checked] datetime)

 

CREATE TABLE #TempForDataFile ([File Id] smallint,

  [Group Id] smallint,

  [Total Extents] int,

  [Used Extents] int,

  [File Name] nvarchar(128),

  [Physical File] nvarchar(260))

 

CREATE TABLE #TempForLogFile ([File Id] int,

  [Size (Bytes)] real,

  [Start Offset] varchar(30),

  [FSeqNo] int,

  [Status] int,

  [Parity] smallint,

  [CreateTime] varchar(30))

 

OPEN c_db

FETCH NEXT FROM c_db INTO @DBName

WHILE @@FETCH_STATUS = 0

 BEGIN

 SET @SQLString = ‘SELECT @@SERVERNAME  as ”ServerName”, ‘ +  — changed by seraj : *8 to *8.000000000

  ”” + @DBName + ”” + ‘ as ”Database”, ‘ +

  ‘ f.name, ‘    +

  ‘ CASE ‘    +

  ‘ WHEN (64 & f.status) = 64 THEN ”Log” ‘  +

  ‘ ELSE ”Data” ‘   +

  ‘ END  as ”Usage Type”, ‘ +

  ‘ f.size*8.00000000/1024.00 as ”Size (MB)”, ‘ +

  ‘ NULL  as ”Space Used (MB)”, ‘ +

  ‘ CASE f.maxsize ‘   +

  ‘ WHEN -1 THEN -1 ‘   +

  ‘ WHEN 0 THEN f.size*8.00000000/1024.00 ‘  +

  ‘ ELSE f.maxsize*8.00000000/1024.00 ‘  +

  ‘ END  as ”Max Size (MB)”, ‘ +

  ‘ CASE ‘    +

  ‘ WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ‘ +

  ‘ WHEN f.growth =0  THEN 0 ‘ +

  ‘ ELSE   f.growth*8.00000000/1024.00 ‘ +

  ‘ END  as ”Next Allocation (MB)”, ‘ +

  ‘ CASE ‘    +

  ‘ WHEN (1048576&f.status) = 1048576 THEN ”Percentage” ‘ +

  ‘ ELSE ”Pages” ‘   +

  ‘ END  as ”Usage Type”, ‘ +

  ‘ f.fileid, ‘    +

  ‘ f.groupid, ‘    +

  ‘ filename, ‘    +

  ‘ getdate() ‘    +

  ‘ FROM [‘ + @DBName + ‘].dbo.sysfiles f’ — Seraj Alam added []

 INSERT #TempForFileStats

 EXECUTE(@SQLString)

 

 ————————————————————————

 SET @SQLString = ‘USE [‘ + @DBName + ‘] DBCC SHOWFILESTATS’ — seraj alam added []

 INSERT #TempForDataFile

 EXECUTE(@SQLString)

 —

 UPDATE #TempForFileStats

 SET [Space Used (MB)] = s.[Used Extents]*64/1024.00

 FROM #TempForFileStats f,

 #TempForDataFile s

 WHERE f.[File Id] = s.[File Id]

 AND f.[Group Id] = s.[Group Id]

 AND f.[Database Name] = @DBName

 —

 TRUNCATE TABLE #TempForDataFile

 ————————————————————————-

 

 SET @SQLString = ‘USE [‘ + @DBName + ‘] DBCC LOGINFO’ — seraj alam added []

 INSERT #TempForLogFile

 EXECUTE(@SQLString)

 —

 UPDATE #TempForFileStats

 SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +

   SUM(CASE

   WHEN l.Status <> 0 THEN l.[Size (Bytes)]

   ELSE 0

   END))/1048576.00

  FROM #TempForLogFile l

  WHERE l.[File Id] = f.[File Id])

 FROM #TempForFileStats f

 WHERE f.[Database Name] = @DBName

 AND f.[Usage Type] = ‘Log’

 —

 TRUNCATE TABLE #TempForLogFile

 ————————————————————————-

 FETCH NEXT FROM c_db INTO @DBName

 END

DEALLOCATE c_db

 

SELECT * FROM #TempForFileStats

————

DROP TABLE #TempForFileStats

DROP TABLE #TempForDataFile

DROP TABLE #TempForLogFile

go

———————————————————————————————————-

print ”

print ‘6. IO per datafile’

print ‘******************’

print ”

use tempdb

go

 

if exists (select [id] from sysobjects where [id] = OBJECT_ID (‘#TBL_DATABASEFILES’))

 DROP TABLE #TBL_DATABASEFILES

 

 

if exists (select [id] from sysobjects where [id] = OBJECT_ID (‘#TBL_FILESTATISTICS’))

 DROP TABLE #TBL_FILESTATISTICS

 

 

DECLARE @INT_LOOPCOUNTER INTEGER

DECLARE @INT_MAXCOUNTER INTEGER

DECLARE @INT_DBID INTEGER

DECLARE @INT_FILEID INTEGER

DECLARE @SNM_DATABASENAME SYSNAME

DECLARE @SNM_FILENAME SYSNAME

DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)

 

DECLARE @MTB_DATABASES TABLE (

ID INT IDENTITY,

DBID INT,

DBNAME SYSNAME )

 

CREATE TABLE #TBL_DATABASEFILES (

ID INT IDENTITY,

DBID INT,

FILEID INT,

FILENAME SYSNAME,

FILENAME1 varchar(600),

DATABASENAME SYSNAME)

 

INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID

SET @INT_LOOPCOUNTER = 1

SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES

WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER

BEGIN

 SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER

 SET @NVC_EXECUTESTRING = ‘INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT ‘+STR(@INT_DBID)+’ AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,”’+@SNM_DATABASENAME+”’ AS DATABASENAME FROM [‘+@SNM_DATABASENAME+’].DBO.SYSFILES’

 EXEC SP_EXECUTESQL @NVC_EXECUTESTRING

 SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1

END

–‘OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC….

 

CREATE TABLE #TBL_FILESTATISTICS (

ID INT IDENTITY,

DBID INT,

FILEID INT,

DATABASENAME SYSNAME,

FILENAME SYSNAME,

SAMPLETIME DATETIME,

NUMBERREADS BIGINT,

NUMBERWRITES BIGINT,

BYTESREAD BIGINT,

BYTESWRITTEN BIGINT,

IOSTALLMS BIGINT)

 

SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES

SET @INT_LOOPCOUNTER = 1

WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER

BEGIN

 SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER

 INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)

 SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)

 SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1

END

select * from #TBL_FILESTATISTICS

 

drop table #TBL_DATABASEFILES

drop table #TBL_FILESTATISTICS

go

—————————————————————————————

print ”

print ‘7. List of last backup full”s’

print ‘*************************************’

print ”

 

select      SUBSTRING(s.name,1,40)              AS    ‘Database’,

      CAST(b.backup_start_date AS char(11))     AS    ‘Backup Date ‘,

      CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())

            THEN ‘Backup is current within a day’

       WHEN b.backup_start_date > DATEADD(dd,-7,getdate())

            THEN ‘Backup is current within a week’

       ELSE ‘*****CHECK BACKUP!!!*****’

            END

                                    AS ‘Comment’

 

from master..sysdatabases    s

LEFT OUTER JOIN   msdb..backupset b

      ON s.name = b.database_name

      AND b.backup_start_date = (SELECT MAX(backup_start_date)

                              FROM msdb..backupset

                              WHERE database_name = b.database_name

                                    AND type = ‘D’)         — full database backups only, not log backups

WHERE s.name <> ‘tempdb’

 

ORDER BY    s.name

go

———————————————————————————————————-

print ”

print ‘8. List of logins’

print ‘********************’

print ”

 

exec sp_helplogins

go

———————————————————————————————————-

print ”

print ‘9. List of users per role’

print ‘*******************************’

print ”

 

exec sp_helpsrvrolemember

go

———————————————————————————————————-

print ”

print ’10.List of special users per database’

print ‘*************************************’

print ”

 

 

declare @name sysname,

      @SQL nvarchar(600)

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#tmpTable’))

      drop table #tmpTable

CREATE TABLE #tmpTable (

      [DATABASE_NAME] sysname NOT NULL ,

      [USER_NAME] sysname NOT NULL,

      [ROLE_NAME] sysname NOT NULL)

 

declare c1 cursor for

      select name from master.dbo.sysdatabases

open c1

fetch c1 into @name

while @@fetch_status >= 0

begin

      select @SQL =

            ‘insert into #tmpTable

             select N”’+ @name + ”’, a.name, c.name

            from ‘ + QuoteName(@name) + ‘.dbo.sysusers a

            join ‘ + QuoteName(@name) + ‘.dbo.sysmembers b on b.memberuid = a.uid

            join ‘ + QuoteName(@name) + ‘.dbo.sysusers c on c.uid = b.groupuid

            where a.name != ”dbo”’

 

            /*    Insert row for each database */

            execute (@SQL)

      fetch c1 into @name

end

close c1

deallocate c1

select * from #tmpTable

 

drop table #tmpTable

go

———————————————————————————————————-

print ”

print ’11. Information about remote servers ‘

print ‘*****************************************’

print ”

 

Print ‘Linked Servers’

print ”

 

exec sp_linkedserver

 

print ‘linked Server login mappings’

print ”

 

exec sp_helplinkedsrvlogin

 

print ‘Remote Logins’

print ”

 

exec sp_helpremotelogin

 

go

———————————————————————————————————-

print ”

print ’12. List of jobs ‘

print ‘*******************’

print ”

 

exec msdb..sp_help_job

go

———————————————————————————————————-

 

print ”

print ’13. Cache Hit Ratio ‘

print ‘*******************’

print ”

 

select      distinct counter_name,

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio%’

      and   A.counter_name = B.counter_name) as CurrHit,

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio base%’

      and   lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ‘ base’)) as CurrBase,

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio%’

      and   A.counter_name = B.counter_name) /

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio base%’

      and   lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ‘ base’)) as HitRatio

from master..sysperfinfo as A (nolock)

where       Lower(A.counter_name) like ‘%hit ratio%’

and   Lower(A.counter_name) not like ‘%hit ratio base%’

 

— Audit list as a double verification

 

select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value

from master..sysperfinfo (nolock)

where       Lower(counter_name) like ‘%hit ratio%’

or    Lower(counter_name) like ‘%hit ratio base%’

group by counter_name

 

go

———————————————————————————————————-

 

print ”

print ’14. SP_WHO ‘

print ‘***********’

print ”

exec sp_who

exec sp_who2

go

 

———————————————————————————————————-

 

print ”

print ’14. SP_LOCKS ‘

print ‘***********’

print ”

exec sp_lock

 

go

 

 

set nocount on

use msdb

go

Print ”

Print ‘Linked Servers’

print ‘***********’

print ”

 

exec sp_linkedservers

 

Print ”

print ’15. Maintenance Plans’

print ‘***********’

print ”

go

 

select @@servername “ServerName”, smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name “Job Part Name”, sjs.enabled “Job Enabled”, sjs.freq_type “Frequency”, sjs.active_start_time “Job Start Time”

from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs

where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id

go

Print ”

print ‘Maintenance Plan 2005′

SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],

CAST(sv.enabled AS bit) AS [IsEnabled],

ISNULL(sv.description,N”) AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N”) AS [OwnerLoginName],

sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],

sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],

sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv

go

 

 

set dateformat dmy

 

use msdb

go

 

select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size

from (

select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size

from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id

inner join backupfile b on

c.backup_set_id=b.backup_set_id and b.file_type=’D’

) y

inner join

(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id

inner join backupfile b on

c.backup_set_id=b.backup_set_id and b.file_type=’D’ where c.backup_finish_date > getdate()-10

) z

on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date

 

 

 

 

 

Print ”

Print ’16. OTHER’

PRINT ‘DTS Packages’

print ‘***********’

print ”

 

go

select @@servername “ServerName”, sd.name, sd.id, sd.categoryid, sdc.name “Category”, sd.description, owner, createdate

from sysdtspackages sd, sysdtscategories sdc

where sd.categoryid = sdc.id

go

exec sp_MSgetalertinfo

 

print ”

print ‘Alerts settings’

print ‘***********’

print ”

 

exec msdb..sp_help_alert

 

Print ”

Print ‘Operators’

print ‘***********’

print ”

 

EXECUTE sp_help_operator

 

print ”

print ‘SQL Mails’

EXECUTE master.dbo.xp_sqlagent_notify N’M’,null,null,null,N’E’

 

print ”

Print ‘SQL Agent Propertes’

EXECUTE msdb.dbo.sp_get_sqlagent_properties

 

print ”

print ‘Startup Parameters 01′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg0′

 

print ”

print ‘Startup Parameters 02′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg1′

 

print ”

print ‘Startup Parameters 03′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg2′

 

print ”

print ‘Startup Parameters 04′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg3′

 

— Though there is very little chance of more than 4 parameters, still it is good to check.

 

print ”

print ‘Startup Parameters 05′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg4′

 

print ”

print ‘Startup Parameters 06′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg5′

 

print ”

 

–exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\Setup’, N’SQLPath’

 

 

print ”

print ‘Login Mode’

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, ‘LoginMode’

go

 

print ”

Print ‘Audit Level’

 

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, N’AuditLevel’

 

Print ”

Print ‘Clustered?’

select convert(int, serverproperty(N’isclustered’))

go

Print ”

Print ‘Is Mapi set?’

DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Windows Messaging Subsystem’,’MAPIX’,@retval OUTPUT SELECT @retval

go

 

Print ”

Print ‘Mail Account’

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, N’MailAccountName’

Print ”

 

set nocount off

 

 

print ‘******************************************************************’

print ‘  FIM   ‘

print ‘******************************************************************’

———————————————————————————————————-

set nocount off