Wednesday, December 26, 2007

SQL 2005: Performance Counters are missing after installation

After installation of SQL Server 2005; Observed that SQL Server performance counters missing.

Below Query returns Zero rows.

select * from sys.dm_os_performance_counters
or
select * from sysperfinfo

Solution:
http://technet.microsoft.com/en-us/library/ms187743.aspx

Look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions" This denotes that performance counters were not enabled. The errors immediately prior to the 3409 listing should indicate the root cause for the failure of performance counter enabling.

For more information about setup log files, see How to: View SQL Server 2005 Setup Log Files

Tuesday, December 25, 2007

Error when executing sp_helpdb


Error below would occur:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb,
Line 53
Cannot insert the value NULL into column 'owner',
table 'tempdb.dbo.#spdbdesc_____________________________ ___
__________________________________________________ _________
________________000100048505'; column does not allow
nulls. INSERT fails.

Solution:

1) Run Query below, to find the databases which has incorrect owner.
SELECT name, SUSER_SNAME(sid)
FROM master.dbo.sysdatabases
WHERE SUSER_SNAME(sid) IS NULL
2) Execute
sp_changedbowner 'sa'


Powered by ScribeFire.

Sunday, December 23, 2007

How to find is Operating System is 32 Bit or 64 Bit

To find out if your operating system is 64bit or 32bit, open a
command prompt and type "SET Processor" <enter>. This will show
you all the environment variables that start with PROCESSOR. For
example, if your operating system is 64bit, whether AMD or Intel eith
EM64T extensions, you'll see:

PROCESSOR_ARCHITECTURE=AMD64

On an Intel Itanium you would see:

PROCESSOR_ARCHITECTURE=IA64



Powered by ScribeFire.

Monday, December 10, 2007

Bring the database which is in suspect

Steps intend to do to get the database online:

1. We then execute

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
- So that it would allow us to make changes directly to the SYSTEM tables(this option is used in extreme circumstances).

3. The following command lets us put the database in an emergency mode

Update SYSDATABASES set status = 32768 where name = 'SampleDB'
- In Emergency Mode it would allow us to perform certain actions which would not be possible in either Normal or Suspect Modes.


4. Having put the database in Emergency Mode we can then create a new log for the database since the current one can corrupt or inaccessible.



DBCC REBUILD_LOG('SampleDB', ‘C:\Data\SampleDB_newlog.ldf’)
- After this we can check the current files of the database using sp_helpfile command after changing the database context in the Query Analyzer to the affected database

5. We then check for consistency issues on the database by executing:
DBCC CHECKDB('SampleDB')
Go
- If the above command fails it usually recommends the next available option to use. You have to clearly understand the option before you try it since it could result in data-loss

6. Turn off the option to make direct changes to the SYSTEM tables using:
sp_configure 'allow updates', 0
reconfigure with override
Go

7. Then proceeded to take a complete database backup of the database.