SQL Server: Prevent Accidental Database Drop with DDL Triggers !!

Tags

, , , ,

Today I am going to share a very simple yet useful piece of code that I have implemented in many SQL instances over the years to prevent the databases from getting dropped accidentally. One of the simplest way is to take the help of DDL triggers that was introduced since SQL Server 2005. Below is the code that I have implemented:

CREATE TRIGGER [Trig_Prevent_DB_Drop] ON ALL SERVER
FOR DROP_DATABASE AS

IF SUSER_NAME() NOT IN (‘sa’)
BEGIN
RAISERROR (
‘Database cannot be dropped from the Server. Please contact Administrator’
,16
,1
);

ROLLBACK;

END

GO

Please share if any other alternative is there as well.

Cheers,

Subhro Saha

SQL Server: Throughput Benchmark !!

Tags

, ,

Today one of my reader mailed me which counters should be captured to create a baseline for measuring the throughput of SQL Server. As per me, two counters are sufficient to measure the throughput of SQL Server: Transaction/sec counter in the SQL Server Databases object and the Batch Requests/sec counter in the SQL Server General Statistics object.

Let us understand the difference between these two counters. ” Batch Requests/ sec” tracks the information about all TSQL command batches received per second even if they don’t participate in a transaction. Whereas  “Transaction/sec” doesn’t measure activity unless it’s inside a transaction or it is a DML statement like INSERT\UPDATE or DELETE.

If in an environment 25 percent of workload is simple SELECT statements that are not in any transactions then Transaction/sec will be off by 25 percent, hence Batch Requests/sec is a better indicator of throughput.

Cheers,

Subhro Saha

 

SQL Server: Error: 15138-The database principal owns a schema in the database, and schema cannot be dropped !!

Tags

, , , , ,

Today one of my reader asked a question about how to fix the error while trying to remove a login from a database. The reader was getting the below error:

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

The error message of SQL Server is self explanatory as there were schema associated with the user and we have to transfer those schema before removing the User.

In this post I will explain the workaround for this error:

Lets assume I am trying to drop a user named “TestUser” from DemoDB database.

Now, run the below query in the database from which we are trying to drop the user.

Use DemoDB ;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘TestUser’)

In my test scenario, I got the below result set from the above query:

Image

Then, use the names found from the above query below in place of the SchemaName below. And drop your user.
   
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
GO
DROP USER TestUser

In my Test scenario I used the below queries:

ALTER AUTHORIZATION ON SCHEMA::db_securityadmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo;

GO

DROP USER TestUser

Hope this helps.

Cheers,

Subhro Saha

SQL Server : Timeout occurred while waiting for latch: class ‘LOG_MANAGER’ !!

Tags

, ,

Today in one of the Production Server we were continuously getting an error “Timeout occurred while waiting for latch: class ‘LOG_MANAGER'” and our application almost came to standstill. At early hour, I got an emergency call and had to respond to it quickly.

Looking at the latch wait error it became clear to me that a transaction log is growing because it could not clear\truncate in timely fashion for some reason. Firstly find the database where log is growing and then we can find out what is preventing log clearing by below command:

SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N’database_Name’

In my case, the log file was inappropriately sized and hence it was increasing frequently which was the main reason for the issue. I sized the log file appropriately and since then we have not faced any issue.

Cheers,

Subhro Saha

SQL SERVER – Fix : Error : Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO for OLE DB provider SQLNCLI for linked server LinkedServerName !!

Tags

, , , , ,

Today I was trying to run a query from a SQL Server 2008 R2 64bit Test machine which connects to a 32bit SQL Server 2000 Test machine through a Linked Server. When I tried running the query after creating the Linked Server, it throwed up the following error message.

OLE DB provider “SQLNCLI10” for linked server “us-XXX-X-XXXXX” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10” for linked server “us-XXX-X-XXXXX” returned message “The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.”.
Msg 7311, Level 16, State 2, Procedure sp_test, Line 94
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “us-XXX-X-XXXXX”. The provider supports the interface, but returns a failure code when it is used.

While doing a some research, I found a small workaround for this, that seem to have been successfully implemented by many . **This is not a solution that I came up with but wanted to share it with the community.**
Just create the following Stored Procedure on the SQL Server 2000 instance, in the MASTER database and GRANT EXECUTE to the Public.

USE master

CREATE PROCEDURE sp_tables_info_rowset_64 @table_name SYSNAME
,@table_schema SYSNAME = NULL
,@table_type NVARCHAR(255) = NULL
AS
DECLARE @Result INT

SET @Result = 0

EXEC @Result = sp_tables_info_rowset @table_name
,@table_schema
,@table_type
GO

 Hope this helps you.

Cheers,

Subhro Saha

 

SQL Server: SQL Server 2000 DTS Runtime for SQL Server 2008 !!

Tags

, ,

Today one of my friend wanted to migrate SQL Server 2000 DTS packages to SQL Server 2008 but was finding it difficult to get all the required installation links that are required for the same.

So I thought it is a good idea to create a blog for the same.

We need to install below two things:

1.Microsoft SQL Server 2000 DTS Designer Components

2. Microsoft SQL Server 2005 Backward Compatibility Components:

That’s it !! It should work for you now.

Let me know if there is any other issue you facing.

Cheers,

Subhro Saha

SQL Server: Error while Recycling the Errorlog.

Tags

, ,

Today while trying to recycle errorlog in one of the Production server, I got the below error:

Unable to cycle error log file from ‘D:\SQL SERVER\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’ to ‘D:\SQL SERVER\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.1′ due to OS error ’32(The process cannot access the file because it is being used by another process.)’. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access.”

Basically it means that file is in use and hence it is cannot create the new file. There is a easy query to find out which process is currently using the Errorlog file:

SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
AND session_id <> @@spid
AND (
TEXT = ‘xp_readerrorlog’
OR TEXT = ‘sp_cycle_errorlog’
)
ORDER BY start_time

If we can go ahead and kill the process then we can Recycle the errorlog..

Hope this helps..Keep debugging 🙂

Cheers,

Subhro Saha

 

SQL Server: Script for finding Database Size from SQL Server 2000 onward !!

Tags

, , , ,

Today I was working on inventory analysis and I needed to find out the database size information across all the servers. Unfortunately few of our servers are still in SQL Server 2000 ( Yeah..I am not Kidding..that’s true !!) and hence I had to come up with a script that would run in all the environment. I had a script in my inventory ( Sorry, I don’t remember the actual author of this tool neither I have the link, if anyone is aware of the actual site then please let me know and I will mention it in my blog)

Below is the script for the same:

CREATE TABLE #db_files (
db_files VARCHAR(300)
,file_loc VARCHAR(300)
,filesizeMB DECIMAL(9, 2)
,spaceUsedMB DECIMAL(9, 2)
,FreespaceMB DECIMAL(9, 2)
)

DECLARE @strSQL NVARCHAR(2000)
DECLARE @dbName VARCHAR(2000)

DECLARE @getDBname CURSOR SET @getDBname = CURSOR
FOR
SELECT NAME
FROM sysdatabases

OPEN @getDBname

FETCH NEXT
FROM @getDBname
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @strSQL = ‘ use ‘ + @dbName + ‘ INSERT INTO #db_files select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,”SpaceUsed”)/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,”SpaceUsed”))/128.000,2)) as FreeSpaceMB from dbo.sysfiles a ‘

EXEC sp_executesql @strSQL

FETCH NEXT
FROM @getDBname
INTO @dbName
END

CLOSE @getDBname

DEALLOCATE @getDBname
GO
select * from #db_files
Drop Table #db_files

 It will produce the below output:

Image

Hope this will help you.

Cheers,

Subhro Saha

List members of a Active Directory group using command line.

Tags

, , ,

I will keep today’s blog very short and simple.  Today I am going to write about a command that I use on day to day basis to list the members of a particular Active Directory group.

We can use the below command in a command prompt for listing all the members in a AD group :

net group /domain <Active Directory Group Name>

Let me quickly show this in a command prompt:

Image

Hope this helps.

Cheers,

Subhro Saha