Commands to truncate a SQL Server log file?

In management studio:

  • Right-click the database, choose properties, then options.
  • Make sure “Recovery model” is set to “Simple”, not “Full”
  • Click Ok
  • Right-click the database again, choose tasks -> shrink files
  • Change file type to “log”
  • Click ok.

Alternatively, the SQL to do it:

 DBCC SHRINKFILE (mydatabase_Log, 1)

An error while installing SQL Server (express) 2008 R2

Error text:

“************** Exception Text **************
System.Configuration.ConfigurationErrorsException: An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly ‘System, Version=, Culture=neutral, PublicKeyToken=……”

Caused by a LandingPage.exe error. It couldn’t bind to System assembly. Delete files from (users_profile)\AppData\Local\Microsoft_Corporation directory.


SQL server login failed: Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

If the SQL server after we install the server alias (CNAME in DNS), the server can not connect to the name.

Disable NTLM reflection protection

Create a new DWORD with the name DisableLoopbackCheck and value 1 in the registry key:

Restart the computer. Done.

This opens it for Man In The Middle attacks, not use in public machine, only for test!

Update #2: (a correct solution)

Register a SPN for the SQL Service
SetSpn -A MSSQLSvc/_fqdn_name_:1433 DOMAIN\sql_service_account

Installing Operations Manager 2007 R2 on SQL 2008 R2

Microsoft released a knowledge base article  (KB2425714) with the following steps to perform the installation.

In short:

  • Use the DBCreateWizard tool to install the OperationsManager database
  • Install the first management server
  • Use the DBCreateWizard tool to install the OperationsManagerDW database
  • On the SQL Server Reporting Services server, rename the local group SQLServerReportServerUser$<hostname>$MSSRS10_50.<SQLInstanceName> to SQLServerReportServerUser$<hostname>$MSSRS10.<SQLInstanceName>
  • Install System Center Operations Manager 2007 R2 Reporting
  • Rename the local group SQLServerReportServerUser$<hostname>$MSSRS10.<SQLInstanceName> back to original name SQLServerReportServerUser$<hostname>$MSSRS10_50.<SQLInstanceName>

Operations Manager 2007 R2 does not show the active alerts, and error is sometimes the console Microsoft.EnterpriseManagement.Common.ServerDisconnectedException

The info file is not appropriate and thoughtful reading: CU5 after the patch, you must manually run two SQL scripts.

Manual operations that must be performed after you update the root management server and Data Warehouse.

This update contains fixes that must be manually applied. These fixes are applied by running the \SQLUpdate\CU5_DataWarehouse.sql file against the Operations Manager data warehouse (OperationsManagerDW) and by running the \SQLUpdate\CU5_Database.sql file against the Operations Manager database (OperationsManager).

To do this, follow these steps:

  1. Log on to the computer that hosts the Operations Manager 2007 database by using a user account that has database system administrator (SA) rightsto the Operations Manager 2007 database instance. To perform the database update remotely, log on to a computer that hosts SQL ServerManagement Studio by using a user account that has the appropriate SA rights to the Operations Manager 2007 database.
  2. Run SQL Server Management Studio.
  3. In the Connect to Server dialog box, connect to the instance of SQL Server that hosts the Operations Manager database. The default database name is OperationsManager.
  4. Click New Query on the toolbar.
  5. From the SQL Editor toolbar, use the Available databases option to select the Operations Manager database.
  6. On the File menu, click Open, select the \SQLUpdate\CU5_Database.sql file that was extracted by the Windows installer (.msi file), and then click Open.
  7. When the file is loaded, click Execute in the SQL Editor toolbar.
  8. For the Operations Manager data warehouse, repeat steps.However, connect to the instance of SQL Server that hosts the Operations Manager data warehouse, and then run the \SQLUpdate\CU5_DataWarehouse.sql file.