26 June 2008

How to use the DBCC SHRINKFILE statement to shrink the transaction log file

To shrink the transaction log file, execute the following statements:

USE MyDatabase
BACKUP LOG MyDatabase TO DISK='C:\MyDatabase.bak'
DBCC SHRINKFILE (MyDatabase_log, 300) WITH NO_INFOMSGS

If the transaction log does not shrinks, run the statements again to make more of the virtual log files inactive.

1) BACKUP LOG MyDatabase TO DISK='C:\MyDatabase.bak'
Backup the transaction log to make most of the active virtual log files inactive

2)DBCC SHRINKFILE (MyDatabase_log, 300) WITH NO_INFOMSGS
Shrink the transaction log file

04 June 2008

Running SSIS package on SQL Server Agent

A SSIS package can be executed using SQL Server agent.

To execute the package a Proxy Account and a credential must be configured in SQL Server.

Problems:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314).  The step failed.

The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:

1) The proxy account have the "Act as part of the operating system" privilege.

2) The SQL Server Account is in the group

SQLServer2005MSSQLUser$<server>$<instance>

3) The Proxy account is in the group

SQLServer2005DTSLUser$<server>$<instance>