http://www.sqlservercentral.com/Content tagged T-SQL, SQL Server 7, 2000, Miscellaneous, Administering posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)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/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/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/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/If you've been with us for a while you know that Andy started a series last year on worst practices. As he says, maybe you can't always do best practices, but at least dont do the worst ones. Read the article and add a comment, participate! http://www.sqlservercentral.com/articles/Administering/worstpracticesortingbyordinal/811/2002/10/01http://www.sqlservercentral.com/articles/Administering/worstpracticesortingbyordinal/811/In this follow up to one of our most popular articles, Andy responds to comments posted by readers and discusses how to manage SQL logins effectively in your applications. http://www.sqlservercentral.com/articles/Administering/thecaseforsqlloginsparttwo/780/2002/08/19http://www.sqlservercentral.com/articles/Administering/thecaseforsqlloginsparttwo/780/Andy sits down with an entry level book to see if he should use it at work as a teaching aid. Did he like it? Should you buy it? Read the review now! http://www.sqlservercentral.com/articles/Installation/reviewofsqlserver2000programmingmspress/694/2002/06/04http://www.sqlservercentral.com/articles/Installation/reviewofsqlserver2000programmingmspress/694/In this follow up article Andy looks at how maintenance plans report errors and how they actually accomplish what they do. A little snooping with Profiler reveals all! http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplansunderthecovers/686/2002/05/21http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplansunderthecovers/686/Version control for stored procedures isn't always popular and certainly isn't easy. Or can it be? Andy discusses a technique he used on a recent project that you might find interesting. http://www.sqlservercentral.com/articles/Administering/versioncontrolforstoredprocedures/681/2002/05/10http://www.sqlservercentral.com/articles/Administering/versioncontrolforstoredprocedures/681/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/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/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/Have you ever experienced a T-SQL query, a stored procedure, view or a trigger returning unexpected results. In this article by Raj Gill, he shows you how ANSI DEFAULTS may be to blame. http://www.sqlservercentral.com/articles/Administering/understandingtheimplicationsofansisql92setoptions/482/2001/10/18http://www.sqlservercentral.com/articles/Administering/understandingtheimplicationsofansisql92setoptions/482/Ever have a large batch of scripts you need to run? It takes a while if you have to open each one in Query Analyzer and execute it. One of our readers proposed an alternative - take a look the small app Andy Warren wrote to make doing this task a breeze. http://www.sqlservercentral.com/articles/Administering/scriptrunner/292/2001/06/14http://www.sqlservercentral.com/articles/Administering/scriptrunner/292/