DBReindexing script runs through QA but fails through SQL Job

An interesting and new thing noticed with a script. A normal DB re-indexing script which has cursor for collecting DB names and another dynamic cursor for collecting table names and then performing ‘Alter Rebuild Index ALL’ on all tables of databases. Here the issue noticed is the script runs fine without any issue in query analyzer(QA) and rebuilds all indexes. BUT when scheduled through a job the same script fails with below error:

“A cursor with the name 'TableCursor' does not exist. [SQLSTATE 34000] (Error 16916).  The step failed.”

I verified weather any table/schema name conflicting with system functions but that’s not the issue. Thinking it’s a permission issue verified the owner of the job and made sure it’s ‘sa’,  but still job fails. When verified the code ‘TableCursor’ is the cursor I am trying to create dynamically to fetch table names. If it’s syntax error it should not run through QA as well.

But later on one of my colleague suggested weather the issue with CURSOR scope? And when verified the answer is YES. The scope of the cursor is the issue, so verified the DB option ‘Default CURSOR’ was set to ‘Local’ and once after changing this option to ‘Global’ this script started running fine through SQL job.

How to query data from 2 or more servers at a time without Linked Servers:

We can use Central Management Server(CMS) in SQL Server to achieve this.

Open SSMS and click on View and then select ‘Registered Servers’ as shown in below capture

Now it will connect to DB Engine and we can connect to Integration/Reporting/Analysis/Compact engines by clicking on the icons directly

Expand DB Engine and will notice 2 sections Local Groups or Central Management Server. We can use any of these options to connect servers. Now I am using CMS. By right clicking on CMS we need to select ‘Register CMS’ see below capture

Then a new window will where we need to select a CM server. After selecting a server save it.

Now I will create a group named ‘Test’ under this registered server as shown in below.

After creating Test group I will add 2 servers to this group. We can by right clicking on Test group we created as shown in below capture and select the ‘New Server Registration’.

I registered 2 servers one is MSSQLSERVERNEW and other is SQLEXPRESS servers under Test group.

Now by clicking on the Test group and select ‘New Query’ option a new query window will open and if I run any query it will fetch data from both servers that are registered under the Test group.

For example I will run “SELECT name FROM sysdatabases;”. If you look at the output it has databases names of both the servers as shown in below capture.

In the same way we can connect any number of servers and retrieve data from them.

Thanks

How to create tables in a schema other than dbo schema by default

First create Login if you don’t have already:

CREATE

LOGIN [best] WITH PASSWORD=N'best', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

then create user named ‘best’ with below command in the database you need. Login gives access only to server if you need to access a database you need to have that user in the DB

"CREATE

USER [best] FOR LOGIN [best] WITH DEFAULT_SCHEMA=[Test]"

make sure you give permission to this user 'best' to create tables.

Now login to server as user ‘best’ and create a table named ‘Test’ in the DB. And you will notice the table is created in Test schema by default.

Thanks

DBREINDEXING Fails for one particular DB with error : “SET options have incorrect settings: 'QUOTED_IDENTIFIER'.”

Issue:

DBREINDEXING job which performs both ONLINE and OFFLINE re-indexing operation running fine on other servers but failing in one server with below error:

“ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.”

Reason/Resolution:

As per microsoft if we have index on a computed column and when trying to perform re-indexing we need to SET 'QUOTED_IDENTIFIER' to ON.

So I just added the statement ‘SET QUOTED_IDENTIFIER ON ‘ before re-indexing code execution. This resolved my issue and the job went fine without issue.

 
Here the interesting thing I noticed is when I am executing code through SSMS it executes successfully but when I’m trying to run the same code using a SQL Scheduled job the step fails. Once after adding the command my job is running fine. This is because by default SQL Agent does not set 'QUOTED_IDENTIFIER' or ‘ARITHABORT’.

Why my mdf file still grows even I have ndf file?

A production DB has eat up all space of disk D. So we added new disk E and created a secondary (ndf) file for the DB. E currently has 100GB and D has 20GB free space left in them. After few days when verified free space of D drive has dropped down below 15GB. So was wondering why DB still growing in D as it has ndf file in E drive. When verified noticed mdf file of D drive was set to ‘Enable AutoGrowth’ and to ‘Unrestricted Growth’. So this option causing the DB to grow in D drive till entire disk space will be consumed and when it won’t find any more free space to grow then it will start using ndf file.

In order to avoid this we have to change the option of file growth from ‘Unrestricted Growth’ to ‘Restricted Growth’ by giving a value which is higher than the current mdf file size. Suppose current mdf file size is 10GB and you can set it to a restricted growth of 12GB(make sure we have ndf file which is set to Auto grow) and once mdf gets filled up(that is till 12GB) data will start storing in ndf file.

If its not set to restricted growth also this is not an issue as SQL Server will automatically decide to grow in ndf file once mdf gets filled up but till the time the entire space of disk will be eaten by mdf file if it’s set to grow unrestrictedly.

How to start SQL job of another server

I got a task to trigger a job of Server B when job of Server A completes. One way of doing is we can monitor how much time job is taking in Server A and accordingly we can schedule job of Server B. But here there might situations where the jobs take longer than expected time, if Server A job is taking only 5mins to complete and it is scheduled to start at 9AM and we have scheduled Server B job to run at 9:30AM and due to some reason Server A job takes more than 30-45mins and still didn’t completed even then the job of Server B starts by 9:30AM which was not supposed to happen.

So I thought to trigger the job of ServerB remotely. So plan is to trigger the Server B job once Server A job completes. To accomplish this we can use several ways and I have used command ‘sp_start_job’.

Sp_start_job serverB.msdb.dbo.sp_start_job @job_name = ‘Remotejob’

The above command when I ran on ServerA it has thrown below error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'ServerB' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Which clearly shows I have missed a basic thing of linking servers. We can link servers using sp_addlinkedserver. I have linked servers using below command and after I was able to see both servers names in sys.servers  table.

sp_addlinkedserver 'ServerB'

Now when I ran the above sp_start_job command again I received below error

Msg 7411, Level 16, State 1, Line 1
Server 'ServerB' is not configured for RPC.

What is this RPC? Its Remote Procedure Calls. When I checked the properties of Linked Servers which is available under Server Objects -> Linked Servers -> Providers -> ServerB (Right Click properties) -> Server Options

Here there are 2 options RPC and RPC Out, I have changed the ‘RPC Out’ option to TRUE which was FALSE. After setting the option to TRUE on both servers I ran the query again and this time it went fine.

As per main task to trigger server B job automatically I have added the command  “Sp_start_job serverB.msdb.dbo.sp_start_job @job_name = ‘Remotejob’”

as final step of the job in Server A, so automatically the last step of Server A job triggering the job of Server B.

Transaction log backup job fails with error : The media family on device 'D:\SQL\Backups\TestDB' is incorrectly formed. SQL Server cannot process this media family.

ISSUE:

One of our maintenance job for taking transaction log backup was failing and when checked the history of Maintenance Plan it has below error:

"The backup data at the end of "D:\SQL\Backups\TestDB" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Workaround:

So verified weather file exists in location or not and yes file exists. Then ran

RESTORE VERIFYONLY FROM DISK= 'D:\SQL\Backups\TestDB';

Received below error:

Msg 3241, Level 16, State 41, Line 1

The media family on device 'D:\SQL\Backups\TestDB' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Then verified the header of backup file using below command:

RESTORE HEADERONLY FROM DISK='D:\SQL\Backups\TestDB';

Output clearly shows that earlier backup is incomplete

RESOLUTION:

To resolve this issue first I have moved the existing backup file to a temp location and took fresh backup using maintenance. The job went successfully and once backup completed successfully deleted the corrupted backup file from temp location. We can delete the corrupted backup file as it is unusable anyway and need to take fresh backup.

Removing corrupted Analysis Services installation

Received a task to upgrade service pack of SQL Server 2008. Looks so simple rite yeah I felt so…. See how it went

Copied SP1 file to server and ran the file it took 5mins after running the exe and thrown below error.

Then after reading error one thing understood something wrong with Analysis Service. Noticed one more thing there are 3 other instances on same server okie so guessed issue with other instances as well as they also have Analysis service running. So searched and found only solution most of articles suggested is to REPAIR the SQL server (I never did that before). So scheduled a downtime and started to run repair of sql server with installation CD. It went fine till end and completed fully giving error for Analysis Service again (LOL), stating only the services/features that are fully installed can only be repaired.

Now the only option left is to re-install the services. I was not knowing how to un-install only particular feature of SQL Server. Searched and this time didn’t found any articles J on how to do that so went back to BOL and found how to do that. So as mentioned in BOL un-installed only Analysis Services from ‘Add/Remove Programs’. To remove certain feature go to Control Panel, select Add/Remove Programs and after clicking on SQL Server 2008 service it gives option to Uninstall\Change, after selecting that option it opens a windows where we can select the features we want to un-install:

After removing the service I have re-installed Analysis service from installation CD to the same instance. Now again ran SP1 but still it failed with same error as mentioned in beginning. So got no option to do hence verified with users weather they actually need AS service? Luckily none of them were using. So removed As completely from the instance and ran SP1 and it went fine now (thank god).

So as per my observation the options are either to run REPAIR if it fails then try re-installing the respective service and if that also fails we should re-install SQL SERVER. I would be happy to hear if any other ways of fixing this issue.

Then started upgrade of other instances where I haven’t touched As service still. It went for some time…..

and went till end successfully J

So finally upgraded all instances with SP1.

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.

Copying files between network locations

Most of times we need to transfer folders/files to different locations like today I got a request to copy backup files between network locations. When tried DOS then understood that DOS can’t recognize UNC paths (network paths like \\dirname\foldername). So when searched found command called ‘ROBOCOPY’, this will help in transferring files between network locations.

Syntax:

Robocopy \\machinename\foldername1  \\machinename\foldername2 filename

Above command will copy only files from 1 to 2. Robocopy has several options which helps in copying subfolders, attributes and so on.

To copy all files and subfolders we can use \S \E.

Example:

robocopy D:\Backup \\testserver\sqlbackup$\ AdventureWorks.bak

Refer below links for detailed information.

Links …  

SQL Server Upgrade plans

• Why does the business or department want to upgrade to SQL Server 2008?
• What SQL Server are in scope?
• What applications access the SQL Servers?
• What users leverage the applications?
• What are the user operating hours?
• Who are the points of contact\management for the users?
• What automated processes leverage the SQL Servers?
• What third party applications, controls, plug-ins, etc are needed?
• What are the upstream and downstream applications?
• Who are the key IT team members?
• When does the project need to be completed?
• Which management members are supporting the project?
• Which budgets are paying for the project?
• Have the budgets been finalized or is management waiting for estimates?

Original link

Moving System Databases files to new location (SQL Server 2008 R2)

Moving tempdb files to new location: (SQL Server 2008 R2)

·         Connect to server.

·         Run the query ‘select * from sysfiles’.

·         Get the current mdf/ldf file locations.

·         Run the command:

                      ALTER DATABASE tempdb

                      MODIFY FILE ( NAME ='tempdev',

                      FILENAME = 'New Location')

                      ALTER DATABASE tempdb

                      MODIFY FILE ( NAME ='templog',

                      FILENAME = ' New Location')

·         While moving 'tempdb' do not forget to mention tempdb file names also...

Eg: alter database tempdb modify file (name = 'tempdev',

filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERNEW\MSSQL\DATA\Tempdb\tempdev.mdf')

alter database tempdb modify file (name ='templog',

filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVERNEW\MSSQL\DATA\Tempdb\templog.ldf')

·         Restart SQL Server Services.

·         Connect back to server and run again above query to find mdf/ldf file locations.

·         Also verify in the folder weather files are moved to new locations.

·         For moving 'msdb','ReportServer','reportservertempdb' and 'model' databases we need to move files physically after using  above given command and restart sql server services.

·         SQL Server will start even if the files of ‘msdb’, 'ReportServer','reportservertempdb' database are not moved fully. But those DB's will not be accessible.
Note: SQL Server will not start if master or model database files are not moved correctly.

Original Page and moving master db to new location.

Orphaned users in SQL Server

When a database is moved from server A to server B the users will not be able to perform any actions this is due to login and user conflict. Such users that are having login conflict are termed as Orphan users.

Difference between login and users:

A login gives access to the server. So any login created will be able to connect to server but will not be able to access any user databases. In order to get access to database, a user should be created in database with mapping to the login. Once a user will be created with mapping to a login then it gives access to the database objects.

To explain in general terms, a society having different houses can be termed as a server with different databases and different rooms of each house are different database objects. So if any person to enter any room of a particular house, first he should be allowed to enter the society and then he should be allowed to enter a particular house and then the rooms.

Same way a user in SQL Server in order to access any database object should first get access to the server and then he should get access to individual databases and then to database objects.

In case of orphaned user, if user SCOTT is there already in the database AdventureWorks and the database is moved to another server but there is no corresponding Login at server level, then user SCOTT cannot access the database or server.

So in order to resolve this login should be created for the user SCOTT at server level and map it the user of AdventureWorks database.

To find Orphaned users:

exec sp_change_users_login 'report'

To fix orphaned users:

Exec sp_change_users_login ‘UPDATE_ONE’,’database_user’,’login_name’

Links …