| Author |
Topic |
|
DP978
Constraint Violating Yak Guru
269 Posts |
 Posted - 2010-01-28 : 12:08:23
|
| Is there any hidden tax on the system while the batch is waiting to execute while using these key words?Ex. Say I want something to run in off hours, 10 pm, would this sitting in queue for 6 hours hurt the server at all?Thanks! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-01-28 : 12:40:23
|
| I'm not sure about the overhead as I never use that. But, if you wanted somethng to run at 10pm why not set up a SQL Agent job to execute at that time? |
 |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 13:23:59
|
| Thanks, I'd still be interested in that answer if anyone can supply it.Lamprey, where abouts would I go to set that up? Apprciate any help. I may not have access to every option, but won't know until I know where it is :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 13:39:16
|
| SQL Server Management StudioSQL Server Agent : Jobs |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 13:44:26
|
| Wow, I must be terribly noob'ish...Is 'SQL Server Agent' on the toolbar up top? Honestly, I don't see it :( I have the Management Studio open.File|Edit|View|Query|Project|Tools|Window|Community|HelpI figured under tools, but only profiler there. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 15:05:58
|
| Open the "tree" for the Server (Left pane)SQL Agent should be in the list (or possibly under "Management")then under that "Jobs" |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 15:36:03
|
| Thanks, I do not think I have permissions on it for this server.I have under the server tree...Databases|Security|Server Objects|Replication|Management (and under management: SQL Server Logs|Activity Monitor|Database Mail|Distributed Trans Coor.|Full Text Search)Appreciate the help anyways... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 15:47:36
|
| Perhaps you have Express? |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 15:51:17
|
| I would doubt it... (would this tell you?) Either way it is in a corporate setting.Microsoft SQL Server Management Studio 9.00.3042.00Microsoft Analysis Services Client Tools 2005.090.3042.00Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.5512Microsoft .NET Framework 2.0.50727.3082Operating System 5.1.2600I just know there are a lot of permissions I do not have, some being Permanent Table Creation/Function Creation/Sproc Creation, I can create temps on all of these though. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 15:54:14
|
Express: Full product - SQL Agent at the bottom |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 15:57:22
|
| Definately no Express tagged on the end...so its the real thing. I see the agent is at the very bottom under Notification Services, under mine there is nothing. :( I guess that was why I was trying to find this work around heh...I guess I'm SOL? heh |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-28 : 15:57:58
|
Yup...looks like its definitely permissions...Talk to your DBA..he will probably ask you a thousand questions..and once he's convinced you're a sane developer who knows what you are doing...he MIGHT give you the permissions you need. EDIT : Just do a Select @@version and confirm you have an Enterprise Edition. |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 16:02:58
|
| Microsoft SQL Server 2005 - XXXXXXXXX (X64) Jun 12 2008 16:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Look right? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 02:36:07
|
Check your XXXX here:http://www.sqlteam.com/article/sql-server-versions(Its not a secret licence number, its the stupid build number that MS provide instead of telling you what version + Service Pack you are on)It won't tell you if its enterprise (another stupidity IMHO) but it will alert you if there are newer service packs that maybe should be installed.Anyways, its the Enterprise Edition running on 64 bit Windows Server 2003 NT 5.2I didn't know DBAs could prevent DEVs having access to SQL Agent, but now I know that is an option I'll go and turn off all access for my DEVs  |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-29 : 03:30:42
|
| And to answer your initial question: no, WAITFOR DELAY does not hurt the server. Of course there is *something* going on but it's not worth mentioning... - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 04:34:57
|
| We have WAITFOR in tight loops that delete gazillions of rows to allow other processes to "get in", so for sure we aren't blocking them with our WAITFORs |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 10:21:10
|
| Well glad we could teach you something Kristen!And thanks Lumbago for the reassurance, I really need to run things at off peak hours and this is the best work around I could find :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 12:09:38
|
| "Well glad we could teach you something Kristen!"The DEVs may not thank you!! |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-29 : 12:21:38
|
| Heh, most likely not, I do not know all of the functions below 'SQL Server Agent' but if they are smart enough I am sure they will find ways around some of these things, like I commonly do ;-)Not that I condone that sort of behavior!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 13:59:07
|
| Yeah, you can INSERT into the tables that SQL Agent uses. The GUI is MUCH easier though! and I can guarantee that my DEVs won't bother to master the format of the SQL Agent tables!! |
|
|
|
|
Waitfor Delay/Time