SQL Server and MS BI With a Geek Touch

DBCC is one of the most commonly used commands by DBAs across the globe. Today I will introduce a simple way to reindex a specific or all indexes in a table using DBCC.

The command used for this is

DBCC DBREINDEX

General Sytax

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

Read more…

Renaming a database is not done very often. However, I have done it myself many times.  this can be done in two ways. The common method used for rename is by using ” Alter Database ” script. An example is as shown below.

ALTER DATABASE OldDbName MODIFY NAME = NewDbName

The above script will rename the database “OldDbName” with new name “NewDbName”. Another way of acheiving the same is by using the stored Procedure “SP_RENAMEDB” The syntax is as below

EXEC SP_RENAMEDB 'OldDbName' , 'NewDbName'

“Alter” is preferred over the SP_RenameDB as the SP will be deprecated in the future version of  SQL Server.

SQL Server might throw an error while renaming a database as below.

This is due to the existing conections to the database. to overcome this error,

1.you need to drop all the connections to the database.  Refer to my previous blog for more info on this.http://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/

2. You can set the database to single user mode . refer the link for more info on this. http://awesomesql.wordpress.com/2009/08/04/changing-the-collation-of-a-database/

There are instances when we may have to drop all the connections to a database for example to rename a database. The script below can be used to drop all the connections to the database.

DECLARE @dbname nvarchar(128)
SET @dbname = 'DB name here' -- db to drop connections
DECLARE @processid int
SELECT @processid = min(spid)
from master.dbo.sysprocesses
where dbid = db_id(@dbname)
WHILE @processid IS NOT NULL
BEGIN
EXEC ('KILL ' + @processid)
SELECT @processid = min(spid)
from master.dbo.sysprocesses
where dbid = db_id(@dbname)
END

You might have come across the error as highlighted below.

Cannot execute as the database principal because the principal "sec_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.

This problem usually occurs when You back up a database from an instance of SQL Server 2005 and  then, you restore the database to an instance of SQL Server 2005 that is installed on another computer. The reason for the error is because SQL Server cannot find a login that matches the security identifier of the impersonated user.

The easiest way to solve this is to create a login and alter the user name/ principal used in the restored database to point to this login. The login can be created using a simple statement as below

CREATE LOGIN SEC_USER  WITH PASSWORD = 'YourPassword'

You can get more info on creating logins from http://technet.microsoft.com/en-us/library/ms189751.aspx .

now alter the principal/User in the restore database using the alter user script. A sample is as given below.

Alter User Sec_User with login  = SEC_USER;

This should solve the error caused by restoring the database. The error can also be a result of lack of permissions to the user. In these cases check / modify user permissions.