Home » Posts tagged 'SQL 2000'

Tag Archives: SQL 2000

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

Data Purity issue after upgrading SQL 2000 to later versions


In SQL Server versions prior to 2005, it was possible to have invalid data values into a database. That can lead to incorrect result or issue at time of query execution.

SQL Server 2005 brings new option to the DBCC CHECKDB and DBCC CHECKTABLE commands to solve this issue. “DATA_PURITY”, The new option to check for issues where column values are not valid or out-of-range. In SQL Server 2005 data purity check is enabled by default & performed each time DBCC CHECKDB runs on database. But this is not same for upgraded databases.

For upgraded databases, We need to run below DBCC command on database & In case DBCC CHECKDB with DATA_PURITY got completed successfully, a bit dbi_dbccFlags will be flipped from 0 to 2. Now from future onwards data purity is enabled by default for upgraded databases as well & it will be performed each time DBCC CHECKDB runs.

DBCC CHECKDB with DATA_PURITY

· How to check Data purity for your SQL instance ?

DBCC TRACEON (3604)

GO

CREATE TABLE #DBCC (

PARENTOBJECT VARCHAR(255),

[OBJECT] VARCHAR(255),

FIELD VARCHAR(255),

[VALUE] VARCHAR(255)

)

CREATE TABLE #DBCC2 (

DATABASENAME VARCHAR(255),

PARENTOBJECT VARCHAR(255),

[OBJECT] VARCHAR(255),

FIELD VARCHAR(255),

[VALUE] VARCHAR(255)

)

EXEC MASTER.DBO.SP_MSFOREACHDB

‘USE ? INSERT INTO #DBCC EXECUTE (”DBCC DBINFO WITH TABLERESULTS”);

INSERT INTO #DBCC2 SELECT ”?”, * FROM #DBCC;

DELETE FROM #DBCC’

SELECT DATABASENAME,

(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND FIELD = ‘DBI_DBCCLASTKNOWNGOOD’) LASTGOODCHECKDBDATE,

(SELECT VALUE FROM #DBCC2 WHERE DATABASENAME = A.DATABASENAME AND FIELD = ‘DBI_CREATEVERSION’) DBCREATEVERSION,

(CASE VALUE

WHEN 0 THEN ‘DISABLED’

WHEN 2 THEN ‘ENABLED’

END) DATAPURITY

FROM #DBCC2 A

WHERE FIELD = ‘DBI_DBCCFLAGS’ AND

VALUE = 2 AND

DATABASENAME NOT IN (‘MASTER’,’MODEL’)

DROP TABLE #DBCC

DROP TABLE #DBCC2

GO

Sample Result :-

· Key Points :-

1) In SQL 2005 & above data purity is enabled by default that means dbi_dbccFlags flag value will be 2 only.

2) For Master & Model dbi_dbccFlags flag value will be 0 only

3) For DB upgraded from SQL 2000 or having dbi_createVersion value lower than 611 that means dbi_dbccFlags value will be 0 till the first time DBCC CHECKDB with data purity completed successfully

4) If dbi_dbccFlags flag value is 2 that means data purity is enabled by default

· Possible Error Message 2570 :- Refer :- http://support.microsoft.com/kb/923247

DBCC results for “Object1″.
Msg 2570, Level 16, State 2, Line 1

It’s always recommended to have look on data purity things after DB upgrade from prior versions of SQL Server 2005.

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

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

SQL Server 2000 Extended Support ending soon………21 days left


· On April 9, 2013, Extended Support for SQL Server 2000 will come to an end, and SQL Server 2000 will no longer be supported.

· What will change :

· Self-Help Online Support will be available for a minimum of 12 months. Example: Microsoft online Knowledge Base articles, FAQs, troubleshooting tools, and other resources, that help customers resolve common issues.

· Updates to this software will stop and customers will no longer receive patches, including security updates.

What Are available Options :

· Upgrade to a supported version of SQL Server.

· Find out more about a Custom Support Agreement (CSA).

· Run SQL Server 2000 unsupported with access to Self-Help Online Support only (not recommended).

· Upgrade to SQL Server 2005 is not recommended as SQL Server 2005 is also transitioned from Mainstream Support to Extended Support.

Refer Link : http://www.microsoft.com/en-us/sqlserver/support/support-updates.aspx

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

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

SQL Server Discovery Report


How can I check what all SQL server features and version are installed on my Machine ?

It’s a frequently asked question. You need to study your environment, need to login each instance to check version & other details, to answer this question.

Till SQL Server 2005, you have no direct solution.

But Starting from SQL Server 2008, Microsoft added a SQL Server discovery report as an useful tool under Tools page of Installation Center.

SQL Server Discovery tool

Lunch SQL Server Installation Center under Configuration Tools ( Click on the Start menu, go to All Programs, click to Microsoft SQL Server <Version Name>, under Configuration Tools click on SQL Server Installation Center) OR Directly Run Setup.exe & browse to Tools Page

Step 1

Step 2

Step 3

Report will be generate & open in default browser automatically

Report Location

The SQL Server Discovery Report will be saved automatically in %ProgramFiles%Microsoft SQL Server100Setup BootstrapLog<last Setup Session>

Other Useful Options

· You can also generate the Discovery report through the command line. Run “Setup.exe /Action=RunDiscovery” from a command prompt

· If you add “/q” to the command line above no UI will be shown, but the report will saved in %ProgramFiles%Microsoft SQL Server100Setup BootstrapLog<last Setup Session>

Limitation

· SQL Server discovery tool cannot be used to discover Remote SQL server installation

 

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)

Backup not starting for Database with Full Text Catalog…………..Failed to change the status to RESUME for full-text catalog in database. Error: 0x80043607


Issue :-

One of our Database with Full Text Catalog is not getting backed up. When I checked, backup keeps pending on 0% without any progress. In SQL server error log, I also found error related to Full text catalog that SQL server is facing issue in setting Full test catalog status.

These is some issue with FTS service due to which when backup ask FTS service to change Full text catalog status, it failed.

SQL server backup change status between PAUSE & RESUME before & after backup.

Failed to change the status to RESUME for full-text catalog “Test_FullTextCatalog” in database “Test”. Error: 0x80043607(An internal interface is being used after the corresponding catalog has been shutdown. The operation will be aborted.).

Solution :-

You need to restart the Full Text service to resolve the issue.

I have restarted the FTS & try backup again and it worked successful.

Please share if you face any more issue in this regard or any other possible solution.

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

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

How to get list of all available parameter of .EXE file ?


Today, We discuss How to get list of all available parameter of .EXE file OR How to find list of SQL Server setup parameters ?

Major problem is of all available parameter details. Sometime, We need to pass parameter to .EXE file to get some different & superior functionality.

We can get details of available parameters by passing “/?” to any .EXE file in windows cmd.

Example 1 : Check parameter of SQL Server Setup

List of parameters

Example 2 : Check parameter of Procmon.exe (other than SQL Server)

List of parameters

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