DBREINDEX maintenance job fails with error “Could not find database 'SQLTester' ". But SQLTester is actually a SCHEMA!!!

I have configured a job to perform reindexing operation on all tables of 'AdventureWorks' database. When I ran job it went fine for few minutes and thrown below error:

Msg 2520, Level 16, State 5, Line 16

Could not find database 'SQLTester'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.

On verifying, noticed there is no database named ‘SQLTester’ in the server but unfortunately the job is failing for the database AdventureWorks. So I ran the query (mentioned below)which I am using in the job step to display all the table names along with schema name

SELECT TABLE_SCHEMA+’.’+TABLE_NAME FROM information_schema.tables

WHERE table_type = 'BASE TABLE' AND TABLE_CATALOG = 'AdventureWorks' and

TABLE_NAME IN (SELECT name from sysobjects where xtype = 'U')

Here in the output I noticed a table which under a SCHEMA named ‘SQLTester’. I REALLY DON’T KNOW WHY SQL IS GIVING ERROR MESSAGE AS ‘CANNOT FIND DATABASE’ if it is actually a SCHEMA. Just tried changing the query (mentioned below) by adding ‘[‘ to the schema names.

SELECT '['+TABLE_SCHEMA+'].'+TABLE_NAME FROM information_schema.tables

WHERE table_type = 'BASE TABLE' AND TABLE_CATALOG = 'AdventureWorks' and

TABLE_NAME IN (SELECT name from sysobjects where xtype = 'U')

When I ran the re-indexing operation with above modified query it worked find even for the tables that are under the schema SQLTester. Now the job is running fine.

Unable to connect to SQL Server '(local)'. The step failed.

One of the SQL backup job failing with the error : Unable to connect to SQL Server '(local)'.  The step failed. . I was trying to take backup of ‘Test’ database, after seeing this error verified again and noticed the DB ‘Test’ exists and is accessible without any issue.

After verifying, noticed job step was configured in this way:

I have changed the ‘Database:’ option to ‘Master’ :

and ran the job again. It went fine without any issue and I was able to take backup of the Test DB without any issue. The error in job history looks bit weird but actual issue is the job step is referring to same DB for which it is taking backup. For my issue this worked.

Query to find Machine name, node names, SQL Server instances names and Active Node Name

Finally!!!! Finally!!!!!

After long wait I got time to find out the actual machine name and different cluster nodes of it and different SQL instances installed on it and the current active node among the nodes and along with SQL Server Version. WOW it cleared out my many confusions.

SELECT SERVERPROPERTY('MachineName') AS MachineName

go

SELECT * FROM sys.dm_os_cluster_nodes

go

DECLARE @Instances TABLE( Value nvarchar(100), InstanceNames nvarchar(100), Data nvarchar(100))

Insert into @Instances

EXECUTE xp_regread  @rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',  @value_name = 'InstalledInstances'

Select InstanceNames from @Instances

go

Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ActiveNode

go

SELECT @@VERSION

Links …

Search for a word in a folder having hundreds of files

Today as part of migration I was asked to search for the occurrence of the word INDIA in a folder named GROUP. I have to replace the name INDIA with New_India in each and every file and the folder has almost more than 300 files :O

Then I found a useful command in Google ‘findstr’. With this DOS command we can search for a particular word in entire directory. Using this command we can search in different ways based on our requirement.

For my example it will be:

Open command prompt.

findstr /s /i /m "\<INDIA\>" *.*

Here *.* indicates to search in all kinds of file formats and names.

S – searches for matching files in current folder and all its sub folders.

I – irrespective of case sensitivity

M – prints file name if it finds a match.

\< \> for beginning of the word and ending of the word respectively.

Links

Could not find a table or object named ''. Check sysobjects. [SQLSTATE 42S02] (Error 2501).

While performing DBREINDEX operation on all tables of a database using the below code

DECLARE @TableName varchar(100)
DECLARE tablename CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN tablename
FETCH NEXT FROM tablename INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@TableName, ' ', 80)
FETCH NEXT FROM tablename INTO @TableName
END
CLOSE tablename
DEALLOCATE tablename

The job keeps failing stating a table cannot be found.

Resolution:

Here issue is dbreindex is failing because the table is owned by different schema other than ‘dbo’. This can be avoided by concatenating the schema name while reindexing. So the query can be changed as below while fetching the table name.

select TABLE_SCHEMA+'.'+TABLE_NAME from information_schema.tables

Hope this helps atleast few!!!

Sample Databases in SQL Server 2008 R2

After installing SQL Server 2008 R2 noticed that sample databases are not available along with the installation. We have to install the sample databases separately.

Please use below links where you will get sample databases installation files for SQL Server 2005/2008/2008 R2 and Denali.

Links …

Restore failed for Server.(Microsoft.SqlServer.SmoExtended).

Restore failed with below error:

This error generally occurs when any of the user/process accessing the database you are trying to restore. In such case use ‘sp_who2’ or any other command and determine what users currently accessing the DB. Discuss with users and kill the SPID’s. (KILL <SPID number>). Once all sessions gets terminated it will allow to restore.

Another scenario:

In general this error raises when you are trying to restore a DB to another instance of same machine and when the mdf/ldf files of the DB are pointing to the files already being used by default instance.

So make sure the mdf/ldf files of named instance are pointing to its own location rather the default instance file locations.