Shrink the transaction log file

The steps needed to shrink the transaction log file
Scenario: The database is in FULL RECOVERY mode and the transaction log file was growing to more than 2GB. We were taking the transaction logbackups in every 1 hour inteval. However the transaction log file still grew because at one point someone just set the recovery mode to FULL and there was no transaction log backups set up. The following steps were taken to reduce the siz of the log file.

--check the log file name
select * from sys.database_files

--Truncate the log by changing the database recovery model to SIMPLE. ( checkpoint occurs-frees up VLFs)  

ALTER DATABASE ARCHIVEhcfsdatabase
 SET RECOVERY SIMPLE;
 GO

-- Shrink the truncated log file the smalles size possible
DBCC SHRINKFILE (ARCHIVEhcfsdatabase_Log,0, TRUNCATEONLY);
GO

--reset the log file size to 64MB

ALTER DATABASE archivehcfsdatabase
Modify FIle(Name = 'ARCHIVEhcfsdatabase_Log',
,Size = 1024
)
 -- Reset the database recovery model.
ALTER  DATABASE ARCHIVEhcfsdatabase
SET RECOVERY FULL;
GO

--Do a full Back up - Until a full back up is done the database is not set to full recovery mode--

Drop a database

1. You have to be in the master database.
2. Take the database to the single user mode.
3. Drop the database.

use master;GO
alter database [databaseName]
set single_user with rollback immediate;
drop database [databaseName];

Creating a XML file from SQL data using VB.net

The project involved creating a xml file in the following format with the data from the SQLserver 2008 tables.

File Sample
<claimTrackingRequest>
<Target>
<identifier>1015100236</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/29 - comment test1 </Text>
</Comment>
</Target>
<Target>
<identifier>1015700095</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/25 - comment test1</Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
</Target>
</claimTrackingRequest>

The above result was returned by the stored proc PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat
( The stored procedure details is listed at the end of the post)
I am using VB.NET to write the result returned by the stored proc to a file with extension XML
I am using a DATASET to pull the result and then using the STREAMWRITER to writer the dataset result to the file.

Sub Main()　
Dim sqlConn As New SqlConnection
sqlConn.ConnectionString = "Data Source=sqlsvr1;Initial Catalog=db01;Integrated Security=SSPI;"Dim sqlCmd As New SqlCommandsqlCmd.CommandText = "PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat"sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlCmd.CommandTimeout = 120
sqlCmd.Parameters.Add("@DTBEGIN", SqlDbType.Date).Value = "07/26/2011"sqlCmd.Parameters.Add("@DTEND", SqlDbType.Date).Value = "07/29/2011"Dim ds As New DataSetTrysqlConn.Open()
Dim a As System.Data.SqlClient.SqlDataAdaptera = New SqlDataAdapter(sqlCmd)
a.Fill(ds)
sqlConn.Close()

Using writer As StreamWriter = New StreamWriter("c:\test\notes.xml", True)
writer.Flush()
For Each _row As DataRow In ds.Tables(0).Rows
writer.Write(_row.Item(0).ToString())
Nextwriter.Close()
End Using
Catch ex As ExceptionEnd Try　
End Sub

 /*The stored proc returning the XML result is as follows */

--The proc is using XML Query

USE [db01]GO/****** Object: StoredProcedure [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] Script Date: 08/03/2011 12:48:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] (@DTBEGIN SMALLDATETIME, @DTEND SMALLDATETIME)AS/*turn these off so that it wont error if it trys to divide by Zero when doing calcs*/SET
SETARITHABORT OFF ANSI_WARNINGS OFF/*Create a temp table*/CREATE TABLE #TEMPTBLCNA (
RECORD_NUM
C1 INT NULL, VARCHAR(1) NULL,COMMENT_CODE VARCHAR(100) NULL,STATUS_CODE INT NULL,STATUS_DESC VARCHAR(200) NULL,COMMENT VARCHAR(1500) NULL,ACCOUNT VARCHAR(12) NULL,HOSPITAL INT NULL,DATEENTERED DATETIME NULL,PTNO INT NULL,NOTEID INT IDENTITY(1,1) )/*GET 100's*/INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)SELECTMIN(C.RECORD_NUM), LEFT(COMMENT_CODE,1),C.COMMENT_CODE,LEFT(C.COMMENT_CODE, 3),m.[STATUS],c.COMMENT,RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),p.HOSPITAL, CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),P.PTNOFROMCOMMENTS C INNER JOIN PATIENT_TABLE P ON C.PTNO = P.PTNOINNER JOIN MasterCodeList mON m.NewCode = LEFT(C.COMMENT_CODE, 3)
WHERE CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'
GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]ORDERBY P.PTNO--select * from #TEMPTBLCNASELECT
( c.ACCOUNT as 'identifier', 'RCV' as 'Type', SELECT '' AS 'Code',a.comment AS 'Text'
FROM #TEMPTBLCNA aWHERE a.ptno = c.ptnoFOR XML PATH(''), TYPE) AS 'Comment'
FROM #TEMPTBLCNA cgroup by c.ptno,c.ACCOUNTFOR XML PATH('Target'), ROOT('claimTrackingRequest')
DROP TABLE #TEMPTBLCNASET
SET ARITHABORT ON ANSI_WARNINGS ON

Searching for a string in the filename and moving, renaming it using SSIS

I had to create a project ot navigate through a folder and look for a file that has  EXE string in the file name ( not as the extension) of the file. e.g. NormanEXE1234.txt. Then once the file was found it had to be copied to the archive folder and the origianl file ahd to be reamed as hcfsnorman.txt.

After almost two days of trying to figure out how to do it, I have completed the task successfully. There were not much write up on this on the internet to my suprise. I could find tutorials only on coppying and moving of the file but not searching for a particular string to match in the filename. Finally, I thought I would put this steps together and here it is....

The following screen( on the left of the screen) shows the different variables declaration. The control flow has two file system task One for archiving the files and the other for renaming the original file.Both the tasks are encapsulated in a For each loop container as we will be navigating through all the files in the folder to find the match for the files we want to move to the archive folder.
The variables userd:

• vArchiveFile : Stores the path where the file will be moved to archive ( destination folder)
• vFileName : Name of the file ( source fie name)
• vFileSourcePath :  The path of the original file where it is found
• vFilsSpec : The string to search in the filename
• vWorkingFileName : The name of the file to be renamed

Double click on the For each loop container and fill up as follows. The filespec property of  the expression of the container should be the filename since we will be searching for the filenames that has EXE string in it.

Double click the FileSystem Task editor and change the destination path variable, the source connection and the operation. The source connection called ExtendedServices is a flat file connection.

The flat file connection is created as follows where in file name we have  file://sanftps/FTPUsers/354norman/ToHCFS/*EXE*.txt

Next, the most important part of the project is to define building the expression for the connection string of the file connection to look for a certain string ( EXE - in this case) in the source folder.
Click on the (..) next to the expression in the proeprty page of the connection manager.
Select the property Conenction string and then click on the expression to open up a screen as shown in the picture below.

This is where we specify the expression for the connection string.

FINDSTRING( @[User::vFileSourcePath] + @[User::vFileName] , @[User::vFileSpec] ,1)  > 0? @[User::vFileSourcePath] + @[User::vFileName] :""

Basically, we are using FINDSTRING string function and tying it to the Boolean Operand to build the connection string. That's all.

Error:System.Data.SqlClient.SqlException: profile name is not valid

We were getting the following error

System.Data.SqlClient.SqlException: profile name is not valid

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

The profile name is profile1@abc.com

user account associated to it was accountA@abc.com

We had to do the following to get rid of this error

Make sure that the account  accountA@abc.com is a role member of the Role DataBaseMailUserRole in the msdb database.This role is found under Security->Role in the msdb database

Set the profile profile1@abc.com to  public

use msdb

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name =  'profile1@abc.com'
@principal_name = 'public',@is_default = 1;


Once we did the above two setps after creating the profile the error was gone and everything started to work as normal.=

Find missing indexes

--missing indexes on a single table
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )AS [index_advantage] ,migs.last_user_seek ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns ,migs.unique_compiles ,migs.user_seeks ,migs.avg_total_user_cost ,migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )ON mig.index_handle = mid.index_handleWHERE
AND mid.database_id = DB_ID() mid.[statement]= '[statusupdates].[dbo].[Referrals]'ORDER
Look at the last_user_seek column and see how frequently it is seeked. This is an important factor to decide whether the suggested index is needed or not


I created the following index based on the equality column and there was no include column NONCLUSTERED INDEX IDX_referrals_Account
CREATE
ON Referrals (Account,Hospitalcode,Deleted,uploaded )
GO
/* to see when was the last index was updated*/
DBCC SHOW_STATISTICS ('referrals', 'IDX_referrals_hospitalcode') GO

/* after adding the new index I updated the statistics of the table */

EXEC
GO sp_updatestats
GO

Finding the bad indexes

Indexes are considered bad if the total writes > total reads



--Find bad indexes for a single table (writes > reads)--find bad indexs SELECT
user_seeks OBJECT_NAME(s.object_id) AS 'Table Name', i.name AS 'Index Name', i.index_id, user_updates AS 'Total Writes', + user_scans + user_lookups AS 'Total Reads', user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference' FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads'

--Find bad indexes for a single table (writes > reads)SELECT OBJECT_NAME(s.object_id) AS 'TableName',i.name AS 'IndexName',i.index_id,SUM(user_seeks) AS 'User Seeks',SUM(user_scans) AS 'User Scans',SUM(user_lookups) AS 'User Lookups',SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',SUM(user_updates) AS 'Total Writes'FROM
sys.dm_db_index_usage_stats AS sINNER JOIN sys.indexes AS i ON s.object_id = i.object_idAND i.index_id = s.index_idWHERE
OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1AND s.database_id = DB_ID()AND OBJECT_NAME(s.object_id) = 'referrals'GROUP BY OBJECT_NAME(s.object_id),i.name,i.index_idORDER BY 'Total Writes' DESC,'Total Reads' DESC ; ASC ;