Initial Data Collection Script


Where would you be able to use it?

The cases where you can use this script effectively are:

1. Performance issues where a quick initial look at the general state of the server is very valuable.

2. Situations where issue is actually happening right now. However, that said, it is always good to collect post mortem data as well for further diagnostics. Performance Dashboard is a good option here but if you want a snapshot of the server and store it somewhere, then this script can help you.

3. Situations where the problem is happening and you need to quickly verify that you are not hitting some known issues.

How is this different from the other scripts that are already existing?

There are scripts already out there which do the same thing. The advantage here is the fact that the output is in HTML format with bookmarks which makes it easy to determine what information is available. Also, it is easy to get to the sections that are of interest in the current scenario.

Usage

  1. Download the script using the link given at the bottom of the page and save it to a file named INITIAL_DIAG.SQL. Open the script file in a SSMS Query Window.
  2. Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
  3. Execute the script and specify INITIAL_DIAG.html as the output file name so that we can get the output in the require HTML format.

Sample Output

********************************************************************************************
INITIAL MICROSOFT PSS DATA COLLECTION
********************************************************************************************

INDEX
1. General Server Configuration
a. Server Info
b. Non-default sp_configure settings
c. Server Settings
d. Active Trace Flags
e. Profiler Trace Information
2. Memory Diagnostics
a.DBCC MEMORYSTATUS output
b.Memory Clerks rollup output
3.Procedure Cache Statistics
4.Database Diagnostics
a.Replicated Databases
b.Database File Usage Stats
c.Index Statistics
d.sp_helpdb output
e.DBCC SQLPERF (LOGSPACE)
f.Hypothetical Indexes
5.Missing Indexes report
6.Blocking Information
7.Batch/Query Statistics

########################################

****************** SERVER INFO *********
########################################

—————————————————————–
Microsoft SQL Server 2005 – 9.00.3257.00 (X64)
Jun 12 2008 16:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

—————————————————————
SQL SERVER NAME: SERVER1

CLUSTERED: No

MACHINE NAME: SERVER1
NET BIOS NAME: SERVER1

SQL PID: 5076
FTS Installed: YES
Security Mode: MIXED
Current SPID: 51

Click here to download the script.

Modification: June 20, 2012: Added additional code to account for the following:

  • Added code to identify read-write workload ratios
  • Added code to find out non-Microsoft DLLs loaded in SQL address space using sys.dm_os_loaded_modules
  • Added code to get output for SQL Server 2012 instances
Advertisements

6 thoughts on “Initial Data Collection Script

  1. Hi Amit,

    Great post ! I am developing similiar solution for Health check for Sql servers in my company and this will be useful for me. I am almost done with my script, but will add few more checks by studying your script.

    I found that there are some errors reported when I run the script:

    1. Below block of code wont work on SQL 2005 [Invalid object name ‘sys.dm_os_sys_memory’.]

    IF ((select @@version) LIKE ‘%9.0%’ OR (select @@version) LIKE ‘%10.0%’ OR (select @@version) LIKE ‘%10.5%’ OR (select @@version) LIKE ‘%11.0%’)
    begin
    print ‘
    print ‘OS System Memory Ouput
    print ‘###############################################’
    select * from sys.dm_os_sys_memory
    print ‘Return to top

    print ‘
    print ‘OS Process Memory Ouput
    print ‘###############################################’
    select * from sys.dm_os_process_memory
    print ‘Return to top
    end

    SO we need to remove (select @@version) LIKE ‘%9.0%’ — sql server 2005

    2. Syntax error Incorrect syntax near ‘TokenAndPermUserStore’. Below is the corrected code (notice the 4 single quotes )

    print ”If you do find out that it is a TOKPENPERM issue, then use “DBCC FREESYSTEMCACHE (””TokenAndPermUserStore””)” as a temporary workaround”

    Cheers,
    \\K

    Like

  2. Pingback: SQL Feature Discovery Script « TroubleshootingSQL

  3. Thanks for really helpful script Amit.
    Found below issue when I ran in my DB with product version “10.50.2500.0”

    Msg 245, Level 16, State 1, Line 2
    Conversion failed when converting the varchar value ‘.250’ to data type int.
    Msg 245, Level 16, State 1, Line 2
    Conversion failed when converting the varchar value ‘.250’ to data type int.

    Failing at live 183 :

    IF ((select cast(substring(cast(serverproperty(‘ProductVersion’) as varchar(20)),6,4)as int)) < 5000 AND (select cast(substring(cast(serverproperty('ProductVersion') as varchar(20)),1,1) as int)) = 9)
    BEGIN
    print ' CURRENT BUILD FOR SQL SERVER 2005 IS BELOW MINIMUM SUPPORT LEVEL
    END
    go

    I think that substring with 6,4 might not work with some of the product versions. I fixed it by modifying it as below :

    Declare @pv varchar(20)
    set @pv = cast(serverproperty(‘ProductVersion’) as varchar(20))

    IF ((select cast(reverse(substring(reverse(@pv),charindex(‘.’,reverse(@pv))+1,4)) as int)) < 5000 AND (select cast(substring(cast(serverproperty('ProductVersion') as varchar(20)),1,1) as int)) = 9)
    BEGIN
    print ' CURRENT BUILD FOR SQL SERVER 2005 IS BELOW MINIMUM SUPPORT LEVEL
    END
    go

    Thanks again
    NJ

    Like

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s