================================================ SEARCHDATABASE.COM'S DBA Tips and Tricks June 27, 2001 ================================================ Welcome to the searchDatabase.com DBA Tips and Tricks newsletter! Today's tip, "Stored procedure for killing all processes" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci750286,00.html ******** Sponsored by Lumigent ***************** "Always wear clean underwear!" Mom taught you many good habits. If she was a DBA she would have given you a few more tips, like "use Log Explorer to find and fix user errors" and "don't recover from a backup, use Log Explorer to recover column, row or table data selectively and in real time." Get your FREE TRIAL-- http://www.lumigent.com/searchdb.htm ************************************************ "Stored procedure for killing all processes" By Cade Bryant You're a SQL Server DBA, and you notice that *something* is hogging your system's resources. You don't want to take the time to wade through dozens of processes and individually kill the ones you think might be causing the problem. Ever wish SQL Server had a "panic button" for terminating all user processes, without stopping/restarting the server? This stored procedure I created does exactly that. Note that when you run it, it may return several error messages. That's because, as it cycles through the numbers representing process IDs, many of the numbers may not be valid user process IDs. This does not prevent the stored procedure from doing its job; it will still terminate all user processes (except your own), thus freeing up system resources. create procedure sp_kill_all as declare @i int declare @sql varchar(100) set @i = 1 while @i < 100 begin set @sql = 'kill ' + cast(@i as varchar(4)) exec (@sql) if @@error = 0 begin print 'terminated ' + cast(@i as varchar(4)) end set @i = @i + 1 end MORE ON THIS TOPIC: The Best SQL Server Web Links: tips, tutorials, scripts, and more. http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax282894,00.html Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today! http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz556880_cts556855,00.html Ask your toughest DB2 and SQL questions--or help out your peers by answering them--in our live discussion forums: http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Check out our new "Ask the Experts" feature! We have Database Design, SQL, Oracle, DB2, and SQL Server gurus waiting to answer your technical questions: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html ------------------------------------------------ LEARNING ZONE FEATURED BOOK OF THE WEEK ------------------------------------------------ "SQL for Smarties: Advanced SQL Programming, Second Edition" By Joe Celko SQL for Smarties was hailed as the first book devoted explicitly to the advanced techniques you need to transform yourself into an expert SQL programmer. Now, in this fully updated second edition, SQL mastermind Joe Celko keeps you moving forward, using his entertaining, conversational style to teach you the best solutions to old and new challenges and to convey the way you need to think if you really want to get the most out of your SQL programming efforts. http://www.digitalguru.com/dgstore/product.asp?isbn=1558605762&ac_id=58 >> Joe Celko will be live at searchDatabase.com on June 28, 2001 at 11:00 AM EST (16:00 GMT) for a real-time audio presentation and chat called "SQL for Smarties." Be sure to tune in tomorrow! ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ================================================ If you would like to sponsor this or any TechTarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
