learnSQLwithME

Hi guys! Today's post is a common on-the-job scenario. In my environment we have a reporting server so analysts can query databases without impacting production. However, the reporting server doesn't have all the same databases that are in production. Once in a while, I will receive a request to grant permissions directly to the production node instead of the reporting server to write queries against a database that is only in production. Here's the easy way to accomplish that. Open SSMS (SQL Server Management Studio). On the 'Connect To Server' Tab, click the 'Options>>' button. Then under the 'Additional Connection Parameters' area, enter: applicationintent=readonly and hit the 'Connect' button. That's it. Now the user can connect to prod with a read only connection. If you have any further questions, please post below. Thank you and Happy reading, -marshé hutchinson #learnSQLwithme

Hi there. This is another short and quick post for you. I like these mini posts because the solutions are easy and it's usually a common issue such as permissions or script syntax. In today's scenario, I have a new instance where I would like to restore a database to, but the database restore is not working. The specific error is: System.Data.SqlClient.SqlError: Directory lookup for the file "log.ldf" failed with the operating system error 2 (The system cannot find the file specified.). With this error, it is best to investigate the location of the file. I tried to get to the location and... it doesn't exist. That is why I am receiving this error. To fix this issue I will need to update the log file path for my instance. In SSMS, right click the instance and choose properties. Go to the 'Database Settings' page > and update the path for the Log directory. Not

Hi Everyone! This post is another short and sweet post. I was at work and a coworker asked me if I could tell them who deleted a particular database on their developer server. A delete action is part of using DDL statements in SQL server. DDL stands for Data Definition Language. DDL commands impact the structure of the database so create, alter or delete are popular examples of this. You can read more about DDL statements here . To find who the culprit is, all you have to do is: In SSMS, Right click your instance >choose 'Reports' > 'Standard Reports' > 'Schema Changes History' Note: This report will show you all the DDL statement executions for the instance. That's it :). If you have any further questions, please post below. Thank you and Happy reading, -marshé hutchinson #learnSQLwithme

Hi There! Today's post is about fixing databases that are in a restoring state. Scenario A I have added a couple of databases to an availability group on my primary server. When I go to the replica server, the databases show that they are in a restoring state. Here's how to fix this issue: I. On the secondary server in SSMS, expand 'AlwaysOn High Availability' > 'Availability Groups' > ServerName (secondary) > 'Availability Databases' > right click your database name > 'Join to Availability Group...' >hit OK . II. When I hit OK above, I received a new error; SQL error 1408. The error reads:  The remote copy of the database is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database. Now I have to go back to the primary server and ta

Hi everyone! This post will cover the single-user mode error for a database. There will be mention of replication, but that is not the focus of the post. If you would like more information on replication, check out my other post here . Here's what happened. I was tasked with setting up replication between two databases on a server. It turns out replication was no longer needed and I didn't need the database at all. So I dropped the replication publication and subscription without removing them from the database first. This is a no-no. You should always remove replication from your database first before deleting any publication(s) and/or subscription(s). Since I did not do it properly, the database is still associated with the replication process and knows the process is broken. What did it do? It forced the database into single user mode. Now I have to get this database out of single user mode so I can remove replication and delete the database

Hi guys! Here's another quick and easy post. Scenario I was trying to copy a SQL agent job from one server to another by just copying the script of that SQL job. You can copy any script by right clicking a job > Script job as > create to > new query window. I pasted the job on a different server and tried to execute it and received this error: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 199 The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]). This error message is simply saying for the variable '@owner_login_name', the user account associated with the variable is incorrect because it does not exist. Fix Simply update the variable for '@owner_login_name' to a valid user account for the current SQL instance. Now when you execute the script again to create the SQL agent job your results will be: Command(s) completed successfully. If you have any furt

Hi everyone! Today's post is about an all too common problem... syntax issues. Whether you fat-finger your keyboard or are always in a rush, chances are you have already encountered many syntax errors. But if you are a newbie,  your syntax error is just around the corner. No worries, this is an easy fix. Syntax errors just let you know part of your code or script is incorrect. Scenario I have a SQL agent job that is using the SQL command line to perform index maintenance, but it is failing. Here's my code : sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.IndexOptimize @Databases = ' databaseName ', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = 'ALL',@WaitAtLowPriorityMaxDuration = 180, @WaitAtLowPriorityAbortA

Hi everyone! This post will be a quick and easy fix. Scenario: You have a SQL agent job that will not execute. The specific error says: Could not obtain information about Windows NT group/user ' domain\username ', error code 0x5. [SQLSTATE 42000] (Error 15404)). Here's how to fix this: Double click the SQL agent job to open the job properties. On the 'General' page change the Owner of the job to 'sa' by clicking the box to the right of the field and then checking the username 'sa exists. Hit 'Ok' . Your job will now run successfully. I told you this was a short post :). If you have any further questions, please post below. Thank you and Happy reading, -marshé hutchinson #learnSQLwithme

Hi there. This post will use SSMS (SQL Server Management Studio) as well as Redgate SQL Clone . At the time I am writing this, the latest version of SQL Clone is 4.0.2.19054 . SQL clone is an application that lets you create a copy of an original database (clone) from an image (master file) that you create. This technology comes in handy because: Developers can test against the clone without altering the original database. The clone is much smaller in size and consumes less storage resources. Once you make an image of a database, you can clone the image to multiple servers. Clones work independently from the image. You can also enforce security by granting/restricting permissions to active directory groups or individuals. Now it's time for the meat an potatoes. I received an alert that my SQL Clone process failed and no clones were generated :( . Here's the specific error: Error while opening connection to the SQL server ServerName: A network-related or i

Hi there. This post will be a quick and easy fix. If you are not already aware, availability groups are the replacement for mirroring in SSMS (SQL Server Management Studio). We like to utilize availability groups so that there is minimal data loss. Also, in the event of some natural disaster or disk failure on a server that is participating in an availability group, the data will fail-over to a backup server (replica). I was adding a database to an availability group when I received the error: Database "database_name" database is not in full recovery mode on each of the server instances. The full recovery model is required for a database to participate in database mirroring or in an availability group. [SQLSTATE 42000] (Error 1465). The error is exactly as it sounds; The database is not in the full recovery model. To fix this: I. In SSMS, right click the database to see the properties. On the Options page, change the database recovery model to Full. II.

Hello again. This post is related to automating SQL server tasks with Task Scheduler. In this example I was tasked with exporting SQL server agent jobs twice a month. To achieve this, I setup a task scheduler task that ran a PowerShell script that: establishes a local connection to the SQL server. cleans the file before writing to the .sql file to avoid adding on (appending) results on subsequent runs of the task scheduler task. when a connection is established, export the SQL agent jobs on the server to a .sql file on a remote server. sends a confirmation email via the SQL server database mail profile that the SQL agent jobs were exported to the specified remote server path. That was a mouthful. Now that you know my task scheduler task is calling a PowerShell script, let's get back to why it is failing.  The good news is this error is not related to the script. The error launch failure means the task could not even run. This is most likely due to a permissi

Hi everyone. Welcome to my first post. In my environment, we still utilize replication. What in the world is replication? Replication is a way to copy data from your source to destination server continuously. Imagine if I am adding records to a database on your source server throughout the day and I would like the data updated to the target server. That's what is possible with replication. Here's a great article for further reading: https://www.sqlshack.com/sql-server-replication-overview-of-components-and-topography/ Back to the issue at hand. 1. After reinitializing replication on the QA server, I received this message under the replication monitor > Distributor To Subscriber History: The concurrent snapshot for publication is not available because it has not been fully generated or the Log reader agent is not running to activate it. This was the other error message I was receiving under the replication monitor > Publisher To Distr

This blog documents different SQL scenarios I have encountered on the job or assisting friends. This information is intended for those who are new to SQL. I hope that SQL beginners will find this information useful. Note: A SQL beginner would be someone that has just completed their first boot camp or is a new junior database administrator; someone that would be familiar with the various technologies and terms used. Thank you and Happy reading, -marshé hutchinson #learnSQLwithME