Cleaning up of msdb database

Cleaning up msdb database is one of the neglected yet most important task of a sql administrator. We have some SSIS packages, full back up once a day and transactional backup in every 15 minutes. We use databaseMail extensively therefore cleaning up msdb database is a good practice.

The msdb database was 241 MB and after cleaning up ( running the following scripts) I am able to bring it down to 153 MB. I am keeping a log of the database growth in a table in every 4 hours. I do not think that the size of the msdb has to be this big. So I am leaving it the way it is and then looking at table where the file growth is captured  befor I make the decision if I need to rsize the db to a smaller size.

The scripts that I ran

--removing anything before a month

DECLARE
SET @date = DATEADD(day, -30, CURRENT_TIMESTAMP)
 --delete backup history

--delete old mail items
--especially, if you are sending attachements
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date
--delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date
--delete the SQL Server agent job history log
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date
EXEC msdb.dbo.sp_delete_backuphistory @date @date datetime

script: List the permissions of a user

I had a  task where I had to list all the permission of a specific user on the database . The following script came to the rescue


SELECT dppriper USER_NAME(dppriper.grantee_principal_id) AS [UserName], .type_desc AS principal_type_desc, .class_desc, OBJECT_NAME (dppriper.major_id) AS object_name, .permission_name, .state_desc AS permission_state_desc
FROM sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE
--dppriper.permission_name = 'EXECUTE'
USER_NAME(dppriper.grantee_principal_id) = 'myuser'

 --**replace the myuser with the user we are looking for
--**If we want to find only the stored procedures that the user has perission on comment out the  clause in the above query.

The size of the message body in dbmail

SQL 2008 can take more than 8000 characters in the meassage body of dbmail . While declaring the message body variable just set the size of the varchar to max.
For .e.g.

declare @msg_body varchar(max),           
 @v_subject


This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)

Check the backup process

Script to see how the backup is progressing.

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins , (total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,* FROM
sys.dm_exec_requests WHERE session_id = (the spid for running the back up - you can find by running sp_who2 active)

MSDTC error while using Transactionscope

In one of my project while writing a client application in VB.NET I had to make a call to two different databases on different servers and in case of error encountered both the transaction had to roll back. I used transactionscope to achieve this. I could use this since the database server is SQL 2008. Transactionscope ( a very cool thing) can be used on in SQL 2005 or higher. However I was running into the following error whenever while trying to open connection2 ( second conection ).

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.



Here is a snippet of my code (in VB.NET)


While debugging through the code, it thre error as soon as it was trying to open the oConn2 ( server2)
I checked in the database server if DTC was enabled. It was. What I had missed was the MSDTC had to be configured in the client server as well. Once the MSDTC was configured on the client server Viola it worked like a charm.



Imports System.Transactions  'you will have to add reference to System.Transactions first

Using scope As New TransactionScope()
Using oconn1 As New SqlConnection(strconn1)


    ocmd1 = New SqlClient.SqlCommand()
    ProgressBar1.PerformStep()
    ocmd1.Connection = oconn1
    ocmd1.CommandText = "PROC_SKIPTRACE_RESULTS_INSERT"
    ocmd1.CommandType = CommandType.StoredProcedure
    ocmd1.CommandTimeout = 600
    Dim paramPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
    paramPTNO.Direction = ParameterDirection.Input
    ocmd1.Parameters.Add(paramPTNO)
   Try
   If oconn1.State = ConnectionState.Closed Then


       oconn1.Open()
   End If
   returnValue = ocmd1.ExecuteNonQuery()
  Catch ex As Exception
    MsgBox(ex.Message.ToString())
  Finally
    oconn1.Close()
  End Try


Using oConn2 As New SqlConnection(strconn2)
 ocmd2 = New SqlClient.SqlCommand()
 ocmd2.Connection = oConn2
 ocmd2.CommandText = "PROC_SKIPTRACE_PATIENT_TABLE_SUSPENSE_UPDATE"
 ocmd2.CommandType = CommandType.StoredProcedure
 ocmd2.CommandTimeout = 600
 Dim paramintPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
 paramintPTNO.Direction = ParameterDirection.Input
 ocmd2.Parameters.Add(paramintPTNO)


Try
 If oConn2.State = ConnectionState.Closed Then
 oConn2.Open()
 End If
 returnValue = ocmd2.ExecuteNonQuery()
Catch ex As Exception
 MsgBox(ex.Message.ToString())
Finally
 oConn2.Close()
End Try


End Using 'oConn2
End Using 'oConn1
scope.Complete()
End Using ' transactionScope

Dropping multiple tables

/*The table names are logged in the table SQLAdmin_Monitor_tbl_size_info
Using cursor to loop thorough the table and dropping them.*/



DECLARE @TableName VARCHAR(100)
DECLARE @dropCommand VARCHAR(255)

DECLARE
FOR

OPEN tableCursor
FETCH next FROM tableCursor INTO @TableName

WHILE (@@Fetch_Status >= 0)
 BEGIN
  SET @dropCommand = N'DROP TABLE ' +@TableName
  EXECUTE(@dropCommand)
  FETCH next FROM tableCursor INTO @TableName
End
CLOSE  tableCursor
DEALLOCATE  tableCursor

Update stats

I had a project where I had to find when the stats were updated last and then had to update the stats of the table which was used heavily. I found the sript on the following blog

http://troubleshootingsql.com/2010/01/22/how-to-check-database-statistics-last-updated-date-time-in-sql-server/


SELECT schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
( select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2 ) as rowcnt,
 convert(DECIMAL(18,8),
convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
FROM sysindexes i
INNER JOIN sysobjects tbls
ON i.id = tbls.id
INNER JOIN sysusers schemas
ON tbls.uid = schemas.uid
INNER JOIN information_schema.tables tl
ON tbls.name = tl.table_name
AND schemas.name = tl.table_schema
AND tl.table_type='BASE TABLE'
WHERE 0 < i.indid
AND  i.indid < 255 and table_schema <> 'sys'
 AND  i.rowmodctr <> 0 and i.status not in (8388704,8388672)
AND  (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0
ORDER BY ModifiedPercent
DESC