Service Broker - When to use for synchronizing databases

Service Broker is used by SQL developers to manage applications running across multiple SQL Server instances . It is part of the SQL Server native feature set and is a messaging and queue service. Asynchronous processing in Service Broker is very effective in supporting decoupled processes for long running tasks. It's very versatile but be warned of attempting to over engineer solutions that could be better served by other features. As a very general feature summary for Service Broker think of - Guaranteed message delivery , Transactions,Backup,API and asynchronous communication. I receive questions regularly about whether Service Broker should be... Read more →

How to capture errors from SQLCMD

Question: I'm executing code using SQLCMD from a batch file . The code points to a sql file and there is also an output file. SQLCMD -E -S MYSERVER\INST1 -i "setup_job_entry.sql" -o "setup_job_entry.log" But I noticed that if the actual SQLCMD returns an error , for example , if I'm connecting to an server which doesn't exist this error message will appear in the output file - but there will not be an ERROR number , which would allow me to trap and return an appropriate message Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network... Read more →

SQL Server Agent Top Tips

Scheduling How to schedule a SQL Server Agent Job to execute once a year ... SQL Agent Jobs – Schedule in seconds (SQL Server DBA) Status List enabled status of SQL Server Agent Jobs (SQL Server DBA) How write the a SQL Server agent Job status to the Windows Application log Send sql server agent job history through email notification General How to monitor Blocked Processes with SQL Alert and email ... Manage SQL Agent jobs without sysadmin (SQL Server DBA) Backup database job with SQL Server Agent (SQL Server DBA) Read more →

Difference between ORIGINAL_LOGIN() and CURRENT_USER

ORIGINAL_LOGIN() and CURRENT_USER are useful functions in SQL Server , used to report on identities within SQL Server sessions. The purpose of the ORIGINAl_LOGIN() is to return the value of the login which made the original connection to SQL Server. CURRENT_USER - Returns the name of the current user. This function is equivalent to SELECT USER_NAME(). The definitions sound similar but there are some differences. When using CURRENT_USER , the value will change according to different impersonations used. In this example , we are using the EXECUTE AS functionality - which allows a login to execute code as another login... Read more →

SQLSERVER-DBA.COM most read posts in 2018

SQL Server DBA scripts Powershell Scripts for DBA Calculate SQL row size How to fix systemoutofmemoryexception when using-ssms SQL server runnable suspended running status model The transaction log for database is full due to active transaction SQL server find queries causing lock timeouts per second Missing indexes equality and inequality on sysdm_db_missing_index_details SQL server dba checklists How to use xp_readerrorlog parameters to search sql error logs Read more →