SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Free White Paper on Data Recovery Best Practices
Need Access to Helpful SQL Server Experts?
(Below COMMENTARY)
January 22, 2004—In this issue:
1. SQL SERVER PERSPECTIVES
- Valuable Information for IT Professionals
2. SQL SERVER NEWS AND VIEWS
- Hotfix Corrects SQL Server 7.0 Access Violation
- Running a Transaction That Affects Many Records Can Stop Your Database
- Results of Previous Instant Poll: Building BI
- New Instant Poll: Deploying Hotfixes
3. ANNOUNCEMENTS
- 5 Years of SQL Server Tips in One Place
- Dig a Little Deeper into SQL Server
4. RESOURCES
- What's New in SQL Server Magazine: Take Control of Joins
- Hot Thread: Unexplained Backup Error
- Tip: Moving the Log Filer
5. NEW AND IMPROVED
- Find Duplicate Data
- Improve Developer Communication
6. CONTACT US
- See this section for a list of ways to contact us.
Sponsor: Free White Paper on Data Recovery Best Practices
Planning for data recovery is about more than just making sure your database is backed up. There are many things to consider. In his paper, Stephen Wynkoop, MVP and Founder SSWUG, discusses the best approaches to a solid data recovery solution. He outlines key planning points and investigates how select tools can help you accomplish a plan for success. Download it today, compliments of Lumigent Technologies:
http://lists.sqlmag.com/cgi-bin3/DM/y/eeMB0FgQMn0BRZ0BEx20At
1. SQL SERVER PERSPECTIVES
(contributed by Brian Moran, news editor, [email protected])
IT professionals are inundated with information that isn't useful for getting their jobs done. Most vendors, including Microsoft, produce in-depth technical information about how things work but fail to produce material that ties all the pieces together. That's why best practices documents that provide the big picture view are so valuable. This month, Microsoft published the Microsoft SQL Server 2000 Analysis Services Operations Guide, available at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/anservog.asp. If you're responsible for building and maintaining an Analysis Services environment, you should read this document. This Analysis Services operations guide is similar to the SQL Server 2000 operations guide that Microsoft published two years ago. The Analysis Services guide includes practical, field-tested advice about how to run and maintain your analysis servers and covers topics ranging from configuration management to security to availability--and more. The Analysis Services operations guide complements the Microsoft SQL Server 2000 Analysis Services Performance Guide that Microsoft published last June. The performance guide deals with topics ranging from data modeling to server design to MDX optimizations. It's available at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ansvcspg.asp. Both novice and experienced Analysis Services developers and administrators can build or refine their skills with the help of these guides.
Regular readers of my weekly SQL Server Magazine UDPATE commentary should be familiar with my complaints about IT professionals "drowning in a sea of information." I know the importance of macro-level information that explains the best way to make all the puzzle pieces fit together, so I enjoy telling my readers about valuable best practices resources. Learn and enjoy!
Need Access to Helpful SQL Server Experts?
Subscribe to SQL Server Magazine and gain full access to valuable SQL Server and Developer-focused expertise. Our content features articles by such experts as Brian Moran, Itzik Ben-Gan, and Kimberly L. Tripp. Whether you're looking for novice or advanced-level information, our experts will help you do your job better. Subscribe today and receive 12 print issues, along with full access to the entire article archive and active forums on the Web during your subscription term. Click here:
http://lists.sqlmag.com/cgi-bin3/DM/y/eeMB0FgQMn0BRZ0BEph0Af
2. SQL SERVER NEWS AND VIEWS
When you transfer a lot of data from a universal database to a computer that's running SQL Server 7.0 with Service Pack 4 (SP4), an access violation might occur. The Microsoft article "FIX: An access violation may occur when you transfer a lot of data to SQL Server 7.0" explains that when the access violation occurs, the computer that's running SQL Server might stop responding. The article gives examples of stack information and error log information that you can use to determine whether you're experiencing this access violation. You can read the entire article and download the hotfix at
http://support.microsoft.com/?kbid=819773
When you try to run a large transaction that affects many records simultaneously, the SQL Server 2000 process that originated the transaction might stop responding. The database might also stop responding until you restart the SQL Server computer. The Microsoft article "FIX: A SQL Server database may stop responding when you run a transaction that affects many records" describes the problem in detail and provides examples of call stacks to help you diagnose the problem. To read the article and download the hotfix, visit the Microsoft TechNet site at
http://support.microsoft.com/?kbid=828017
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you build or maintain business intelligence (BI) applications?" Here are the results (+/- 1 percent) from the 211 votes (deviations from 100 percent are due to a rounding error):
- 17% Yes, I build them
- 3% Yes, I manage them
- 41% Yes, I build and manage them
- 13% No, but my company uses BI applications
- 26% No, my company doesn't use BI applications
The next Instant Poll question is "How do you handle hotfix deployment?" Go to the SQL Server Magazine Web site and vote for 1) We use Automatic Updates, 2) We roll out fixes the day they come out, 3) We roll out fixes within 1 week, 4) We roll out fixes within 1 month, or 5) We roll out fixes more than 1 month after they come out.
http://www.sqlmag.com
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
Subscribe to the SQL Server Magazine Master CD and get high-speed, portable access to all articles, code, tips, tricks, and expertise ever published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue and retrieve desired information quickly. Subscribe today and start saving time. Click here:
http://lists.sqlmag.com/cgi-bin3/DM/y/eeMB0FgQMn0BRZ0BEkN0AA
Discover SQL Server solutions. Delve into real-world success stories. Drill down into building highly available database servers. Go to SQL Server Magazine's Special Reports and dig a little deeper.
http://lists.sqlmag.com/cgi-bin3/DM/y/eeMB0FgQMn0BRZ0BEkP0AC
4. RESOURCES
Sometimes, the table order you specify doesn't affect the order in which SQL Server processes joins or the query result. In his January T-SQL Black Belt column, "Take Control of Joins," Itzik Ben-Gan shows you two techniques you can use to control the order while keeping your code simple. Read this article today at
http://www.sqlmag.com/articles/index.cfm?articleid=40621
Every night, Hansje backs up about a dozen databases by using a backup device that resides on a mapped drive on a remote server. The backup works for most of Hansje's databases, but one database, which contains two MDF files, returns the following error:
Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
Hansje can't find information about this error in SQL Server Books Online (BOL), but his error log shows the following message:
BackupDiskFile::RequestDurableMedia: failure on backup device 'M:\airlogBU.BAK'. Operating system error 64(The specified network name is no longer available.).
The error happens at the end of the backup process and occurs every time Hansje runs the backup, regardless of whether the backup is manual or scheduled. Because the database in question is quite large, Hansje hasn't tested the backup to see whether it's OK. Moving the backup file to another location is difficult. What problem is causing the error? Offer your advice and see what other people have said on SQL Server Magazine's Administration forum at
http://www.sqlmag.com/forums/messageview.cfm?catid=3&threadid=22342
Q. My database has data and log files on the same physical disk array. I'd like to put the log file on a separate RAID array. What's the quickest way to move the log file?
A. Using sp_detach_db is the quickest and easiest way to move the log file. You can remove a log file from a database by using DBCC SHRINKFILE and ALTER DATABASE, but that technique takes longer. With sp_detach_db, you can quickly detach the database from the server, then reattach it by using sp_attach_db, specifying a new location for the physical log file. You should take care when moving files in a production database because you don't want to make a mistake that would cause downtime. You can find examples of how to use sp_detach_db in SQL Server Books Online (BOL).
Send your technical questions to Brian Moran at [email protected].
5. NEW AND IMPROVED
(contributed by Dawn Cyr, [email protected])
Azlexica announced the Find Duplicates Wizard for SQL Server, software that lets you perform a multi-key duplicate analysis on your SQL Server 2000 or 7.0 database, then export the results to your spreadsheet software for further review. Because the Find Duplicates Wizard performs its processing directly on the server, you don't have to send data across the network for analysis. You can define as many as 256 combinations of database columns for the software to use to identify duplicates. The software then displays duplicates in tagged groups so that you can easily see which rows are duplicates. The software costs $397. For more information, contact Azlexica at [email protected] or
http://lists.sqlmag.com/cgi-bin3/DM/y/eeMB0FgQMn0BRZ0BEx30Au
Original Software announced TestPLAN 1.3, an application that lets development teams test plans, track defects, and communicate better. Testers can post document files, image files (such as screenshots), and data files, so test teams can better understand and recreate bugs and defects. In addition, testers can store test scripts, then launch them to recreate and fix bugs. TestPLAN works with other Original testing products and stores test documents and scripts on several database management systems including SQL Server, Oracle, and DB2/400. For pricing and other information, contact Original Software at 630-268-1488, [email protected] or
http://lists.sqlmag.com/cgi-bin3/DM/y/eeMB0FgQMn0BRZ08Q80A2
6. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
Contact Richard Resnick at [email protected] or 800-949-4007.
SQL Server Magazine UPDATE is brought to you by SQL Server
Magazine, the only magazine completely devoted to helping developers
and DBAs master new and emerging SQL Server technologies and issues.
Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email
