SQL Server Satrtup Parameters .

SQL Server Parameters when starting Instance:

1) -m -> Single User Mode

2) -f -> Minimal Configuration Mode

3) -c -> Starts instance quickly by not contacting Service Control Manager.

4) -g -> To set MTL portion of SQL Server

5) -n -> Does not use the Windows application log to record SQL Server events.

6) -T -> Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior.

7) -m <ClientApplicationName> -> Only connections from a specific client application are allowed.

Manage SQL Server Services from the Command Line

You can start and stop Sql Services by cmd by following commands:-

NET START MSSQLSERVER:-

 Starts SQL Server as a service. 

NET STOP MSSQLSERVER :-

Stops SQL Server when running as a service. 

NET PAUSE MSSQLSERVER:-

 Pauses SQL Server when running as a service. 

NET CONTINUE MSSQLSERVER:-

 Resumes SQL Server when running as a service.

To manage named instances of SQL Server, use the following commands: 

NET START MSSQL$instancename :-

Starts SQL Server as a service, where instancename is the actual name of the database server instance. 

NET STOP MSSQL$instancename:-

 Stops SQL Server when running as a service, where instancename is the actual name of the database server instance. 

NET PAUSE MSSQL$instancename :-

Pauses SQL Server when running as a service, where instancename is the actual name of the database server instance. 

NET CONTINUE MSSQL$instancename:-

 Resumes SQL Server when running as a service, where instancename is the actual name of the database server instance.

You can add startup options to the end of net start MSSQLSERVER or net start MSSQL$instancename commands. Use a slash (/) instead of a hyphen (–) as shown in these examples: 
net start MSSQLSERVER /f /m 
net start MSSQL$CUSTDATAWAREHOUS /f /m

Find OS information with SQL Query

EXEC master..xp_cmdshell 'systeminfo'

Track who, when create and modified Login in Sql Server Database from Default Trace.

You can find the user created and modified date from below query.

First Create a Login and user:-

USE [master] 

CREATE login ashish WITH password='Access@123' 

USE [test] 

CREATE USER ashish FROM login ashish 

SELECT createdate,  updatedate, 
       * 
FROM   sys.sysusers 
WHERE  name LIKE 'ashish%' 

Now find who create the user:-

SELECT @TRACE_ID = id 
FROM   sys.traces 
WHERE  is_default = 1 

SELECT @FILENAME = CONVERT(NVARCHAR(4000), value) 
FROM   sys.Fn_trace_getinfo(@TRACE_ID) 
WHERE  property = 2 

SELECT hostname, 
       loginname 
FROM   sys.Fn_trace_gettable(@FILENAME, DEFAULT) 
WHERE  eventclass = 109 
       AND databasename = 'TEST' ---your DB name 
       AND targetusername = 'ASHISH' ---new created login

How To Troubleshoot if you are not able to enable and install .net framework 3.5 during install SQL Server 2008 or 2012 on Windows 8.

When you try to enable .Net framework from Windows Feature you will get following error.

Its showing error like “Windows couldn't connect to the internet to download necessary files”

  

Select checkbox and click ok

  

Click On Download and Install this Feature

 

You will get this error :-

Try to connect internet if internet connection already connected and still you are getting same error then you need to do some troubleshoot with registry file of .Net

Press win + R key from your keyboard and type regedit and press ok

GO To HKEY_LOCAL_MACHINE>>SOFTWARE>>Policies>>Microsoft>>Windows>>WindowsUpdate>>AU

Click on the AU and change value data 1 to 0 and click OK

Now restart the System and try to enable .Net Framework the same way it will work  

Find all tables size in a database.

Follow below script and you can specify like operator for that tables you want. For example I want onlt that table that start with load

DECLARE @space_table TABLE (table_name varchar(500), row_count int, reserved varchar(500)

, data varchar(500), index_size varchar(500), unused varchar(500))

INSERT INTO @space_table(table_name, row_count, reserved, data, index_size, unused)

EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT * FROM @space_table where table_name like ‘Load%’