CoNetrix

Blog: SQL

One of our customers reported their Veeam backups were failing. We determined the cause to be the vCenter services were stopped and would not restart. The vCenter issue was a result of the SQL Express database having grown to its 10GB maximum size. We were able to get the vCenter services running temporarily by purging performance data from the database using the procedure at http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1007453. [more]

This procedure removed enough data to get the services running, but didn’t reduce the overall size of the database significantly. I found a VMware SQL stored procedure named “dbo.cleanup_events_tasks_proc” that reduced the size of the database by 60%. After a couple of shrink file operations, the database and the vCenter services were up and running. 

However the Veeam backups failed yet again the next night. While the Veeam errors indicated that the vCenter services were again offline, this time it was because the virtual disk containing the SQL Server Express vCenter database was completely full. The transaction log for the vCenter database had bloated to 24GB and filled up the disk. This was confusing initially because I had checked the recovery model of the database prior to running the stored procedure to make sure it was set to “Simple” to prevent this very issue. 

With SQL Server the growth of the transaction log is directly proportional to amount of “work” that SQL Server has to perform between BEGIN TRANSACTION and COMMIT TRANSACTION commands. Certain SQL Server commands (insert, update, and delete) are always wrapped in implicit transactions. But some bulk operation transactions can be executed with explicit BEGIN/END TRANSACTION commands to control roll back. The stored procedure that I ran wraps a potentially large batch purge process in a SQL transaction that enables the entire process to be rolled back in the event of a failure. In this case, the lengthy stored procedure resulted in a ridiculously huge transaction log. Lesson learned is that “Simple” recovery model doesn’t guarantee the transaction logs will always be a manageable size.

Networking, SQL, Veeam, VMware, vCenter,
 

The sa account for the SQL Instance on a number of PCs got locked out. One way to unlock all sa accounts is by putting the instance in single-user mode and creating a recovery account, then you can get connected to the SQL instance with that recovery account and unlock the sa account.

Here are the instructions for how to do that: [more]

 

  1. Open SQL Server Configuration manager and Stop the SQL service. 
  2. Once the service stops, right-click the service and select properties. 
    1. Got to the Advanced tab, find the Startup Parameters and add –m; to the front of the line (This will start the SQL Service in Single-User mode the next time it starts up) 
  3. Then expand SQL Server Network Configuration, and click on Protocols for your SQL instance. 
    1. Then right-click Named Pipes, go to properties, and change the Pipe Name to just \\.\pipe\sql\query (This will allow you to connect to the SQL instance via sqlcmd) 
  4. Then start the SQL Server service again. 
  5. Once the service has started open up a Command Prompt on the PC and type sqlcmd. 
    1. At the 1> Prompt, enter CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret1’ and press ENTER (Where TopSecret1 is the password you would like to use for the recovery account) 
    2. At the 2> Prompt, enter Go and press ENTER 
    3. At the next 1> Prompt, enter sp_addsrvrolemember ‘recovery’,’sysadmin’ and press ENTER 
    4. At the 2> Prompt, enter go and press ENTER 
  6. After creating the recovery account, go back into SQL Server Configuration Manager, stop the manager and undo the changes you previously made to the Startup Parameters and the Named Pipes configuration. (Remove –m; from Startup Parameters for SQL Server services and change Pipe Name back to what it was originally) 
  7. Then login to a PC or Server that has Microsoft SQL Server Management Studio installed and connect to the SQL Instance on that PC with the recovery account you just created. 
  8. Once you’re connected to the SQL Instance, go to Security -> Logins, then right-click the sa account and select Properties.  In the Properties screen, click Status on the left.  Then uncheck the box for Login is locked out then click OK. You will get a prompt that the Password needs to be reset, so click General on the left. For Password and Confirm Password enter the password you’d like to use and click OK. 
  9. Go back to the SQL Server Configuration Manager on the PC and restart the SQL Server service. 
  10. After restarting the services you should now be able to access the DB with the sa account. 
  11. After verifying that everything is working correctly, log back into the SQL Instance through SQL Management Studio, but this time use the SA account you unlocked. Go to Security -> Logins and delete the recovery account you created earlier.

 

  Not sure if it will work for all versions of SQL, but it worked on SQL 2008 R2 Express

Networking, SQL, SA,
 

After migrating an Orion server to its own standalone server and a separate SQL server (the server was installed with a SQL Express instance running on the same VM). During the re-setup process, we needed to enter the SA account credentials to log into the migrated database. We found the username as “SolarWindsDatabaseUser”, but had no idea what the password was. The original setup never prompted to enter the password and seemed to generate it itself. After some research, we found it is stored in SWNetPerfMon.db in the Orion subfolder of the install – C:\Program Files (x86)\SolarWinds\Orion\SWNetPerfMon.db. This is just a plaintext file that you can open in notepad/wordpad.
Networking, SQL, Orion,
 

When you install a second SQL Reporting Services instance, it will place the databases in the default SQL instance by default. This happens even if both SQL instances are named instances. To place the second SQL Reporting Services databases in the correct SQL instance, you must do the following. [more]

  1. Open SQL Reporting Services Configuration Manager
  2. Select the Report Server you want to manage
  3. Click the Databases tab > Change Database
  4. Choose Create a new report server database
  5. Enter the server name and SQL instance where you want the SQL Reporting Services databases installed
  6. Take the defaults through the rest of the install and enter credentials as needed
  7. Install your application and point it to the second SQL instance where you just setup the new SQL Reporting Services instance.
Networking, SQL, SQL Reporting Services,