There are scenarios where your windows account or SQL Server Service windows account is not working as per the expectation. Below are the most common issues observed if there is any issue with Windows Service account.
- Service Account is locked
- Service Account is disabled in domain
- Account is expired
- Need to change password
To check the above properties of domain account, follow the below steps:
Go to Start | Run and then type “dsa.msc”. That command will open the Active Directory Users and Computers console.
Next right click on the active directory group in the console and click on “Find”. Type the windows account name in the find dialog box and click on the “Find Now” button.
Right click on the windows account name from the search result –> Select Propertie –> Go to Account tab. Here you have to check whether account is locked out or expired or disabled in domain or needs the password changed.
If you find any of the above issue, work with AD or Windows team to fix it.
You can execute the below script to get the SQL Server Properties.
create table #server(ID int, Name sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver
declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT
(select Value from #server where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #server where Name = N'Language') AS [Language],
(select Value from #server where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],
(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@RegRootDir AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]
drop table #server
|Microsoft SQL Server
||English (United States)
||NT INTEL X86
||C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL
While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text option.
You can lock the windows/desktop using single keystroke “Windows” + “L”, instead of CTRL + ALT + DEL + ENTER
You can use the below script to identify the data, log and backup file drive.
@@SERVERNAME as svrName,
select distinct SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
select distinct SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
select distinct SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'