Microsoft SQL SERVER DBA

Best Practices on Replication Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning. Ideally, publishers, distributors, and subscribers should be on separate physical hardware. Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice. Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed. Use default replication settings, unless you can ensure that a non-default setting will actually improve replication performance or other issues. Be sure that you test all changes to ensure that they are as effective as you expect. Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes. Periodica...

Useful Queries for DBA ====================== Finding the Blocking Statement in SQL Server SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) BlockedObjectName, tl.resource_type, h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 GO -----------------------------------------------------------------------------------------------------------...

SQL SERVER – 15 Best Practices for Better Database Performance: 1. Store relevant and necessary information in the database instead of application structure or array. 2. Use normalized tables in the database. Small multiple tables are usually better than one large table. 3. If you use any enumerated field to create look up for it in the database itself to maintain database integrity. 4. Keep primary key of lesser chars or integer. It is easier to process small width keys. 5. Store image paths or URLs in database instead of images. It has less overhead. 6. Use proper database types for the fields. If StartDate is database filed use date time as datatypes instead of VARCHAR (20). 7. Specify column names instead of using * in SELECT statement. 8. Use LIKE clause properly. If you are looking for exact match use “=” instead. 9. Write SQL keyword in capital letters for readability purpose. 10. Using JOIN is better for performance than using sub queries or nested queries. 11. Use stored p...

Frequently Raised Errors in Log-Shipping 1. Question : IS it possible to log ship database between SQL 2000 & SQL 2008?  Answer: No, thats impossible, In SQL 2008 transaction log architecture is changed compared to SQL 2000 and hence you won’t be able to restore tlog backups from SQL 2000 to SQL 2008 or vice versa. 2. Question:I’m getting the below error message in restoration job on secondary server, WHY? [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 7000000026200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000015100001 can be restored. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. Answer: Was your sql server or agent restarted Y’day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the su...

Hello, readers!🙏வணக்கம்  வாசகர்களே! Welcome to my blog!🙏. I'm Veerakumar, and I have to share my inspiring journey of professional growth and career transitions. In 2010, I graduated with a Bachelor's degree in Mechanical Engineering, laying the foundation for my future endeavors. From 2010 to 2012, I embarked on a role as a Quality Control Engineer, where I applied my expertise to ensure product excellence and maintain high standards. This experience allowed me to develop a keen eye for detail and a commitment to delivering top-notch results. In 2014, driven by my passion for technology, I took a leap of faith and ventured into the exciting world of IT. I embraced the role of an IT Support Engineer, providing technical assistance, troubleshooting hardware and software issues, and ensuring smooth operations for my organization. This marked the beginning of my journey in the vast and ever-evolving field of information technology.  one of my most well wisher (Selventhira...