SQL

WSUS – Connection to SUSDB

Posted by robd on March 07, 2017
SQL / No Comments

Today I wanted to migrate the WSUS server to a SQL server and as such you have to connect to:

But I kept getting:

Long story short I opened SQL Management Studio as the local admin (not my domain admin) and bobs your uncle.

Tags: ,

VMware vCenter Server 5.5 Database is Full

Posted by robd on May 27, 2015
SQL, vmware, vSphere / 9 Comments

So today a vCenter Server Service wouldnt start Java was going mental and we kept seeing event IDs 1105 and 1827.  So what this means is our SQL Express instance reached the limits the size of databases i.e. 10GB on SQL Express 2008 R2.

Error_event1

Error_event

You can confirm this by looking at the size of the VIM_VCDB.MDF file in c:\program files\Microsoft SQL Server\MSSQL10_50.VIM_SQLEXP\MSSQL\Data, if its 10GB then your in trouble.

So the first thing to do is fire up Microsoft SQL Server Management studio and run a standard report against the VIM_VCDB by right clicking the DB > Reports > Standard Reports > Disk Usage by Top Tables:

Error1

From here you can see which tables are causing the problems, in my case its dbo.VPX_EVENT_ARG which is massive.

So from here you need to purge the above table:

  1. From Microsoft SQL Server Management studio
  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.
    Error3

  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.

Error5.5

Error5

  1. Next run a built-in stored procedure to shink the logs:
  2. Go to VIM_VCDB > Programmability > Stored Procedures.

Error6

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

Now the records have been purged you need to shrink the DB:

  1. As before connect using SQL Server Management Studio.

ExecuteScript

2. Right click on the VIM_VCDB database and New Query, when the white SQLQuery box opens type the below and click ! Execute

3. It’ll start running and when its finished you should see:

ExecuteScript2

Check the size of the file again and you should be able to start the services.

 

Tags: , , , ,

How to Restore a Database with a SQL Script

Posted by robd on January 17, 2012
SQL / No Comments

Now you’ve backed up your database and ruined your server, its time to restore it, here’s how:

 

How to backup a Database with a SQL Script

Posted by robd on January 17, 2012
SQL / No Comments

Often at work I’ll need to back-up a SQL server as I’m about to apply patches to the server or do something worrying with power etc!

 

There’s loads of ways to backup SQL:

–          If it’s a VM snapshot the bugger (potentially slow depending on the resources)

–          Log on to the Management Studio and right click the database you want to backup and follow all the instructios

–          Or my favourite method, Log onto the Management Studio start a Query for your desired database and run the following:

 

 

Its fast and straight forward and with the stats line you can monitor the progress!

Copy Protected by Chetan's WP-Copyprotect.