Need Technical Details for this TSQL

Question

Need Technical Details for this TSQL

Agile

Ten Centuries

Points: 1153
More actions
November 26, 2009 at 5:24 am

#140213

Guys can you help me by giving technical details on this code: Especially on the bold one.
EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'
DBCC SHRINKFILE([Dbase_log], 100)
BACKUP log [DBaseName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([DBase_log], 100)
Thank you in advance
Thanks,
Morris

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Never mind the bold one, the other three are an extremely bad idea. Shrinking the log just means it'll grow again, slowing things down as it does. Truncating the log means that you've broken the log chain, can no longer do log backups and won't be able to do point-in-time recovery until another full backup is taken.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Agile Ten Centuries Points: 1153 More actions · Answer 2

Thank you for the reply,I just consult this question because it really seems theres something bad with that codes, honestly that code was just given by the programmer that made our payroll system. Because we complain about slow processing of transactions so he give that to us to run it everytime the processing of system is slow.

Is there any alternative?

And also this line. Is it a standard stored procedure in SQL Server?

EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'

Thanks,
Morris

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Damien-987863 (11/26/2009)
Because we complain about slow processing of transactions so he give that to us to run it everytime the processing of system is slow.

That's insane. So every time things run slow he's telling you to break the recovery chain of the DB and risk a loss of data? Ask him if he's willing to be accountable for a loss of data if the DB crashes and you can't recover because his script broke the log chain. Actually, ask him if he even knows what the commands do.

And also this line. Is it a standard stored procedure in SQL Server?
EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'

It is, though the replacement on SQL 2005 is ALTER INDEX ... REBUILD. It should not be run ad-hoc when things are slow. Especially because it's a very intensive process as it rebuilds all the indexes in the database.

That (or a maintenance plan with the rebuild indexes task) should be run regularly. Once a week may be sufficient to start.

Then you can ask the 'programmer' for a fix for the poor performance, rather than a shotgun-approach to fixing symptoms with nasty side effects.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Agile Ten Centuries Points: 1153 More actions · Answer 4

I think your right that it is a shotgun approach. I will follow your suggestion to start on weekly index maintenance plan. I think I should talked to that programmer and asked if he know what he is doing.

Thank you very much. It's a big help!

Thanks,
Morris