Monday 30 April 2012

Error property owner is not available for Dataabse. This property may not exists for this object, or may not be retrievable due to insufficient access rights.

Got this Error property owner is not available for Dataabse. This property may not exists for this object, or may not be retrievable due to insufficient access rights. I couldn’t go into the Database properties to change some settings.


Here is how I fixed it:

Run this Stored Procedure to check if the owner is to figure out who is owner of the database.

sp_helpdb

For us the owner was set to NULL, executed the following SQL Query on the database.

USE VIM_VCDB
EXEC sp_changedbowner 'sa'

Note VIM_VCDB was the database name

This worked for me now able to access the propety pane.


Vcenter Server Database Size Issue VPX_EVENT

We had an issue with VCentre Server database the database size was continuously growing and we reached a limit as we use SQL Server 2005 Express Edition.

SQL Express edition 2005 has a hard limit of 4 GB. To identify the problem here is what we did:

1)               First executed the tablesizesql query on the VCP Database which will display the size of each table. And, the size is available in Data column and it’s in KB. You’ll have to calculate the number/1024 to get the size in MB.


2)            We found the following tables occupying majority of the space:

VPX_EVENT
VPX_EVENT_ARG

3)            Stopped the Vcenter Service.

4)            Run the truncatetables query on the VCP Database to release and  truncate the tables listed in Step 2.

alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT

truncate table VPX_TASK

truncate table VPX_ENTITY_LAST_EVENT

truncate table VPX_EVENT

truncate table VPX_EVENT_ARG

alter table VPX_EVENT_ARG add

constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade, constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references VPX_OBJECT_TYPE (ID)

alter table VPX_ENTITY_LAST_EVENT add

constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade

5)            Shrink the Database File (MDF). Its always good exercise to shrink Database files in small chunks as it takes long time than shrinking log files.

6)            Started the Vcenter Service

This resolved the issue.

SQL Server 2005: get table space and size in a Database

One of the most important tasks for a DBA is to get the size of tables and this command does the job pretty well in SQL Server 2005. Here is the query

begin try
SELECT
 (row_number() over(order by a3.name, a2.name))%2 as l1,
 a3.name AS [schemaname],
 a2.name AS [tablename],
 a1.rows as row_count,
 (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
 a1.data * 8 AS data,
 (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
   (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
 (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
   (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
 (SELECT
  ps.object_id,
  SUM (
   CASE
    WHEN (ps.index_id < 2) THEN row_count
    ELSE 0
   END
   ) AS [rows],
  SUM (ps.reserved_page_count) AS reserved,
  SUM (
   CASE
     WHEN (ps.index_id < 2) THEN
   (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
     ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
   END
   ) AS data,
  SUM (ps.used_page_count) AS used
 FROM sys.dm_db_partition_stats ps
 GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
 (SELECT
  it.parent_id,
  SUM(ps.reserved_page_count) AS reserved,
  SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name
end try
begin catch
select
 -100 as l1
, 1 as schemaname
,       ERROR_NUMBER() as tablename
,       ERROR_SEVERITY() as row_count
,       ERROR_STATE() as reserved
,       ERROR_MESSAGE() as data
,       1 as index_size
,   1 as unused
end catch

Friday 27 April 2012

SCCM Report: Count of laptops desktops at each site

SCCM Report: Count of laptops desktops at each site

Well this one was a quite good exercise. Got a request from manager to list how many laptops, desktops are there at each site. Site here is country or city

This report is not out of box in SCCM 2007 R2 config manager console.

First place to start is to look at the SCCM Schema from MS site 


but that will take some time to study and understand the tables, views and how they’re connected. I would suggest running this query on a copy of SCCM database (reporting db) and making sure it works for you before running it on Production.

In the next post I'll document you how to configure this as a report in SCCM and use subscription to schedule this report. Here is the SQL Query.


SELECT                      dbo.v_R_System.AD_Site_Name0 
  
as SiteNameAD,                 
                      ,count (CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
                   
                        WHEN 8  THEN 'Laptops'
                        WHEN 9  THEN 'Laptops'
                        WHEN 10 THEN 'Laptops'
                        WHEN 11 THEN 'Laptops'
                        WHEN 12 THEN 'Laptops'
                        WHEN 14 THEN 'Laptops'
                        WHEN 18 THEN 'Laptops'
                        WHEN 21 THEN 'Laptops' 
                        end ) as 'Laptops', 
                        count (CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
                        WHEN 3  THEN 'Desktops'
                        WHEN 4  THEN 'Desktops'
                        WHEN 5  THEN 'Desktops'
                        WHEN 6  THEN 'Desktops'
                        WHEN 7  THEN 'Desktops'
                        WHEN 15 THEN 'Desktops'
                WHEN 16 THEN 'Desktops'
                end ) as Desktops 
               
FROM         dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
                      group by dbo.v_R_System.AD_Site_Name0

Tuesday 17 April 2012

error failed to connet to the configuration database: an exception of type system.argumentnullexception was thrown. additional exception information: value cannot be null parameter name: service.

Error failed to connet to the configuration database: an exception of type system.argumentnullexception was thrown. additional exception information: value cannot be null parameter name: service.

I was adding a new WFE in an existing SharePoint farm and when I ran the configuration Wizard got the error:

 Failed to connet to the configuration database: an exception of type system.argumentnullexception was thrown. additional exception information: value cannot be null parameter name: service.

I figured out we moved our database servers from old to new sometime back and the configuration database had entries for the old database server name.

Solution 1: Created SQL Alias on the WFE server pointing to new databse server and ran the configuration wizard worked all good for me

Click on the below link to check the steps on how to create an SQL Alias

Solution 2: If the first one didn’t work, you may have to create a new configuration database but that has its own dependencies. Number of Solution packages you have , settings etc. it’s an addition overload but may have to do this if SQL Alias doesn’t help.

Login to the SharePoint Server
Click on All Programs > Microsoft SharePoint 2010 Products
Launch SharePoint 2010 Management Shell

Run the following command
New-SPConfigurationDatabase

This will ask for following details:
Database Name: New Configuration Database Name
Database Server: Database Server Name
Prompts for Farm Credentials, enter the same

Now run the Product and Configuration wizard this will solve the issue.


Monday 16 April 2012

Create SQL Alias on SharePoint

SQL Alias is something which you may have to create when you’re moving your database server. I had a requirement to move from an old database server to new one  and as a part of the migration had to create SQL Alias. This is how you create it.

You will have to create SQL Alias on each web front and application server of SharePoint.

Click on Start > Run and Type cliconfg
Click on Ok
Now enable TCP/IP Protocol.
Select TCP/IP and Click on Enable




Click on Alias Tab
Click on Add
Select TCP/IP from Network Libraries
And enter the old database server name in Server Alias
And set the Server Name to New Database Server Name
Click Ok

Tuesday 3 April 2012

Extract WSP from SharePoint Central Admin


Extract or Download WSP from SharePoint Central Admin
I was building a new infrastructure for the production environment and as a part of the process had to install solution packages from old to the new environment.
This power shall command was quite handy to download the WSP’s from SharePoint Central Admin to a local drive.

$sharepointfarm = Get-SPFarm
$sharepointfile = $sharepointfarm.Solutions.Item("extendeddiagnosticproviders.wsp").SolutionFile

$sharepointfile.SaveAs("c:\solutions\extendeddiagnosticproviders.wsp")