Adding / Dropping Users and Roles to multiple dbs

-- adding users and roles to multiple databases
exec sp_msforeachdb @command1 ='use ? CREATE USER [domain\userorgroup] FOR LOGIN [domain\userorgroup] exec sp_addrolemember N''db_datareader'', N''domain\userorgroup'''

-- dropping users and roles to multiple databases
exec sp_msforeachdb @command1 ='use ? exec sp_droprolemember N''db_datareader'', N''domain\userorgroup'' DROP USER [domain\userorgroup]'

USP_DropXMLIndexes

Removes all XML Indexes from a table, takes schema name and table name as parameters.
e.g. EXEC USP_DropXMLIndexes 'Person', 'Contact'

CREATE PROCEDURE USP_DropXMLIndexes
              @schemaname VARCHAR(128),
              @tablename  VARCHAR(128)
             
AS
-- USP_DropXMLIndexes by sql solace
 DECLARE  @sqlstring NVARCHAR(500)
                    
 WHILE EXISTS (SELECT *
               FROM   sys.internal_tables AS IT
                      JOIN sys.tables AS T
                        ON IT.PARENT_ID = T.OBJECT_ID
                      JOIN sys.schemas AS S
                        ON T.SCHEMA_ID = S.SCHEMA_ID
                      JOIN sys.xml_indexes AS XI
                        ON IT.PARENT_ID = XI.OBJECT_ID
                       AND IT.PARENT_MINOR_ID = XI.INDEX_ID
               WHERE  S.NAME = @schemaname
                      AND T.NAME = @tablename)
   BEGIN
     SELECT @sqlstring = 'DROP INDEX [' + XI.NAME + '] ON [' + S.NAME + '].[' + T.NAME + '] '
     FROM   sys.internal_tables AS IT
            JOIN sys.tables AS T
              ON IT.PARENT_ID = T.OBJECT_ID
            JOIN sys.schemas AS S
              ON T.SCHEMA_ID = S.SCHEMA_ID
          JOIN sys.xml_indexes AS XI
              ON IT.PARENT_ID = XI.OBJECT_ID
                 AND IT.PARENT_MINOR_ID = XI.INDEX_ID
     WHERE  S.NAME = @schemaname
            AND T.NAME = @tablename
                        
     PRINT @sqlstring
    
     EXECUTE sp_executesql @sqlstring
   END

GO

SQL Server 2005 Performance Dashboard Reports

SQL Server 2005 Performance Dashboard Reports

A reports add-in for DBAs...

http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

2005 : XML Indexes

Find tables with XML Indexes

SELECT  s.name as SchemaName,  t.name as TableName, xi.name as XMLIndexName
FROM sys.internal_tables AS it
JOIN sys.tables AS t
  ON it.parent_id = t.object_id
JOIN sys.schemas as s
on t.schema_id = s.schema_id
JOIN sys.xml_indexes AS xi
  ON it.parent_id = xi.object_id
  AND it.parent_minor_id  = xi.index_id

Finding disabled XML Indexes

SELECT  *
FROM    sys.xml_indexes
WHERE   is_disabled != 0;

Disabling an XML Index

ALTER INDEX indexname ON tablename DISABLE

Disk Space Monitor - Further Scripts

Further sql to support the drive space monitor.

Daily diskspace view -

create view ViewDailyDiskFree
as
select 
 computer, 
 drive, 
 min(percentage) as percentage,
 CAST(FLOOR(CAST(Date AS float)) AS datetime) as dateoccurred,
 datediff(dd,date,getdate()) as daysago
from 
 tbldiskspace
group by 
 computer, drive, CAST(FLOOR(CAST(Date AS float)) AS datetime), datediff(dd,date,getdate())

SQL 7/2000 Cross-tab.
Uses 'case' statement and 'group by' to acheive cross-tab report >

select  computer, 
  drive,
  sum(case daysago when 6 then percentage else 0 end) as percentage_day_6,
  sum(case daysago when 5 then percentage else 0 end) as percentage_day_5,
  sum(case daysago when 4 then percentage else 0 end) as percentage_day_4,
  sum(case daysago when 3 then percentage else 0 end) as percentage_day_3,
  sum(case daysago when 2 then percentage else 0 end) as percentage_day_2,
  sum(case daysago when 1 then percentage else 0 end) as percentage_day_1,
  sum(case daysago when 0 then percentage else 0 end) as percentage_today
from
 ViewDailyDiskFree
group by
 computer, drive
order by
 computer, drive

SQL 2005+ version
Uses PIVOT operator to achieve cross-tab report.

select  computer,
  drive,
  isnull([6],0) as percentage_day_6,
  isnull([5],0) as percentage_day_5,
  isnull([4],0) as percentage_day_4,
  isnull([3],0) as percentage_day_3,
  isnull([2],0) as percentage_day_2,
  isnull([1],0) as percentage_day_1,
  isnull([0],0) as percentage_today
from 
 (select computer, drive, percentage , daysago from ViewDailyDiskFree) p
pivot
 (
 sum(percentage)
 for daysago in ([6],[5],[4],[3],[2],[1],[0])
 ) as pivottable

Disk Space Monitor

Monitor drive capacities in a sql environment -

How to implement ...

1. create a database for the project
2. run the sql script to create the 3 tables in the new database
3. populate tblSQLPhysicalServers manually with servernames to monitor
4. edit .vbs script file to change the database connection parameters
5. schedule the .vbs file to run using scheduled tasks in control panel (frequency as desired)

Table Schema Script -

-- table creation script

CREATE TABLE [dbo].[tblSQLPhysicalServers] (
 [servername] [nvarchar](50)
)
GO

CREATE TABLE [dbo].[tblDiskSpaceLog](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [notes] [varchar](1000) NULL,
 [date] [datetime] NULL DEFAULT (getdate())
) 
GO

CREATE TABLE [dbo].[tblDiskSpace] (
 [Computer] [varchar](128),
 [Drive] [varchar](2),
 [DiskSize] [decimal](28, 5) NULL,
 [FreeSpace] [decimal](28, 5) NULL,
 [Percentage] [decimal](10, 5) NULL,
 [Date] [datetime] NULL
)
GO

VBS Script -

'*********************************************** 
'*                                             * 
'*  Drive Monitor Script - save as a .vbs file * 
'*                                             * 
'*********************************************** 

On Error Resume Next 

Const intBytesPerMegabyte = 1048576 
   
Dim AdCn 
Dim AdRec 
Dim strMonitorSQL, strSQL, strSQL2, strdate 
Dim strServerName, strDataBase , strUsername, strPassword, strConnection 

strServerName = "RIO\SQL2005" 
strDataBase = "Monitor"  
strUsername = "rd"  
strPassword = "pass" 
strConnection = "Provider=SQLOLEDB.1;Data Source=" + strServerName + ";Initial Catalog=" + strDataBase + ";user id = '" + strUsername + "';password='" + strPassword + "' " 

Function fnPadLeadingZero(intInputNumber, intTotalDigits)  
    If intTotalDigits > Len(intInputNumber) Then  
        fnPadLeadingZero = String(intTotalDigits -Len(intInputNumber),"0") & intInputNumber  
    Else  
        fnPadLeadingZero = intInputNumber  
    End If  
End Function  

Set AdCn = CreateObject("ADODB.Connection") 
Set AdRec1 = CreateObject("ADODB.Recordset") 
Set AdRec2 = CreateObject("ADODB.Recordset") 
AdCn.Open = strConnection 
strSQL = "Select ServerName from tblSQLPhysicalServers" 
AdRec1.Open strSQL, AdCn,1,1 
  
strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('Disk Monitoring - Started')" 
AdRec2.Open strMonitorSQL, AdCn,1,1 

'loop servers  
While Not Adrec1.EOF  
Computer = Adrec1("ServerName") 
  
'connect to wmi for server 
Set objWMIService = GetObject("winmgmts://" & Computer) 
'wscript.echo err.number 

'check for wmi connection error 
If err.number <> 0 Then 

  'wmi connection error 
  AdRec2.Open "insert into tblDiskSpaceLog(Notes) values ('Disk Monitoring - wmi error')", AdCn,1,1 
  strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('" + Computer + ":  Error-- " + Err.description + "')" 
  AdRec2.Open strMonitorSQL, AdCn,1,1 

Else 

  'wmi connected ok 

  Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")   
  If err.number <> 0 Then 
    'cant connect to w32_logicaldisk 
    strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('" + Computer + ":   Error-- " + Err.description+ "')" 
    AdRec2.Open strMonitorSQL, AdCn,1,1 
  Else 
    'w32_logicaldisk connected... 
     
   For Each objLogicalDisk In colLogicalDisk   
   If objLogicalDisk.drivetype=3 Then 

   strdate = YEAR(Date()) & "-" & fnPadLeadingZero(Month(Date()),2) & "-" & fnPadLeadingZero(DAY(Date()),2) & " " & time() 


   If objLogicalDisk.FreeSpace = "" Then 
    
        'freespace property empty 

   strSQL2 = "Insert into tblDiskSpace (Computer,Drive,DiskSize,date) values('"+Computer+"','" + objLogicalDisk.DeviceID + "'," + CInt(objLogicalDisk.size/intBytesPerMegabyte) +  + ",'"  + strdate + "')" 
        AdRec2.Open strSQL2, AdCn,1,1 

   Else 

   'freespace property given. 

   strSQL2 = "Insert into tblDiskSpace (Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"_ 
   &Computer&"','" & objLogicalDisk.DeviceID &"',"& objLogicalDisk.size/intBytesPerMegabyte &_ 
    "," & objLogicalDisk.freespace/intBytesPerMegabyte &_ 
    "," &((objLogicalDisk.freespace/intBytesPerMegabyte)/(objLogicalDisk.size/intBytesPerMegabyte))*100_ 
   &",'" & strdate &"')" 

         AdRec2.Open strSQL2, AdCn,1,1 
    
   If err.number <> 0 Then 
   wscript.echo err.description 
   End If 

   End If 

    'debug to see sql used for insert 
    'strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('" + replace(sql,"'","_")+ "')" 
    'AdRec2.Open strMonitorSQL, AdCn,1,1 

   End If 

   Next   
  End If 

End If 
  
err.Clear 
Adrec1.movenext 
  
Wend 
  
AdRec2.Open "insert into tblDiskSpaceLog(Notes) values ('Disk Monitoring - Completed')", AdCn,1,1 

Periodically review the recorded data e.g. within daily checks.
Use sql to query the tblDiskSpace table, like this -

SELECT     Computer, Drive, DiskSize, FreeSpace, Percentage, Date
FROM         tblDiskSpace
WHERE Dateadd(dd, -1,getdate()) < date
AND Percentage < 25
ORDER BY Date DESC

(this query become a scheduled job itself)

SSIS Configuration File

The SSIS Config file is called MsDtsSrvr.ini.xml. It is located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn on my install.

I had to hunt it down as after installing Integration Services the service would not start.
It turned out that it didnt like the fact that I have named instances of SQL 2005 (no default instance) and SSIS doesnt detect named instances by default.

Adding the instance name inside the servername tags sorted my problem.

<dtsserviceconfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><br /> <stopexecutingpackagesonshutdown>true</StopExecutingPackagesOnShutdown><br /> <toplevelfolders><br /> <folder xsi:type="SqlServerFolder"><br /> <name>MSDB</Name><br /> <servername>RIO\ALPHA</ServerName><br /> </Folder><br /> <folder xsi:type="FileSystemFolder"><br /> <name>File System</Name><br /> <storepath>..\Packages</StorePath><br /> </Folder><br /> </TopLevelFolders> <br /></DtsServiceConfiguration> </servername>