Search for text in any database objects

If we ever get in a situation where we need to find a particular string in a stored procedure or trigger or any other database object, use the below script:

-- SQL 2000
select * from ( SELECT o.name, d.colid, o.type, convert(varchar(4000), c.text)
+ CASE d.colid WHEN 1 THEN '' ELSE convert(varchar(4000), d.text) END as LineText
FROM syscomments c, syscomments d, sysobjects o
WHERE c.id = d.id and c.id = o.id
and ( (c.colid = d.colid - 1) or (c.colid = d.colid and d.colid=1) ) ) a
where LineText like '%string%'
order by name, colid


-- SQL 2005
select * from sys.sql_modules
where definition like '%string%'

Is your msdb database growing huge? Do you need to clean up a lot of job history as well as database mail log?

MSDB database has a log of all the job history and also the database mail that can keep on growing and this is one thing a DBA team should be avoiding.

Below is a quick script that erases everything older than 90 days and I schedule it to run every week. This deletes all the logs older than 90 days, which is more than enough of log information unless one has a specific reason to have more information logged all the time.

 DECLARE @deletebeforedate datetime
SET @deletebeforedate = DATEADD(dd, -90, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @deletebeforedate
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date = @deletebeforedate
EXEC msdb..sp_maintplan_delete_log null,null,@deletebeforedate
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @deletebeforedate
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @deletebeforedate

  

Delete files older than certain number of days using a windows batch file

Same old problem keeps repeating at every client place! The customized backup jobs sometimes  fail to clean up the files older than 2 days (or whatever the threshold is) and the backup jobs fail eventually when the drive gets full, One smart and quick solution for this is to use the 'forfiles' utility and schedule it as a task in windows or use xp_cmdshell to schedule the same as a SQL server agent job. The command looks something like the below:

forfiles /p D:\backup\ /s /m *.trn /d -2 /c "cmd /c del @file"

Change the extension from .trn to .bak if you  are trying to cleanup full backup files instead of transaction log files.

Windows batch file that accepts user input to execute set of sql files

Last week, I was trying to get Performance Dashboard deployed across some 80+ sql instances which requires running a couple of scripts against each of those sql instances. Also there are separate set of scripts for instances running on 2005 and 2008. So I came up with this small windows batch file that which takes user input prompting the user to input the server name and then the version. Based on the server name it uses sqlcmd utility to connect to the instance and run the script from either folder called '2005' or '2008' which is again based on the version number input by the user.

@echo off
set /p servername= Please enter the servername:
set /p version= Please enter the version number 2000 or 2005 or 2008:
if %version%==2000 (@echo cannot run performance dashboard against 2000 instance)
if %version%==2005 (sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2005\setup.sql" -b
sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2005\fix.sql" -b)
if %version%==2008 (sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2008\setup.sql" -b
sqlcmd -E -S %servername% -i "C:\batch files\perf_dashboard\2008\fix.sql" -b)

Script to identify unused indexes

While indexes can increase the read performance, they can decrease the write performance during any updates. Any time a query modifies the data in a table the indexes on the data also must change. Any database that has a large number of transactions modifying the data will perform good with fewer indexes. But the key is striking a balance between fewer indexes that can improve the write performance and more indexes to improve the read performance.

Below is a query that can give useful statistics of how the indexes are being used. If the number of seeks and number of scans is 0 with the number of updates significantly large, then there is no point in having the index. The below is a good starting point for assessing and distinguishing between useful and unused indexes.



USE DBNAME
GO
SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
 , OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
 , I.NAME AS [INDEX NAME]
-- , I.INDEX_ID AS [INDEX ID]
 , USER_SEEKS AS [NUMBER OF SEEKS]
 , USER_SCANS AS [NUMBER OF SCANS]
 , USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
 , USER_UPDATES AS [NUMBER OF UPDATES]
FROM   
   SYS.DM_DB_INDEX_USAGE_STATS SS
   INNER JOIN SYS.INDEXES I
       ON I.OBJECT_ID = SS.OBJECT_ID
            AND I.INDEX_ID = SS.INDEX_ID
    INNER JOIN    SYS.OBJECTS O on O.OBJECT_ID=I.OBJECT_ID
WHERE DATABASE_ID = DB_ID()
  AND OBJECTPROPERTY (SS.OBJECT_ID,'IsUserTable') = 1
 AND  SS.OBJECT_ID = o.object_id--OBJECT_ID(@TABLENAME)
ORDER BY
       OBJECT_NAME(SS.OBJECT_ID),USER_SEEKS
    , USER_SCANS
    , USER_LOOKUPS
      , USER_UPDATES ASC

The same result can be obtained for a specific table using the below query. Replace DBNAME & TABLENAME in the code below:

USE DBNAME

GO

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]

     , OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]

     , I.NAME AS [INDEX NAME]

     , USER_SEEKS AS [NUMBER OF SEEKS]

     , USER_SCANS AS [NUMBER OF SCANS]

     , USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]

     , USER_UPDATES AS [NUMBER OF UPDATES]

FROM SYS.DM_DB_INDEX_USAGE_STATS SS

     INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = SS.OBJECT_ID AND I.INDEX_ID = SS.INDEX_ID

     INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = I.OBJECT_ID

WHERE DATABASE_ID = DB_ID()

  AND OBJECTPROPERTY(SS.OBJECT_ID, 'IsUserTable') = 1

  AND O.name = 'TABLENAME'  -- Add this condition to filter for the specific table

ORDER BY OBJECT_NAME(SS.OBJECT_ID), USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES ASC;

Connect to SSIS remotely and edit default location where packages are stored

There may be situations when some users have to connect to SSIS remotely form the workstations because users cannot be logging into the actual servers. There are some steps that need to be done for having users to successfully connect to SSIS. Else they may get an error message

Perform the below steps to change the configuration of how SSIS accepts remote connections in a cluster

Note that the first step is only required for those instances in a cluster and it did not require me to make these changes to connect to a server not in cluster.

1. Search for the MsDTSsrvr.ini file on the server and open it with notepad:

The content of the .ini file:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>servername\instancename</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>M:\MSSQL$SQL3\SSISPackages</StorePath>

    </Folder>

  </TopLevelFolders> 

</DtsServiceConfiguration>

Edit the highlighted ones in red as required.

The store path is by default D:\AppWin32\MSSQL\90\DTS\Packages or C:\Program Files\Microsoft SQL Server\90\DTS\Packages unless you wish to modify like in the above case. That location is where the packages get stored when you import into a file system and not msdb.

When we import any package into the msdb d/b there is no physical .dtsx file stored on the server.

1. Add the user/group to Distributed Com group on the server

2. Go to Admin tools -- Component services -- Computers -- My computer -- DComConfig -- MsDtsSrvr, right click on it and hit properties

3. Go to ‘Security’ tab and edit the first 2 sections: ‘Launch and Activation Permissions’ and ‘Access Permissions’ and hit edit to add/remove users as required.

Execute an SSIS package under a proxy account using SQL Agent job

Database administrators might sometimes come across the situations where the users need access to run SSIS packages (in environments where the DBA team does not generally own the responsibility of ancillary services). We can’t let the users have admin privileges to be able to run the packages as jobs. In such situations proxy account comes into play. A proxy account can be created and the job can be configured to be run under that proxy account. Follow the below steps to accomplish that

1. A new NT account needs to be created for the purpose of having a SQL job (SSIS package) executed under a proxy account. Most important the NT account has to be created with ‘No Password Expiry’ option.

2. Provide the SQL DBA team a list of users that need to have access and execute the jobs on SQL Server.

3. Add the userid (that needs to execute the jobs) provided by the Business Unit to SQLAgentUserRole in the msdb database. Also make that userid owner for the job that needs to be executed.

Creating a new proxy by linking it to an already existing credential:

4. Grant access to the above created NT account in this case on SQL Server with sysadmin permissions. (creating a new login).

5. Create a new credential:

Expand security --> go to credentials --> right-click and hit new credential.

Put in the password for the already existing NT account which has sysadm permissions on the SQL server

6. Expand SQL Server Agent --> right click on Proxies and hit new proxy

On ‘General’ tab enter the proxy name and enter the credential name created in the first step select the subsystems as required

7. On the ‘Principal’ hit on the button ‘Add’ and add the required logins that are required to make use of the proxy account.

8. Edit the job properties and hit ‘Edit’.

In the drop down list of ‘Run as’ select the newly created proxy account and hit ok.

The job should run successfully now.