Fix : Message Executed as user: Proxy Domain account. The process could not be created for step 1 of job 0xD656A2765BDCF54F91F7D2CA16398CC3 (reason: A required privilege is not held by the client). The step failed.

I am running a job on SQL 2016 and server is windows 2012 R2. The job is run as proxy account which is domain account. In the job step: Type is Operating System(CmdExec), Command is Powershell as an example below.

PowerShell.exe "E:\PowerShell\FindLastRebootDate\ServerLastReboot.ps1"

The job failed with the error below.

Message Executed as user: Proxy Domain account. The process could not be created for step 1 of job 0xD656A2765BDCF54F91F7D2CA16398CC3 (reason: A required privilege is not held by the client).  The step failed.

I was able to fix the issue by.

Step 1. Change the service account running SQL Agent to Local service and restarted the SQL Agent
Step 2. Change the local service running SQL Agent to the previous service account back and restarted the SQL Agent

After that I run the job, the job ran successfully.

Thank you
Mya

Checking Server online Status - PowerShell

We managed 100 + servers and we want make sure they come back after monthly maintenance reboot. We will run the following script by confirming the servers are Up. I retrieve servername from a Table called SQLServers from Database - DBA_ServerDW from ServerInstance name SQLTEST.

$servername = invoke-sqlcmd -ServerInstance SQLTEST -Database DBA_ServerDW `
-Query "select ServerName from DBA_ServerDW.dbo.SQLServers
where servername not like '%\%'"
ForEach ($server in $servername)
{
   # Ping the machine to see if it's on the network
   $ServerN=$server.ServerName
   $results = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$ServerN'"
   $responds = $false  
   ForEach($result in $results) {
      # If the machine responds break out of the result loop and indicate success
      if ($result.statuscode -eq 0) {
         $responds = $true
         break
      }
   }
         If ($responds) {
      # Gather info from the server because it responds
      Write-Output "$ServerN responds"
   } else {
      # Let the user know we couldn't connect to the server
      Write-Output "$ServerN does not respond"
   }
}

Database Partition and Filegroups

HELLO 2015...

Currently, I am working on removing Data warehouse 2011 partition and filegroup which we do not want to keep it in data warehouse. The Data warehouse uses Range Right partitioning. 

I will use the following Partition Scheme and Function examples. I have bolded the partition scheme and function which I need to remove.

Partition Scheme

CREATE PARTITION SCHEME [TestPartitionScheme] AS PARTITION [YYYYMMPartitionFunction] TO ([FG1], [FG2011], [FG2011], [FG2011], [FG2011], [FG2011], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2015])

GO

Partition Function

CREATE PARTITION FUNCTION [TestPartitionFunction](date) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00.000', N'2011-02-01T00:00:00.000', N'2011-03-01T00:00:00.000', N'2011-04-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2012-02-01T00:00:00.000', N'2012-03-01T00:00:00.000', N'2012-04-01T00:00:00.000', N'2012-05-01T00:00:00.000', N'2012-06-01T00:00:00.000', N'2012-07-01T00:00:00.000', N'2012-08-01T00:00:00.000', N'2012-09-01T00:00:00.000', N'2012-10-01T00:00:00.000', N'2012-11-01T00:00:00.000', N'2012-12-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2013-02-01T00:00:00.000', N'2013-03-01T00:00:00.000', N'2013-04-01T00:00:00.000', N'2013-05-01T00:00:00.000', N'2013-06-01T00:00:00.000', N'2013-07-01T00:00:00.000', N'2013-08-01T00:00:00.000', N'2013-09-01T00:00:00.000', N'2013-10-01T00:00:00.000', N'2013-11-01T00:00:00.000', N'2013-12-01T00:00:00.000', N'2014-01-01T00:00:00.000', N'2014-02-01T00:00:00.000', N'2014-03-01T00:00:00.000', N'2014-04-01T00:00:00.000', N'2014-05-01T00:00:00.000', N'2014-06-01T00:00:00.000', N'2014-07-01T00:00:00.000', N'2014-08-01T00:00:00.000', N'2014-09-01T00:00:00.000', N'2014-10-01T00:00:00.000', N'2014-11-01T00:00:00.000', N'2014-12-01T00:00:00.000', N'2015-01-01T00:00:00.000')

GO

In the example, Range Right Partition Function keeps data earlier than 2011-01-01T00:00:00.000 (Lowest boundary) will go to the file group FG1 .  In Data warehouse database, I have created a view which will show me tables which are partitioned and their file groups.  I gave credit to Kendra Little from

http://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/

CREATE VIEW [dbo].[FileGroupDetail]

AS

SELECT  pf.name AS pf_name ,

        ps.name AS partition_scheme_name ,

        p.partition_number ,

        ds.name AS partition_filegroup ,

        pf.type_desc AS pf_type_desc ,

        pf.fanout AS pf_fanout ,

        pf.boundary_value_on_right ,

        OBJECT_NAME(si.object_id) AS object_name ,

        rv.value AS range_value ,

        SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows

                    ELSE 0

            END) AS num_rows ,

        SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,

        SUM(CASE ISNULL(si.index_id, 0)

                WHEN 0 THEN 0

                ELSE 1

            END) AS num_indexes

FROM    sys.destination_data_spaces AS dds

        JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id

        JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id

        JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id

        LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id

                                                        AND dds.destination_id = CASE pf.boundary_value_on_right

                                                                                    WHEN 0 THEN rv.boundary_id

                                                                                    ELSE rv.boundary_id + 1

                                                                                END

        LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id

        LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id

                                            AND si.index_id = p.index_id

                                            AND dds.destination_id = p.partition_number

        LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id

                                                        AND p.partition_id = dbps.partition_id

GROUP BY ds.name ,

        p.partition_number ,

        pf.name ,

        pf.type_desc ,

        pf.fanout ,

        pf.boundary_value_on_right ,

        ps.name ,

        si.object_id ,

        rv.value;

GO

--------------------

Now I want to know which table are partitioned and number of rows. I ran the following scripts

select 

partition_filegroup,

object_name,

sum(num_rows)

from ph.FileGroupDetail

where partition_filegroup in('FG2011')

group by partition_filegroup,object_name

To find out how many partition I need to remove from the partition.

select * from dbo.FileGroupDetail

where object_name = 'Table Name' and partition_filegroup = 'FG2011'

I am following Kendra Little video and built switch out tables for each partitioned table. In this example, we have 4 partition

Create a switch out table in the file group which you need to drop

CREATE TABLE [TEST_SWITCHOUT](

[CHTIDDD] [decimal](3, 0) NOT NULL,

[CHSEQQ] [decimal](5, 0) NOT NULL,

[CHTYPEE] [char](3) NOT NULL,

)

ON [FG2011]

GO

SWITCH OUT Script for each partition of the table

RAISERROR ('Switching out.',0,0)

ALTER TABLE TEST

SWITCH PARTITION 2 TO dbo.TEST_SWITCHOUT;

GO

Truncate table dbo.TEST_SWITCHOUT

Go

RAISERROR ('Switching out.',0,0)

ALTER TABLE TEST

SWITCH PARTITION 3 TO dbo.TEST_SWITCHOUT;

GO

Truncate table dbo.TEST_SWITCHOUT

Go

RAISERROR ('Switching out.',0,0)

ALTER TABLE TEST

SWITCH PARTITION 4 TO dbo.TEST_SWITCHOUT;

GO

Truncate table dbo.TEST_SWITCHOUT

Go

Drop table dbo.TEST_SWITCHOUT

Go

MERGE BOUNDARY POINTS

We are ditching the following months which we want to remove.

--2011

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-01-01T00:00:00.000')

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-02-01T00:00:00.000')

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-03-01T00:00:00.000')

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-04-01T00:00:00.000')

REMOVE FILE GROUP and FILE

Use YourDatabase

Alter Database YourDatabase Remove File  FG2011

go

USE [YourDatabase]

GO

ALTER DATABASE [YourDatabase] REMOVE FILEGROUP [FG2011]

GO

IF you have this error, run the script to see which object is still in the file group.

the file cannot be removed because it is not empty

select * from sys.partitions p

inner join sys.allocation_units a on a.container_id = p.hobt_id

inner join sys.filegroups f on f.data_space_id = a.data_space_id

where f.name='FG2011'

select * from sys.objects

where object_id = '949578421'

Update SQL Agent jobs to a new job owner

Good Bye... 2014

I dedicated all my 2014 work and script to my little brother, Aung Phyo Tha, who suffers from brain aneurysm and is still unconscious for 46 Days. Please pray for him to wake up very soon. Thank you.

The following script is to update all sql agent jobs to a new owner. I have tested the script and it worked. Please run it in Development first. Enjoy Scripting.....

Declare @Job_ID uniqueidentifier
Declare @sql nvarchar(4000)
Declare @NewJobowner varchar(10)

Set @NewJobowner = 'Domain\newowner'

Declare Job_Csr cursor Local for

select job_id from msdb..sysjobs_view
where enabled = 1

Open Job_Csr;
Fetch Job_Csr into

@job_ID

While @@FETCH_STATUS = 0

Begin


Exec msdb.dbo.sp_update_job @job_id = @job_Id,
@owner_login_name = @Newjobowner


Fetch Job_Csr into
@Job_ID

End

Close Job_Csr
Deallocate Job_Csr

Win 2012 IP Conflict - Power Management Setting on servers (Possible resolution)

We are having Ip Conflict issues with alwaysOn SQL servers.Below is alert message from SCOM.

Alert description: The system detected an address conflict for IP address XX.XX.XXX.XX with the system
having network hardware address 08-55-56-92-3P-FC. Network operations on this system may
be disrupted as a result

Comment

There is no duplicate ip address on ARP Table. Looks like a failover event if not clearing itself up and throwing this warning.

Issues

It took Cluster Failover manager offline and caused availability group error,

Possible Solution

http://www.theemailadmin.com/2013/12/troubleshooting-dag-failovers-random-drops/

NIC have setting “Allow the computer to turn off this device to save power” is set, unless somone has a good reason we would want a server NIC to sleep I would suggest this setting be cleared.

Connecting to a database using Domain account via SMS

I have my windows login given by my company. I use my windows authentication to login to my computer and then connect to any databases via SQL Server Management Studio(SMS) . 

As a DBA, I will need to grant a proper access a domain account (service accounts for some applications) to databases. Sometimes, they have issues connecting to databases though I give them proper access. So, I make sure that the domain account which is given access to my database is able to connect to the database.

In order to connect to database using a domain account,

First, I make a shortcut of SQL Server Management Studio on my desktop.

Second,  update the Target command with the following command. The path "c:\program files..." might be different because it depends on the location of your SQL server management studio is installed 

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\ACCOUNTNAME "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Then, double click on the shortcut and enter domain account password. 

SQL Server Management Studio will open and you will see your windows login(domain\yourlogin) but you are connected to a database as the domain account. Type sp_who2 in on the server and you can see your login as a domain account.

sp_MSforeachtable

Today, Grant Fritchey tweeted about sp_MSforeachtable . What will we see if we run the following statement on Azure database?

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

It retruns
Could not find stored procedure sp_msforeachtable.

It is deprecated on Azure. Then, I tried to run on local development database. It returns total record count in each table in database. It will be useful for a DBA in future. Next step, I looked up sp_Msforeachtable in Google. I found two more useful scripts.

Checking integrity of tables in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

Checking space used of each table in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';


Checking space used of each database

declare @cmd varchar(500)
set @cmd='use [?];exec sp_spaceused '

exec sp_MSforeachdb @cmd

Daily DBA Cheatsheet

1. Creating linked server with local name

EXEC master.dbo.sp_addlinkedserver @server = N'mylocalname', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'servername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylocalname,@useself=N'False',@locallogin=NULL,@rmtuser=N'dblink',@rmtpassword='########'

2. SQL Server Data Type Conversion Chart

http://www.microsoft.com/en-us/download/details.aspx?id=35834

3. Filtered Index

I want to create a unique key with multiple null. To fulfil this, I can create unique filtered index.

Create Unique NonClustered Index NonClustomer_Emailaddress on Customer
(emailaddress)where emailaddress is not null

4. sys.processes -  Find how many processes running on a instance

SELECT DB_NAME(dbid) as 'Database Name', COUNT(dbid) as 'Total Connections' FROM master.dbo.sysprocesses WITH (nolock)WHERE dbid > 0GROUP BY dbidSELECT
@@MAX_CONNECTIONS AS 'Max Allowed Connections'

5. Server Edition & Version

select
SERVERPROPERTY('Edition'),
SERVERPROPERTY('ProductLevel'),
SERVERPROPERTY('BUildClrVersion'),
SERVERPROPERTY('ProductVersion')


6. Row Count ( The following code is from http://www.sqlservercentral.com/articles/T-SQL/67624/)

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY o.NAME 

7. DBCC

The following DBCC, DMVs and sp commands help me.

dbcc inputbuffer(SPID)

dbcc sqlperf(logspace)

sp_who2

sp_configure

sp_spaceused

select * from sys.sysprocesses

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB FROM sys.database_files

select * from sys.masterfiles

msdb.dbo.sp_help_job

exec master.dbo.xp_sqlagent_enum_jobs 1,garbage 

SQL Server 2012 on Wins 2012

SQL Server 2012 standard edition on Windows Server 2012 Data Center

Today, I received a brand new virtual machine installed with Windows Server 2012 Data Center edition from network administrator.

Here is specs of the new machine:

Operating system : 64 - bit Windows server 2012 Data Center

Processor: 2 Dual core processors (4 virtuals CPUS, 2 Sockets)

Memory : 12 GB

Local Drive : 80 GB

Data Drive : 200 GB (RAID 5)

Log Drive : 100 GB (RAID 10)

I was surprised by windows server 2012 UI.Window Server 2012 looks like it was truly made for the computer illiterate. Sad!  To make my life easy on the box, I pinned all tools/apps I needed on Task bar.

First, I will need to install SQL Server 2012 standard edition in my new server.
Before installing SQL Server 2012, I installed .net 3.5 from add features via server manager. net.3.5 installation is very important step prior to SQL Server 2012 is installed. Because it will fail SQL Server installation.

Install .net 3.5 from add and remove features. We will need to have window 2012 installer to point for installation. After installing .net 3.5, run windows update. Then, restart the server.

The server came back up. I have maunted SQL Server 2012 iso and it is ready for me to install. So, I clicked on the setup.exe and I recived .net error.

An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies.

Repairing the .NET 4.0 framework didn't solve the issue. Neither did removing all SQL stuff through 'Add or Remove Programs'. Looking into the error a bit further...
 The system cannot find the file specified. (C:\Documents and Settings\_USERNAME_\Local Settings\Application Data\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0\user.config line 5) ---> System.IO.FileNotFoundException: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

So the problem probably wasn't that System.dll couldn't be found, but some user.config file for the landingpage executable.

The solution is as simple as it is radical: remove the entire (temporary) folder 'C:\Documents and Settings\_USERNAME_\Local Settings\Application Data\Microsoft_Corporation' (where USERNAME_ is, of course, the current username). After this the setup should start up without any problems.

After deleting Microsoft _Corporation folder, the error is fixed.

SQL Server 2012 installation continues...

I left reporting service at last since I want to make sure Database engine is installed successfully. After DB engine is done successfully, reporting service installation continues..

To test client connection, try to connect from your local machine to database server. I recived the error.

To fix this, enable TCP port 1433 in windows firewall.

SQL server 20012 SP1

Service pack 1 can be downloaded from microsoft web site.

http://www.microsoft.com/en-us/download/details.aspx?id=35575

So, I downloaded service pack1 from microsoft and applied it. Now, I will need to restart the server. Go to settings, and click on power button to restart the server.

The server came back up. To check the database is applied service pack1 , I run the following script.

Next, I want to make sure all the services are running on service account  and automatic start up. I added the service account in administrator group.

Last, I am ready to migrate my database from development to this brand new server. I would like to thank to network administrator to rebuild this machine with windows server 2012 data center edition.