Home » Articles posted by rohitmssqlfun

Author Archives: rohitmssqlfun

SQL Server 2000 || Disable sa login


Question : How to disable sa user in SQL Server 2000?

Reason : Due to Security requirement of audit, We have to disable sa user for all SQL instances in environment having SQL 2000 \2005\2008 & 2008 R2.

Answer : We can disable sa user from SQL Server 2005 onwards but this is not possible for SQL Server 2000.

We have below system store procedure available in SQL Server 2000 but below is applicable for windows login only.

EXEC sp_denylogin ‘exampleuser’

EXEC sp_revokelogin ‘exampleuser’

The only solution, We can do

1. Change the password to very strong

2. Clear Server Roles

3. Clear all Database Access

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/)

Wishing You a Very Happy Holi


Steps for Windows Cluster Installation on Windows Server 2008


Here, You have steps with screenshot to install 2 Nodes windows cluster on Windows Server 2008.

Step 1: Validate Cluster Configuration

a) Open Cluster Administrator & click on “Validate a Configuration”

b) Wizard for cluster validation appears, click Next

c) Add Node to create cluster

d) Select Type of test want to perform

e) Confirmation Wizard for Test

f) Test Completed, You can view report by clicking on View Report button

Step 2 : Create Cluster

a) Open Cluster Administrator and click on “Create a Cluster”

Note : You cannot create cluster until test validation completed successfully.

b) Create Cluster Wizard appears

c) Add Nodes to create cluster

d) Specify Windows Cluster Name & windows cluster VIP

e) Confirmation Wizard, check details & click Next

f) Cluster Installation Start

g) Cluster Created successfully, You can view report bu clicking on “View Report” button

Step 3: Add Quorum

a) Open Cluster Administrator, Right click on Storage & select Add a disk

b) Select the disk available in system

c) Disk successfully added in cluster storage

d) Select “Configure Cluster Quorum Settings” to create Quorum

e) Select Quorum type

f) Select the available cluster disk

g) Check confirmation & click Next

h) Configuration Started

i) Cluster Quorum Configuration completed, you can view report by clicking on “View Report” button

j) Cluster installation completed

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/)

System Configuration Check – Without Starting SQL Server Installation


Question : Can we do system configuration check with starting SQL Server installation? One of my friend need to submit it to get installation approval. This is to ensure that no configuration issue occur at time of installation.

Answer : Yes, We can do it.

Step 1: Run Setup.exe

Step 2 : Go to Tool & click “System Configuration Checker”

Step 3 : You are done, You got configuration report. You can ignore the warnings But for healthy system start only on 100% green.

Support : It support SQL Server 2008/2008 R2/2012.

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

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

Cumulative Update – 8 for SQL Server 2012 Service Pack 1 Is Now Available !


The 8th cumulative update release for SQL Server 2012 Service Pack 1 is now available. Cumulative Update 8 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU8 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

KB Article For CU8 of SQL Server 2012 SP1

§ CU#8 KB Article: http://support.microsoft.com/kb/2917531

Previous Cumulative Update KB Articles of SQL Server 2012 SP1

§ CU#7 KB Article: http://support.microsoft.com/kb/2894115

§ CU#6 KB Article: http://support.microsoft.com/kb/2874879

§ CU#5 KB Article: http://support.microsoft.com/kb/2861107

§ CU#4 KB Article: http://support.microsoft.com/kb/2833645

§ CU#3 KB Article: http://support.microsoft.com/kb/2812412

§ CU#2 KB Article: http://support.microsoft.com/kb/2790947

§ CU#1 KB Article: http://support.microsoft.com/kb/2765331

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

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

Cumulative Update – 15 for SQL Server 2008 Service Pack 3 Is Now Available !


The 15th cumulative update release for SQL Server 2008 Service Pack 3 is now available for download at the Microsoft Support site. Cumulative Update 15 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

Those who are facing severe issues with their environment, they can plan to test CU15 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.

KB Article For CU15 of SQL Server 2008 SP3

· CU#15 KB Article: http://support.microsoft.com/kb/2923520

Previous Cumulative Update KB Articles:

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

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

2013 with You & SQL Server


The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 41,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 15 sold-out performances for that many people to see it.

Click here to see the complete report.

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 130 other followers