Home » Posts tagged 'SQL 2005'

Tag Archives: SQL 2005

SQL Server Cluster LooksAlive and IsAlive Check


Windows Cluster Service use the “heartbeat” process to check whether each node is alive at both the OS level and SQL Server level. Heartbeat signals works on UDP.

Microsoft Windows Cluster service perform 2 health checks to perform node & resource availability check.

LooksAlive Health Check

1) LooksAlive is a quick lightweight health check

2) Run at interval of 5 Seconds

3) Does not impact performance but does not perform a thorough check

4) The check will succeed if the service appears to be running even though it might not be operational

5) If Fails calls “IsAlive” health check

6) Polling interval can be changed by adjusting LooksAlivePollInterval property of Cluster service

IsAlive Health Check

1) Run at interval of each 60 Second

2) Perform more detail check then LooksAlive

3) Run @@SERVERNAME to ensure that SQL Server is responding to queries

4) Does not ensure that all user databases are operational

5) Retired 5 times & then try to reconnect SQL Server

6) If all 5 retry fails, the SQL Server resource fails & Windows Cluster service will try to bring it online on other node as per configuration

7) Polling interval can be changed by adjusting IsAlivePollInterval property of Cluster service

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Creation & Deletion of Database Snapshot by SQL Agent Job


Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.

So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.

Step 1 :-

DECLARE @CREATE_SS VARCHAR(MAX)

DECLARE @DT VARCHAR(100)

SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),':’,’_’)

SET @CREATE_SS =

‘CREATE DATABASE TEST_’+@DT+’ ON

( NAME = TEST, FILENAME =

”C:\TEST_’+@DT+’.SS” )

AS SNAPSHOT OF TEST’

EXEC (@CREATE_SS)

Step 2 :-

IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1

BEGIN

DECLARE @DROP_SS VARCHAR(MAX)

DECLARE @SS_NAME VARCHAR(100)

SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)

ORDER BY CREATE_DATE ASC

SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME

EXEC (@DROP_SS)

END

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

SQL Server 2005 || Database backup failed because Full Text catalog files are OFFLINE


Error : Database backup is getting fail with below reason.

The backup of full-text catalog ‘FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog becomes offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Cause : This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files and full-text catalogs in the database to be online.

Possible Reason : The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

Solution 1 (If FTS files are available but SQL server showing offline):

1. Locate the folder that contains the files for the problematic full-text catalog.

2. Run the ALTER DATABASE statement. Specify in the statement the correct location for the full-text catalog.

3. Rebuild the problematic full-text catalog in the database.

4. Perform a full backup of the database in SQL Server 2005 again.

Solution 2 (If FTS files are not available):

1. Detach database & attach again with all MDF, NDF, LDF & FTS files.

Web Reference :

1) http://support.microsoft.com/kb/923355

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Renaming or Changing SQL Server Cluster Instance


Renaming or Changing SQL Server cluster instance process is bit different from renaming a stand-alone instance.

Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/)

Refer : Rename or Change SQL Server Standalone Named Instance (http://mssqlfun.com/2014/07/29/rename-or-change-sql-server-standalone-named-instance/)

The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). You can change the name of the virtual server, you cannot change the instance name like in the case of standalone named instance. You can change a virtual SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance part of the name, instance1, will remain unchanged.

Rename a virtual server :-

1. Using Cluster Administrator, change the SQL Network Name or SQL Server DNS Name to the new name.

2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.

3. Bring the SQL Server resource back online.

Verify the Renaming Operation :-

1) Using @@ServerName

2) Using sys.servers

Minimize network propagation delay :-

ipconfig /flushdns

ipconfig /registerdns

nbtstat –RR

You are done ! You should ask your application teams to reconnect with new Name or if they are using IP address then no changes required.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Antivirus Exclusion Policy for SQL Server


Anti-virus & SQL Server on one system together are friends not enemies, if configured properly.

Anti-virus are very useful programs from security, audit & venerability detection & removal point of view. But if team managing anti-virus server did not configure anti-virus policies properly then your SQL Server is going to face the problem.

Here, we will discuss the file types that must be in exclusion list of anti-virus scanning policy. In other words, Let anti-virus programs deal with what they do best, and let SQL Server handle what it does best and avoid, at all possible costs, any interaction between the two

1. Binaries: Or the the paths to the actual executable for any of your running SQL Server Services (MSSQL, SQL Server Agent, SSAS, etc). Typically these are found, by default, in the C:\Program Files\Microsoft SQL Server folder – though this could easily be a different path on many production machines. (And, note, you’ll likely want to make sure that C:\Program Files (x86)\Microsoft SQL Server is included in any exclusions as well on x64 machines).

2. SQL Server Error Logs : Not your database log files, but the text files that SQL Server uses to keep its own ‘event logs’ running or up-to-date. (Which, in turn is also different than Windows’ system event logs as well.) By default the path to these files is, in turn, covered in the paths outlined above – or it’s part of the ‘program files’ data associated with your binaries – though you CAN move the location of these logs if desired (as an advanced operation via the startup parameters).)

3. Data And Log Files: Your actual .mdf, .ndf, and .ldf files – or the locations of your data files and log files. (Which you’ll want to make sure get excluded from anything that anti-virus monitors – otherwise creation of new databases, file-growth operations, and other normal ‘stuff’ can/will get blocked by anti-virus operations – which would be fatal in many cases.)

4. Backups: Yes, the path to any of your backups – or backup locations is also something you’ll want to make sure that anti-virus doesn’t monitor.

5. Others: Any other files related to SQL server & for its proper working. Like .TUF, .SS, .TRC etc.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

ERROR || The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.


Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

· This is not the case with non-clustered index.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg(http://mssqlfun.com/)

Database Snapshot failed due to disk space issue


Database snapshot is really a useful feature. As we know, Snapshot database file will keep increasing parallel to the DML operations on database. But if disk containing snapshot file run out of space then what will be the impact on new transactions, database & snapshot?

Microsoft design snapshot in very well manner. If Snapshot failed to records the data changed by DML transaction due to disk space issue then your snapshot will become unusable & reaches to suspect mode and on the other hand your actual database will keep running un-impacted.

Snapshot database cannot be recovered from suspect state so you need to drop & recreate the snapshot database.

1) While running below transaction, We got error that unable to write on snapshot file due to disk space issue.

2) We check data in table to check id transaction completed successfully or failed due to snapshot issue. We found transaction is completed & database is running fine.

3) After that, When we try to select snapshot database, We got error that database is suspect mode.

4) We also try to bring snapshot database in emergency mode to see if we can troubleshoot it but We got error that this operation can not be performed on snapshot database.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Follow

Get every new post delivered to your Inbox.

Join 228 other followers