SQL Trivia – What all schemas cannot be dropped in a SQL Server database?

August 4, 2016 Leave a comment

 
–> The following four built-in database schemas cannot be dropped:
 

1. The “dbo” schema: is the default database schema for new objects created by users having the db_owner or db_ddl_admin roles. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

2. The “guest” schema: is used to contain objects that would be available to the guest user. This schema is rarely used.

3. The “sys” schema: is reserved by SQL Server for system objects such as system tables and views.

4. The “INFORMATION_SCHEMA” schema: is used by the Information Schema views, which provide ANSI standard access to metadata. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. Information schema views are based on sys catalog view definitions.
 

… notes from: Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012


Categories: SQL Trivia Tags:

SQL Error – Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option


 
Today while running an ETL I encountered an error I’d never seen before. The Informatica ETL triggers a Stored Procedure with 2 parameters which takes table names and creates a dynamic MERGE statement. The SP was failing and the Informatica Monitor was showing following error:

Severity: ERROR
Thread: MAPPING
Message Code: CMN_1022
Message: Database driver error…
CMN_1022 [EXEC spMergeTables ‘STG.ABCtblXYZ’,’ABC.tblXYZ’
Microsoft SQL Server Native Client 11.0: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
SQL State: 42000 Native Error: 2754
State: 1 Severity: 16
SQL Server Message: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

Database driver error…
Function Name : ExecuteDirect
]

Thus, somewhere within this SP the error is being generated with severity greater than 18.
 

So, to identify the cause I tried to execute the above MERGE statement directly and I got the same error:

EXEC spMergeTables 'STG.ABCtblXYZ','ABC.tblXYZ'

(0 row(s) affected)
Msg 2754, Level 16, State 1, Procedure spMergeTables, Line 107
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

 

In the SP after creating the dynamic string for the MERGE statement the SP executes the string variable, so I replaced the EXEC(@str), with PRINT(@str), to PRINT the MERGE statement rather than executing it, which came as follows:

MERGE ABC.tblXYZ AS tgt
USING STG.ABCtblXYZ AS src
ON tgt.[PK_ID] = src.[PK_ID] 
WHEN MATCHED THEN  
	UPDATE SET <tgt.column = src.column>
WHEN NOT MATCHED BY TARGET THEN 
	INSERT (<Columns list>) 
	VALUES (<column values>);

On executing the above MERGE statement, I got the actual error which is as follows:

Msg 823, Level 24, State 12, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x000000270e0000 in file ‘F:\TempDB\tempdb2.ndf’.
Additional messages in the SQL Server error log and system event log may provide more detail.
This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for more information, see SQL Server Books Online.

So, its clear now that there was something wrong with the tempdb file and the OS is returning error with severity as 21, which is greater than 18.
 

As this was a tempdb file issue, thus I restarted the SQL Server services and reconnected the SSMS client, and re-ran the Job, and this time the ETL got executed successfully🙂


Categories: SQL Errors

Using some best practices for SQL Server in Azure VM (IaaS) to get MAX performance

June 30, 2016 1 comment

 
I was setting up an environment on Azure Premium Storage (G-Series) server for my Project, and was not getting the performance I was expecting. The comparison was against an existing Database Server setup on an On-Premise VM.

Microsoft Azure Premium Storage delivers high-performance, low-latency disk support for VMs running I/O-intensive workloads. Uses SSD (Solid State Drives) to store your data and Caching capability to provides highest throughput and low latency. You can configure disk caching policy on the Premium Storage disks as ReadOnly, ReadWrite or None, based upon your workloads, and type of data processing.

The highest Premium Storage Disk type, P30 provides you Disk size of 1TB, with 5000 IOPS, which can provide throughput of 200 MBPS.
 

Here are some of the Best Practices [suggested by Microsoft] to ensure that your SQL Server instance is optimized upto its maximum on an Azure Virtual Machine:

1. Use Premium Storage. Standard storage is only recommended for dev/test.

2. Keep the storage account and SQL Server VM in the same region.

3. Format the Data disks, and use NTFS 64-KB allocation unit size for data and log files.

4. Stripe multiple Azure data disks to get MAX IOPS and throughput.

5. Avoid using operating system or temporary disks for database storage or logging.

6. Enable read caching on the disk(s) hosting the data files and TempDB.

7. Do not enable caching on disk(s) hosting the log file.

8. Setup SQL Server tempDB on D: Drive which is a SSD (Solid State Disk) in providing MAX throughput on handling heavier workloads.

9. Enable SQL Server database page compression.

10. Enable instant file initialization for data files, to reduce the time that is required for initial file allocation. Grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. Database File Initialization.

11. Disable autoshrink and Limit autogrow on the database.

12. Move all databases and error log & trace file directories to data disks, including system databases.

13. Enable the Lock Pages in Memory Option (Windows level). This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).

14. Set MAX SQL Server memory to 64GB.

15. Run a full Index REBUILD (without REORGANISE) and UPDATE STATS with FULL SCAN option on all tables in the database.

16. And last and not the least, tune your Queries. Badly created Queries and Indexes won’t run on highly optimized systems🙂


SQL Error – Windows could not start the SQL Server (MSSQLSERVER) on Local Computer (moved Master DB)


 
I was trying to move my Master DBs to another folder by using steps mentioned in my one of [previous blog post]. But after all steps, when I tried to Start the services I got error with following popup:

Master DB move 01

Error Description:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.
 

–> So, I checked the error log file and it showed the reason for error.
Default location: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\


2016-06-27 12:18:28.02 Server The service account is ‘NT Service\MSSQLSERVER’. This is an informational message; no user action is required.
2016-06-27 12:13:33.30 Server Registry startup parameters:
     -d E:\SystemDatabases\Master\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l E:\SystemDatabases\Master\mastlog.ldf
2016-06-27 12:13:33.30 Server Command Line Startup Parameters:
     -s “MSSQLSERVER”

2016-06-27 12:13:33.41 spid5s [INFO] HkHostDbCtxt::Initialize(): Database ID: [1] ‘master’. XTP Engine version is 0.0.
2016-06-27 12:18:28.12 spid5s Starting up database ‘master’.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\master.mdf for file number 1. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\master.mdf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s SQL Server shutdown has been initiated

 

–> The above error log (in bold) tells that the service account NT Service\MSSQLSERVER does not have access to the files moved to new location.

–> So, to provide access, go to the new folder location –> Right click and select Properties –> Go to Security tab, click on Edit –> click on Add, to add the Service Account. Apply the Service Account (here in my case NT Service\MSSQLSERVER) and change the Locations to your PC, as its a local Service Account:

Master DB move 02

Give “Full Control”, and click OK on popups.
 

Now Start the “SQL Server” services and this time it will work.

To check and confirm the new file location of Master DB, Execute following SQL query:

USE master
GO

SELECT *
FROM sys.database_files

Master DB move 03
 


Categories: DBA Stuff, SQL Errors Tags:

DBA – Move master Database to another drive – in simple steps

June 28, 2016 1 comment

 
Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a spacial handling for this case of master DB.
 

–> Let’s first check the location of master DB:

USE master
GO

SELECT * FROM sys.database_files

Move Master 01
 

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Move Master 02

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files to E:\SystemDatabases\Master\ location, so you just need to replace the existing path with following:

1. master data File:
-dC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-dE:\SystemDatabases\Master\master.mdf

2. master Log File:
-lC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-lE:\SystemDatabases\Master\mastlog.ldf
 

–> Now Stop the SQL Server services, by going to: RUN –> services.msc

–> Manually Copy the master.mdf & mastlog.ldf files to the new location

–> Start the SQL Server services.

–> To confirm the new location, just execute following query and check the path:

USE master
GO

SELECT * FROM sys.database_files

Master DB move 03
 


Categories: DBA Stuff Tags: