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
select * from sysperfinfo


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.


1) Run Query below, to find the databases which has incorrect owner.
FROM master.dbo.sysdatabases
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:


On an Intel Itanium you would see:


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
sp_configure 'allow updates', 1
reconfigure with override
- 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:
- 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

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

Tuesday, November 13, 2007

Query to find computed columns

Query to find computed columns:


Tuesday, November 06, 2007

SET OPTION considerations when running DBCC with indexes on computed columns

SET OPTION considerations when running DBCC with indexes on computed columns
SET OPTION considerations when running DBCC with indexes on computed columns

Error Message:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

Also refer KB:

Powered by ScribeFire.

Monday, August 20, 2007

Update stats on database with Read only

sp_updatestats would fail if a database on "Read Only" with below error

Server: Msg 3906, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'doctrack' because the database is read-only.
Updating dbo.doclink1

Sunday, August 19, 2007

SQL Server Performance Audit Checklist

Identify SQL Server Hardware Bottlenecks
Performance Audit Checklist

Counter Name Average Minimum Maximum
Memory: Pages/sec

Memory: Available Bytes

Physical Disk: % Disk time

Physical Disk: Avg. Disk Queue Length

Processor: % Processor Time

System: Processor Queue Length

SQL Server Buffer: Buffer Cache Hit Ratio

SQL Server General: User Connections

Monday, July 30, 2007

How to use Kerberos authentication in SQL Server

On Situation like;

  This could be either because of a network problem (i.e. the machine could not
connect to the domain controller for authenticating the user) or the user
account (KUT01\SQL_rest_test_3 ) running sqlservr.exe does not have
permission to query the domain controller (KU). The later could happen if
sqlservr.exe is running using an domain account
(KUT01\SQL_rest_test_3) different from the user 'KU\SQLService' above 
the machine is on a different domain (KUT01) than 'KU\SQLService' and there
is no two-way trust.

        When you invoke the BULK Insert  from computer T080 , 
         inserting data into SQL Server on  T081\SQLTest03,

         and specifying a data_file on third computer
by using a UNC path         

Use below;

How to use Kerberos authentication in SQL Server

Powered by ScribeFire.

Monday, July 23, 2007

"Unable to validate product key" during installation of SQL Server 2000 Standard Edition.

"Unable to validate product key" during installation of SQL Server 2000 Standard Edition.
 Faced this problem today on Windows 2000 Advanced server with SP4.

Just followed as advised on the KB# 555496, it resolved the problem.

Powered by ScribeFire.

Friday, July 06, 2007

SQL Server - How To Tell If There Is A Trace Running

Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool. Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

Here is how you view the number of traces currently running:

SELECT count(*) FROM :: fn_trace_getinfo(default)
WHERE property = 5 and value = 1

Here is how you can find more detail about the running traces:

 SELECT * FROM :: fn_trace_getinfo(default)

You can terminate a trace with the 'sp_trace_setstatus' stored procedure using the traceid:

EXEC sp_trace_setstatus 1, @status = 0

EXEC sp_trace_setstatus 1, @status = 2

setting the status to 0 stops the trace
setting the status to 2 closes the trace and deletes its definition from the server

Powered by ScribeFire.

Friday, April 20, 2007

How to reassign the DTS package owner

In most cases, it doesn't really matter who owns the package - who is

executing it is more important. However, there is an undocumented

stored procedure called msdb..sp_reassign_dtspackageowner, which you

can use.

powered by performancing firefox

Thursday, April 19, 2007

SQL Server 2005 - Troubleshooting

PERFORMANCE Troubleshooting Performance Problems in SQL Server 2005 Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 SQL Server 2005 Waits and Queues

DEADLOCKS Troubleshooting deadlocks in SQL2005

CONNECTIVITY SQL Server 2005 Connectivity Issue Troubleshoot - Part I Troubleshoot Connectivity Issue in SQL Server 2005 - Part II Troubleshoot Connectivity Issue in SQL Server 2005 - Part III Understanding Kerberos and NTLM authentication in SQL Server Connections

START UP FAILURE Error Messages of SQL Server 2005 Start Up Failure

DATA RECOVERY Emergency mode repair DBCC CHECKDB Series

INDEXES New Tools to Diagnose Index Health Rebuilding Indexes, Disk Queue Length, and More

FRAGMENTATION DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000

TRANSACTION LOG;EN-US;873235 How to stop the transaction log of a SQL Server database from growing unexpectedly;EN-US;317375 A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

MEMORY PRESSURE;EN-US;309256 How to troubleshoot SQL Server error 8645;EN-US;907877 How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

TEMPDB Working with tempdb in SQL Server 2005 TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild

SQLOS - SCHEDULING How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888

BOOKS ONLINE LINKS Troubleshooting the Database Engine Troubleshooting Integration Services Troubleshooting Reporting Services

TOOLS Link para efectuar uma pesquisa através do nos Books Online SQLdiag Utility SQL Server 2005 Performance Dashboard Reports

Nota: Para utilizar esta nova ferramenta é necessário ter previamente instalado no servidor e no cliente o SQL Server 2005 SP2. SQL Server 2005 Best Practices Analyzer (CTP de Fevereiro 2007) SQL Server Profiler + System Performance Monitor Exemplo de um custom Report – TASKPAD view Events and Errors Message Center SQL Server Best Practices


Troubleshooting and Performance Tuning

TechNet Webcast: A SQL Server DBA’s Guide to CLR Integration (Level 300)

TechNet Webcast: Performance Diagnosis in SQL Server 2005 (Level 300)

TechNet Webcast: SQL Server 2005 Database Tuning Advisor (Level 300)

TechNet Webcast: SQL Server 2005 Troubleshooting: Supportability Features for SQL 2005 (Level 300)

TechNet Webcast: Troubleshooting Performance Problems in Microsoft SQL Server 2005 (Level 200)

TechNet Webcast: Troubleshooting SQL Server 2005 Integration Services (Level 300)


Online Indexing Operation in SQL Server 2005

Physical Database Storage Design

Database Mirroring Best Practices and Performance Considerations

SQL Server I/O Basics, Chapter 2 White Papers General

Wednesday, March 14, 2007

Unable to truncate trans. log after removal of replication.

ACTION: Problems with truncating the transaction log even though the

database is not configured for replication.

RESULT: When truncating the transaction log by executing DUMP TRANSACTION db

WITH NO_LOG the following error was generated:

"The log was not truncated because records at the beginning of the log are

pending replication. Ensure the Log Reader Agent is running or use

sp_repldone to mark transactions as distributed."

Also executing sp_repldone then fails stating, "The database is not


CAUSE: The database has been left in an intermediate state where replication log records is still available in the transaction log even though SQL Server does not have publishing enabled, the database is not published and no objects are marked for replication.

RESOLUTION: Enable publishing again and then run sp_repldone and after that remove replication again.

First, enable publishing:

1. In SEM, choose Tools\Create and Manage Publications

2. Select the database whose log we want to shrink and click Create


3. Select 'Next' on the Create Publication Wizard

4. Choose Yes, use local machine as the distributor, and then click Next

5. Click OK on any messages that might appear warning that SQL Agent is running under the local system account

6. Select Transaction Replication, the click Next

7. Accept No, do not allow immediate-updating subscriptions, and then click Next

8. Accept All subscribers will be servers running SQL Server, then click


9. Click 'Publish All" then click Next

10. Accept the default publication Name and Description, then click Next

11. Accept No, create a publication without data filters, then click Next

12. Click Finish. Enter the 'sa' password if prompted. Click OK, Close,

Close to exit remaining dialog boxes.

Second, clear log of transactions marked for replication:

13. Login to Query Analyzer as 'sa' and open the database who log we want to shrink.

14. Execute "sp_repldone NULL, NULL, 0, 0, 1"

15. Check that this worked using "sp_repltrans". It may take a little time before all the transactions have been marked as distributed.

16. Execute "dump tran with no_log"

17. Confirm that the log file shrank by running "DBCC sqlperf(logspace)". It may take a little time before the % used shows as decreased.

18. Disable publishing through SEM by choosing Tools= />
Publishing= />

powered by performancing firefox

Wednesday, February 28, 2007

Server: Msg 8964, Level 16, State 1, Line 1


Server: Msg 8964, Level 16, State 1, Line 1

error: Object ID 515532920. The text, ntext, or image node at page
(1:377289), slot 1, text ID 897099563008 is not referenced.

Table error: Object ID
515532920. The text, ntext, or image node at page (1:1124698), slot 1,
text ID 896822476800 is not referenced.

CHECKDB found 0 allocation errors and 8 consistency errors in table 'CC_Document' (object ID 515532920).

CHECKDB found 0 allocation errors and 8 consistency errors in database 'crmproded'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (crmproded ).


The text fragments
listed in the CHECKDB output aren't referenced by any rows in the
clustered index. When a data row has a LOB value then usually (unless
you've tweaked the text_in_row option) the LOB value is stored off-row
with a root pointer stored in the data row itself. What's happened in
this case is that some data rows have been deleted but their
corresponding LOB values have not been. This should be benign but we
should try to work out why it happened.

powered by performancing firefox

Tuesday, February 20, 2007

SQL Server 2005 may take up to 24 hours to install...

While performing of SQL Server 2005 installation on Windows 2003 Server in a network after waiting
for over an hour for SQL Server 2005 to install, I decided to check the knowledge base just in case.

Lucky I did...

SQL Server 2005 may take up to 24 hours to install...