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 →

The transaction log for database is full due to LOG_BACKUP

Question: I'm getting the The transaction log for database is full due to LOG BACKUP Error 9002 Severity 17 State 2 error message . When I look at the Message associated with the error message the details are "One or more recovery units belonging to database 'database_name' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure" I can see clearly the disk space available on the drive is... Read more →

Day 2 Operations - Are you ready?

Question: I was in a meeting yesterday and heard a new term - "Day 2 Operations". In the context of the conversation Iit made sense, but I'd never heard it. Is this a formal concept? What are the specifics? Answer: In general terms - Day 2 operations refers to features of a product or platform which are in an operational state. Thinking about Day 2 operations as part of a lifecycle - we'd be discussing product usage in the advanced stages of the lifecycle, Day 0 -customer requirements -Architectural Design Day 1 -Build -Deployment -Best practises configuration Day 2 -Operations... Read more →