Drop Failed for User in SQL server

Sometimes its irritating getting the same error again and again, where we feel we cant anything more than that.

When ever trying to delete a user from the database, you will get to see this error below.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138).

The error itself is the meaning. It can't be dropped as it owns a schema.

Solution : -- Simple.. :)

Go to the security under the Database where you wanted to delete the user from,

goto users --> Right Click on the username--> select Properties

Now you can see clearly in General Tab

                                  Schemas Owned by this user:

          So please note the selected 'schemas' here, It doesn't matter if it is greay/inactive.

Now exit out from that window. 

(OR)
Simple Query : 
                         SELECT *, 'UserName' as ownerName
                         FROM sys.schemas s
                         WHERE s.principal_id = USER_ID('UserName')

Now you have to do the following :

Go to Schamas under security, select the schamaName which you have noted down above or from the query result. Right Click on it and select the properties. Now you can see the that same username placed as Schema Owner in General Tab

                                                Replace that username with dbo and click OK button.

Do the same for all the schema's you have noted above.

Now you can be able to delete the user.

You have other processes to do this, This is just one of them.

Please give me your feed back. New ideas most welcome.

SmartScreen Filter

This probably not related to sql.
Some times when you are trying to download a file from trusted site, where you have been downloading from the site for a while.
Suddenly you kick of with the following message.

I have got the same and I went for the details of the error. I have been told SmartScreen Filter is stopping that download.

All you have to do is make sure the site/file is not harmful and disable the SmartScreen Filter from your browser. Which started delivering from 'Internet Explorer 9' to provide more security.

Open Internet Explorer --> Tools --> SmartScreen Filter --> Turn Off SmartScreen Filter...
Please look at below...

Fig: FromTools

Fig: TurnOff

Latest Backup and Restoration Dates on Databases

These are the small scripts which we can use to find when did your database got backup lastly and When did  your database got restored.

--The following script will bring all the databases in the server when they got last back 'd up.


SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), 
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A 
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

--  The following script will give you little bit more information about its restoration.
          It will give you the restoration Date, where it is restored from and also importantly the backup start date and End date.


SELECT RH.destination_database_name, RH.restore_date, BS.backup_start_date, 
  BS.backup_finish_date, BS.database_name as source_database_name, 
  BMF.physical_device_name as backup_file_used_for_restore
FROM msdb..restorehistory RH
 INNER JOIN msdb..backupset bs ON RH.backup_set_id = BS.backup_set_id
 INNER JOIN msdb..backupmediafamily BMF  ON BS.media_set_id = BMF.media_set_id
ORDER BY RH.restore_date DESC

Table Backup with native SQL, Process 4

-- Table Backup with native SQL, Process 4
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
 Original table, Insert into newtable select * from backtab)

Here we can see Process 4 : We discussed other 3 ways in earlier posts..Process1, Process2 and Process3

So two steps first copy the skeleton/Structure/schema of the table and in next step load that table.

       Step 1:

SELECT * FROM OriginalTab_Backup FROM OriginalTab where 1 = 2
You can use any type of comparison in where condition, those should not equal.

It will just print the structure of the 'OriginalTab' table in 'OriginalTab_Backup'.
      
       Step 2:

Now we have to load the table, We can use few other ways to do so. But I am using the below.
INSERT INTO OriginalTab_Backup SELECT * FROM OriginalTab

You can also use Bulk Insert to load the table, which is most used one. I will post this in future.
By using this process, You can take the backup of full table or selected columns.
like below..

SELECT Col1,Col2,Col3 FROM OriginalTab_Backup FROM OriginalTab where 1 = 2

INSERT INTO OriginalTab_Backup SELECT  Col1,Col2,Col3 FROM OriginalTab.

Table Backup with native SQL, Process 3

-- Table Backup with native SQL, Process 3
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
 Original table, Insert into newtable select * from backtab)

Here we can see  Process 3 : Please click here for Process1 or Process2
You can do this same job by using a simple T-SQL line.

SELECT * INTO OriginalTable_Bak FROM OriginalTable

Note: By using this process you can backup the table data. You cant get keys, references and indexes directly.

Describe INTERSECT and EXCEPT

INTERSECT and EXCEPT

To better understanding please follow this.
Intersect :  It will give you mutual records from Left query and Right query of the Intersect.
Example: 1) A Intersect B or you can follow the below example queries.

Except : It will give you only the left query or Left table result. It means, wouldn't count the mutual records
Example: 1) A Except B -- All the rows from A, which avoids If any same rows from B.


Table A has 50 rows  10 of them are copied from Table B
Table B has 70 rows 20 of them are copied from Table A
                  So Now A INTERSECT B : 10 + 20 = 30
                   
                  So A EXCEPT B : 50 - 30 = 20
                  So B EXCEPT A : 70 - 30 = 40


Small example :

I have database named mydb, consists 5 tables. I got Full backup on 08/10/2010
2 weeks later I had to compare the database with the old backup.
I have installed the database on the same server with different name as mydb_Test.

Note: Of Course If you have the old database available on one of your test servers, which had configured linked server set up .. (No need to install the database again..) Just use the Fully Qualified Object Name as LinkedServerName.DatabaseName.sys.tables
Where,LinkedServerName : should be one of the row from the following query.
            select name as ServerName from sys.servers


Now, My job is to findout the new objects introduced in the current version of
the database as well the dropped objects. Such as Tables, Stored Procs, Triggers, Functions..

Of course, We definitely have couple of other procedures. But I have used this and gotten results fine.

----------------Tables

-- Find new tables

SELECT Name AS NewTables FROM mydb.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name FROM myDB_Test.sys.tables
ORDER BY name


-- Find Mutual tables

SELECT name AS MutualTables FROM myDB_Test.sys.tables 

INTERSECT
SELECT name  FROM mydb.sys.tables
ORDER BY name

-- Find Dropped tables

SELECT Name AS DroppedTables FROM myDB_Test.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name AS NewTables FROM mydb.sys.tables
ORDER BY name

This is the same procedure to find stored procedures and Triggers. Just replace tables with procedures and triggers in the code above.

For the functions, Please follow..


SELECT name AS NewFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS MutualFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
INTERSECT
SELECT name,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS DroppedFunction ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'

Table Backup with native SQL, Process 2

-- Table Backup with native SQL, Process 2
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
Original table, Insert into newtable select * from backtab)

Here we can see Process 2 :
You have an option with Import/Export Wizard.
See below how it works..  

Right Click on the database Tasks -> and click Import Data... (OR) Export Data...as below

fig : 1 DBProperties_rightClick
both bring the same window as below...

fig : 1 a) Import and Export Wizard
Cleck next and Choose the source, where you wanted to retrive data from(SQL,EXCEL,Flat File and etc...) see below.

fig : 2 Select Source

Click next and Choose the destination, where you wanted to load the data(SQL,EXCEL,Flat File and etc...) see below..

fig : 3 Select Destination

Choose one of this as the following figure....

fig : 4 Type

Now you have to configure the destination file as bleow.. As I have selected my destination is Flat file, I just need to select Row Delimiter and Column delimiter from the drop down.

fig : 5 Configuring Destination

So, Now you have two options here, Select Run Immediately to have the import/export task done now.
Or you can check the other box as Save SSIS Package and will let you run whenever you want in future.
Please look at below.

                                           fig : 6 Save and Run

fig : 7 Finish

fig : 8 Result

Script to find Log file Original Size and Growth value.

Here is a small script you to run.
You can get various results from the same script by small modifications.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name as 'Locate/PhysicalName', (size*8)/1024 SizeMB,
(growth*8)/1024 Growth
FROM sys.master_files
WHERE database_id >4 -- to avoid system databases
and DB_NAME(database_id) <> ('ReportServerTempDB')
and Name like ('%_Log') -- Just log files

Table Backup with native SQL, Process 1

-- Table Backup with native SQL, Process 1
Version : SQL 2008 R2 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
Original table, Insert into newtable select * from backtab)

You have an option with Generating Scripts by limiting the data, means It may not support for large tables...Sometimes it depends on your RAM.
See below how it works..

Step (i) :
Right click on the database -> Tasks -> Generate Scripts

                          Fig  : DBRightClick

Step (ii) :

Select Choose Objects tab and click on Select specific database object
Expand tables and check the table which you would like to take the backup and click next button.

                             Fig : GenerateScritps1
Step (iii) :
 Now you have quite a few options to save the table backup.
 a) Save to a file by locating the file in valid location.
 b) Save to clipboard
 c) Save to new query window.

                               Fig : GenerateScritps2
 So, here most importantly after selecting one of the above you have to click the
 advanced button on top right corner of the selection.
 (Note : This part will be the first in step(ii) in sql 2005 and 2008)
 It will let you see the new window 'Advanced Scripting Options'
 You have to Scroll down and look for the option 'Type of the data to script'
 Please select Schema and Data from the Drop down.

Step (iv) :
Click ok to close this Advanced window and Click next and Finish buttons to close
 the process.

CHECKSUM and TORN_PAGE_DETECTION

CHECKSUM and TORN_PAGE_DETECTION

Its basically making sure our data not get corrupted.
It will do verify the data against the backup while restoring the database.

You have total 3 options in the SQL server to take care of this task.

--You can find two scenarios to do the stuff in sql server
Note : We need to choose one of these, But you will get the benfit-over while
retoring the database.
1) CheckSum and
2) Torn_Page_Detection and
3) None

CHECKSUM do basically check each and every bit writing in to the page while restoring it
from the backup(You choosen the option and got the backup), If it finds anything
mismatched. Then it will throw the (824) error. It is much safer, though little burden
(as it is checking each bit).

Torn_Page_Detection is the one which will write a bit for every 512 bytes of data as it is
not corrupted. It is lightweight-er but its not that safe compared to CHECKSUM.

None will make your database recover/restore very fast compaing the other options.
But, there is no guaranty you have good data.

You can find the work Description below :

So, when ever your taking the backup of the database....
We usually think have to get the good backup (which is not corrupted).. Basically SQL will
take care of that....

Otherhand while restoring the same backup located on the disk for some reasons you may get (corrupted
your backup) wrong data as pages(DB) doesnt load properly during Power loss,hardware failure..

So in that case these two options will help us..
If its checksum It will read every bit from the back up and write it into disk. If find some
thing wrong while writing the disk... then it will try 4 times to read properly from the
backup.. If it doesnt read the page successfully It will write on the SQl log and application
log as displaying 824 error while recovering the database.

But it will restore the database successfully.
It will write the record in the suspect_pages system table in msdb database


-- To find out the suspect pages in the database while restoring.
select * from msdb.dbo.suspect_pages

Understanding Statistics

-- Statistics:
Two way to create statistics
1) Manual
2) Automatic

1) Manual : If you mention the index/Key in the table while creating or on existing table..
The statistics creates with the Index Name

2) Automatic :
When ever you are doing transactions based on the column which you never created an Index/ Key on it..
You can see the new field under statistics, which is like..
_WA_Sys_00000001_112G3A24

Where WA Stands Washington as the SQL product from there
Sys stands the SQL generates...
00000001 stands the column Number..and
Finally, 112G3A24 Hexdecimal number of the Object_ID(Object_Name/TableName)

 Help: Please click here to convert your hexadecimal into Numeric Number         
         
If you want to check this, Please try the below Example..

CREATE TABLE StatsTest (ID INT IDentity(1,1) Primary KEY
, Name varchar(10) )
INSERT INTO StatsTest VALUES ('Test1'),('Test2'),('Test3')

SELECT * FROM StatsTest

UPDATE StatsTest  SET Name = 'StatsTest'  WHERE ID =1

  CHECKPOINT

-- You dont see anything in the stats now

UPDATE StatsTest  SET Name = 'StatsTest1'  WHERE Name = 'Test2'

 Run the above piece of code, refresh statistics on the table and observe.
You can see the new object under statistics as below

_WA_Sys_00000002_1920BF5C

It means 00000002 is the number of the column in the table
where 1920BF5C is the Hexdecimal number for the (table-starttest)Object_ID.

--You can get some useful info from here.

Logins Audit in Error Log

Some times its hard to find log in auditing,where we need to enable / disable or set some properties to load these in error log...

It's very simple...

Right click on the Server --> select the Properties

Now in the Server properties window go to security tab and check one of the 4 radio button's which you desired from "Login auditing".

Once you select one of that click "OK" button to make these changes in place.

Please look at the Figure 1 below.

Figure 1: Server Properties

How to describe VLF's

VLF: Virtual Log File

To find Number of VLFs in your log file by using
Command:
        DBCC loginfo.

A simple question from one of the deabtor..
Ex: I have 10 MB Log File with 4 VLF s , each size is 2621440. I wanted to change it as 100MB of Total. After Changing do you know how the VLF s would be?

Explanation:

Basically the adding Chunck ( FutureSize - OriginalSize).. that would be
100MB-10MB = 90MB
So If you are adding < = 64MB Chunk Should creates (adds to the old VLFs) 4 equal sized VLFs
   If you are adding > 64MB and <= 1GB chunk should creates 8 equal sized VLFs
   IF you are adding > 1GB chunk should creates 16 equal sized VLFs

Note : The original VLFs remains same....

Now the calculation part :
Note : The VLFs sizes always in Bytes...

So we have to consider the second case in our situation.
Convert Total Chunk into Bytes ..
90MB = 90*1024 KB = 90* 1024*1024 Bytes = 94371840 Bytes

Now Divide in to 8 Equal parts...11796480 Bytes (As it creates 8 VLFs for 90MB considering Second Ifcondition above)

Finally, You have 12 VLFs there after modifying your log from 10MB to 100MB
and the first/original 4 remains same as earlier
and the latest 8 VLFs holds 11796480 Bytes each.

Switching Database Restrict Access (User Modes)

Hi All,

This may help somewhere in the process of getting things faster.
Here you can switch all your databases from one user mode to other available in SQL server...
        a) SINGLE_USER Mode
        b) RESTRICTED_USER Mode
        c) MULTI_USER Mode.

Note: Conditions may apply to do this, just make sure before doing these. Something like below..
     i) Taking the log backup before applying this.
    ii) Run sp_who2 active to see anyone working on the database(s) and inform them to commit their transactions.. Other wise it may causes issues.


/*
what ever the mode you wanted your databases to,Just set that value below where mentioned.*/

-- I)If you wanted to change your database access as one of the following conditions Then use this script.
  /*
  1) MULTI_USER to SINGLE_USER
  2) MULTI_USER to RESTRICTED_USER
  3) SINGLE_USER to RESTRICTED_USER
  4) RESTRICTED_USER to SINGLE_USER
  */
--CODE From here

DECLARE @Mode varchar(20) =  'Usermode' --please mention one of your desired user mode here
DECLARE @DbName varchar(50)
DECLARE @SQL varchar(1024)

DECLARE UserModes CURSOR
FOR

SELECT Name FROM SYS.databases
WHERE user_access_desc <> @Mode
and database_id >4

OPEN UserModes
FETCH NEXT FROM UserModes into @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
ALTER DATABASE ['+@DbName+'] SET '+ @mode + ' WITH ROLLBACK IMMEDIATE'
FETCH NEXT FROM UserModes INTO @DbName

EXEC (@SQL)
PRINT 'Database '+@DbName+' successfully moved to ' + @Mode +' Mode'
END
CLOSE UserModes
DEALLOCATE UserModes
GO

Note : Why Rollback Immediate while altering the database?
It does rollback all the transactions currently processing with the users. Finally, It can do the altering task.

-- II) If your database in SINGLE_USER or RESTRICTED_USER mode and you wanted to change it to MULTI_USER Then use following.

DECLARE @Mode varchar(20) =  'Usermode' --please mention one of your desired user mode here
DECLARE @DbName varchar(50)
DECLARE @SQL varchar(1024)

DECLARE UserModes CURSOR
FOR

SELECT Name FROM SYS.databases
WHERE user_access_desc <> @Mode
and database_id >4

OPEN UserModes
FETCH NEXT FROM UserModes into @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
ALTER DATABASE ['+@DbName+'] SET '+ @mode + ' WITH NO_WAIT'
FETCH NEXT FROM UserModes INTO @DbName

EXEC (@SQL)
PRINT 'Database '+@DbName+' successfully moved to ' + @Mode +' Mode'
END
CLOSE UserModes
DEALLOCATE UserModes
GO

Note : Why No_Wait while altering the database ?
It May or may not alter the database. If no one connected to that particular database It can successfully let you alter the database. Otherwise, It will fail to do altering the database.

BTW... You have another option here to set the time for the rollback...
WITH ROLLBACK AFTER 60 
Where 60  in seconds.... Basically it will wait 60 seconds to rollback the other transactions on the database and does the altering with rollback successfully.
Command looks like below:
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK AFTER 60