Installing SQL Server from Command Prompt using Update Source:

Open Command Prompt using “Run as Administrator” elevated privileges and run setup as follows:

Note: Make sure there are no other SQL Instances are running on this current node where you are starting installation.

Browse to the installation folder where you have setup.exe and start installation

First Node:

setup.exe /action=installfailovercluster /updatesource=D:\software\CU3

Additional Nodes:

setup.exe /action=addnode /updatesource= D:\software\CU2

 D:\software is the location where you have your updates for SQL Server

For a standalone you can do a similar thing:

setup.exe /action=install /updatesource=c:\sql2012cu3

setup.exe /action=install /updatesource=\\sharedpath\share\sql2012cufiles\
Query:

SELECT DB_name() as DBName,name as [username], type_desc as usertype, create_date, modify_date FROM sys.database_principals where (type='S' or type = 'U') and name not in 
('dbo','guest','information_schema', 'sys')
select distinct @@Servername as [Server Name], sd.name as [DB Name], HARS.role_desc as 'Role Type',
rs.synchronization_state_desc, rs.synchronization_health_desc,
rs.database_state_desc, 
CASE rs.is_suspended WHEN 0 THEN 'NO' WHEN 1 THEN 'YES'   END AS [Is Suspended],
 CASE rs.suspend_reason WHEN 0 THEN 'User Action'
 WHEN 1 THEN 'Suspend from Partner'
 WHEN 2 THEN 'Redo'
 WHEN 3 THEN 'Capture'
 WHEN 4 THEN 'Apply'
 WHEN 5 THEN 'Restart' 
 WHEN 6 THEN 'Undo'
 WHEN 7 THEN 'Revalidation'
 WHEN 8 THEN 'Error in the calculation of the secondary-replica synchronization point' END AS [Suspended Reason], 
  rs.suspend_reason_desc as [Suspended Reason], rs.last_commit_time
from sys.dm_hadr_database_replica_states rs
Join sys.databases  sd
on rs.database_id = sd.database_id

JOIN sys.dm_hadr_availability_replica_states hars
ON rs.group_id =  hars.group_id

JOIN sys.availability_replicas
ON hars.group_id =  sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_group_states 
ON sys.dm_hadr_availability_group_states.group_id =  sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states
ON sys.dm_hadr_availability_group_states.group_id =  sys.dm_hadr_availability_replica_cluster_states.group_id

JOIN sys.dm_hadr_database_replica_states HDRS
ON sys.dm_hadr_availability_group_states.group_id =  HDRS.group_id

WHERE operational_state_desc IS NOT NULL
AND rs.database_state_desc IS NOT NULL

Start SQL Services from Cmd Prompt

Posted: April 14, 2016 in General

Hello All,
Today, I was facing an issue on on my most Critical Cluster where many SQL Instances have been installed. I was trying to fail-over one of my SQL Instance to another one. But, SQL Services dependency resources were coming ONLINE and SQLServr.exe was not coming online. I tried to restart SQL Services multiple times through SQL Server Configuration Manager, But no luck. There was no error messages recorded on EventVwr, Cluster Events & Cluster Log etc. After a long try, I tried to start SQL Services using Command prompt using the command prompt as follows:

You can use either one the command as follows:

Default Instance:

net start “SQL Server (MSSQLSERVER)”

-or-

net start MSSQLSERVER

Named Instances:

net start “SQL Server ( instancename )”

-or-

net start MSSQL$ instancename

After running this command, I straight away got a clear error message saying that. My SQL Services running under Service account was failing due to “Password Expiry”.

You can also use this command to run SQL Services using startup options as follows:

-d master database Data file path

-e SQL Server Errorlog path

-l master database log file path

-f Start SQL Services with minimal Configuration

Command:

net start “SQL Server (MSSQLSERVER)” /f /m

-or-

net start MSSQLSERVER /f /m

If parameter paths are not provided parameters. SQL Server will use the default file paths.

Command to Start SQL Server Agent Services:

Default Instance:

net start “SQL Server Agent (MSSQLSERVER)”

-or-

net start SQLSERVERAGENT

Named Instance:

net start “SQL Server Agent( instancename )”

-or-

net start SQLAgent$ instancename

 

Query:
Open Powershell Console and run the query as follows:

$cluster = Get-Cluster 'WindowsClustername'
foreach ($node in Get-ClusterNode -Cluster $cluster){
$tmp += Get-WmiObject -Query "SELECT * FROM Win32_Volume WHERE FileSystem='NTFS'" -ComputerName $node.nodename
}
$tmp | Select-Object Label, Name, Capacity, Freespace | Out-File D:\output_1.csv
Query:
Using this query you can size for all databases individual File details

SELECT 
    db.name AS DBName,
    saf.name AS FileName,
    CASE
    WHEN
      RIGHT(RTRIM(mf.physical_name), 3) = 'mdf'
     THEN
     'Primary Data File (MDF)'
     WHEN
      RIGHT(RTRIM(mf.physical_name), 3) = 'ndf'
     THEN
     'Secondary Data File(NDF)'
     WHEN
      RIGHT(RTRIM(mf.physical_name), 3) = 'ldf'
     THEN
     'Log File (LDF)'
     END AS FileType,
    cast(convert(DECIMAL(15, 2), saf.size)* 8192 / 1048576 as decimal(10,2)) AS [FileSize in MB],
    --mf.file_id as FileID,
    mf.Physical_Name AS [DB File Location]
FROM
    sys.master_files mf, 
    sys.databases db, sysaltfiles saf 
    where db.database_id = mf.database_id and
    mf.database_id = saf.dbid and 
    saf.dbid = db.database_id and 
    saf.fileid = mf.file_id
    order by dbname, mf.file_id ASC

Script:

use tempdb
GO

DBCC FREEPROCCACHE — clean cache
DBCC DROPCLEANBUFFERS — clean buffers
DBCC FREESYSTEMCACHE (‘ALL’) — clean system cache
DBCC FREESESSIONCACHE — clean session cache
DBCC SHRINKDATABASE(tempdb, 10); — shrink tempdb
dbcc shrinkfile (‘tempdev’) — shrink db data file, include individual data files one by one
dbcc shrinkfile (‘templog’) — shrink log file
GO

Note: Please be mindful when u run this script. Please use this only when you don’t have a choice of restarting SQL Services and there is huge space crunch issue on TEMPDB drive.

Cleaning procedure cache, buffer caches etc will have negative impact on the database performance itself until those are not re-created. I would not do this on PROD.

How to Login to SQL Server with NT AUTHORITY\SYSTEM using Microsoft PSExec Tool when there is no SysAdmin account

How to Login to SQL Server with NT AUTHORITY\SYSTEM using Microsoft PSExec Tool when there is no SysAdmin account

Problem

How to Login to SQL Server when you forgot password for SA/Admin account? Sometimes, We may forgot our SA account password or the people who have admin access to SQL Server might have left organization/Company without sharing passwords to anyone or giving SysAdmin access to anyone else in the team. How can we login to SQL Server at that time?

Solution

Microsoft, PSExec tool gives us an option to run a remote process or an application using default System account, if SYSTEM account has permission on that application (SQL Server).

PSExec gives us an opportunity to login to SQL Server using NT AUTHORITY\SYSTEM.

You can download PSExec tool bundle from this link as follows:

PS Exec Download Link

use command prompt and type following command:

For Ex: If you have downloaded PSExec Tool into your E:\ on your server. You have to run this command as follows:

E:\PSTools>psexec -i -s "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

CommandPrompt

Fig -1 Running PSExec using Command Prompt


Paramaters which you should use with PSExec Tool

–i allow the program to run so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session.

-s runs the process in SYSTEM account.

The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will already populated with windows Authentication User Name as NT AUTHORITY\SYSTEM . If NT AUTHORITY\SYSTEM has a login authority to the server, you will be able to login to the SQL Server. If you do not use “–i” switch, you won’t be able to launch management studio! So, make sure you use –i and –s to launch/Connect SQL Server using SSMS.

Normally, This user id is not disabled most of the time! However, if this login ID is already disabled for logging into SQL Server.. then you won’t be able to login to SQL Server using this technique.

Script:

DECLARE @dbname nvarchar(max) — database name
DECLARE @logfilename nvarchar(max)
DECLARE @QueryStr nvarchar(max)
DECLARE @QueryStr2 nvarchar(max)

DECLARE db_cursor CURSOR FOR
SELECT instance_name AS DatabaseName
FROM
(
SELECT *
FROM sys.dm_os_performance_counters
WHERE  counter_name IN
(
‘Data File(s) Size (KB)’,
‘Log File(s) Size (KB)’,
‘Log File(s) Used Size (KB)’,
‘Percent Log Used’
)
AND instance_name != ‘_Total’
)

AS Src
PIVOT
(
MAX(cntr_value)
FOR counter_name IN
(
[Data File(s) Size (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)],
[Percent Log Used]

)
)
AS pvt
Where [Percent Log Used]  >75 AND
DATABASEPROPERTYEX(instance_name, ‘Recovery’) IN (‘FULL’,’BULK_LOGGED’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @logfilename = (Select name from master..sysaltfiles where dbid in
(select database_id from sys.databases where name = @dbname and fileid = 2))

SET @QueryStr = ‘BACKUP LOG ‘ + @dbname + ‘TO DISK = N”Nul” WITH COMPRESSION’
EXECUTE (@QueryStr)
–PRINT @QueryStr

SET @QueryStr2 = ‘DBCC SHRINKFILE(‘ + @logfilename + ‘,100)’
EXEC (@QueryStr2)
–PRINT @QueryStr2

FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

 

Query:
If exists (select * from tempdb.sys.all_objects where name like ‘#DB_Frag_Report_Temp%’ )
drop table #DB_Frag_Report_Temp
create table #DB_Frag_Report_Temp
(DatabaseName varchar(100),ObjectName varchar(100),Index_id int, indexName varchar(100),avg_fragmentation_percent float,IndexType varchar(100),Action_Required varchar(100) default ‘NA’)
go
insert into #DB_Frag_Report_Temp (DatabaseName,ObjectName,Index_id, indexName,avg_fragmentation_percent,IndexType)

exec master.sys.sp_MSforeachdb ‘ USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent, index_type_desc

— , record_count, avg_page_space_used_in_percent –(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent > 15′
go

update #DB_Frag_Report_Temp
set Action_Required =’Rebuild’
where avg_fragmentation_percent >30
go

update #DB_Frag_Report_Temp
set Action_Required =’Rorganize’
where avg_fragmentation_percent <30 and avg_fragmentation_percent >5
go

select * from #DB_Frag_Report_Temp