Wednesday, June 13, 2018

Unable to ALTER SQL Server database from single user to multi user

When i try to alter database from single user to multi user i got facing below error

Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205)

Solution :



USE [MyDatabase]
GO
SET DEADLOCK_PRIORITY LOW
GO
ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Drop DEFAULT Constraint in SQL Server


Syntax to drop existing default constraint from a table

USE [DatabaseName]

GO

ALTER TABLE [Table_Name]
DROP CONSTRAINT [DefaultConstraintName]


Below is the example to drop default constrain from orderqty table


USE [MyDatabase]

GO

ALTER TABLE [dbo].[orderqty]
DROP CONSTRAINT [CK__orderqty__qty__628FA481]


Add Default constraint for existing table in SQL Server

Below is the syntax for adding default constraint for an existing table



ALTER TABLE tbl_Name
                       ADD CONSTRAINT default_columnName
                                         DEFAULT value FOR columnNmae




example :
Below is the query to create DEFAULT constraint on qty column when the orderqty table already created



ALTER TABLE orderqty
ADD CONSTRAINT default_qty
DEFAULT 1 FOR qty;


We created default constraint and we did not inserted value for qty field using INSERT statement in below example but "1" value inserted for qty


Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)


Problem

Cannot drop the user 'dbo'.

Rename failed for User 'dbo'.  (Microsoft.SqlServer.Smo)

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)





Solution 

USE My_Database; 
ALTER AUTHORIZATION ON My_Database::DATABASE_NAME TO sa

Tuesday, August 8, 2017

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).


If SQL Server agent not started and if you find below error in event viewer

Error Details

 Log Name             :         Application
Source                  :         SQLSERVERAGENT
Date                      :         8/8/2017 3:49:43 PM
Event ID               :         103
Task Category      :         Service Control
Level                    :         Error
Keywords             :      Classic
User                      :          N/A
Computer             :      VmSQLServer
Description           :
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).















Solution 

Provide instance in  in "ServerHost" under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

Goto run
type regedit
expand HKEY_LOCAL_MACHINE --> SOFTWARE --> Microsoft --> Microsoft SQL Server
click on SQLServerAgent in Microsoft SQL Server
In right side pane double click on ServerHost and provide instance name under  value data

Friday, June 9, 2017

Read only access to all databases for existing Login






SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'Domain\Login',
       @login_name = 'Domain\Login'

Select  '
    USE ' + QUOTENAME(NAME) + ';

    CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];

    EXEC sys.sp_addrolemember
      ''db_datareader'',
      ''' + @user_name+ ''';

   
GO
'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 

Thursday, May 25, 2017

About Trace Flag -T 1117


In your SQL Server environment for any data base having more than one data file along with temp database data files with the same size and with auto-grow option.

Lets assume that you have a database with eight files & initial size of 1024 MB and auto-grow of 200 MB each.

The default behaviour is for one of these eight files to grow by the specified increment. This will happen when all of the files are in full and after one file to grow by the specified increment. This will happen when all the files are full, and after one file grows and SQL Server algorithm will direct new extent allocation to the file with the most available space, so new extents will be written to the one file that just grew.

To overcome this we have the trace flag 1117, it changes the behaviour of file growth. If one data file in file group grows, it forces other files in that file group to also grow.

All the files have about same percent of free space after auto-grow and the benefits of evenly distributing IO will be preserved

But Trace 1117 applied to the entire SQL Server instance. you can't apply to any one specific database. It will effect all files in the same filegroup of database. If the database having multiple filegroups and auto-grow event occurs in one filegroup will not affect the other filegroups.