Skip to content

Archive for May, 2012

May 25

How to change Netezza database to read only mode?

Netezza have not documented any procedure to change database in read only mode. But we do have a commands to change database to read only and then change back to read write.

To change the database to read only mode you can run below command in SYSTEM database. Only the admin user or any user with alter privileges on a database can run below commands.

alter database TestDB lock for update;

This commnad will immediately lock TestDB to read only mode. All user sessions (other than user which ran the command) will get error messages if they try to do any modifications to this DB. This will only allow the current user session (user which ran this command) to do any modificatins to this database. Once this session is closed, then no one can make any changes untill we unlock the database for changes.

To verify if your database is in read only mode or not, you can run below query and look for DBLOCKTYPE value as ‘U’

select DATNAME, DBLOCKPID, DBLOCKTYPE from _T_DATABASE

Now to change this DB back to read write mode, run below command in system database.

alter database TestDB unlock;

These scripts will run good for TwinFin systems with Netezza version 6 plus. Have not tested on Netezza version below 6.

May 11

Create file encountered operating system error 3

Applying SQL Server 2005 SP4 may fail with error like below:-

In bootstrap log you will see below message -

Product                   : Database Services (MSSQLSERVER)

Product Version (Previous): 4060

Product Version (Final)   : 

Status                    : Failure

Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log

Error Number              : 29537

Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again. 

Summary

     One or more products failed to install, see above for details

     Exit Code Returned: 29537

Also you will see below error message in windows event viewer under application Log:-

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘F:\MSSQL.1\MSSQL\DATA\temp_MS_AgentSigningCertificate_database_log.LDF’.

Resolution

You will see below error when the default database data and log file path (mentioned under SQL Server instance properties –> Database Settings) does not exist at OS level. Possibly after installation some one have moved the data and log files to new location and have deleted the old folder structure, but forgot to update the default data and log file path.

To resolve the issue, just update the default database data and log file path under  SQL Server instance properties –> Database Settings and prove a valid path. Then try running your installer again.