Tuesday, July 31, 2012

SQL Server : Tables Relationship Diagram Using TSQL Script


How do you create relationship diagram/report between tables of a given database ? Mostly people use Database Diagram for this purpose, but this can be achieved by TSQL script as following.

 
-- Tables Relationship Script
-- Script By: Syed Muhammad Yasir for http://connectsql.blogspot.com
-- Updated August 1, 2012
 

SELECT  CASE WHEN a.parent_object_id IS NULL
THEN parent.name + '-1--*-' + child.name
ELSE parent.name + '-1--1-' + child.name
END AS TablesWithRelations
FROM    ( SELECT DISTINCT
parent_object_id, referenced_object_id
FROM      sys.foreign_keys ) fk
LEFT JOIN ( SELECT DISTINCT
fkindexes.parent_object_id,
fkindexes.referenced_object_id
FROM    ( SELECT    fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id, COUNT(*) cindexes
FROM      ( SELECT    object_id,
            parent_object_id,
            referenced_object_id
  FROM      ( SELECT    row_number() OVER ( PARTITION BY parent_object_id, referenced_object_id
ORDER BY object_id ) rid,
              object_id, parent_object_id, referenced_object_id
              FROM      sys.foreign_keys ) fk
  WHERE     rid = 1 ) fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fkc.parent_object_id
                                    AND ixcolumns.column_id = fkc.parent_column_id
JOIN sys.indexes ix ON ix.object_id = ixcolumns.object_id
                       AND ix.index_id = ixcolumns.index_id
WHERE     ix.is_unique = 1
GROUP BY  fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id ) fkindexes
JOIN ( SELECT   fk.parent_object_id,
    ixcolumns.index_id,
    COUNT(*) cindexestotal
FROM     ( SELECT DISTINCT
                parent_object_id
      FROM      sys.foreign_keys ) fk
    JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fk.parent_object_id
GROUP BY fk.parent_object_id,
    ixcolumns.index_id ) totalindexes ON totalindexes.parent_object_id = fkindexes.parent_object_id
                                         AND totalindexes.index_id = fkindexes.index_id

WHERE   cindexestotal - cindexes = 0 ) a ON a.parent_object_id = fk.parent_object_id
                        AND a.referenced_object_id = fk.referenced_object_id
JOIN sys.tables child ON fk.parent_object_id = child.object_id
JOIN sys.tables parent ON fk.referenced_object_id = parent.object_id
ORDER BY TablesWithRelations

Thursday, July 26, 2012

SQL Server : @@VERSION Showing Incorrect Service Pack Information


Recently, due to SQL Server Log message The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs. we have decided to resolve it by installing Service Pack 3 for SQL Server 2005. To confirm, that target server has version prior to Service Pack 3, I have used following simplest query.
SELECT @@VERSION
From query result it became sure that said server has Service Pack 2 installed and need to be upgraded to Service Pack3. 
On receiving confirmation mail from Systems department that said instance is upgraded, I went for same above query to verify and surprisingly, it was still showing the same result with Service Pack 2.
For further confirmation I have used a different query (SERVERPROPERTY('PRODUCTLEVEL') and found actual results (i.e. Service Pack 3). 

Basically, it was a simple misconception. Till date i thought @@version shows patch information of SQL Server, but actually it shows operating system patch.

Wednesday, July 25, 2012

SQL Server Log: I/O is frozen on database DatabaseName


One more daily base SQL Server log message reported from one of our production server was
I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
AND
I/O was resumed on database DATABASENAME. No user action is required.

First thing to note is that it’s just a message and not an error and no user action is required. On investigation for said production server, I found that our System Administrators has enabled SQL Server backup through VSS (Volume Shadow Copy Services) and this process actually freeze I/O temporarily to take shadow copy and release it back once process is complete. How it works read here.


------------------------------------------------------------------------------------
Read More about SQL Server Log Errors/Messages 



Tuesday, July 24, 2012

SQL Server has encountered 1 occurrence(s) of cachestore flush


SQL Server Log report from one of our production server was continuously showing following messages.
Log Date
Process Info
Process Text
2012-07-23T20:00:08.880
spid17s
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T20:00:08.880
spid17s
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T20:00:07.190
spid17s
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.640
spid16s
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.640
spid16s
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2012-07-23T06:00:04.580
spid16s
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

It happens when you configure user database with Auto Close option.
SQL Server, close a user database automatically, when last session is closed and reactivated when a login request is received.  We must keep this option OFF for a better performance. Why so read this.

Monday, July 23, 2012

SQL Server Scripts: Get All Nested Stored Procedures List (Procedures with dependent Procedures)


A stored procedure can be called from another stored procedure as nested stored procedure. Recently on production server, we were asked for all stored procedures in which other stored procedures are called as nested. Here is simple script.
SELECT  * FROM (SELECT  NAME AS ProcedureName, SUBSTRING(( SELECT  ', ' + OBJDEP.NAME
FROM    sysdepends
        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
WHERE obj.type = 'P'  
AND Objdep.type = 'P'
AND sysdepends.id = procs.object_id
ORDER BY OBJ.name

FOR
XML PATH('')
), 2, 8000) AS NestedProcedures
FROM sys.procedures  procs )InnerTab
WHERE NestedProcedures IS NOT NULL


Wednesday, July 18, 2012

SQL Server: Restrict Login from Valid Machine IPs Only (Using Logon Trigger)


Today, I have practically learned how to stop valid database users to login on SQL Server instance from invalid machines (IPs).
Process is very simple. Just create a Logon Trigger and check if login user is coming from valid IP or not. If not, then just kick him out.

Download Script
USE master
GO
-- Create table to hold valid IP values
CREATE TABLE ValidIPAddress (IP NVARCHAR(15)
CONSTRAINT PK_ValidAddress PRIMARY KEY)

-- Declare local machine as valid one
INSERT INTO ValidIPAddress
SELECT '<local machine>'
-- Create Logon Trigger to stop logins from invalid IPs
CREATE TRIGGER tr_LogOn_CheckIP ON ALL SERVER
    FOR LOGON
AS
    BEGIN
        DECLARE @IPAddress NVARCHAR(50) ;
        SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',
                                           'NVARCHAR(50)') ;
        IF NOT EXISTS ( SELECT  IP
                        FROM    master..ValidIPAddress
                        WHERE   IP = @IPAddress )
            BEGIN
            -- If login is not a valid one, then undo login process
                SELECT  @IPAddress
                ROLLBACK --Undo login process
            END

    END
Once trigger is created, you can find it under Server Objects -- > Triggers tab


From invalid IP, which you have not added in secure list will see following error on log-in attempt.

Tuesday, July 17, 2012

SQL Server Log: DeviceIoControl failed, error 21

This morning in log report of one of our production server I found an unusual  error.
DeviceIoControl failed, error 21
DeviceIOControl is a function which sends code to your disk drivers to perform read or writes functions and error 21 clearly indicates that device which we have specified to perform a job is not ready.
This happened because, on our production server, we had configured a backup job which takes daily full backup to a removable device and unfortunately it was removed by our systems department, which caused this error.



------------------------------------------------------------------------------------
Read More about SQL Server Log Errors/Messages 

Monday, July 16, 2012

SQL Server: Script to Generate HTML Report/mail for Databses Current Size, Growth Rate and Available Disk Space

Working with multiple databases on multiple instances is a tough job. You need to monitor all these instances for everything. Best way to keep eye on every instance activity is SQL Server Jobs.
How quickly databases on these instances are growing and does target instance has required space on hard drives ? These are basic questions which every DBA keeps in mind during instance monitoring.
Amna Asif has suggested a better script to create a proper report (to mail) for actual database space, required and currently available on hard drives.
 Script to send an alert through mail, with information that how many drive
 space is required from next databases growth on a specific instance and how many
 space is available.


 Script By: Amna Asif for ConnectSQL.blogspot.com
 */


 DECLARE @dbName varchar(200),
    @Qry Nvarchar(max)
 DECLARE @dbsize VARCHAR(50),
    @logsize VARCHAR(50),
    @reservedpages VARCHAR(50),
    @usedpages VARCHAR(50),
    @pages VARCHAR(50)


 SET @dbName = ''


---Get LOG File Spaces of All Databases--
 CREATE TABLE #LogSpaceStats
    (
      RowID INT IDENTITY
                PRIMARY KEY,
      dbName SYSNAME,
      Totallogspace DEC(20, 2),
      UsedLogSpace DEC(20, 2),
      Status CHAR(1)
    )
   
 INSERT #LogSpaceStats
        ( dbName, Totallogspace, UsedLogSpace, Status )
        EXEC ( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS'
            )
    
--Get Info of All Drives
 DECLARE @ServerDrives TABLE
    (
      RowID int IDENTITY
                PRIMARY KEY,
      Drive char,
      DriveSpace varchar(100),
      Required_Space varchar(100)
    )
 INSERT INTO @ServerDrives
        ( Drive, DriveSpace )
        EXEC master.sys.xp_fixeddrives
--Temporary Table to hold requried data
 CREATE TABLE #ServerFileStats
    (
      RowID INT IDENTITY
                PRIMARY KEY,
      dbName SYSNAME,
      Database_DSize varchar(100),
      Allocated_Space varchar(100),
      Unallocated_Space varchar(100),
      Unused varchar(100),
      Database_LSize varchar(100),
      UsedLogSpace DEC(20, 2),
      FreeLogSpace DEC(20, 2),
      FDataFileGrowth DEC(20, 2),
      FLogFileGrowth DEC(20, 2),
      DataFileDrive char,
      LogFileDrive char
    )
  
--Cursor Used to get each database size on given instance
 DECLARE cur_dbName CURSOR
    FOR SELECT  NAME
        FROM    SYS.DATABASES
        WHERE   state_desc = 'ONLINE'
                AND is_read_only = 0
 OPEN cur_dbName
 FETCH NEXT FROM cur_dbName into @dbName
 WHILE @@FETCH_Status = 0
    BEGIN
        SELECT  @Qry = ' SELECT @dbsizeOUT = sum(convert(bigint,
                              case when status & 64 = 0 then size
                              else 0 end))
                              ,@logsizeOUT = sum(convert(bigint,
                                    case when status & 64 <> 0 then size
                                    else 0 end)) 
                                       FROM [' + @dbName + '].dbo.sysfiles '
                             
        EXEC sp_executesql @Qry,
            N'@dbsizeOUT  nvarchar(50) OUTPUT,@logsizeOUT  nvarchar(50) OUTPUT',
            @dbsizeOUT = @dbsize OUTPUT, @logsizeOUT = @logsize OUTPUT ; 


        SELECT  @Qry = ' SELECT @reservedpagesOUT = sum(a.total_pages)
                                 ,@usedpagesOUT = sum(a.used_pages)
                      FROM [' + @dbName + '].sys.partitions p join [' + @dbName
                + '].sys.allocation_units a on p.partition_id = a.container_id 
                      LEFT JOIN [' + @dbName
                + '].sys.internal_tables it on p.object_id = it.object_id'


        EXEC sp_executesql @Qry,
     N'@reservedpagesOUT  nvarchar(50) OUTPUT,@usedpagesOUT nvarchar(50) OUTPUT',
            @reservedpagesOUT = @reservedpages OUTPUT,
            @usedpagesOUT = @usedpages OUTPUT ; 
       
        SELECT  @Qry = ' INSERT INTO #ServerFileStats                
                         SELECT DB_size.Database_Name
                         , DB_size.Database_DSize
                         , DB_size.Allocated_Space
                         , DB_size.Unallocated_Space
                         , DB_size.Unused
                         , DB_size.Database_LSize
             , (lss.TotalLogSpace*(lss.UsedLogSpace/100)) UsedLogSpace
             , (TotalLogSpace-(TotalLogSpace*(UsedLogSpace/100))) FreeLogSpace
             ,CASE mfD.is_percent_growth
              WHEN 0 THEN CONVERT(DEC(15,2),(mfD.growth* 8192 / 1048576))
              ELSE CONVERT(DEC(15,2),(CONVERT(DEC(15,2),REPLACE(DB_size.Database_DSize,'' MB'',''''))
                              *mfD.growth/100)) END  FDataFileGrowth
                          ,
                          CASE mfL.is_percent_growth WHEN 0 THEN CONVERT(DEC(15,2),(mfL.growth* 8192 / 1048576))
                          ELSE CONVERT(DEC(15,2),(CONVERT(DEC(15,2),REPLACE(DB_size.Database_DSize,'' MB'',''''))
                          *mfL.growth/100)) END  FLogFileGrowth
                         ,LEFT(mfD.physical_name,1) DataFileDrive
                         ,LEFT(mfL.physical_name,1) LogFileDrive
                         FROM
                         (
                          SELECT Database_Name = ''' + @dbName
                + '''
, Database_DSize = ltrim(str((convert (dec (15,2),'
       + @dbsize
       + '))* 8192 / 1048576,15,2) + '' MB'')
, ''Allocated_Space''=ltrim(str((CASE WHEN '
       + @dbsize + ' >= ' + @reservedpages
       + '
THEN convert (DEC (15,2),'
                + @reservedpages
                + ')* 8192 / 1048576
ELSE 0 end),15,2) + '' MB'') 
                                    , ''Unallocated_Space'' = ltrim(str((CASE WHEN '
               + @dbsize + ' >= ' + @reservedpages
                + '
THEN  (convert (DEC (15,2),'
                + @dbsize + ') - convert (DEC (15,2),' + @reservedpages
                + '))* 8192 / 1048576
ELSE 0 end),15,2) + '' MB'')
                                    , ''Unused'' =ltrim(str((CAST(('
                + @reservedpages + ' - ' + @usedpages
                + ')AS BIGINT) * 8192 / 1024.)/1024,15,2) + '' MB'') 
                , Database_LSize = ltrim(str((convert (dec (15,2),'
                + @logsize
                + '))* 8192 / 1048576,15,2) + '' MB'')
  )DB_size LEFT JOIN #LogSpaceStats AS lss on lss.dbName=DB_size.Database_Name
                          INNER JOIN ' + @dbName
                + '.sys.databases db ON DB.name=DB_size.Database_Name
                          INNER JOIN ' + @dbName
                + '.sys.master_files mfD on mfD.database_id=DB.database_id AND mfD.type_desc=''ROWS''
                          INNER JOIN ' + @dbName
                + '.sys.master_files mfL on mfL.database_id=DB.database_id AND mfL.type_desc=''LOG'''


        EXEC ( @Qry


            )
  FETCH NEXT FROM cur_dbName into @dbName
    END
 CLOSE cur_dbName
 DEALLOCATE cur_dbName


 UPDATE @ServerDrives
 SET    Required_Space = SumDriveS.sumofdrivespcae
 FROM   ( SELECT    SUM(CONVERT(DEC(20, 2), sumofdrivespcae)) sumofdrivespcae,
                    DRIVE AS DRIVE
          FROM      ( SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FDataFileGrowth, ' MB', '')))
                                                sumofdrivespcae,
                                fss.DataFileDrive AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUP BY  fss.DataFileDrive
                      UNION
                      SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FLogFileGrowth, ' MB', '')))
                                                sumofdrivespcae,
                                fss.LogFileDrive AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUP BY  fss.LogFileDrive ) SumDrive
          GROUP BY  SumDrive.DRIVE ) SumDriveS
        LEFT OUTER JOIN @ServerDrives sd on SumDriveS.Drive = sd.Drive


------------------------------------------------------------------------------
-----------------------------------------Report Mailing-----------------------
DECLARE @Loop int
 DECLARE @Subject varchar(100)
 DECLARE @strMsg varchar(4000)


 SELECT @Subject = 'SQL Monitor Alert: ' + @@SERVERNAME + '        '
        + Convert(varchar, GETDATE())
  Declare @Body varchar(max),
    @TableHead varchar(1000),
    @TableTail varchar(1000),
    @TableHead2 varchar(1000),
    @Body2 varchar(3000)
 Set NoCount On ;
-- Create HTML mail body
 Set @TableTail = '</table></body></html>' ;
  Set @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black 1px;padding-left:3px;padding-right:3px;padding-top:2px;padding-bottom:2px;font-size:10pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Database Name</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>File Group</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>DF Total Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>DF Allocated Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>DF Unallocated Space</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>DF Unused</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>LF Total Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>LF Used Space</b></td>'
    + '<td align=center bgcolor=#5F9EA0><b>LF Unused Space</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>DF FileGrowth</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>LF FileGrowth</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>DF Drive</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b> LF Drive </b></td></tr>' ;


  Select @Body = ( SELECT    td = CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY dbName ))
                            + CHAR(10),
                            td = ISNULL(dbName, 'Unknown') + CHAR(10),
                            td = ISNULL('Data/LOG', 'Unknown') + CHAR(10),
                            td = ISNULL(Database_DSize, '0.00') + CHAR(10),
                            td = ISNULL(Allocated_Space, '0.00') + CHAR(10),
                            td = ISNULL(Unallocated_Space, '0.00') + CHAR(10),
                            td = ISNULL(Unused, '0.00') + CHAR(10), '',
                            td = ISNULL(Database_LSize, '0.00') + CHAR(10),
                            td = ISNULL(convert(varchar, UsedLogSpace), '0.00')
                            + ' MB' + CHAR(10),
                            td = ISNULL(convert(varchar, FreeLogSpace), '0.00')
                            + ' MB' + CHAR(10),
                            td = ISNULL(convert(varchar, FDataFileGrowth),
                                        '0.00') + ' MB' + CHAR(10), '',
                            td = ISNULL(convert(varchar, FLogFileGrowth),
                                        '0.00') + ' MB' + CHAR(10), '',
                            td = ISNULL(DataFileDrive, '0') + CHAR(10), '',
                            td = ISNULL(LogFileDrive, '0') + CHAR(10), ''
                  FROM      #ServerFileStats
                  ORDER BY  dbName
        FOR       XML RAW('tr'),
                      ELEMENTS )


-- Replace the entity codes and row numbers
 Set @Body = Replace(@Body, '_x0020_', space(1))
 Set @Body = Replace(@Body, '_x003D_', '=')
 Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
 Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')


 DECLARE @flag BIT
 SELECT @flag = 1
 FROM   @ServerDrives
 WHERE  convert(dec(15, 2), DriveSpace) < convert(dec(15, 2), Required_Space)
        * 2
 SET @flag = ISNULL(@flag, 0)


 SET @TableHead2 = '<html><head>' + '<style>'
    + 'td {border: solid black 1px;padding-left:1px;padding-right:1px;padding-top:1px;padding-bottom:1px;font-size:8pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Drive</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Drive Space</b></td> '


 IF ( @flag = 0 )
    set @TableHead2 = @TableHead2
     + '<td align=center bgcolor=#E6E6FA><b>Required Drive Space</b></td></tr>' ;
 ELSE
    set @TableHead2 = @TableHead2
     + '<td align=center bgcolor=#FF7F50><b>Required Drive Space</b></td></tr>' ;
         
 Select @Body2 = ( SELECT   td = ROW_NUMBER() OVER ( ORDER BY Drive ),
                            td = ISNULL(Drive, 'Unknown') + char(10),
                            td = ISNULL(DriveSpace + ' MB', 0) + char(10),
                            td = ISNULL(Required_Space + ' MB', 0)
                   FROM     @ServerDrives sd
        For        XML RAW('tr'),
                       Elements )


 Select @Body = @TableHead2 + @Body2 + @TableTail + '<br/><br/><br/><br/>'
        + @TableHead + @Body + @TableTail
-- Send mail
 EXEC msdb.dbo.sp_send_dbmail
      @recipients = 'abc@xyz.com',
    @subject = @Subject,
    @profile_name = 'MyMailProfileName',
    @body = @Body,
    @body_format = 'HTML' ;


 --Drop Temporary Tables When Not Required
 DROP TABLE #ServerFileStats
 DROP TABLE #LogSpaceStats

DF = Data Files
LF = LogFiles