22 September 2017

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.

14 August 2017

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.





28 July 2017

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/

25 May 2017

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.

18 April 2017

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: