Expanding VM HDD Size - Oracle VM Box

1. Open the command Prompt
2. CD C:\Program Files\Oracle\VirtualBox
3. VBoxmanage modifyhd D:\Linux\Windows\Wind.vhd --resize 30000
4. Exit from command prompt
5. Boot the VM and expand the HDD

SQL Server 2017 On Linux- RHEL

sql-server-linux-setup

Install SQL Server On Linux

How To Automate SQL Server Trace

How to automate SQL Server Trace:

============================

• Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace.
• Start the trace and then stop it.
• Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. ...
• Save the trace file.
• Open the file and create a stored procedure.
• Add below lines to sp to create trace with date and time

 declare @TrcFileName nvarchar(300) 

set @TrcFileName = N'C:\STARTTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+
CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))
+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @TrcFileName, @maxfilesize, NULL 

• To make SP to start when sql server restarts:

USE MASTER
GO
EXEC SP_PROCOPTION SPNAME, 'STARTUP', 'ON'
GO

• To check SP which are running at start up:

SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

--see if trace is running

select * from sys.traces
--Mark Trace Store Proc startup option True
EXEC sp_procoption 'StoreProcName', 'startup', 'true';
--Verify start option value is 1
USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO

Msg 19069, Level 16, State 1, Procedure sp_trace_create, Line 1 The trace file name is not valid because it contains a rollover file number (NNN in C:\file_NNN) while the trace rollover option is enabled.

In this case remove the _ (underscore) at the end of the filename.

To rollover files manually use 2 as a parameter in the script.


Lot of thanks to sources :
http://www.techbrothersit.com/search?q=sql+server+profiler
https://dbamohsin.wordpress.com/tag/file-rollover/

SQL Server 2005 - Insufficient memory or maximum allowed connections

Error:

The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

Solution:

SQL Services was in hung state and it was not allowing any connections to SQL Server

Restarted SQL Server Instance and issue resolved.

SSRS Email Subscription Option Missing

SSRS EMAIL SUBSCRIPTION OPTION 

If there is no EMAIL subscription option for the report:

1. Go to SSRS Report services Configuration
2. Choose EMAIL Settings

3. Configure and click Apply.
4. Now go back to report and check subscriptions.

Move System Databases

Here is the steps to move the system databases:
==================================
Master 

- Change the physical location of the files from startup parameters once the SQL Server is offline, then copy and move the MDF and LDF to new location.
- Now rename existing files as old on previous location
-Now bring SQL Server online

MSDB

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('msdb');
GO

USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'Z:\Data\MSDBData.mdf');
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'Z:\Log\MSDBLog.ldf');
GO

Model

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'model');
GO

USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'Z:\Data\model.mdf');
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = 'Z:\SystemDBs\Log\modellog.ldf');
GO

TEMPDB

Use master
GO
SELECT name AS [LogicalName] ,physical_name AS [Location] ,state_desc AS [Status]
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
GO

 USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'Z:\TempDB\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb2, FILENAME = 'Z:\TempDB\Data\TempDB2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb3, FILENAME = 'Z:\TempDB\Data\TempDB3.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb4, FILENAME = 'Z:\TempDB\Data\TempDB4.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'Z:\TempDB\Log\templog.ldf');
GO


Resource  DB 

Can I change the Resource database path? Should I?
No

Thanks to :
https://blogs.msdn.microsoft.com/vsanil/2012/11/02/resource-database-common-questions/
https://sqlandme.com/2013/07/08/sql-server-how-to-change-sql-server-errorlog-location/

Unable to open the physical file , operating system error 5. (Access is denied)

Solution:

1. Add permissions to the disk contain Data and Log files
2. Add service account to login with SA
3. Take DB offline
    Alter database DB_NAME set offline with rollback immediate
4. Bring back the DB online
    Alter database DB_NAME set online with rollback immediate

this resolves the issue, else we need to try restarting SQL Services. Worst-case we need to restore the database with latest backup.

SQL 2016 - A severe error occurred on the current command. SQL Server Error Log / SQL Services Not coming online

The SQL Server Error log fails with the below error:

A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

Solution 1: Repair SSMS tools of SQL Server 2016.
Solution 2: Repair the SQL Server Instance.

Solution 3: Create alias under SQL Server network configuration.

 =======================================================================

Solution 3 will be more appropriate when we see the below error in windows event viewer along with the above mentioned error.

Severity: 16 Error:-1, OS: -1 [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Solution:

https://blog.sqlauthority.com/2016/12/12/sql-server-clustered-instance-online-error-sql-server-network-interfaces-error-locating-serverinstance-specified-xffffffff/

Maximum Worker Thread

About Worker Thread:

When a request is received, SQL Server will try to locate an idle thread to schedule the request. If an idle worker was not located, new worker will be created. If all workers have been created and none is idle, then the request is queued which will be picked up by any worker thread from the pool of worker threads created.

The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.

MWT Reference Range:

CPU CORE COUNT	MWT- 32 BIT	MWT-64 BIT
Up-to 4 processors	256	512
Octa-Core 8 processors	288	576
16 processors	352	704
32 processors	480	960
64 processors	736	1472
128 processors	4224	4480
256 processors	8320	8576

-- show Max number of worker threads on the SERVER

select serverproperty('instancename') as instancename, max_workers_count  as 'AllowedOn64Bit' from sys.dm_os_sys_info 

-- show number Current no of worker threads

select serverproperty('instancename') as instancename, COUNT(*) as MAXCOUNT  from sys.dm_os_workers

-- show the THREADPOOL wait time

select * from sys.dm_os_wait_stats where wait_type = 'THREADPOOL'

---- most of the sessions are waiting for LCK_M_S

select * from sys.dm_os_waiting_tasks

SQL Server 2000 - the application failed to start because msvcr71.dll or msvcp71.dll was not found

Error:The application failed to start because msvcr71.dll or  msvcp71.dll  was not found  

These files must be present on C:|Windows\System32 folder and also they should be present on SQL\Binn folder, either any software installation/upgrade /Anti virus might bombed these files.

Solution:

1. Search these files on the computer.
2. Paste them to SQL Binaries folder  - C:\Pf\MS\MSSQL\Binn
3. Paste them to C:\Windows\System32 folder also
4. Bring SQL Services online

Hope it helps someone! Thanks.

SQL Server Agent can't Connect-Comes Online - 2016

ERROR: SQLServerAgent could not be started (reason: Unable to connect to server 'WIN\Group'; SQLServerAgent cannot start).


Solution:

1. Connect to SQL Server Instance
2. Go to SQl Server Agent
3. Select -> right Click -> properties -> choose Connection
4. paste instance name as follows
    servername.domain.biz\instance_name
5. Click ok 

Now go to failover cluster manager and bring agent online. Issue resolved.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution:

There are several solutions on this, but for us the below solution worked great.

Step 1:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 2:

USE [master]
GO


EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Step 3:

Full permissions on C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local  for windows nt groups

Thanks to:

http://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null