How to know db version or build no from a backup file? or How to get the exact version on which master db was backed up?

Generally we don't need this info for user db but when you have to recover or restore master db then you should have exact db version or build no else you cannot recover your db like i am giving you a scenario suppose you instance is corrupted now you are not able to access your sql server instance and even you don't have exact db version like 9.00.**** In this case you need exact build no else you can not recover your sql server Instance.To get this info you need a master db backup file.You can run below cmd to get this info:

Restore headeronly from disk='D:\MSSQL\Backup\master.bak'

Now output will show you lot much info like user name,LSNs,device type,server name etc but you should look into below three coloumns  to get exact version no of sql server.As per this output the version on which master db was backed up is 10.50.1600.Now you got the exact build so install sql server till this build no and restore master db easily.

SoftwareVersionMajor  SoftwareVersionMinor  SoftwareVersionBuild
10                                  50                                   1600




You can get another article regarding how to restore master db in this blog as well.Kindly find the link:Restore master db

How to recover or repair a suspect database in sql server

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

You can run below cmd to get about all errors due to whihc db went in suspect mode:

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

Once you have enough info about errors then your next step should be Repair.If error is showing datafiles missing then check your datafile drive connectivity from storage side if its missing then you dont need to do anythig just contact someone from storage or one who manages storage in your env and ask them to check data file drive is healthy or not.

And if there is another issue then you can repair your database from below cmds:

SP_RESETSTATUS [DBNAME]
go
ALTER DATABASE [DBNAME] SET EMERGENCY
go
ALTER DATABASE [DBNAME] SET SINGLE_USER;
go
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
ALTER DATABASE [DBNAME] SET Multi_USER;

Put your db name in place of [DBNAME] in above script.

Note:-REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

"Error 14258: Cannot perform this operation while SQL ServerAgent is starting. Try again later."

I saw this error today while i was diagnosing my log shipping issue in which primary and standby servers was not in sync.i checked log but i didn't find anything even Sql agent was running and all jobs was showing as successful.

Then i tried to bounce Agent service on primary node then i got below error:

"Error 14258: Cannot perform this operation while SQL Server Agent is starting. Try again later."

To fix this issue, I tried stopping and then restarting just the Agent
service and it won't start, reporting the same error then my guess was that SQL Server was running in lightweight pooling mode. I set it back to the default of
thread mode.

sp_configure 'allow updates', 1
go
reconfigure with override
go
sp_configure 'lightweight pooling', 0
go
reconfigure with override


And after this change it worked for me..

How to disable Auto Commit in SQL Server

You can turn auto commit OFF by setting implicit_transactions ON:

SET IMPLICIT_TRANSACTIONS ON

When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.
And when you need to enable it just run above cmd with OFF clause.

SET IMPLICIT_TRANSACTIONS OFF

autocommit is the default for Sql Server 2000 and up.

Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Whenever you get this issue make sure your security context is correctly supplied.In that case you have to use domian user acount in sp_addlinkedserver stored procedure whihc has access rights to Active Directory.The domain name is required in the Security settings for the remote login. So in the Security settings I entered Remote login: domain\user; With password: password.Find below dummy example.

Step1:-EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'LINLDP2'

Output : Success

Step2: EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false', @rmtuser='Domainname\username', @rmtpassword='#Security@SWIM#'

Also,Make sure your SQL Server Service is running under the account which has access right to Active Directory.