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...