database principal owns a schema in the database, and cannot be dropped

Hi ALL,

Some times we need to drop the users on Database, during the phase we may encounter the below situtation like:

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

Check if any schmes are pointed user (unable to delete)

select * from sys.schemas where principal_id = user_id('USERNAME')

if you find any thing: do the transfer those dbo by executing the below command.

SELECT N'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;'
FROM sys.schemas
WHERE principal_id = USER_ID('USERNAME');

NOTE: copy the above output and execute...

After doing this, you are able to DROP the desrired users.... :)

Thanks,
Satish Kumar.

Finding Database with MDF and LDF file sizes

Hi All,

Some times we will notify the requests like databases sizes with MDF and LDF files with locations. At the case please use the beloe script:

SELECT DB_NAME(mf.database_id) AS databaseName,
mf.physical_name,
size_on_disk_bytes / 1024 / 1024 AS size_in_MB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY databaseName

Note***: If the value is in 0 (zero) means - the size is less than 1 MB because the sizes are calculated in MB’s.

Thanks,
Satish Kumar.

Msg 7311, Level 16, State 2, Line 1

The reason for this error message is as follows: “You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server”.

Resolution:

To Fix the above issue we have two options:

* Creating Store procedure on Master database (or)

1) Connect to SQL Server
2) Create the below store procedure in Master database
2.1) create procedure sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null
as
declare @Result int
select @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

2.2) GRANT EXECUTE ON sp_tables_info_rowset_64 TO USER

* Updating SQL Server system catalog files

Please refer the KB article to do this --> http://support.microsoft.com/kb/906954

Note: the kb article updates several things on Master database instaed of updating system catalogs, i would prefer step 1... bcoz i m done with step 1 and its successfull with out any issues :)

Thanks,
Satish Kumar.

Ole Automation Procedures' does not exist, or it may be an advanced option

Please execute below to resolve the issue:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

EXEC master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

Thanks,
Satish Kumar.

automate profiler trace in sql server 2005

Create the Trace Definition

The most efficient means to define the SQL commands used for constructing a profiler trace is to use SQL Profiler.

* Start SQL Profiler and select File --> New Trace.
-Select the option of events, columns, and filters you want in your trace.
* Start the trace and then stop it.
* to check the script file Click File > Export > Script Trace Definition > For SQL Server 2005
* Save the trace file on local disk.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[trc_DBNAME_PROD] Script Date: 10/30/2011 21:06:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[trc_DBNAME_PROD] @Folder nvarchar(200)
as
/*
Start a 120 minute profiler trace storing the captured output in provider folder.
The folder must exist. A subfolder will be created using the start date and time to allow for repeated running of this profile without replacing the previuos captured trace files. On SQL Server 2005, XP_CMDSHELL needs to be enable to create the subfolder.
*/
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,120,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 1024 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--> By your saved trace file…..<--
-- Set the events… add the events under this section
/* for example please see the below format
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on

-->By your saved trace file…..-
-- Set the events… add the events under this section
/* for example please see the below format
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 18
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HC1APAU1P'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%Profiler%'
exec sp_trace_setfilter @TraceID, 11, 0, 0, N'CDS_service'
--exec sp_trace_setfilter @TraceID, 11, 0, 0, N'test'
exec sp_trace_setfilter @TraceID, 35, 0, 0, N'CDSAML_PROD'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

· Automation process is completed
· Please create a store procedure according to your naming conventions

1) Please create a JOB with the below script
2) USE [master]
3) GO
4)
5) DECLARE @return_value int
6)
7) EXEC @return_value = [dbo].[trc_DBNAME_PROD]
8) @Folder = N'E:\ProfilerTrace\Template'
9)
10)SELECT 'Return Value' = @return_value
11)
12) GO

Please select the your desired location to @ Folder = ‘*******’

1) If you want to do manual stop, u need to execute the below script
select * FROM ::fn_trace_getinfo(default) –- to find the running trace ID’s.. please don’t stop trace ID 1 because its default treace

EXEC sp_trace_setstatus @traceid = 2, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = 2, @status = 2; -- Close trace and delete it from the server

NOTE***: Please enable XP_CMDSHELL, bocz the above script will create folders on local disk...

Thanks,
Satish Kumar.

connect to ssis service failed access is denied

Some times users may face the issues when the users are trying to connect remote integration services with the below error message:

connect to ssis service failed
access is denied

Work Around:
Please refer to this LINK given by MicroSoft:

http://support.microsoft.com/kb/2000474

Thanks,
Satish Kumar.

Execute permissions to Integration services on SQL Server

follow the below process to grant access for Integration services:

GO: MSDB --> Security --> Roles --> db_ssisltduser --> add the user/group name

Give SQLAgentOperatorRole --> if they want them to schedule it.

For more on integration securities please refer the link:

http://msdn.microsoft.com/en-us/library/ms141053.aspx

Thanks,
Satish Kumar.

send an email if the query returns any data

Hi All,
Some times we have requirement to send an e-mail, if the query returns data. please use the below code:
==============================================
use DATABASE_NAME
go
if exists(SELECT DstDesc, DstDT, COUNT(*) as [Count] from Distributions where
DstDT > '25-Aug-2011' group by DstDesc, DstDT having COUNT(*) > 1)

begin
--print 'test'
exec msdb..sp_send_dbmail @profile_name = 'SQL_MAIL_Profile_NAME',
@recipients = 'user_name@mail.com',
@subject = 'Duplicate Distribution',
@body = 'Please find below the duplicate entries in Distributions table',
@query = 'set nocount on
SELECT DstDesc, DstDT, COUNT(*) as [Count] from Distributions where
DstDT > ''25-Aug-2011'' group by DstDesc, DstDT having COUNT(*) > 1
set nocount off',
@execute_query_database = 'DATABASE_NAME',
@query_result_header = 1,
@query_result_width = 256
end
==============================================
Thanks,
Satish Kumar.

shrink database failed with PAGE granularity

Msg 651, Level 16, State 1, Line 1Cannot use the PAGE granularity hint on the table "dbo.table_name" because locking at the specified granularity is inhibited.The statement has been terminated.

The issue will error occurs when you have one or more non-clustered indexes created on table without a clustered index on table. One should always create a clustered index on table.

Resolution for above error is just to create a Clustered Index on the table AND re-run the shrink database

Thanks,
Satish Kumar.

Query to pull the users created in a database

SELECT
SU.Name AS UserName,
SU.createdate As createdate,
SR.Name AS RoleName
FROM sysUsers AS SU
INNER JOIN
sysUsers AS SR ON SU.GID = SR.UID WHERE
SU.GID <> SU.UID
ORDER BY RoleName, UserName

Enabling Service Broker on MSDB

Sometimes SQL Server jobs will be failed due to the following issue:

Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.

Solution
Enable Service Broker on MSDB …
To check Service Broker is enabled on MSDB, please execute the given query:
select is_broker_enabled,* from sys.databases where name = 'MSDB'

If the output value of “is_broker_enabled” is 0 -> Service Broker NOT enabled
Note: A value of 0 indicates that Service Broker message delivery is not activated in the MSDB database

If the output value of is_broker_enabled is 1 -> Service Broker IS enabled

To enable the Service Broker on MSDB database is as follows:

1) Connect to SQL Server
2) Stop the SQL Agent Services
3) Run the query "ALTER DATABASE MSDB SET ENABLE_BROKER"
4) Check the service broker is enabled or not by executing the below query:
4.1) select is_broker_enabled from sys.databases where name = 'MSDB'
4.2) The output value “1” means service broker is successfully enabled
5) Start the SQL Agent Services

Reason to stop SQL Agent services: Enabling Service Broker is not allowed when the database is in USE. Obviously SQL Agent will use MSDB database.

After the execution of the above, please exeute the below scripts to confirm that we are receving mails:

EXEC msdb.dbo.sp_send_dbmail
@recipients = user_name@mail.com',
@body = 'TEST.',
@subject = 'TEST' ;

SELECT @@SERVERNAME, it may return NULL / Wrong Server name

when you run SELECT @@SERVERNAME, it may return NULL / Wrong Server name.

Whenever you change the Network Name of Server Name, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

To confirm do the following:
SELECT @@SERVERNAME
select SERVERPROPERTY('MACHINENAME')
If the two above outputs shown different name means do the following:

To make SELECT @@SERVERNAME reflect the new changed Network Name perform the below task.

USE master
GO
EXEC sp_dropserver 'OLDSERVERNAME'
GO
EXEC SP_addserver 'NEWNETWORKNAME','LOCAL'
GO
Once done please restart the SQL Services and then Query SELECT @@SERVERNAME.

enabling DeadLoack trace on SQL Server

BEGIN
DBCC TRACEON (1204, 3605, -1)
WAITFOR TIME '23:30';
DBCC TRACEOFF (1204, 3605, -1)
END

to find the SQL Port nunber by t-sql from registries

DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

--For SQL Server 2000
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8
BEGIN
if @InstanceName='MSSQLSERVER'
Begin
SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END
ELSE
BEGIN
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END

EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey,
@value_name = 'TcpPort',
@value = @value OUTPUT

Select @@SERVERNAME as ServerName,@value as PortNumber
END


--For SQL Server 2005 and up
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'


EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey_InstanceName,
@value_name = @InstanceName,
@value = @value OUTPUT
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey,
@value_name = 'TcpPort',
@value = @value OUTPUT

Select @@SERVERNAME as ServerName,@value as PortNumber
END

Query for knowing SQL Server Uptime.

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))

ELSE

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name like N'%Agent%')

BEGIN

PRINT 'SQL Server is running but SQL Server Agent <> running'

END

ELSE BEGIN

PRINT 'SQL Server and SQL Server Agent both are running'

END

Enabling page level locking

Sometimes index rebuild task will be failed due to the following issue:

Description: Executing the query "ALTER INDEX [IDX_XXX_OF_DATE] ON [dbo].[DI..." failed with the following error: "The index "IDX_XXX_OF_DATE" (partition 1) on table "XXXX_OF_CF" cannot be reorganized because page level locking is disabled.".

Solution

Enable page level locking…

To find the disabled page level locking indexes, please execute the given query:

SELECT * FROM sys.indexes WHERE allow_page_locks = 0

Copy the index names from the above output and enable locking by executing the below query:

Alter Index <index name> On <table name> Set (ALLOW_PAGE_LOCKS = ON)

If we want to do the same for multiple indexes in single database at a time, please execute the following:

Use <dbname>

GO

select 'alter index ['+s.[name]+'] on ['+t.[name]+'] Set (ALLOW_PAGE_LOCKS = ON);' from sys.indexes as s inner join sysobjects as t on s.[object_id] = t.[id] where s.[allow_page_locks] = 0 and is_unique = 0

Copy the given OUTPUT and paste it into new NEW QUERY WINDOW and execute it.

Check for disabled indexes and re-run the job.