| Author |
Topic |
|
PaulTeal
Yak Posting Veteran
67 Posts |
 Posted - 2003-08-08 : 09:14:18
|
| Is there a way to force a stored procedure to run without logging any transactions, similar to BCP?I have to modify some data each night (not just copy some data) and all of the sudden a few days ago the stored procedure I created to modify the data started to take 4.5 hours instead of 30 minutes like it had for months.This started about the same time that our transactional backups started failing. I am our SQL Developer, not our DBA, so I am not allowed to touch backups. The DBA is running manual backups periodically until she can get the transactional backups working again.What I want to try is to run the stored procedure without logging any transaction in the transaction log, kind of like BCP. This would let me know if the problem with the transaction log is affecting the stored procedure or if the delay is caused by something else.Is this idea whacked out? Or do you think I am on the right track?Paul Tealpaul@partytilyoupop.com |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-08 : 09:17:56
|
| Nope - can't do that.>> until she can get the transactional backups working againIsn't that some sort of a priority for your company?Is maint.exe involved somewhere?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
PaulTeal
Yak Posting Veteran
67 Posts |
Posted - 2003-08-08 : 09:54:32
|
| In our organization, the DBA is separate from the Developer: The developer writes the database definitions, indexes, performance tuning, etc. and the DBA sits on her butt and blames the developer when anything goes wrong -including backups. Strange world.Paul Tealpaul@partytilyoupop.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-08 : 09:59:50
|
| Get the DBA to review and agree to anything that gets relesaed - then the production system is nothing to do with the developers.All of a sudden system testers, infrastructure people, users, analysts, managers, ... will start getting the blame.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
PaulTeal
Yak Posting Veteran
67 Posts |
Posted - 2003-08-08 : 10:14:32
|
| That's a great idea, NR. I have a hard time finding resources for procedures and best practices like that. Have you seen any books that cover these kinds of issues? All I can seem to find are books on syntax, etc. I'm especially interested in procedures for rolling out a development server into a production server, since I have to develop on our production systems (no test environment). It can be nerve-racking. I am pitching them on buying a development server.Paul Tealpaul@partytilyoupop.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-08 : 10:17:51
|
| You don't have a dev box? How do you do development?What type of system is it? How many users? How much data?Time to find a new dba...EDIT: Are you 2000? If so, at the very least the "Prod dba" should have built you another instance...Brett8-)SELECT POST=NewId() |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-08 : 10:35:59
|
| >> since I have to develop on our production systems (no test environment).lol.seehttp://www.nigelrivett.net/SQLServerReleaseControl.htmStarted to rewrite and expand this about a year ago - doubt if I'll complete it unless a company forces me too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
PaulTeal
Yak Posting Veteran
67 Posts |
Posted - 2003-08-08 : 12:59:59
|
| You don't have a dev box? NoHow do you do development? Very carefully - seriously, it is extremely nerve wracking and difficultWhat type of system is it? SQL Server 200How many users? 1,200 - 2,000Paul Tealpaul@partytilyoupop.com |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-08-08 : 13:26:41
|
| Why not at least develop on a locally installed instance of SQL Server or MSDE. Delevloping on a production box is asking for trouble, esp. with that many users. I'd say that you need some sort of development environment, or Devleopment and Production will step on each other (like I'm sure they do at times now). I'd also probably document all of your requests for a development environment. Might give you some ammo when they try to fire you for accidently killing their production server.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Unlogged Sproc?