http://www.sqlservercentral.com/Content tagged T-SQL, SQL Server 7, 2000, Miscellaneous posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)When automating administrative actions for SQL Server, there are a number of ways to handle the workflow. Longtime SQL Server DBA Roy Carlson brings us a technique for using a text file as input to the standard SQL Server tools. http://www.sqlservercentral.com/articles/Miscellaneous/2801/2008/01/14http://www.sqlservercentral.com/articles/Miscellaneous/2801/New Author! Deadlocks aren't the most common problem but they happen they can really degrade performance. Shiv works through how to nail down what is causing the deadlock. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/2007/04/06http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/When automating administrative actions for SQL Server, there are a number of ways to handle the workflow. Longtime SQL Server DBA Roy Carlson brings us a technique for using a text file as input to the standard SQL Server tools. http://www.sqlservercentral.com/articles/Miscellaneous/2801/2008/01/14http://www.sqlservercentral.com/articles/Miscellaneous/2801/New author Jambu Krishnamurthy brings us a look at table variables and how they differ from temporary tables in SQL Server 2000. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2691/2006/11/21http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2691/Attaching and detaching databases is old hat these days right? Do you know how to reattach a database that has more than 16 files? Or do you know what happens if you try to reattach a database that had two log files but one is missing/deleted? And even if you know the answer to that - do you know how to fix it without restoring from backup? Maybe it's not ALL old hat just yet! http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/2006/06/23http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/Performing routine database maintenance such as reindexing is important for keeping your databases running at peak performance. When you use INSERT, UPDATE, and DELETE statements to modify table data, indexes can become fragmented. Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, you get inefficient data reads when accessing tables and slower database performance. http://www.sqlservercentral.com/redirect/articles/1772/2005/03/10http://www.sqlservercentral.com/redirect/articles/1772/Do you use the maintenance plans or hate them? Wish they would do more? Curious about how they work under the hood? Cmon, you gotta read this one! Trust us, it's not another "how-to" article! Well, maybe just a little bit! http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/2005/03/04http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/A new article from MSDN magazine on stopping SQL Injection attacks by examining how the SQL can be exploited. It's a good basic article for developers as well as DBAs who might have to review code.http://www.sqlservercentral.com/redirect/articles/1470/2004/08/20http://www.sqlservercentral.com/redirect/articles/1470/New Author! Deadlocks aren't the most common problem but they happen they can really degrade performance. Shiv works through how to nail down what is causing the deadlock. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/2007/04/06http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/As Robin points out there is no built in way to make sure users have strong passwords when using SQL authentication. There are a couple changes you can make (with appropriate warnings!) that will allow you to do this. Read on to find out why and how. http://www.sqlservercentral.com/articles/Security/howtomakesureyouhavegoodpasswords/1299/2004/03/02http://www.sqlservercentral.com/articles/Security/howtomakesureyouhavegoodpasswords/1299/Do your developers really understand how to prevent injection attacks? Or scarier still, how many know what an injection attack is? Chris has some great examples of how sql injection works and how to prevent it. http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/2004/02/02http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/For the most part the DTC (Distributed Transaction Coordinator) just works. What happens when it doesn't? Chris had to resolve an issue with it recently and documented some of the steps he used. http://www.sqlservercentral.com/articles/Administering/debuggingmsdtcissues/1257/2004/01/19http://www.sqlservercentral.com/articles/Administering/debuggingmsdtcissues/1257/Are you using alerts to help you keep track of things? Are you using as many alerts as you should be? Jeremy has a great list of alerts that he considers so important they are on his 'best practice' list. Definitely worth reading. http://www.sqlservercentral.com/articles/Administering/alertalertbackupandrestorebaby/1253/2004/01/08http://www.sqlservercentral.com/articles/Administering/alertalertbackupandrestorebaby/1253/Haidong continues to point out ideas that might get you thinking about ways you can do more administration with less work. In this article he demonstrates a couple useful tasks you can do with xp_cmdshell. http://www.sqlservercentral.com/articles/Administering/usingxp_cmdshell/1250/2004/01/05http://www.sqlservercentral.com/articles/Administering/usingxp_cmdshell/1250/As Chris points out, in most applications these days you end up having to go with optimistic locking, which presents a few challenges. Chris works through the list of options. If you're building web/disconnected apps and need anything besides last update wins, this one is for you. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/dealingwithchangingdata/1233/2003/12/19http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/dealingwithchangingdata/1233/Kumar discusses the differences between scale up and scale out, then does a very good walk through of how to build a scale out solution. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/scalingout/1209/2003/12/03http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/scalingout/1209/Another product review! Chris put AdeptSQL (a schema comparison and sync product) to work and wrote up the results. This article also includes some feedback from the vendor that is worth reading. http://www.sqlservercentral.com/articles/Administering/reviewofadeptsql/1173/2003/11/03http://www.sqlservercentral.com/articles/Administering/reviewofadeptsql/1173/Aladdin is an "alter table" script generation tool for SQL Server 7/2000. Aladdin is currently in BETA. They are currently looking for for 25 people willing to use the beta product and report their experiences. Users who fully participate in the beta will get a free, single user license when the product is released. To participate in the Aladdin BETA program, please download the software and send an email to Ken Otto: ottosoft@hotmail.com - Subject: Aladdin Beta Test. http://www.sqlservercentral.com/redirect/articles/1139/2003/10/01http://www.sqlservercentral.com/redirect/articles/1139/Don't like the blackbox approach of maintenance plans? Like the maintenance plans but need a tweak to suit your needs? New author Robin Back has put together a monster script to allow you to build your own quickly and easily. http://www.sqlservercentral.com/articles/Administering/createmaintenancejobwithaclickwithoutusingawizard/1102/2003/08/20http://www.sqlservercentral.com/articles/Administering/createmaintenancejobwithaclickwithoutusingawizard/1102/Collations hard to sort out (I know, bad joke)? Mike did what a good DBA gets paid to do, figure out an easy way to fix something and remove the chance of a user mistake at the same time. Good explanation and a couple scripts show how to handle column level collations. http://www.sqlservercentral.com/articles/Administering/changingroguedatabasecollations/994/2003/05/26http://www.sqlservercentral.com/articles/Administering/changingroguedatabasecollations/994/This article by Simon Galbraith (from Red Gate software, maker of SQL Compare) discusses migrating changes from development to staging, QA, and on to production. If you've never seen the need for a schema compare tool (Steve Jones!), this is worth reading.http://www.sqlservercentral.com/redirect/articles/982/2003/05/06http://www.sqlservercentral.com/redirect/articles/982/New author! Mike Pearson discusses how auto close was at the root of a performance problem he was troubleshooting, how you can check your servers easily, and discusses why both auto close and auto shrink are bad ideas in a production environment. http://www.sqlservercentral.com/articles/Administering/autocloseandautoshrinkjustdont/984/2003/05/05http://www.sqlservercentral.com/articles/Administering/autocloseandautoshrinkjustdont/984/Greg returns with an article based on some practical experience using QA templates to save time and work. Good examples, good work through, good idea! http://www.sqlservercentral.com/articles/Administering/usingqueryanalyzertemplates/974/2003/04/28http://www.sqlservercentral.com/articles/Administering/usingqueryanalyzertemplates/974/A monster book at 980 pages, it's written in 'how-to' format and has a ton of good material. Andy gave it the once over for us and reports back - see what he thinks! http://www.sqlservercentral.com/articles/Installation/reviewofsql2000fastanswers/959/2003/04/18http://www.sqlservercentral.com/articles/Installation/reviewofsql2000fastanswers/959/This is a product we haven't reviewed yet, but looks interesting. A SQL editor type app, features listed include integration with Visual SourceSafe, 'intellisense' statement completion, and keyboard macro's. http://www.sqlservercentral.com/redirect/articles/950/2003/04/04http://www.sqlservercentral.com/redirect/articles/950/We've had a good run of jobs about job management lately, Randy sent us another one that looks at the job of job management and shows us how he tackled the problem. http://www.sqlservercentral.com/articles/Administering/managingjobsusingtsql/945/2003/04/02http://www.sqlservercentral.com/articles/Administering/managingjobsusingtsql/945/Quite a while back Andy wrote an article about why not using primary keys and clustered indexes was a worst practice. Chris weighs in this week with a great article that gives some more in depth info on clustered indexes and why he thinks they should be used on every table. http://www.sqlservercentral.com/articles/Performance+Tuning/clusterthatindex/952/2003/03/30http://www.sqlservercentral.com/articles/Performance+Tuning/clusterthatindex/952/This week Andy looks at where, when, and how jobs should be run and why you need to think about those items before you build the job. Part of this is deciding what runs on production servers and what doesn't. http://www.sqlservercentral.com/articles/Administering/managingjobspart3/936/2003/03/11http://www.sqlservercentral.com/articles/Administering/managingjobspart3/936/How many jobs do you have? 10? 100? 1000? Andy makes the point that what works to manage for a small number of jobs doesn't work when that number doubles or triples (well, unless you only had 1 job to start with!). In part one of two, this article looks at ideas for using categories and naming conventions to get things under control. http://www.sqlservercentral.com/articles/Administering/managingjobspart1/906/2003/01/31http://www.sqlservercentral.com/articles/Administering/managingjobspart1/906/