dev

The common T-SQL script to shrink/truncate log file is using the script below

1
2
3

USE [foo]
GO
DBCC SHRINKFILE(foo_log, 1)

But, if the script run on SQL Server 2008 instance, resulting that the log file still have the same size. In other word you cannot shrink the log file.

To solve the issue here’s a work around:

Microsoft SQL Server 2008 has a default setting ‘FULL‘ for Recovery Model, so that means that we cannot just shrink Log File to minimum size.
Change the recovery model to ‘SIMPLE‘ and Shrink the Log File using:

1
2

USE [foo]
DBCC SHRINKFILE(foo_log, 1)

If ‘FULL‘ Recovery Mode still needed, cause is crucial for transaction DB, you can use ALTER sql command to  change and recover Recovery Mode to its original state.

1
2
3
4
5
6

USE [foo]
GO
ALTER DATABASE [foo] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(foo_log, 1)
ALTER DATABASE [foo] SET RECOVERY FULL WITH NO_WAIT
GO

although CURSOR and FETCH is classified as ‘evil‘ in SQL server, but sometimes we cannot avoid using it. Cause in some scenarios, pivot and other similiar method to perform looping and transformation is not sufficient enough. here’s a link of good example implementing cursor.

all example copy from microsoft sites,

http://msdn.microsoft.com/en-us/library/ms180152.aspx

[Read the rest of this entry...]

Problem:

Cannot delete Data Transfer Process (DTP) from DSO/CUBE to DSO/CUBE. Despite all the pre-conditions have been met, such as deleting data and requests in the target and source.

Error Message:

Delta DTP cannot currently be deleted (see long text)
Message no. RSBK037

Diagnosis
You want to delete a delta DTP that has been successfully used to load requests from the source into the target. If you delete the DTP, you will also delete the information on the source data that was successfully transferred. As a result the source data would be transferred again, if you create a new delta DTP that links the same source with the same target.

Procedure
You have two options:
1. Delete all the requests loaded with this DTP from the target and then delete the DTP.
2. Do not delete the DTP and continue to load deltas using this DTP.

Solution:

Go To Transaction RSBKDTP to delete DTP directly.

Source: http://forums.sdn.sap.com/thread.jspa?threadID=1568522&messageID=8581401

Additional Note:

Data Transfer Process (DTP) is related to various object such as Target, Source and Transformation. Here’s an extra table check for transformation. After deleting DTP, Cube or DSO  than the transformation should be deleted too.

RSTRAN Transformation
RSTRANFIELD Mapping of Rule Parameters – Structure Fields
RSTRANRULE Transformation Rule
RSTRANSTEPROUT Rule Type: Routine
RSTRANRULESTEP Rule Steps for a Transformation Rule
RSTRANSTEPMAP Mapping for Rule Step Within a Rule

Source:http://www.sapks.com/2010/01/dtp-tables.html