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

——————————Data file size—————————-
if exists (select * from tempdb.sys.all_objects where name like ‘%#dbsize%’)
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default (‘NA’), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go

insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Status”)) ,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Recovery”)),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS Free_Space_MB
from sys.database_files  where type=0 group by type’

go

——————-log size————————————–
if exists (select * from tempdb.sys.all_objects where name like ‘#logsize%’)
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go

insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files  where type=1 group by type’

go
——————————–database free size
if exists (select * from tempdb.sys.all_objects where name like ‘%#dbfreesize%’)
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))

insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
‘use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ”MB”)
,”unallocated space” = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) – convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ” MB”)
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions’
———————————–

if exists (select * from tempdb.sys.all_objects where name like ‘%#alldbstate%’)
drop table #alldbstate
create table #alldbstate
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))

–select * from sys.master_files

insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,’status’)),recovery_model_desc from sys.databases
–select * from #dbsize

insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> ‘online’

insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> ‘online’

insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> ‘online’

select
@@SERVERNAME as ‘DB Servername’,
d.Dbname as ‘Database Name’,d.dbstatus as ‘Database Status’,d.Recovery_Model as ‘Database Recovery Model’,
(file_size_mb + log_file_size_mb) as ‘Database Size’,
d.file_Size_MB as ‘Data File Size in MB’,d.Space_Used_MB as ‘Datafile Space Used in MB’,d.Free_Space_MB as ‘Datafile Freespace in MB’,
l.Log_File_Size_MB ‘Log File size in MB’,log_Space_Used_MB as ‘Logfile Space Used in MB’,l.log_Free_Space_MB as ‘Logfile Free Space in MB’,fs.Freespace as ‘Database Freespace in MB’,
case when  sda.[mirroring_state] = NULL then ‘No’
when sda.[mirroring_state] between 0 and 6 then ‘Yes’
else ‘No’ end as ‘Is Mirrored’
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name join sys.databases sd
on d.Dbname = sd.name join sys.database_mirroring sda
on sd.database_id = sda.database_id

order by d.Dbname

——————–Enabling xp_cmdshell ————————

sp_configure ‘show advanced options’, 1
go
reconfigure with override
go
sp_configure ‘xp_cmdshell’, 1
go

——————–
reconfigure with override
if exists (select 1 from sysobjects where name like ‘files_total_space’)

begin

drop table files_total_space

end

go

if exists (select 1 from sysobjects where name like ‘drives_db_details’)

begin

drop table drives_db_details

end

go

DECLARE     @Drive TINYINT,

@SQL nVARCHAR(100)

SET   @Drive = 97

— Setup Staging Area

create  TABLE drives_db_details

(

Drive CHAR(1),

Info NVARCHAR(80)

)

Create table files_total_space

(

DBName nvarchar(255),

Logical_FileName nvarchar(255),

DB_Size float,

DB_File_Path nvarchar(500),

DB_File_Drive nvarchar(4),

Drive_Total_Space nvarchar(100),

Available_Free_Space nvarchar(100)

)

insert into files_total_space(DBName,Logical_FileName,DB_size, DB_File_Path )

select DB_NAME(dbid), name,(size * 8), [filename]  from master..sysaltfiles

where filename not like ‘%mssqlsystemresource%’

WHILE @Drive <= 122

BEGIN

SET   @SQL = ‘EXEC XP_CMDSHELL ”fsutil volume diskfree ‘ + CHAR(@Drive) + ‘:”’

INSERT      drives_db_details

(

Info

)

EXEC  (@SQL)

UPDATE      drives_db_details

SET   Drive = CHAR(@Drive)

WHERE Drive IS NULL

SET   @Drive = @Drive + 1

END

declare @drive_name nvarchar(3)

declare @total_space nvarchar(100)

declare @available_space nvarchar(100)

declare @exec_stmt nvarchar(1000)

declare drive_details_cur cursor for

SELECT            Drive,

str(SUM(CASE WHEN Info LIKE ‘Total # of bytes             : %’ THEN (((CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float)/1024)/1024)/1024) ELSE CAST(0 AS BIGINT) END), 5, 1) AS [Total Space in GB],

str(SUM(CASE WHEN Info LIKE ‘Total # of free bytes        : %’ THEN (((CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float)/1024)/1024)/1024)  ELSE CAST(0 AS BIGINT) END),5, 1) AS [Available Free Space in GB]

FROM        (

SELECT      Drive,

Info

FROM  drives_db_details

WHERE Info LIKE ‘Total # of %’

) AS d

GROUP BY    Drive

ORDER BY    Drive

open drive_details_cur

fetch drive_details_cur into @drive_name, @total_space, @available_space

while (@@fetch_status = 0  )

begin

update  files_total_space set DB_File_Drive = upper(@drive_name), Drive_Total_Space = @total_space, Available_Free_Space = @available_space

where SUBSTRING(DB_File_Path, 1,1) = @drive_name

fetch drive_details_cur into @drive_name, @total_space, @available_space

end

close drive_details_cur

deallocate drive_details_cur

–Final Output

select @@SERVERNAME as ‘DB Servername’, DBName as [Database Name], Logical_FileName as [Logical File Name],

cast(str((DB_Size/1024), 10, 2) as nvarchar(20)) + ‘ MB’ as [Database Size], DB_File_Path as [Physical File Path],

DB_File_Drive as [File Drive], Drive_Total_Space as [Total Space on Drive in GB],

Available_Free_Space as [Space Available in GB] from files_total_space

drop table files_total_space

drop table drives_db_details

go
——————Disabling xp_cmdshell—————–

sp_configure ‘xp_cmdshell’, 0
go
reconfigure with override
go
sp_configure ‘show advanced options’, 0
go
reconfigure with override
go

GAM and SGAM (Global Allocation Map & Shared GAM):

GAM: Extents have been allocated: 1 – Free space 0 – No space

SGAM: Mixed Extents have been allocated: 1 – Free Space + Mixed Extent and 0 – No space

Each GAM / SGAM covers 64000 extents – 4 GB

PFS (Page Free Space): Percentage of free space available in each page in an extent.

DCM (Differential Changed Map):  This tracks the extents that have changed since the last BACKUP DATABASE statement. 1 – Modified, 0 – Not modified

BCM (Bulk Changed Map): This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. 1 – Modified, 0 – Not modified (Used only in bulk logged Recovery model)

In each data file pages are arranged like below

Along with that we have three different data pages

Data

Index

Text/ Image (LOB, ROW_OVERFLOE, XML)