SQL Server Resources - Edgewood Solutions

To access the Edgewood SQL Server Resources, fill in a one-time registration form. Once you've completed the registration form, you'll be able to access all the Edgewood resources all of the time. We're always adding new content, so keep checking in!

Click Here to Register to Access Edgewood's Free SQL Server Resources

Select a topic and submit query to preview some of the Edgewood resources.

Title	Summary
Searching and finding a string value in all columns in a table 6/18/2008	Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column. Is there any way this can be dynamically generated?
SQL Server Disasters with Preventive Measures 6/13/2008	Over the years I am sure we have all seen and heard about various SQL Server disasters either at our organization, on the web, or at conferences\user group meetings. Friday the thirteenth seems like an appropriate time to outline some of the common disasters we have seen over the years and provide some hind sight into the situation to prevent a future disaster. Have you seen disasters in your environment? I would bet you have at some point. Let's see if our top 13 disasters matches your experiences.
SQL Server Backup and Recovery Interview Questions 6/11/2008	If you are preparing for a SQL Server DBA interview as the interviewer or interviewee, today's tip should offer value to you. This tip has interview style questions graded as either easy, moderate or advanced related to backup and recovery. To help gauge your skills, the question is available to read, but the answer is intentionally hidden. Once you have thought about your answer, highlight the answer and see how you have done. Good luck!
Problem using DISTINCT in case insensitive SQL Server databases 5/27/2008	SQL Server gives you the ability to store mixed case data in your databases, but depending on how you create your databases SQL Server will ignore the case when you issue T-SQL commands. One of the problems you may be faced with is that you want to get a distinct list of values from a table to show the differences in your table, but if your database is setup as case insensitive the DISTINCT clause does not show the differences it all gets grouped together. So based on this what options are there?
Scripting out T-SQL commands generated by SQL Server Management Studio 5/23/2008	With SQL Server Management Studio you have the ability to do just about everything you can do using T-SQL commands. One problem with using the GUI is that it is difficult to remember everything you clicked on to reproduce the behavior a second time. Another issue is that most of what you do through the GUI is done immediately which may not always be the best scenario. How can I capture what SQL Server is doing so I can reproduce the behavior or run the commands at a later time.
Interview Questions for New Features in SQL Server 2005 5/14/2008	Staying up on the latest SQL Server technologies is a huge benefit to employers. Many seek only the best and brightest to build solutions for the organization. The general premise is the more knowledgeable their team is the more opportunities the organization will have to succeed. With the release of SQL Server 2005 many new features were introduced, but not taken advantage of across the industry for one reason or another. Break out of your SQL Server 2000 shell and test your knowledge on the SQL Server 2005 new feature set.
Selecting the database recovery model to ensure proper backups 5/13/2008	One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.
Getting started with SQL Server stored procedures 5/9/2008	I have been using SQL Server for some time, but all of the code that is issued against the database is embedded in the application code. I know that you can create stored procedures, but I am not exactly sure where to start or what I need to do to implement stored procedures.
Full Text Search Noise Words and Thesaurus Configurations 5/5/2008	I have heard that Full Text Search uses a noise words to eliminate meaningless words in searches. I have also heard that a thesaurus is used, but I am not exactly sure how. Can you provide some details related to how both of these technologies are used in Full Text Search? I am interested in more of a background on these specific Full Text Search technologies as well as where the files are located and how I can update them.
Dynamic SSIS workflow control based on variables 4/29/2008	When developing SQL Server Integration Services (SSIS) packages there is sometimes the need to only run certain steps or paths in the package execution either based on time period or maybe a parameter value that is passed to the package or queried from the database. How do you setup an SSIS package to have different execution paths based on a parameter value?
SQL Server Health and History Tool 4/21/2008	I have heard about the SQL Server Health and History tool, but I have never used it. How long has this tool been around for? In your opinion is this a tool worth using? What sorts of functionality does the tool have to offer? What types of limitations does the tool have? Is this tool primarily intended for DBAs, Developers or Network Administrators? Where can I download a copy of the tool and what do I need to do to install it?
Identify SQL Server 2005 Standard Login Settings 4/16/2008	Gaining insight into my SQL Server standard and Windows logins has historically been a challenge in terms of determining password changes, failed login attempts, etc. I have noticed that you have experienced the same issue with your tip entitled 'When was the last time the SQL Server sa password changed' and I have noticed some information in the forums on the topic as well. With SQL Server 2005 can I gain any more insight into the SQL Server standard logins part of the equation? I know I can talk to my Network Admin counter parts for some of the Windows related login information. Any and all recommendations would be appreciated.
Methods to determine the status of a database 4/15/2008	I think that just about all aspects of the SQL Server Management Studio can be exposed by using T-SQL instead of having to use the GUI. I have been trying to write some routines to determine whether a database is online as well as to check some other information about the status of a database. What options are there to get status information for a database without using the GUI?
Reading the SQL Server log files using T-SQL 4/14/2008	One of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript. Are there any other easy ways to search and find errors in the error log files?
SQL Server DBA Phone Interview Questions 4/9/2008	The phone interview. It has tales of bringing normally rationale people to a terrified state. I have even heard of a DBA that was so worried about a SQL Server phone interview that 'they just happened to be in the office park' where the company was located and actually wanted the interview face to face. The reality is, just about all organizations that I work with have a phone interview as a right of first passage in the process. The employer wants to quickly determine if the DBA candidate could be qualified for the position from a technical perspective and if they will fit into the team. As a DBA, what sorts of things should you be on the lookout for during a phone interview? What do you think the employer is expecting? Is this the technical interview or not? Should you try to avoid the phone interview all together and just 'pop-in' for a face to face interview?
Designing Tables for Audit Data in SQL Server 4/2/2008	My company is just starting to look at adding functionality to retain historical data for key tables and columns for auditing purposes in many of our SQL Server databases. I have seen some of your recent tips related to triggers (Forcing Trigger Firing Order in SQL Server and Trigger Alternatives in SQL Server - OUTPUT Clause). Based on using triggers or a similar technology, what is the best way to store the historical data? What options are available to store the data? Are there really a variety of options? Should the historical data be in the same database as the original data? Does it really make a difference?
Simple approach to populate a column with a sequential number not using an identity column 4/1/2008	I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column I also need to populate the existing records with an incremental counter what options are there to do this?
Issues with running backup log with no_log or truncate_only 3/27/2008	On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes any issues, but I know that I am able to free up space in my transaction log and shrink the file. Is this the correct way to handle this situation?
Why Upgrade to SQL Server 2008 3/25/2008	With the recent release of SQL Server 2008, getting up to speed on the technology is necessary from a number of different perspectives. From your view of the world what are the drivers from a business, technology and career perspective? Have you heard insight or opinions related to employers view of SQL Server 2008? Do you have any suggestions when talking to management about moving to SQL Server 2008?
Analyze and Correct a large SQL Server MSDB Database 3/24/2008	It has come to my attention that some of our MSDB databases are getting what I would consider large for a system database. Some of our MSDB databases are over 2 GB which is a little perplexing because I know we do not create any user defined objects in that database. Can you give me some insight into the issue? I know we have this issue with both SQL Server 2000 and 2005 instances.
Free Performance Profiler Tool for SQL Server 2005 Express 3/20/2008	With the release of SQL Server 2005 Express, Microsoft has offered a lot of new features that did not exist with MSDE. A couple of these things include Reporting Services features as well as a GUI management tool to manage SQL Server instead of having to do everything via command line. Although they have added a lot of new features, not all of the tools needed to maintain and troubleshoot SQL Server are included. One of these tools that is not included is Profiler. You can still run a server side trace on your SQL Server Express databases as well as import the data into a table for analysis, but sometimes having a GUI tool to troubleshoot an issue is much simpler than using a server side trace. So what are the options?
How to stop logging all successful backups in your SQL Server error logs 3/18/2008	The SQL Server error log is a great place to find information about what is occurring on your database server. One helpful thing that each log offers is that it shows you all of the failures and/or errors that have occurred since the last start of SQL Server or since the last time you cycled the error log. On the flip side of that, it also shows you success messages such as backups, logins, DBCCs, etc... Although this may be helpful to log all of this information it unfortunately clutters up your error logs pretty quickly especially if you are doing a lot of database backups every day. So how can you turn off all of those successful backup messages.
SQL Server Database Requirements 3/17/2008	In our organization, I have noticed that database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps. For some developers that process seems to yield a decent product, but not always. I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases. For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.
SQL Server T-SQL Interview Questions 3/7/2008	If you are preparing for a SQL Server Developer or DBA interview as the interviewer or interviewee, today's tip should offer value to you. This tip has interview style questions graded as either easy, moderate or advanced related to T-SQL, the relational engine programming language. To help gauge your skills, the question is available to read, but the answer is intentionally hidden. Once you have thought about your answer, highlight the answer and see how you have done. Good luck!
How to join tables where columns include NULL values 3/4/2008	When building database tables you are faced with the decision of whether to allow NULL values or to not allow NULL values in your columns. By default SQL Server sets the column value to allow NULL values when creating new tables, unless other options are set. This is not necessarily a bad thing, but dealing with NULL values especially when joining tables can become a challenge. Let's take a look at this issue and how this can be resolved.
Script to determine permissions in SQL Server 2005 2/22/2008	At times I run into application issues when I am unable to perform particular functions in the application that some of my peers are able to complete. I need to be able to quickly and easily understand my permissions in SQL Server. Do you know of an easy way to do so? Do you know how I can compare 2 different users to see what the difference could be at an instance, database or object level? Any and all suggestions would be appreciated.
SQL Server Reporting with Microsoft Excel 2/21/2008	Our business users are very savvy with Microsoft Excel. The issue we struggle with is getting the SQL Server data to them in a streamlined manner. We are familiar with some of your earlier tips related to integrating Microsoft Excel and SQL Server. We have gone down the paths and they have been helpful for a number of standardized reports. Now that the users can see the data, we are getting a number of requests to modify reports on a daily basis. Unfortunately, we do not have the staff to support all of the reporting needs. Do you have any suggestions to conduct SQL Server reporting directly with Microsoft Excel from a user perspective?
Upgrading databases and changing database compatibility levels 2/18/2008	When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server. From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed. This tip will show you how to check the current compatibly level, how to change the compatibly level and also some of the differences between earlier versions and SQL Server 2005.
Tempdb Configuration Best Practices in SQL Server 2/12/2008	In SQL Server 2005, TempDB has taken on some additional responsibilities. As such, some of the best practice have changed and so has the necessity to follow these best practices on a more wide scale basis. In many cases TempDB has been left to default configurations in many of our SQL Server 2000 installations. Unfortunately, these configurations are not necessarily ideal in many environments. With some of the shifts in responsibilities in SQL Server 2005 from the user defined databases to TempDB, what steps should be taken to ensure the SQL Server TempDB database is properly configured?
SQL Server Performance Tuning Interview Questions 2/7/2008	In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA or Developer interview to assess the candidates skills related to SQL Server performance tuning. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!
Import, Export, Copy and Delete Integration Services Packages in SQL Server 2005 2/6/2008	I have seen many of the changes with SQL Server 2005 Integration Services (SSIS) versus SQL Server 2000 Data Transformation Services (DTS). Integration Services certainly has much more functionality out of the box than DTS and I am learning SSIS as my projects move forward. One item that has seemed to be a thorn in my side is deploying an SSIS package. So, I have read your tip (SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package) related to deploying a package and wanted to find out if any other options are available? Can you shed some light on the situation?
Creating delays in SQL Server processes to mimic user input or delay process steps 1/30/2008	Sometimes when running processes there may be the need to create delays before the next step runs. This could be for processes that are run externally and therefore you have no control over when that process finishes, the need to mimic a delay in user response if you are doing testing or maybe you are collecting data at various intervals and want to delay the next collection time. These are just a few examples of the need to create a delay, so what approaches exist?
SQL Server System Databases 1/24/2008	I have seen the Master, Model, MSDB, etc. databases in Management Studio and in a variety of scripts. Right now I have a fairly rudimentary set of knowledge about these databases just based on reviewing those scripts. As I expand my SQL Server knowledge, I am interested in learning more about the SQL Server system databases and how I can use them to help me on a daily basis. I am sure their are many recommendations when working with these databases and I am interested in those as well.
SQL Server 2008 First Impressions 1/23/2008	At our organization we have had limited exposure to SQL Server 2005 because SQL Server 2000 just works. We have worked with SQL Server 2005 in some particular areas, but we do not have the enterprise experience we need to move forward. We know support will be lifted for SQL Server 2000 and know we need to jump on SQL Server 2005 or 2008 in short order. How can I get some first hand experience and what sorts of things should I try to do?
Gathering I/O statistics down to the database file level 1/21/2008	When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible. SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database?
Determining SET Options for Current Session 1/18/2008	With each session that is made to SQL Server the user can configure the options that are SET for that session and therefore affect the outcome of how queries are executed and the underlying behavior of SQL Server. Some of these options can be made via the GUI, while others need to be made by using the SET command. When using the GUI it is easy to see which options are on and which options are off, but how can you get a better handle on all the options that are currently set for the current session?
Access to SQL Server 2008 Virtual Environment 1/16/2008	Back in June 2007, we announced that SQL Server 2008 is on its way in this tip SQL Server 2008 has arrived. In addition, we talked about how to learn more about SQL Server 2008 in this tip Learning SQL Server 2008. The problem with learning something new is that it often takes a lot of time to setup an environment before you can actually do anything. With server virtualization this is much easier to setup and take down servers at will, but this still requires effort to get the virtual server setup in the first place. Are there any easier solutions for getting your hands on SQL Server 2008?
SQL Server 2005 Exposed - Static Port Assignments 1/15/2008	We have noticed some connection issues on a variety of our web based applications when the corresponding SQL Server is rebooted. We have noticed the issue with SQL Server 2005 Express edition as well as the SQL Server 2005 Standard edition. It seems like after the reboot we have to change our application connection strings and firewall settings in some circumstances in order to correct the situation. We know in some of the circumstances the applications are in a transient state, but others have been deployed and unchanged for a while. When we have to reboot servers, the whole team is on guard to test the applications and ready to troubleshoot the issues. We having found that in some circumstances SQL Server 2005 is listening on different ports. Should we write this issue off to gremlins or is there a real solution for our problem?
Verbose SQL Agent Logging 1/14/2008	How often have you reviewed a SQL Server Agent Job’s history to determine why a job failed only to be confronted with a minimal level of detail? When a job failure occurs it is imperative to be able to quickly identify the root cause in order to determine the correct remediation. As such, what are some of the options to consolidate the logging on a per job basis? How can I setup this functionality for my key SQL Server Agent Jobs?
Interview Questions - SQL Server System Databases 1/11/2008	In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA interview to assess the candidates skills related to SQL Server system databases. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!
Forcing Trigger Firing Order 1/10/2008	I have two triggers defined on my table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. How can I make sure that they fire in the correct order to enforce my business logic? In addition, as our system changes, what are some of the caveats that I need to be aware of when managing the trigger firing order?
Getting exclusive access to restore databases 1/8/2008	A task that you may be faced with as a DBA is the need to refresh a test or development database on a periodic basis. This may be a scheduled process or it may be an ad hoc process. One of the things that you need to do when restoring a database is to ensure you have exclusive access to the database otherwise the restore process will not work. What options are there to ensure you have exclusive database access, so the restore process will work without issue?
Switching data in and out of a SQL Server 2005 data partition 1/7/2008	One of the challenges of working with large datasets or datasets that become stale is the need to move large chunks of data in and out of your tables. This can be done with large INSERT and DELETE statements or by using views, but with SQL Server 2005 data partitioning makes this task much easier to manage than in previous versions of SQL Server. In this tip we will take a look at how to use the SWITCH operator to move data in and out of partitions.
Adding or dropping identity property for an existing column 1/4/2008	One nice feature of SQL Server that is used quite a bit is the use of identity columns. This function gives you a simple way of creating a unique value for every row in your table. Adding a new column and making it an identity column is an easy thing to do as well as dropping an existing column that is an identity column, but how can you modify an existing column to make it an identity column or remove the identity property from an existing column?
10 Step Checklist: Re-Architecting a SQL Server Backend System 12/28/2007	All systems generally follow the same basic life cycle from inception, development, maintenance and sun-setting. However, some systems seem to take a slightly different path where they are re-architected either in entirety or particular modules of the application are significantly re-architected. Re-architecting a production backend system that needs to continue business operations presents a significant challenge. Although the positive side of the situation is that the problems that system suffers from have already surfaced and the business processes have been tested to determine if they are beneficial or not. Now it is a matter of rolling up your sleeves re-architecting. So where do we start?
Rollback: Identifying and Deleting Archive Tables 12/27/2007	In an recent tip (Backing up SQL Server Data for Rollback Purposes), the second option in the tip outlined steps to backup data prior to making a mass data changes. This is a critical process in order to rollback if an issue arises. Unfortunately, one missing component in that tip is identifying and deleting unneeded archive tables. Depending on the deployment schedule, the amount of archived data can quickly add up, which expands backup\restore windows, increases tape\storage costs and may lead to a situation where unneeded data lingers for months or years. Although I want to have a solid rollback plan, I also need to be able identify and delete the archive tables once they are 30 days old. Any suggestions on how to do so?
SQL Server Database Backup Retention Periods 12/26/2007	As a best practice we issue full SQL Server database, differential and transaction log backups. We have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. The tapes are rotated on a weekly basis in order to maintain 4 weeks of data on the tapes stored off site. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
Backing up SQL Server Data for Rollback Purposes 12/20/2007	Many of our SQL Server releases include data changes, not just code changes. In some respects the SQL Server data changes are more of an issue to manage than the code changes, because we can easily isolate the code changes and roll them back as needed. With the data changes if we add a simple lookup value to a table and update particular records we can do some detective work and trace back the data. It takes time, but we can typically trace it back. Unfortunately, when we change data by a percentage or make numerous changes in a table, those changes are a bit more difficult to trace back because rolling back a percentage is not as precise as we require. Thus far our SQL Server rollback plan has been to just restore a preliminary SQL Server database backup that was issued, but this is an time consuming proposition if we have only one small issue. Can you offer a better approach to isolate the SQL Server data changes and only rollback specific data?
Limiting amount of data returned with TEXTSIZE 12/19/2007	When working with large-value data types such as varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data types sometimes you do not need to return the complete column contents, but maybe only a portion of the data. This may be for testing or maybe just to check to see if there is a value or not. This can be done by using the SUBSTRING function or the LEFT function, for each column that uses this data type, but are there any other functions that can be used to limit the amount of data returned by a SELECT statement for all columns of these data types?
Disabling (or Enabling) SQL Server Agent Jobs 12/18/2007	As a portion of our backup and recovery process I need to disabled (or enable) all of our SQL Server Agent Jobs depending on the situation and the SQL Server instance. I know I can use the GUI to click through and make the changes, but on a number of our SQL Server instances we have 50+ jobs, so this is a monotonous task. Do you have any scripts to enable or disable all SQL Server Jobs? What about enabling or disabling all SQL Server Jobs by Job name or Job Category name?
Passing dynamic parameter values to an SSIS package 12/17/2007	When using SQL Server Integration Services (SSIS) the ideal situation is to make the code as re-useable as possible, so the same code-set can be used to handle multiple situations instead of having a hard-coded solution. In a previous tip, "Dynamic Flat File Connections in SQL Server Integration Services" we looked at how to create a dynamic file source based on some variable settings within the SSIS package. This solution was great, but how do I take this further and pass in a dynamic value into an SSIS package?
How to store longer SQL Agent Job Step output messages 12/14/2007	When working with SQL Agent jobs sometimes it is difficult to determine why a job failed or even exactly what occurred. For each job step SQL Server provides a message, but it is not always that easy to determine exactly what was occurring during that step. The default message that is saved in the job history is 1024 characters. In most cases this may be enough, but if you have a long running process you may need to store more than 1024 characters. Is there any way to get additional information within the job history to help troubleshoot issues as well as just knowing what occurred?
Importing Excel data using SSIS and dealing with unicode and non-unicode data issues 12/13/2007	One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet. We have talked about different approaches to doing this in previous tips using OPENROWSET, OPENQUERY, Link Servers, etc... These options are great, but they may not necessarily give you as much control as you may need during the import process. Another approach to doing this is using SQL Server Integration Services (SSIS). With SSIS you can import different types of data as well as apply other logic during the importing process. One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode. SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space. Is there any simple way to do this in SSIS?
T-SQL Scripts: Calculating Table Width 12/12/2007	I am concerned our database design is overly denormalized. I believe we have some very wide tables which makes some of our coding very easy, but I am concerned about the data access and overall performance. Before I go too far down the path of just changing some of these tables or trying to change the ways of my team for future database design, how can I find out how wide some of these tables really are based on the data as compared to the theoretical maximum size? Can you provide a script or two that I can run on my SQL Server databases? Can you also provide any resources for practical database design? I know having some denormalization is reasonable, I am just not sure if some of the database design has gone to an extreme or not.
Interview Questions - SQL Server Agent 12/11/2007	In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA interview to assess the candidates skills related to SQL Server Agent. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!
SSIS: Perfmon Counters for the Data Flow Engine 12/10/2007	We have adopted SQL Server 2005 Integration Services and we are moving a great deal of data on a consistent basis throughout the day for a number of systems. Unfortunately, we have been seeing some memory related issues and wanted to find out how we could monitor these on a regular basis? We want someway to collect performance related data and monitor the overall process. What tools and metrics are available for monitoring an SSIS Package? Can you provide some examples?
Solid State Disk Drive Considerations for SQL Server 12/7/2007	Are solid state disk drives really the next big thing? Will they be able to expand the IO needs of an application further than the current set of disk drive technologies? Is this a technology I should consider with my future SQL Server platforms? The reason that I ask is because our SQL Server based applications are experiencing IO issues. We have split our databases into numerous file groups on numerous disk drives and have had some relief. At the same time we have been focusing on tuning our code and have made strides in that area. We just cannot tune the code quick enough to meet the demands. Are solid state disk something we should consider evaluating and migrating to in the near term to help with our performance issues?
Properly Sizing the TempDB Database 12/6/2007	Properly sizing the TempDB database has recently surfaced with some new customers and has also been a thread (http://blogs.mssqltips.com/forums/t/64.aspx) posted in the MSSQLTips forums. Here is the general scenario: On all of my SQL Server instances (development, test and production) the TempDB database is configured for auto growth for the database and transaction log. During a recent performance tuning engagement I learned a lot about how TempDB is being used in my environment. It was brought to my attention how much the TempDB database is being used by our applications. I had no idea our core business applications are using the TempDB database so heavily. In addition, I did not know how large the TempDB database and transaction log have grown. As such, I know I cannot change the applications in short order so I need to get a handle on how to resolve these issues and size the TempDB database correct. How should I do so?
Joining data and differences of using UNION and UNION ALL 12/5/2007	Sometimes there is a need to combine data from multiple tables or views into one comprehensive dataset. This may be for like tables within the same database or maybe there is a need to combine like data across databases or even across servers. I have read about the UNION and UNION ALL commands, but how do these work and how do they differ?
Expanding advanced search capabilities with the FREETEXT command or not? 11/29/2007	I have been reading about the Full Text Search capabilities in SQL Server 2005 and your tips have been a big help to improve how we query our data. Thus far converting our core queries to leverage Full Text Search has been a big help. I have been seeing information about the FREETEXT command and I would like to learn how to use this FREETEXT command versus the CONTAINS command. Should I use the FREETEXT command over the CONTAINS command or vice versa? Can you also provide some examples of using the FREETEXT command to learn about the variety of capabilities available?
A Trigger Alternative = OUTPUT Clause 11/28/2007	I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table basis to audit the data, but I do not want to audit all of the INSERT, UPDATE or DELETE statements that run from a variety of applications (fat clients, web clients, automated processes, monthly batch processes, etc). How can I audit only the specific processes versus all transactions that run? Are any elegant options available directly with the INSERT, UPDATE or DELETE statements?
Please do not drop the triggers 11/27/2007	We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time. To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables. We did this to prevent the large number of inserts into the audit tables from extending the down time even further. Not having the trigger fire for this process was something we were willing to sacrifice. Unfortunately, once we released the system to production we no longer had the triggers and had to conduct a manual review to ensure we had all of our triggers. Missing the data once we went to production was a major issue on top of having to extend the down time. Do you have any suggestions on how to better manage the triggers in this scenario?
Warning: When problems strike 11/26/2007	Blunders, mistakes, absent mindedness, being over extended - you name, it happens. Whether it happens to you or someone on your team, the repercussions can be severe. Over most people's careers you have seen issues to a varying degree of severity. To me the biggest blunder is knowing that a problem exists and either ignoring it or procrastinating on the implementing the resolution. Do not be upset when the problem strikes and you have to drop everything you are doing to fix an issue that could have been prevented. This tip focuses on SQL Server problems that could have been prevented. See if you can relate to any of these items.
Getting Started with Reporting Services 11/21/2007	SQL Server has a lot of great features and taking the time to find out what these features are and secondly taking the time to understand these new features is often time consuming and usually only done if there is a real pressing problem. I still run into these challenges and it amazes me that there is always some way to solve the problem with SQL Server. I think one of the greatest new add-ons to SQL Server is Reporting Services, but it still seems like a lot of people have not yet adopted Reporting Services. Microsoft even included Reporting Services features with SQL Server 2005 Express, so this is definitely something you should take the time to investigate. The big question though is how to get started.
Making data imports into SQL Server as fast as possible 11/20/2007	When bulk loading data into SQL Server, in most cases you want to have this process run as fast as possible. Depending on the options you select and how indexes are setup on the table, the time to load the data could differ quite drastically. In this tip we will take a look at different options for bulk loading along with examples and the time it takes for each of these operations to complete.
Script to create commands to disable, enable and drop / recreate Foreign Key constraints 11/19/2007	Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?
Dynamic stored procedure execution form 11/16/2007	The purpose for most stored procedures is for execution within applications, but there are some stored procedures that may be used for administrative purposes and only get executed ad hoc. In addition, during testing you run stored procedures interactively to make sure things are working correctly. You have the ability to run any stored procedure directly from a query window and include the necessary parameters, but is there any easier way to know what parameters a stored procedure requires and to pass the parameters directly to a stored procedure?
CLR Function - Delete older backup and log files 11/15/2007	In a previous tip we looked at how to put together a CLR function for sorting text data. In addition, we have also written tips about how to mimic the functionality of maintenance plans without having to use a maintenance plan. In one of these previous tips, "Maintenance task to delete old backup files" we outlined how to delete older backup files by using a VB Script. To take this deleting of older files a step further, this tip will look at this same task to remove older backup and log files, but this time using a CLR function.
SQL Server Crosswalk - Database Management Commands 11/12/2007	It is common in many SQL Server database management scripts to rename databases, detach and attach databases, change the database status, etc. With all of the changes related to deprecated SQL Server features, are any of these commands (sp_attach_db, sp_renamedb, sp_dboption, etc.) marked for replacement in SQL Server 2008? If so, what are the corresponding commands that I need to start using in order to not re-write my code in the future? What is the new coding standard?
Identifying Deprecated SQL Server Code with Profiler 11/9/2007	In your recent tip (WARNING: SQL Server Deprecated Features) you have outlined a number of deprecated features. The listing with the mapping to the new code set is beneficial. Unfortunately I have an enterprise SQL Server environment and I need to be able to identify the deprecated features in more of an automated manner. We do not have the time to check all of our code (stored procedures, functions, etc.) and I know we have T-SQL embedded in middle tier and front end applications. How can I analyze this code in an efficient manner?
SQL Server script to rebuild all indexes for all tables and all databases 11/6/2007	One of the main functions of a DBA is to maintain database indexes. There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server 2000 and SQL Server 2005. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. What other options are there to rebuild indexes on all databases besides using a maintenance plan?
Maximizing your view into SQL Query Plans 11/5/2007	One nice feature of SQL Server is being able to display graphical query plans. The problem with graphical query plans though is that they are sometimes extremely large and often difficult to maneuver through the plan to identity where the issues are within your statement or statements. Within SQL Server Management Studio you have the ability to zoom in and zoom as well as the ability to find certain parts of the query plan by hovering over a reduced version of the query plan, but often you are constrained by the size of the results pane to view the plan and it is still quite difficult to find the information you are looking for. Are there any other ways of making navigation of a query plan easier?
Using Solutions and Projects to manage your SQL Server code 11/1/2007	When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc... Keeping all of these components straight as well as logically grouped together is sometimes a challenge in itself. Are there are any built-in tools that allow you better manage project components vs. one big file?
SQL Server 2000 to 2005 Crosswalk - Code Identification 10/31/2007	When troubleshooting a potential SQL Server performance problem, it is difficult to know if the code is problematic without being able to review all of the code. You could ask the Developer for the code you suspect, run Profiler to capture code, leverage a third party tool for the data collection or try to leverage the native T-SQL commands to review the code. Historically tracking down the code was available with sp_who2, DBCC INPUTBUFFER and fn_get_sql. With the introduction of SQL Server 2005, is a simpler means with more bells and whistles available with the dynamic management views and functions?
Clearing Cache for Valid Performance Testing 10/26/2007	When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?
SQL Server 2000 to 2005 Crosswalk - Locking Resources 10/25/2007	A common problem in many environments is locking and blocking. Locking and blocking can cause performance problems and a poor user experience. If this problem worsens, it can be escalated to deadlocking. At a high level, these two problems are due to multiple processes trying to access or modify the same data and SQL Server's lock manager ensuring data integrity. This problem can be corrected by a number of techniques which may include database design, consistent data access in stored procedures, shortening the transaction length, issuing dirty reads, etc. What's really necessary is taking the first step to determine where the locking is occurring. With all of the changes from SQL Server 2000 to 2005, what is the best way to find out what locks are being issued and by whom in SQL Server 2005?
Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint 10/18/2007	SQL Server doesn't always select the best execution plan for your queries and thankfully there are several different hints that can be used to force SQL Server into using one execution plan over another. One issue that you may be faced with is when using parameters in your WHERE clause, sometimes the query runs great and other times it runs really slow. I recently had a situation where the hard coded values in the WHERE clause worked great, but when I changed the values to parameters and used the exact same values for the parameters the execution plan drastically changed and the overall time it took to run the query increased by about 5 times. This situation is referred to as parameter sniffing where SQL Server stores the values used as part of the execution plan and therefore other queries with different values may act totally different. So what options are there to get around this.
Execute Complex Search Queries with Ranked Results 10/17/2007	Just like every other company, we need to have our queries run as fast as possible for our users to have the best experience possible with our application. One of the core components of our application is to search across a number of columns in a table. We have been trying to accomplish this task with a variety of T-SQL options and nothing has been very quick. To add fuel to the fire, we need to rank the data so that the most relevant data is sorted from top to bottom. I have seen some of your recent tips related to Full Text Search. Can this technology help me achieve high performance searching while ranking the data?
WARNING: SQL Server Deprecated Features 10/16/2007	Should I really be concerned about the deprecated features\commands that I use in SQL Server? In earlier versions, I have heard that commands will no longer be available and I have seen references in documentation. Thus far my scripts always seem to work with the new versions of SQL Server. Recently, I have heard rumors that Microsoft is no longer going to support the particular features once they are designated as deprecated features. So is Microsoft really going to deprecate the features and am I really going to have modify all of my scripts?
Updates for SQL Server 2005 Books Online 10/15/2007	In a recent tip (Applying SQL Server Service Packs) you have talked about updates to the server side components of SQL Server as well as the client side components. One item that I have had an issue with is related to updates for SQL Server 2005 Books Online. Can you please confirm that this portion of the product is updated or not as a portion of those processes? I am concerned that I have outdated Books Online and do not even know it.
Project Management for DBAs and Developers 10/12/2007	When most DBAs and Developers hear the term 'project management', I am sure this is not a term that makes them jump for joy. Those emotions are probably from past experiences where they have had issues. To me "past performance does not always dictate future results" when it comes to a SQL Server DBA\Developer who wants to make a difference in their organization. DBAs\Developers are in a unique position in the IT department and overall organization because in many circumstances they work with Network Admins, SAN Admins, Reporting Groups, users, etc, which is not always the case with other groups in the organization. As a DBA\Developer you can consider yourself the hub that connects many spokes in the organization. As such, as DBAs\Developers we have the ability work from the bottom up to make changes which can have a ripple affect across the organization. You make ask how is that possible, I am totally overwhelmed and I need to keep the boat afloat, but I want to be working on the latest and greatest technologies.
Determining Free Space Per Database 10/11/2007	On all of my databases, I cap the growth of the data and log files. I have not done my due diligence and performed capacity planning (Capacity Planning for SQL Server 2000 Database Storage), so I know I need to take those steps. I have been recently caught with a full database and I had to rush to expand it to keep the application up and running. Independent of the capacity planning process, I want to be able to monitor the database size and free space. Do you have a script that I can run to perform the checks on my SQL Server 2005 databases?
Automate SQL Server Testing with Profiler, SQLCMD and SQL Server Integration Services 10/8/2007	One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set. In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.
Finding Text within Text with the LIKE Operator / Pros and Cons 10/8/2007	One problem that you may be faced with is the need to find text data that is contained in a larger set of text. There are two ways this can be done either using the LIKE operator or by using Full Text indexing. Let's take a look at some of the options of using the LIKE operator and some pros and cons of this approach.
Using Views to Simplify Data Access 10/5/2007	One challenge that just about everyone is faced with is ever changing database schemas. From the onset of a project the database schema might be perfect on day one, but as the application evolves and the business needs change database table structures have to change. In addition, as database become more and more complex there is often the challenge of having to join several tables together on an ongoing basis which is time consuming and also creates the possibility of mistakes. So what other options are there to ensure your applications do not break when your database schema changes and what is an easier way to handle multi-join queries that are used over and over again?
CLR Functions - Getting started with a string sort function 10/4/2007	With the introduction of SQL Server 2005, Microsoft released the Common Language Runtime (CLR) to allow developers and DBAs to take advantage of managed code outside of SQL Server. The original thought when this was announced was this was going to be a bad thing, because people that knew how to develop in a .NET language, but not T-SQL, would adopt this across the board. Over the past couple of years the opposite has happened. T-SQL still continues to be the primary language that is used and I have seen very few implementations where the CLR is being used. In this tip we will take a look at a simple example of where the CLR can come in handy and what a big improvement it can make on certain tasks.
Learning SQL Server 2008 10/3/2007	I have been hearing all of the buzz about SQL Server 2008 from many of your recent tips (SQL Server 2008 - Features, Function and Value) and your web cast (SQL Server 2008 - First Look). I did not get a good jump on learning SQL Server 2005 and we have been slow to migrate to SQL Server 2005 because of this and a few other reasons. I really want to get on the bandwagon and try to adopt SQL Server 2008 earlier and maybe even upgrade some of our legacy applications directly to SQL Server 2008. Do you have any recommendations on going down that path?
Full Text Search Querying Alternatives 10/2/2007	In one of your recent tips, you outlined setting up a SQL Server 2005 Full Text Catalog and some basic queries. Can you outline some of the Full Text Search querying alternatives? I thought your first tip (Making the case for Full Text Search) was beneficial to get started, now I think I want to take the next step with Full Text Search to see if it can functionally meet some application needs that we have been struggling with for some time.
Index Maintenance Checklist 9/27/2007	In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The third item on the list was to maintain indexes. There have been several tips on the MSSQLTips.com website about index maintenance, but we will take a look at a checklist of things to perform to ensure your indexes are not degrading performance on your system.
Finding process percentage complete with dynamic management views 9/26/2007	Some tasks that are run in SQL Sever take a long time to run and it is sometimes difficult to tell whether these tasks are progressing or not. One common way of determining that status is to look at the data returned from sp_who2 or sp_lock to ensure that things are still working and the process is not hung. With SQL Server 2005 several dynamic management views have been added, so let's take a look at some of these and how they can assist.
Making the case for Full Text Search 9/18/2007	I have heard about Full Text Search and I know it has been around for the last few versions of SQL Server. I have never really used it and I have seen some of the other tips related to the technology on your web site, so I assume someone must be using it. Can you provide any use cases for Full Text Search? I think I understand the general premise, but not sure if it is any quicker than just using a LIKE statement or how I would need to change my queries.
Streamlining SQL Server Documentation 9/14/2007	I work for a company that has auditing requirements and with those auditing requirements comes the need to document the environment, specific processes and policies in our organization. I read your Defining and Establishing SQL Server Policies and Procedures tip and found that it was a help with the policies that we needed to have for the audit. Although document is not a very exciting topic, it is something that I need to do for my job and for my organization to meet compliance. If we do not meet compliance, our organization can have some problems, problems that I would like to avoid. Can you provide me with some suggestions on how to document our SQL Server environment? I am having a hard time just getting the information down on paper, so I need some help from the start. Thanks in advance!
Comparing Multiple Datasets with the INTERSECT and EXCEPT operatorsv 9/11/2007	When joining multiple datasets you have always had the ability to use the UNION and UNION ALL operator to allow you to pull a distinct result set (union) or a complete result set (union all). These are very helpful commands when you need to pull data from different tables and show the results as one unified distinct result set. On the opposite side of this it would be helpful to only show a result set where both sets of data match or only where data exists in one of the tables and not the other. This could be done with using different join types, but what other options does SQL Server offer?
Applying SQL Server Service Packs 9/10/2007	As with most applications there are hot fixes, updates and/or service packs that are often released. Some of these updates are automatic while others require you to take action and apply the update. SQL Server is one of those applications that requires you to take action versus having the updates automatically applied. This is a good thing, but it requires the DBA to take some type of action. In addition, applying the service packs to the database engine is pretty much understood, but did you also know that these updates also need to be applied on the clients as well?
Maintenance task to delete old backup files 9/6/2007	In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands. A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.
SSIS: Merging multiple sources with the MERGE JOIN task 9/5/2007	When loading data into SQL Server you have the option of using SQL Server Integration Services to handle more complex loading and data transforms then just doing a straight load such as using BCP. One problem that you may be faced with is that data is given to you in multiple files such as sales and sales orders, but the loading process requires you to join these flat files during the load instead of doing a preload and then later merging the data. What options exist and how can this be done?
T-SQL - Parsing a URL 8/31/2007	In my environment I have a few tables with thousands of rows that have URLs in particular columns. We are going through some changes in the environment and need to be able to parse out the data (base URL, file name, query parameters) from the specific columns then move the parsed data to another database design. Moving the data is no problem. I plan on using SQL Server 2005 Integration Services and I have a good sense of how to use that tool. By the way, all of the URLs are in varchar fields if that makes any difference. Can you provide any recommendations on how to parse that data?
SQL Server Consolidation - Pros and Cons 8/30/2007	In the recent years, there have been a few different trends related to the hardware platform for SQL Server. One trend was to have a dedicated SQL Server for each application. This trend has been countered in some organizations by a major consolidation effort. The consolidation effort in some circumstances consists of consolidating the hardware and storage to a unified set of devices although the same number of logical machines exists. In other circumstances, the consolidation is for the hardware, storage, Windows and SQL Server instances. Both alternatives have advantages and disadvantages, so what is the correct path? What should organizations follow? When considering migrating to SQL Server 2005 or 2008, should a consolidation effort be considered as well if we have a large number of SQL Servers are in scope for the project?
Automating Transaction Log Backups for All Databases 8/28/2007	Maintenance plans are a great thing, but sometimes the end results are not what you expect. The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code. For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups. What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?
Keeping data available in the data cache with PINTABLE 8/27/2007	Have you ever wondered why even after optimizing a database that it still takes considerable time to fetch the results? The problem is whenever a query is fired SQL Server fetches the data from the database by means of bringing the data pages into memory. Depending on the database activity the data you may be using quite frequently may be getting paged in and out of memory which may account for why sometimes it takes longer then other times to fetch the data. This paging is done automatically, but if you feel that a table is being used very frequently then this is where the DBCC PINTABLE command may come in handy.
SSIS - How to strip out double quotes from import file 8/24/2007	When loading data using SQL Server Integration Services (SSIS) I am importing data from a CSV file. Every single one of the columns in the CSV file has double quotes around the data. When using the Data Flow Task to import the data I have double quotes around all of the imported data. How can I import the data and remove the double quotes?
Hard Drive Configurations for SQL Server 8/22/2007	At my company we are looking at purchasing a new SQL Server and I am not sure how many disk drives we should purchase. We do not have an unlimited budget because we are a small company, but I am interested in how we should configured disk drives for SQL Server if we bought 1, 2, 4, etc disk drives. I also am interested in the rationale behind the decisions. Can you shed any light on the subject?
Checklist - Selecting a SQL Server Product 8/17/2007	I have seen the recent addition of SQL Server product listings on your web site and see the value in being able to find all of the SQL Server products per category in one place. Going to your web site rather than having to search all over the Internet to find the products myself is a big time saver. Now that I know where the products are located, how should I go about evaluating them? I know I only have a limited amount of time to take a look at the products and need to select the correct one for our environment. I really do not want to get bugged by all of the sales people at these companies, but I do want to take a look at their products. So how should I go about evaluating products in the SQL Server market?
Are you a 'typer' or a 'clicker'? 8/16/2007	Over the last few months I have heard the terms 'typer' and 'clicker' related to how SQL Server DBAs manage SQL Server. Working with one specific customer, the customer came right out and asked me 'if I was a typer or a clicker'. I was surprised to hear that question, but I understood why they asked it as they observed me work. They noticed all of the T-SQL commands that I issued as opposed to using Enterprise Manager. They were also surprised to see how many SQL Server system tables that I accessed and the information that I gathered to troubleshoot the issue. This same distinction between DBA management preferences became crystal clear to me during a recent Northern Virginia SQL Server User Group session. As the group was having a discussion, I was surprised to see hear how how many people provided input based on clicks in the GUI (Management Studio or Enterprise Manager) versus issuing T-SQL commands. So is managing SQL Server via the GUI or T-SQL commands better than the other approach? Is there information that is only available in one technique and not the other?
Web Based Database Administration for SQL Server 8/15/2007	I am working at an organization where I need to have access to my SQL Servers around the clock. I do not have a company laptop to check into the environment and troubleshoot issues directly so I am looking for alternatives to meet the need. We are on a tight budget so I wanted to find out what native tools are available. Do you have any suggestions for SQL Server tools to meet this 24X7 management need?
Retrieving random data from SQL Server with TABLESAMPLE 8/14/2007	In a previous tip we talked about how to randomly retrieve records from the database. This tip took a look at both the RAND() function and the NEWID() function. Both of these approaches are helpful, but one problem with both of these is that depending on how you use them you may end up reading through your entire table to get a random distribution of data. The good part about this is that you are pretty much guaranteed that you will get a totally random set of records each time. Because of the potential overhead with reading through the entire table are there any other approaches to randomly retrieving data?
Simple way to find errors in SQL Server error log 8/13/2007	When managing SQL Server there are so many different places to look for data. These include the error logs, system event logs, profiler data, performance counter data, etc... Once you have collected the data you then need to parse through and interpret the data you collected. One of these areas where errors and other informational data is stored is the SQL Server error log. The problem with the error log file is that there is so much data collected it is sometimes hard to determine where the real errors lie. By default all backups and integrity checks are logged in the error log. In addition, if you are auditing logins these messages are also stored in the error log, so this further compounds the problem. It is great to have all of this data, but trying to find your problems can become quite a chore. So how can you find the errors much easier?
Defining and Establishing SQL Server Policies and Procedures 8/9/2007	As your IT team grows more and more people may have direct impact on your SQL Server databases. At the onset it may have been just a couple of developers and a DBA, but as your company becomes more successful and more resources are put on developing, maintaining and supporting a database driven application the potential for inconsistent processes and undocumented changes increases. Just when you need more control and structure things tend to sway the other way, because now there are more people and components that need to be managed. So what is the best process to put controls in now so when things grow beyond one or two people chaos does not follow?
SQL Server Code Review Checklist 8/7/2007	In a recent tip, you outlined steps for code deployment (Code Deployment Best Practices). That is a good first step to get a process in place to work with the team and set expectations. As a DBA, I need to find out the technical steps I should take when reviewing T-SQL code. Can you provide me with a checklist or recommendations to work through this process?
Code Deployment Best Practices 8/3/2007	I am the only SQL Server DBA at my organization and I feel like I am constantly pushing out code. I have Developers send me emails, I get help desk tickets, I have to go out to file shares and VSS to check for code that needs to get pushed out. I have been at a point where I am not able to get my other work done which is becoming frustrating. The other issue that I have is that I am unable to track the changes over time. With 4 different places that I need to check for code, this has become a nightmare that I have to get in control and soon. How should I go about doing this?
Hardware 101 for DBAs 8/2/2007	I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server. I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server. Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team. I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up. Can you help define some of the key hardware related items and the significance in SQL Server?
Building SQL Server Indexes in Ascending vs Descending Order 7/30/2007	When building indexes often the default options are used to create an index which creates the index in ascending order. This is usually the most logical way of creating an index, so the newest data or smallest value is at the top and the oldest or biggest value is at the end. Although searching an index works great by creating an index this way, but have you ever thought about the need to always return the most recent data first and ways you can create an index in descending order so the most recent data is always at the top of the index? Let's take a look at how this works and the advantages of creating an index in descending vs ascending order.
Database Integrity Checks Checklist 7/27/2007	In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The second item on the list was to check database and table integrity. This is pretty much a simple task to perform and there is not much if any interaction that needs to take place between the DBA and SQL Server. Although this is pretty straightforward to setup and run, this is not always implemented and secondly the output is seldom reviewed to see if there are any issues. This tip will address some of the items you should consider when putting your integrity check process in place.
Listing SQL Server Object Dependencies 7/26/2007	When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?
Database Restore Verification 7/24/2007	I have over night processes on a few different SQL Servers in my environment that are backup and restore related. I have used the scripts on MSSQLTips.com to check if the SQL Server Agent Jobs\Job Steps have run as well as the backup verification code. Some of the backup and restore processes I have written and others I have inherited. Some of the processes seem to be very reliable and others are not. I need a way to validate the restore processes have completed properly just like the SQL Server Agent Job and backup code. Can you provide this script so I can include it in my daily verification process?
If it's on the Internet, it must be true 7/23/2007	Among many other things, the Internet is a great resource and has revolutionized many things including troubleshooting SQL Server issues. Based on my observations, more often than not, when a SQL Server issue occurs DBAs, Developers or Network Administrators will ask their team if they have seen the issue they are experiencing and for the resolution. Depending on the answer, the next resource that is tapped is their favorite search engine i.e. Google. After a few different keyword searches, you may have your answer in a matter of minutes as compared to combing through book after book to find an answer. So once you have this information what should you really do?
SQL Server Best Practices Analyzer (Tip 1 of 2) 7/20/2007	We have a number of SQL Server 2000 instances in our environment. I am not sure if they have been consistently deployed or not. Further, I am not sure if the SQL Server 2000 instances are following best practices or not. How can I get some feedback on my SQL Server 2000 environment to shed some light on the situation?
Assigning shortcuts to commonly run tasks 7/17/2007	The SQL Server management tools offer a lot of hidden features, but finding all of these features is sometimes done by accident or by necessity to get a particular job done. One particular feature that would be helpful is to assign shortcuts for particular stored procedures that you run quite frequently. By default SQL Server offers default shortcuts such as Alt+F1 (sp_help), Ctrl+1 (sp_who) and Ctrl+2 (sp_lock). How can you assign your own frequently used stored procedures to shortcut keys?
Modifying tables and keeping like columns together 7/13/2007	When designing tables you may not always know what columns need to exist prior to when the table is created. Therefore over time table changes are made where you may add or drop columns. Based on this table modification you may want to have certain columns next to each other for easier management and grouping of like data in the table structure. It doesn't really matter to SQL Server where the column is located, but to us humans it sometimes makes a difference. Dropping a column is not a big deal because the column just goes away, but when adding a column the default process is to add the column to the end of the table. So how can you modify the table structure so all of the like columns are next to each other?
Backup Checklist 7/12/2007	In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The first item on the list was to address and implement a sound backup strategy. This is probably one of the easiest things to take care of, but often the wrong options are selected or the wrong or no backup plan is put in place. This tip will address some of the items you should consider when putting your backup process in place.
Assigning DBA Rights in SQL Server 7/10/2007	As a DBA you have the responsibility to ensure the security for the application and users related to the SQL Servers that you manage. But what are the security principals that you follow for the administrators? Are there any hard and fast rules that must be followed? What concerns should be taken into consideration when the rights are assigned to DBAs? How is this any different for a large organization versus a small team where many of the team members wear numerous hats (DBA, Developer, Network Admin, System Admin, etc.)?
DBA database management checklist 7/3/2007	For both old and new DBAs there are fundamental procedures that should be addressed and proper processes put in place to handle various areas of database management for SQL Server. Whether you are a full time DBA or this is one of many job roles that you perform the same basic steps should be implemented and adhered to in order to have some peace of mind that you are performing the correct procedures to ensure a healthy running SQL Server environment. For old DBAs these items should be a no-brainer, but often a refresher is good reminder to make sure everything is in place. For new DBAs some of these basic items are not all that apparent and often some of the most basic DBA 101 items are sometimes overlooked. So based on this, what is a good plan to implement to make sure the basic SQL Server DBA items are being addressed?
Retrieving SQL Server Column Properties with COLUMNPROPERTY 6/28/2007	SQL Server stores a lot of data about your database objects in various places and in various formats. When pulling data about table columns this data can be pulled directly from the syscolumns table. Some of this information is useful as it is, but some of the data needs to be interpreted to understand. In addition to pulling data directly from syscolumns you can also use the information schema view. If you query from INFORMATION_SCHEMA.COLUMNS you get a lot of data in a useful format, but there is still some missing data. So how can you retrieve additional data about column level properties?
Changing Not For Replication Value for Identity Columns 6/27/2007	When setting up replication there are many things to think about and many options you can choose from when setting up your publications and subscriptions. In most cases replication is an after thought and not part of the original database or application design, so there may be some required changes that need to be done when replication is setup. The most important part is that the table has a primary key. All tables should have a primary key when they are created, but sometimes this is not addressed and for replication to work this needs to be setup. In addition, if you are using merge replication you need to have a RowGuid column. Also, if you use identity columns you need to make sure the not for replication parameter is turned on.
SQL Server Agent Job Ownership 6/26/2007	I am making a number of SQL Server security changes at the server and database level all at once, which is a problem in and of itself. I will be changing SQL Server service accounts, removing rights to BUILTIN\Administrators group, limiting specific user rights, moving to group based security, changing SQL Server Agent Job owners, etc.
SQL Server Best Practices Analyzer (Tip 2 of 2) 6/26/2007	With SQL Server 2000, I had a good feel for many of the best practices, from an administration, development and configuration perspective. When deploying SQL Server 2005 some of those same principals will apply. But I'm sure with the vast amount of change in SQL Server 2005 that there are some new rules as well. How can I validate my SQL Server 2005 environment is properly configured? Are there really absolute rules when configuring a SQL Server 2005 instance?
File Validation with xp_fileexist 6/25/2007	In a recent tip (Accessing the Windows File System from SQL Server) options were outlined to capture files from a specific directory into a temporary table or table variable for additional processing. A similar file operations task that is needed in particular T-SQL code is to validate a specific file exists or not. Then logic can be written to either process the file, retry at a specific interval or fail the process. As such, how can this be accomplished and can you provide me with some concrete examples in SQL Server 2000 and 2005?
Transactional Replication Snapshot Issues 6/21/2007	SQL Server replication has been around for quite some time. I think just about every DBA has setup replication in some kind of test environment if not fully deployed this in a production environment. One of the issues that you are faced with when setting up transactional replication is getting your subscribers all of the initial data prior to replicating new transactions. There are various ways this can be done, but probably the most used technique is using the snapshot process.
Missing SQL Server Agent History 6/20/2007	SQL Server Agent is a useful tool to allow you to schedule reoccurring jobs to run on your server. Most of these jobs are usually maintenance type jobs or data loads, but this tool can be used to run any T-SQL code or even execute programs external to SQL Server. In addition to the ability to schedule jobs to run, SQL Server Agent also gives you the ability to see historical run information for the jobs as well as for each step within the jobs. This information is very helpful to have on hand for knowing if the job succeeded as well as how long the job took to run. This way the next time you need to run one of these jobs you have an idea of how long it will take to complete. If you have jobs that run on a frequent basis or if you have a lot of jobs that are scheduled you may notice that there is not much historical information stored when you view job history, so what can be done to keep more historical run information?
Memory Allocation for SQL Server 2000 Enterprise Edition 6/19/2007	I have a Windows 2003 Enterprise Edition server with SQL Server 2000 Enterprise Edition. The machine has 10 GB of memory, but I am not confident that the machine is using all of the memory that I have configured from an Enterprise Manager perspective. How can I validate how much memory SQL Server is using? I have a sneaking suspicion that only a small percentage of the memory is used, but I am not 100% sure. If the SQL Server is not using all of the memory, how can I have SQL Server access the 10 GB of memory or is that too much?
Capacity Planning for SQL Server 2000 Database Storage 6/18/2007	I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.
Identify All Tables with Columns of a BLOB Data Type 6/15/2007	After some recent SQL Server performance tuning, I noticed that one of my large core tables (important) has a column with a text data type. I had no idea that this table had a BLOB (binary large object) data type. After running some quick queries on the table\column and doing some quick analysis I have found that none of the entries exceed a few hundred characters. After talking to the development team, they indicated that a 500 character limit is enforced on the front end. With this being said, I clearly do not need a column with the text data type and could use a varchar or nvarchar (Unicode) column with a width of 500. Since I was not aware of this particular BLOB column in one of my large core tables (important), I suspect some other tables in my environment have column text, image, ntext, etc data types. How can I easily find these columns on a per table basis without having to review them in SQL Server Enterprise Manager or SQL Server Management Studio?
SQL Server Database Specific Performance Counters 6/14/2007	Both SQL Server and Windows offer a lot of data to help troubleshoot and monitor overall usage and performance for your SQL Server databases. Within SQL Server there are several DBCC commands as well as a lot newly exposed data from the Dynamic Management Views in SQL Server 2005.

Click Here to Register to Access Edgewood's Free SQL Server Resources

Title

Summary