:::: MENU ::::

Posts Categorized / DBA best practices

  • Mar 21 / 2009
  • 0
DBA best practices, DBA Interview questions, DBCC Commands, dbDigger, Transaction log files

How to Prevent the Unexpected Growth of transactional log files in SQL Server

To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:

  • Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
  • Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
  • Change the recovery model. By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups. By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database. You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
  • Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
  • Design the transactions to be small.
  • Make sure that no uncommitted transactions continue to run for an indefinite time.
  • Schedule the Update Statistics option to occur daily.
  • To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
  • Feb 11 / 2009
  • 0
DBA best practices, dbDigger, SSMS tips and tricks

how to register new SQL Server instance through SQL Server management Studio(SSMS)

Microsoft has introduced SQL Server Management Studio (SSMS) with SQL Server 2005 release. It is now a favorite tool of SQL Server DBAs. Most of new DBAs and students that are new to SQL Server feel a bit confusion to adopt various facilities for tasks provided in SSMS. I have sketched a step by step configuration for some basic tasks through SSMS.
In this post we will see how to register new SQL Server instance through SQL Server management Studio(SSMS). Follow these steps in your SSMS

Register New Server Instance

Fill our required fields in this frame

Register New Server Instance 1

You may also change default connection properties under connection properties tab.

Register New Server Instance 2

Test the connection parameters through test button and save your settings. New instance would be get registered. Please note that above snaps have been taken from Management studio 2008, so you may get some minor differences in case of SSMS 2005. One of such differences is use custom color option under connection properties tab. This option is not available in SSMS 2005.

  • Oct 17 / 2008
  • 0
DBA best practices, dbDigger, Documentation

Documenting the SQL Server Environment

Documentation is indispensable in IT environment. In case of SQL Server with more than one DBA in organization, documentation is best way to avoid confusions and mistakes. At broad level following information may be documented related to SQL Server environment.

  • Server name
  • Physical location
  • Disk Drives and configuration
  • Installed memory
  • Used network ports
  • Base server Operating system version and service pack level
  • SQL Server version and service pack level
  • Database names
  • Configured Maintenance plans
  • Configured DTS packages
  • A list of users and their permissions for databases
  • A list of users and their permissions for OS

Above info may vary in organization to organization, you may modify it according to your specific IT environment and requirement. It will be valuable and helpful.

  • Sep 17 / 2008
  • 0
DBA best practices, DBA thoughts, dbDigger, Documentation, T-SQL Scripts

Naming cionventions for SQL Srever user objects

database objects Naming conventions

Following SQL Server objects naming conventions may be used primarily to standardize the DB objects naming structure. Conventions will be applied to Tables, Views, Stored Procedures, Functions and Triggers.

Tables

  • Table name should be descriptive of its content e.g staff, staff_education.
  • No space should be used; under score may be used where necessary.
  • Adding tbl as a prefix should be avoided.
  • Domain of functionally may be reflected in name as for Inventory Management System related tables, we may name as IMS_Wing, IMS_Vendor, IMS_Issue.

Views

  • View name should have VW as prefix. E.g vw_staff_posting, vw_sancstrength
  • View name may also contain names of its base table/tables. E.g vw_staff_staffeducation

Stored Procedure

  • Name of user stored procedure should be suffixed with USP.
  • Stored Procedure name should reflect application to which it belongs. For example for any AD Hoc reporting application we may use USP_AdHoc_GetSummary

User Defined Functions

  • Name of user defined function should have UDF as a prefix.
  • UDF name should reflect the application to which it belongs. For example for any AD Hoc reporting application we may use UDP_AdHoc_calculateSummary

Triggers

  • Trigger name should always have T as prefix.
  • Next to T it should have I, D or U to reflect insert, delete or update event on which it will be invoked. E.g TI_staffLog, TU_staffLog, TD_StaffLog.
  • Sep 15 / 2008
  • 0
DBA best practices, DBA Interview questions, dbDigger

Do nots for a DBA

Here are some actions that a DBA needs to avoid under given circumstances

  • Do not re build an index during working hours
  • Do not stop the database engine without warning/notice
  • Do not perform a service pack upgrade during working hours
  • Do not start any backup/restore process, if you have intensive I/O going on
  • Do not de fragment the drive which ha database files
  • Do not configure screen savers on production servers, if you have cpu intensive tasks there
  • Do not perform any vendor upgrades without testing or backing up
  • Aug 12 / 2008
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, T-SQL Tips and Tricks, User Defined Stored Procedures

DBA Best Practices for Queries and Stored Procedures

Queries and stored procedures are close companions of a DBA. So make your life easy by following the baseline given below, while writing queries and stored procedures by tomorrow.

  1. Maintain all code in a source control system.
  2. Keep transactions as short as possible. This reduces locking and increases application concurrency, which helps to boost performance.
  3. Avoid using query hints unless you know exactly what you are doing, and you have verified that the hint actually boosts performance.
  4. Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure.
  5. Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED.
  6. SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
  7. Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them.
  8. For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner. object_name or schema_owner.object_ name.
  9. One way to help ensure that stored procedures query plans are reused from execution to execution of the same stored procedure is to ensure that any user connections information, SET options, database options, or SQL Server configuration options don’t change from execution to execution of the same stored procedure. If they do change, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.
  10. Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including:
  11. Reduced network traffic and latency, boosting application performance.
  12. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
  13. Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
  14. Stored procedures help promote code reuse. While this does not directly boost an application’s performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  15. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
  16. Stored procedures provide better security to your data.

A part chosen from SQL Server DBA best practices By Brad M.mcGehee

Consult us to explore the Databases. Contact us