DUMP TRANSACTION Database WITH NO_LOG

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

DUMP TRANSACTION Database WITH NO_LOG

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2015-04-16 : 07:32:09

[code]
Hi

This command is deprecated in SQL server 2008 and higher version .
I have migrated SSIS package from SQL SERVER 2005 to SQL server 2012 .Now the challenge is, In 2005 this
DUMP TRANSACTION <Database> WITH NO_LOG command was used which is
not working in SQL 2012 .How can I replace its functionality in
2012.

Kindly please suggest me one this. [/code]

Vijay is here to learn something from you guys.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-16 : 10:24:52

The equivalent would be to set the recovery model to SIMPLE and then shrink the log file.

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;
GO

CHECKPOINT;
GO

DBCC SHRINKFILE ('LogicalNameOfLogFile', FileSizeInMegabytes);
GO

ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
GO

In general, changing the recovery model to SIMPLE is not recommended. It breaks the log chain, and limits your recovery options. However, if you are not taking log backups and don't care about that, it is okay to set it to SIMPLE. If you are not taking log backups, you could just as well leave the database in SIMPLE recovery model (i.e., omit the last statement).

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2015-04-23 : 05:40:49

HI James ,

My Databse is already in simple recovery mode and we do no take log backup so is it fine to use below command only.

CHECKPOINT;
GO

DBCC SHRINKFILE ('LogicalNameOfLogFile', FileSizeInMegabytes);
GO

Or safer side should I use Alter command also to cahnge the recovery model to SIMPLE like below.

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;
GO

CHECKPOINT;
GO

DBCC SHRINKFILE ('LogicalNameOfLogFile', FileSizeInMegabytes);
GO

Vijay is here to learn something from you guys.

Kristen
Test

22859 Posts

Posted - 2015-04-23 : 08:41:14

is this a one-time-only Shrink?

Not a good idea to Shrink files regularly (e..g on a schedule) as they get fragmented - and if the file repeated gorws to, say, 100GB then it needs to be that big.

However, if it is normally 10GB and grows to 100GB because of a one-off action (e.g. deleting stale data) then it would be OK to shrink it back to 10GB (but not a good idea to shrink it to anything smaller - such as 10MB intending to then let it grow back again)

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2015-04-23 : 10:20:52

HiKristen,

My job runs every midnight and takes more than 5 hours to complete and job is calling SSIS package and I am going to use

ALTER DATABASE MyDatabaseName SET RECOVERY SIMPLE;
GO

CHECKPOINT;
GO

DBCC SHRINKFILE ('LogicalNameOfLogFile', 1);

GO

Because DUMP TRANSACTION <Database> WITH NO_LOG is no more in the
2012. Please advise on this,is it feasible to use it.

Thanks
VIjay

Vijay is here to learn something from you guys.

Kristen
Test

22859 Posts

Posted - 2015-04-23 : 12:22:07

As I said: you should not schedule a regular task which uses SHRINK on either the Log File or the Data File.

It might be that they used that in the old days - it was probably to solve a different problem, and back then we did things like that for various reasons to work around other issues. But nowadays I can't think of a reason to use it and regular use will fragment your files and make performance worse.

I suggest you just get rid of it as part of your migration to SQL 2012 (although you might want to, manually, shrink the file [just once!!] if it is large)

It might be that the SSIS package was importing lots of data with a TRANSACTION set? If so that would make a large log file. I expect you don't need that? if SSIS is making a large log file then it would be better to "improve" the SSIS (e.g. set a suitable batch size)

If you shrink the log file today, and tomorrow night it grows back again, and then you shrink it again ... you still need the big log file every night, so there is no point shrinking it each day - hopefully that makes sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources