Tips
Backing up SQL Server Data for Rollback Purposes
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. 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?
Best Practice for renaming a SQL Server Database
One of the Junior SQL Server Database Administrator in my company approached me yesterday with a dilemma. He was assigned a task to rename a few of the databases in Beta and Production environments; the reason being the database name was based on some other project that is no longer relevant to the data which is presently stored within the database. At first I started to tell him, but figured it would be smarter to document the same and share the information.
Best Practice Recycling SQL Server Agent Error Logs
In most production environments, the SQL Server is restarted very rarely as a result both the SQL Server Error Log and SQL Server Agent Log keep growing and at times it becomes very difficult to open up and analyze Error Logs when you encounter issues. It is a good practice for the DBA to schedule a SQL Server Agent Job which runs once a week to execute sp_cycle_agent_errorlog system stored procedure to create a new SQL Server Agent Error Log.
Best Practices Backup System Databases in SQL Server
The role of system databases for the functioning of SQL Server cannot be underestimated due to the significant amount of information which is stored within these databases. System databases which are available in SQL Server 2005 and later versions are Master, Resource, MSDB, MODEL, TempDB, Distribution, ReportServer and ReportServerTempDB. It is a best practice to create daily backups of all the system databases once all the user databases on the server are backed up successfully. If not daily, the DBA should at a minimum backup all the system databases each time a server or database configuration is added or modified. These include Service Packs, Hot Fixes, Cumulative Update, login changes, job changes, operator changes, database configu
Best practices for SQL Server database ALTER table operations
ALTER table operations may be required to meet changing logical or operational demands. Once a table is populated with data, then any change to the table structure requires additional concerns in terms of data protection, smooth operation of the system and size of the table. ALTER table operations may be carried out both through T-SQL or SSMS designers. Most of the ALTER TABLE operations if performed using SSMS causes a drop and recreation of the table. This may lead to performance degradation or dead locks. It is therefore recommended to carry on ALTER table tasks through T-SQL except some rare operations that require table recreation even through T-SQL.
In coming lines we will go through performing ALTER table operations through T-SQL
Best practices for taking on the SQL Server DBA role as a developer
I am an application developer and my company doesn't have a database person on staff. Since I have a little bit of database knowledge, it has now become my responsibility to perform the data modeling and development work. What advice can you offer?
Best practices for working with read only databases in SQL Server
Databases whose architecture or data is not required to be updated should be considered to be set as READ ONLY databases. For one of my archival databases, I am looking to make it READ ONLY and would like to know what steps I should take. In this tip I go over some best practices before setting a database to READ ONLY.
Checklist to Re Architect a SQL Server Database
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?
Comment SQL Server TSQL Code
I do a horrible job of commenting my T-SQL code in SQL Server. I could use a few pointers because I have gone back to change my code and it has taken me forever. How should I go about commenting my T-SQL code so it is simple and helpful? Does any of the functionality differ between SQL Server 2000 and 2005?
-- A request from an MSSQLTips.com Subscriber
Common SQL Server Development and Administrative Issues
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.
Determining Free Space Per SQL Server Database
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?
DOS Commands for the SQL Server DBA
I have seen your recent tips (Introduction to Windows PowerShell for the SQL Server DBA Part 1, Introduction to Windows PowerShell for the SQL Server DBA Part 2 and PowerShell Support in SQL Server 2008 - Invoke-Sqlcmd Cmdlet) on PowerShell and see value in them. Unfortunately, we do not have PowerShell installed on our SQL Servers and I am not sure when that is going to happen. Until that day arrives could you give me some insight into valuable DOS commands that I can leverage in administrative and research situations on my SQL Servers?
Generate scripts for SQL Server 2008 object migration
Our development team created a new module for a production web portal. This new module includes new tables, foreign keys, indexes, lookup tables, associated data, views, stored procedures, user defined functions, etc. which were created in an existing database on one of our development servers. How can I generate a script to move all the objects for this specific module to our production SQL Server database? I do not want to lose any related keys, indexes, data, constraints, etc. Do you have any suggestions on how this can be accomplished?
Grant Truncate Table Permissions in SQL Server without ALTER Table
How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table? Check out the examples and explanation in this tip.
Identify and Delete SQL Server Archive Tables for Rollback
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?
Increase the Number of SQL Server Error Logs
The SQL Server error log is a great place to find information about what is happening on your database server. Each SQL Server Error log will have all the information related to failures / errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs. By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. In this tip, you will see the steps which you need to follow to increase the number of SQL Server Error Logs.
Requesting and researching SQL Server enhancements with Connect
A colleague wanted to know a way to make it easy to write stored procedures that reference objects in a different database when the name of the databases might change. For example, when the code is moved into production. He couldn't find a way and neither could I. What could we do about it? Had we missed some feature in SQL Server? Would a feature to help us out ever be part of SQL Server? In this tip we look at Microsoft's Connect database to report and find issues/features with SQL Server.
SQL Server 2005 Index Best Practices
In continuing with our series of tips on Best Practices for SQL Server I'm turning my sights on Maintenance. Specifically in this tip we will be discussing Index maintenance: when, if, why, and how are questions that will be addressed. Many tips here at MSSQLTips.com are devoted to just this topic and most of the detailed steps on how to perform index maintenance are going to be links to tips we've previously published. I will also not be presenting what index fragmentation is. If you are looking for information on either topic please refer to the Next Steps section below. What we will focus on is the Best Practices associated with indexing maintenance as a whole. With that understanding, let's proceed!
SQL Server Comparison Tools
There is often the need to compare both data and database structures from two databases either on the same server or on different servers. Most text editors have a built-in process to allow you to do a diff and identify any differences between the two files, but comparing data is not quite that easy. Using this diff process is great if you only want to compare a few files, but what if you need to scan your entire database to look for differences?
SQL Server Cursor Example
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
SQL Server DBA Checklist
I am looking for items that I should address on a daily basis on my SQL Servers. As such, what are the critical aspects of SQL Server that should I check on a daily basis? Should I perform additional checks on a weekly, monthly or yearly basis? How can I automate some of these tasks so I do not spend my whole day reviewing SQL Servers rather working on the latest and greatest technologies?
SQL Server DBA database management checklist
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. So based on this, what is a good plan to implement to make sure the basic SQL Server DBA items are being addressed?
SQL Server Disasters with Preventive Measures
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 Health and History Tool
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?
SQL Server Worst Practices
Since today is Friday the thirteenth, what are some of the SQL Server worst practices and how can I prevent or fix them? Can you provide some real world examples? Can you focus on the technical aspects of SQL Server that DBAs and Developers have the ability to correct?
Tempdb Configuration Best Practices in SQL Server
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?
Use consistent SQL Server Naming Conventions
I find it rare that a company possesses established and well-documented naming conventions. Of those that exist, it is tough to find a case where they are followed consistently. Not having or following a naming convention can lead to much frustration and difficulty in writing code around your database entities. This is especially important when your team is large and/or spread out as well as when multiple people are responsible for creating objects and writing modules.
Using the Microsoft SQL Server 2005 Best Practices Analyzer
Database Administrators are often asked questions like "are all the SQL Servers within an organization configured according to the industry standards?". In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2005 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not.
Using the Microsoft SQL Server 2008 R2 Best Practice Analyzer
Database Administrators are often asked questions like "are all the SQL Servers within an organization configured according to the industry standards?" In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2008 R2 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not. Database administrator can also use SQL Server 2008 R2 Best Practice Analyzer to analyze SQL Server 2008.
Using Windows Groups for SQL Server Logins as a Best Practice
We are taking a break from the tips format this month to provide you with some Best Practice articles for the many aspects of Microsoft SQL Server development, administration, and support. This article will focus on the use of Windows Groups for security in your SQL Server instances in lieu of relying on SQL Server logins for administering your security contexts.
You might be a DBA
To bring some comic relief to MSSQLTips.com at the end of the year, check out today's newsletter. It is in the spirit of Jeff Foxworthy. So here you go, you might be a DBA, if...
Top 10
SQL Server Cursor Example
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
Grant Truncate Table Permissions in SQL Server without ALTER Table
How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table? Check out the examples and explanation in this tip.
Tempdb Configuration Best Practices in SQL Server
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?
Best Practice for renaming a SQL Server Database
One of the Junior SQL Server Database Administrator in my company approached me yesterday with a dilemma. He was assigned a task to rename a few of the databases in Beta and Production environments; the reason being the database name was based on some other project that is no longer relevant to the data which is presently stored within the database. At first I started to tell him, but figured it would be smarter to document the same and share the information.
SQL Server DBA Checklist
I am looking for items that I should address on a daily basis on my SQL Servers. As such, what are the critical aspects of SQL Server that should I check on a daily basis? Should I perform additional checks on a weekly, monthly or yearly basis? How can I automate some of these tasks so I do not spend my whole day reviewing SQL Servers rather working on the latest and greatest technologies?
SQL Server 2005 Index Best Practices
In continuing with our series of tips on Best Practices for SQL Server I'm turning my sights on Maintenance. Specifically in this tip we will be discussing Index maintenance: when, if, why, and how are questions that will be addressed. Many tips here at MSSQLTips.com are devoted to just this topic and most of the detailed steps on how to perform index maintenance are going to be links to tips we've previously published. I will also not be presenting what index fragmentation is. If you are looking for information on either topic please refer to the Next Steps section below. What we will focus on is the Best Practices associated with indexing maintenance as a whole. With that understanding, let's proceed!
SQL Server DBA database management checklist
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. So based on this, what is a good plan to implement to make sure the basic SQL Server DBA items are being addressed?
Determining Free Space Per SQL Server Database
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?
Best practices for taking on the SQL Server DBA role as a developer
I am an application developer and my company doesn't have a database person on staff. Since I have a little bit of database knowledge, it has now become my responsibility to perform the data modeling and development work. What advice can you offer?
DOS Commands for the SQL Server DBA
I have seen your recent tips (Introduction to Windows PowerShell for the SQL Server DBA Part 1, Introduction to Windows PowerShell for the SQL Server DBA Part 2 and PowerShell Support in SQL Server 2008 - Invoke-Sqlcmd Cmdlet) on PowerShell and see value in them. Unfortunately, we do not have PowerShell installed on our SQL Servers and I am not sure when that is going to happen. Until that day arrives could you give me some insight into valuable DOS commands that I can leverage in administrative and research situations on my SQL Servers?
Last 10
Grant Truncate Table Permissions in SQL Server without ALTER Table
How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table? Check out the examples and explanation in this tip.
SQL Server Cursor Example
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
Using the Microsoft SQL Server 2008 R2 Best Practice Analyzer
Database Administrators are often asked questions like "are all the SQL Servers within an organization configured according to the industry standards?" In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2008 R2 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not. Database administrator can also use SQL Server 2008 R2 Best Practice Analyzer to analyze SQL Server 2008.
Use consistent SQL Server Naming Conventions
I find it rare that a company possesses established and well-documented naming conventions. Of those that exist, it is tough to find a case where they are followed consistently. Not having or following a naming convention can lead to much frustration and difficulty in writing code around your database entities. This is especially important when your team is large and/or spread out as well as when multiple people are responsible for creating objects and writing modules.
Using the Microsoft SQL Server 2005 Best Practices Analyzer
Database Administrators are often asked questions like "are all the SQL Servers within an organization configured according to the industry standards?". In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2005 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not.
Best practices for working with read only databases in SQL Server
Databases whose architecture or data is not required to be updated should be considered to be set as READ ONLY databases. For one of my archival databases, I am looking to make it READ ONLY and would like to know what steps I should take. In this tip I go over some best practices before setting a database to READ ONLY.
Best Practice Recycling SQL Server Agent Error Logs
In most production environments, the SQL Server is restarted very rarely as a result both the SQL Server Error Log and SQL Server Agent Log keep growing and at times it becomes very difficult to open up and analyze Error Logs when you encounter issues. It is a good practice for the DBA to schedule a SQL Server Agent Job which runs once a week to execute sp_cycle_agent_errorlog system stored procedure to create a new SQL Server Agent Error Log.
Best practices for SQL Server database ALTER table operations
ALTER table operations may be required to meet changing logical or operational demands. Once a table is populated with data, then any change to the table structure requires additional concerns in terms of data protection, smooth operation of the system and size of the table. ALTER table operations may be carried out both through T-SQL or SSMS designers. Most of the ALTER TABLE operations if performed using SSMS causes a drop and recreation of the table. This may lead to performance degradation or dead locks. It is therefore recommended to carry on ALTER table tasks through T-SQL except some rare operations that require table recreation even through T-SQL.
In coming lines we will go through performing ALTER table operations through T-SQL
Best Practice for renaming a SQL Server Database
One of the Junior SQL Server Database Administrator in my company approached me yesterday with a dilemma. He was assigned a task to rename a few of the databases in Beta and Production environments; the reason being the database name was based on some other project that is no longer relevant to the data which is presently stored within the database. At first I started to tell him, but figured it would be smarter to document the same and share the information.
Best Practices Backup System Databases in SQL Server
The role of system databases for the functioning of SQL Server cannot be underestimated due to the significant amount of information which is stored within these databases. System databases which are available in SQL Server 2005 and later versions are Master, Resource, MSDB, MODEL, TempDB, Distribution, ReportServer and ReportServerTempDB. It is a best practice to create daily backups of all the system databases once all the user databases on the server are backed up successfully. If not daily, the DBA should at a minimum backup all the system databases each time a server or database configuration is added or modified. These include Service Packs, Hot Fixes, Cumulative Update, login changes, job changes, operator changes, database configu
