Proper Way to Truncate Log After Performing Full Backup (SQL Server 2000)

Hello all - I have a SQL Server 2000 database setup using the Full Recovery Model. Each night, we backup the entire database, and as such would like to truncate the log at this time as well.

Is the best way to do this to also backup the Transaction Log, and then perform a DBCC SHRINKFILE command? It just seems like there should be an easier way...?

Thanks!

Let's back up a few steps here.

What are you gaining by shrinking the transaction log every night?

The transaction log is designed to continually reuse its space internally. Whenever you back up the transaction log, all virtual log files (logical areas within the physical log file) which are not involved in any current transaction are marked as "free" and can be reused.

So, one way to manage the growth of a transaction log is to vary the frequency of log backups. The more frequent the log backup, the less data needs to be stored in the log (and the less data is lost in the event of a disaster).

Once the log reaches a steady-state, it shouldn't grow unless some exceptional update happens.

When you continually shrink and grow the file, you end up with filesystem fragmentation, which isn't good for performance. Also, the log file growth itself takes time.

Programmatically developing an entire package..

Hi all,

I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.

Thanks,

Prithvi.

This is the code for creating connections and adding dataflow task. I need to add a script component transformation, but initially I wanted to see if a one-to-one mapping works fine.

Public Sub CreateConnections()

'Add the OLE DB and Flat File Connection Managers

Console.WriteLine("Creating the MyOLEDBConnection")

Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")

cnOLEDB.Name = "MyOLEDBConnection"

cnOLEDB.ConnectionString = "<connection string>"

Console.WriteLine("Creating the MyFlatFileConnection")

Dim cnFile As ConnectionManager = myPackage.Connections.Add("FLATFILE")

cnFile.Name = "MyFlatFileConnection"

cnFile.Properties("ConnectionString").SetValue(cnFile, "C:\sample.csv")

cnFile.Properties("Format").SetValue(cnFile, "Delimited")

cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, False)

cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)

cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)

cnFile.Properties("TextQualifier").SetValue(cnFile, """")

End Sub

Public Sub AddDataFlowTask()

'Add a Data Flow Task

Console.WriteLine("Adding a Data Flow Task")

Dim e As Executable = myPackage.Executables.Add("DTS.Pipeline")

Dim taskDF As TaskHost = CType(e, TaskHost)

taskDF.Name = "DataFlow"

Dim DTP As MainPipe

DTP = CType(taskDF.InnerObject, MainPipe)

' Add the FLAT FILE Source

Console.WriteLine("Adding the File Source")

Dim DFSource As IDTSComponentMetaData90

DFSource = DTP.ComponentMetaDataCollection.New()

DFSource.ComponentClassID = "DTSAdapter.FlatFileSource.1"

DFSource.Name = "FlatFileSource"

' Connect, populate the Input collections and disconnect

Dim SourceInst As CManagedComponentWrapper = DFSource.Instantiate()

SourceInst.ProvideComponentProperties()

DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyFlatFileConnection").ID

DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyFlatFileConnection"))

SourceInst.AcquireConnections(vbNull)

SourceInst.ReinitializeMetaData()

SourceInst.ReleaseConnections()

'checking If the external metadata columns are available as source output columns and the output is 0 which means no columns are being passed as output from source

Dim column As IDTSOutputColumn90

Try

Console.WriteLine("connection name: " & DFSource.RuntimeConnectionCollection(0).Name.ToString)

Console.WriteLine("output collection name: " & DFSource.OutputCollection(0).Name)

Console.WriteLine("output collection description :" & DFSource.OutputCollection(0).Description)

Console.WriteLine("source output columns count :" & DFSource.OutputCollection(0).OutputColumnCollection.Count.ToString

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message.ToString)

End Try

'tried to print col names but it does not print any

For Each column In DFSource.OutputCollection(0).OutputColumnCollection

Console.WriteLine(column.Name.ToString)

Console.WriteLine(column.DataType.ToString)

Next

' Add the OLEDB Destination

Console.WriteLine("Adding OLEDB Destination")

Dim DFDestination As IDTSComponentMetaData90

DFDestination = DTP.ComponentMetaDataCollection.New()

DFDestination.ComponentClassID = "DTSAdapter.OLEDBDestination"

DFDestination.Name = "OLEDBDestination"

' Create an instance of the component

Dim DestInst As CManagedComponentWrapper = DFDestination.Instantiate()

DestInst.ProvideComponentProperties()

If DFDestination.RuntimeConnectionCollection.Count > 0 Then

DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyOLEDBConnection").ID

DFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyOLEDBConnection"))

End If

DestInst.SetComponentProperty("AccessMode", 0)

DestInst.SetComponentProperty("OpenRowset", "tempSSIS")

DestInst.SetComponentProperty("FastLoadKeepNulls", True)

' Map a connection between the source and destination

Dim path As IDTSPath90 = DTP.PathCollection.New()

path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFDestination.InputCollection(0))

Dim InColumns As IDTSVirtualInputColumnCollection90 = DFDestination.InputCollection(0).GetVirtualInput().VirtualInputColumnCollection()

' the number of input columns to destination is zero

Console.WriteLine("input columns : " & InColumns.Count.ToString)

Try

DestInst.AcquireConnections(vbNull)

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message)

End Try

DestInst.ReinitializeMetaData()

'Console.WriteLine("input columns : " & DFDestination.InputCollection(0).InputColumnCollection.Count.ToString)

For Each input As IDTSInput90 In DFDestination.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

Console.WriteLine(vColumn.Name.ToString)

Console.WriteLine(vColumn.DataType)

DestInst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In DFDestination.InputCollection(0).InputColumnCollection

exCol = DFDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

DestInst.MapInputColumn(DFDestination.InputCollection(0).ID, col.ID, exCol.ID)

Next

DestInst.ReleaseConnections()

End Sub

Please see if i did wrong any where(which I always happen to do). But based on the msdn material, the above code should work, i guess.