Tag Archives: SQL

VMware vSphere Client could not connect to vCenter version 5.1.0

Recently we had an issue, where we were unable to connect to one of our VMware ESXi vCenter’s (v5.1.0) with vSphere Client. Everything worked, all virtual machines were up, except vCenter.

Digging deeper, the log files revealed the following:
2014-09-19T09:01:09.494+03:00 [02156 warning 'Default'] [VdbStatement] SQL execution failed: cleanup_events_tasks_proc
2014-09-19T09:01:09.494+03:00 [02156 warning 'Default'] [VdbStatement] Execution elapsed time: 69482 ms
2014-09-19T09:01:09.494+03:00 [02156 warning 'Default'] [VdbStatement] Diagnostic data from driver is 42000:1:9002:[Microsoft][SQL Server Native Client 10.0][SQL Server]The transaction log for database 'VIM_VCDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2014-09-19T09:01:09.494+03:00 [02156 warning 'Default'] [VdbStatement] Bind parameters:
2014-09-19T09:01:09.494+03:00 [02156 error 'Default'] [Vdb::IsRecoverableErrorCode] Unable to recover from 42000:9002
2014-09-19T09:01:09.494+03:00 [02156 error 'Default'] [VdbStatement] SQLError was thrown: "ODBC error: (42000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]The transaction log for database 'VIM_VCDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases" is returned when executing SQL statement "cleanup_events_tasks_proc"
2014-09-19T09:01:09.494+03:00 [02156 error 'vpxservicesvpxdEventTaskCleanup'] [VpxdEventTaskCleanup] Error during Event/Task Cleanup: "ODBC error: (42000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]The transaction log for database 'VIM_VCDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases" is returned when executing SQL statement "cleanup_events_tasks_proc"
2014-09-19T09:01:09.494+03:00 [02156 warning 'VpxProfiler'] VpxUtil_InvokeWithOpId [TotalTime] took 69482 ms

So we need to clean up the Transaction log for the MS SQL server, or clean up some tables… so we found an article from VMware support – KB1025914.

From VMware, to purge the data in the VPX_EVENT table:

1. Connect to Servername\SQL Database and log in with the appropriate credentials.
2. Click databases to expand and select VIM_VCDB > Tables.
3. Right-click the dbo.VPX_PARAMETER table and select Open.

Note: If you are using SQL Server 2008, right-click the dbo.VPX_PARAMETER table and click Edit Top 200 Rows.
4. Modify event.maxAge to 30, and modify the event.maxAgeEnabled value to true.
5. Modify task.maxAge to 30, and modify the task.maxAgeEnabled value to true.

Note: To improve the time of the data cleanup, run the preceding steps in several intervals. To do this, ensure to keep the default value of event.maxAge and task.maxAge and perform step 6 to run the cleanup. Then, reduce the event.maxAge andtask.maxAge value by 60 and run the cleanup. Repeat the steps until the value is reached to 30 for the final cleanup process.
6. Run the built-in stored procedure:
a. Go to VIM_VCDB > Programmability > Stored Procedures.
b. Right-click dbo.cleanup_events_tasks_proc and select Execute Stored Procedure.

This purges the data from the vpx_event, vpx_event_arg, and vpx_task tables based on the date specified for maxAge.
c. When this has successfully completed, close SQL Management Studio and start the VMware Virtual Center Server service.
To ensure that the default Statistics Level is set to 1:

1. Using the vSphere Client, log into vCenter Server as an administrator.
2. Go to Administration > vCenter Server Settings > Statistics.
3. Under Statistics Intervals, ensure the Statistics Level column is set to 1.
4. To change the value, select the Interval Duration, click Edit and select Level 1 from the dropdown

To download SQL Management Studio 2008 R2 follow this Microsoft SQL Server 2008 R2 RTM – Management Studio Express.

Everything went smooth, except, we didn’t know the SQL server name, and SQL Server 2008 R2 didn’t help much. Later we found, that the name is “.\VIM_SQLEXP”, see the screenshot below.

Executing very large script on Microsoft SQL Server 2008 R2 fails

I was trying to execute very large .sql script in Microsoft SQL Server 2008 R2, that runs on Microsoft Windows Server 2008 R2.

First I tried to copy/paste script into Microsoft SQL Server Management Studio. The server has plenty of memory about 5 GB RAM, and script file is about 150 MB in size and line count is about 1 million, so I do not see problem here. Copy/paste part went ok, however after executing script and after long wait, Management Studio surrendered with “Query completed with errors”:
Microsoft SQL Server Management Studio
------------------------------
Cannot execute script.
------------------------------
ADDITIONAL INFORMATION:
Insufficient memory to continue the execution of the program. (mscorlib)
------------------------------
OK
------------------------------

bl-sql-1

Next I tried various combinations of T-SQL command line tools:
osql -S iDGZdb-dev -i test2.sql -U myuser
sqlcmd -S IDGZDB-DEV -i test2.sql -U myuser
osql -t 100000 -a 65535 ...

Still no success. Various errors appeared in console. Running locally on SQL server:
[SQL Server Native Client 10.0]
Shared Memory Provider: No process is on the other end of the pipe.
[SQL Server Native Client 10.0] Communication link failure

Running on remote machine:
[SQL Server Native Client 10.0]
TCP Provider: An existing connection was forcibly closed by the remote host.
[SQL Server Native Client 10.0]Communication link failure

Tried to search for Google and didn’t find any solution. There were many forums where users ask for help about this issue, but no single answer. Suggested “failed network equipment” is nonsense, if I run script locally, there should be no network involved.

Looked in the System event log under Application logs on the Windows Server where SQL Server resides. Luckily there was an error from the SQL Server:
Log Name: Application
Source: MSSQLSERVER
Date: 2013.02.04. 17:37:41
Event ID: 7836
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: iDGZdb-dev
Description:
A fatal error occurred while reading the input stream from the network. The maximum number of network packets in one request was exceeded. Try using bulk insert, increasing network packet size, or reducing the size of the request. The session will be terminated.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">7836</EventID>
<Level>2</Level>
<Task>2</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2013-02-04T15:37:41.000000000Z" />
<EventRecordID>223808009</EventRecordID>
<Channel>Application</Channel>
<Computer>iDGZdb-dev</Computer>
<Security />
</System>
<EventData>
<Binary>...some hex stuff...</Binary>
</EventData>
</Event>

But unfortunately, the error reveals very little about the problem. Didn’t get this part of suggestion “… or reducing the size of the request”. The request of 150 MB is too big for the latest edition of MS SQL? Seriously?

Note: Ok, ok… Microsoft SQL Server 2012 is out, and I am not an expert in this area, however, if MySQL can handle scripts in size of gigabytes and Oracle too, then I must be doing something very wrong, but again, then where are all suggestions in the net? Even StackOverflow has only guesses about “network equipment”.

Next, I tried to increase the packet size as suggested in System log message. More statements were executed from the SQL script, however, it still stopped with a memory error:
There is insufficient system memory in resource pool 'internal' to run this query.
Network packet size (bytes): 32576

How I solved this? I surrendered, and instead of wasting time with weird T-SQL error messages, I wrote a little C# script, that executes my file line by line. All of my SQL statements were one-liners. It took me about 1/2 hour to write this script and execute it.

Here is relevant code fragment:
SqlConnection connection = new SqlConnection(Properties.Settings.Default.YourConnectionString);
connection.Open();
System.IO.StreamReader file = new System.IO.StreamReader(yourFilename);
while ((line = file.ReadLine()) != null)
{
    try
    {
        SqlCommand command = new SqlCommand(line, connection);
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        LogErrorSomewhere(ex.Message, line);
    }
}

file.Close();

P.S. If you know, how to execute a such script using standard SQL tools, then you are welcome to leave me a comment.