Bad Practice

Question

Post reply

Bad Practice

Trey Staker

SSCarpal Tunnel

Points: 4796
More actions
March 25, 2010 at 10:50 am

#231130

I've got a 3rd party companies' support that uses sql server as their DB for their product that is insisting I add this command to my full backups, sql server 2005. compat 90 on all the databses.
DUMP TRANSACTION <DATBASENAME> WITH TRUNCATE_ONLY
They are driving me nuts! Anyone have some links handy to help me with the battle I'm about to fight with their engineers and "DBA" once I get past level one support?
I have to do it or I loose support from them on their applications so I added this disclaimer in the job's description:
** Disclaimer from the DBA -- This does nothing to a database in FULL
or BULKLOGGED in SQL Server 2005 or greater. This is also in the
process of being depricated by Microsoft because it is a known bad
practice and has been fully depricated in SQL Server 2008.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Is this done before or after the BACKUP DATABASE command?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Give them the explanation that you have already done that and then don't do it. Or you could run the command and then perform a full backup immediately.

Then demand an escalation.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 3

It is run just before the full back up. It really does no harm exec put a warning in the sql error log. It's the principle of it.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 4

CirquedeSQLeil (3/25/2010)
Give them the explanation that you have already done that and then don't do it. Or you could run the command and then perform a full backup immediately.
Then demand an escalation.

LOL we think a lot a like when it comes to this. This is exactly what I was planning on doing.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Lowell SSC Guru Points: 323518 More actions · Answer 5

Trey already knows this...he identified it as deprecated...this is so other folks(like me) don't have to google it.

the DUMP word was deprecated in favor of BACKUP; you cannot even use it in SQL 2008.

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

the statement was trying to truncate the transaction logs;

in SQL 2008, the equivilent command was also deprecated and no longer valid at all.:

BACKUP LOG WITH TRUNCATE_ONLY

*The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Lowell (3/25/2010)
the DUMP word was deprecated in favor of BACKUP; you cannot even use it in SQL 2008.
http://msdn.microsoft.com/en-us/library/ms144262.aspx
the statement was trying to truncate the transaction logs;
in SQL 2008, the equivilent command was also deprecated and no longer valid at all.:
BACKUP LOG WITH TRUNCATE_ONLY
*The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.

That is excellent information. The real question is: "Will the support staff accept it or be able to understand it?"

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 7

Level 1 support won't but if I can get past them and speak to their engineers, then yes they can. I've spoken with them before and they admit they they are week on the "DBA" stuff, they've got a limited staff and don't mind a little direction. I just need to be prepared.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Trey Staker (3/25/2010)
Level 1 support won't but if I can get past them and speak to their engineers, then yes they can. I've spoken with them before and they admit they they are week on the "DBA" stuff, they've got a limited staff and don't mind a little direction. I just need to be prepared.

That is good to know. My statement was a generalization about L1 support.

The bigger fight will be to persuade them to rewrite their phone support scripts for this situation.

I have seen support provide "backup" scripts that used a cursor to loop through a table to copy the records out to a different table one by one. Mind you, I ax any scripts like that, that come from support.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Trey Staker (3/25/2010)
It is run just before the full back up. It really does no harm exec put a warning in the sql error log. It's the principle of it.

Actually, it does do harm, it breaks the t-log chain.

For this scenerio assume the following:

full backup taken daily at 6:00 PM

differential backups taken at 8, 12, 4 (6 times a day)

t-log backups taken at 10, 30, and 50 minutes after the hour

Start with Monday nights full backup, everything works great, so does Tuesdays. However, Wednesday morning at 9:00 AM, a hardware failure corrupts the database. You are able to run a t-log tail backup and are getting ready to restore Tuesday nights full backup. Unfortunately, the full backup file is corrupt making the full backup unusable.

Two scenerios exist here:

Your current scenerio, truncating the t-log just prior to running the full backup.
Your hosed unless you happen to have another copy of the backup file stored else where and it isn't corrupt.

Second scenerio, no truncating of the t-log occurs prior to Tuesday nights full backup.
Restore Monday nights full backup (norecovery), latest differential backup prior to Tuesday's full backup (norecovery),
then restore all t-log backups (norecovery, with exception of the tail log backup), and you are recovered up to the point of failure.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Also, iirc, the TRUNCATE_ONLY option on BACKUP LOG has also been depreciated as well.

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 11

Thanks Lynn. That is a great point and will use your scenario when speaking with the engineers.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Lynn Pettis (3/25/2010)
Also, iirc, the TRUNCATE_ONLY option on BACKUP LOG has also been depreciated as well.

Yes it has.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 13

CirquedeSQLeil (3/25/2010)
Lynn Pettis (3/25/2010)
Also, iirc, the TRUNCATE_ONLY option on BACKUP LOG has also been depreciated as well.
Yes it has.

For the record in 2005 it will let you execute the BACKUP LOG with TRUNCATE_ONLY but will put this into the sql server log:

Message

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.

Level 1 support response to this was, "Well you seem to know more about this than me but this is how it works with our application." I knew then that I wasn't getting anywhere with them but like I said once I get to the next level of support they'll listen to me and hopefully change it before their next release of their app and support scripts.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum