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

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

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         
             \\Box3\Share_Data\

Use below;

How to use Kerberos authentication in SQL Server

Powered by ScribeFire.

"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.

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.

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.

sp_reassign_dtspackageowner

powered by performancing firefox

SQL Server 2005 - Troubleshooting

PERFORMANCE

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance Problems in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx SQL Server 2005 Waits and Queues

DEADLOCKS

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/05/617960.aspx Troubleshooting deadlocks in SQL2005

CONNECTIVITY

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/sql-server-2005-connectivity-issue-troubleshoot-part-i.aspx SQL Server 2005 Connectivity Issue Troubleshoot - Part I

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx Troubleshoot Connectivity Issue in SQL Server 2005 - Part II

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx Understanding Kerberos and NTLM authentication in SQL Server Connections

START UP FAILURE

http://blogs.msdn.com/sql_protocols/archive/2006/04/28/585835.aspx Error Messages of SQL Server 2005 Start Up Failure

DATA RECOVERY

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx Emergency mode repair

http://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx DBCC CHECKDB Series

INDEXES

http://www.microsoft.com/technet/technetmag/issues/2007/03/SQLIndex/?related=/technet/technetmag/issues/2007/03/SQLIndex New Tools to Diagnose Index Health

http://www.microsoft.com/technet/technetmag/issues/2007/04/SQLQA/default.aspx Rebuilding Indexes, Disk Queue Length, and More

FRAGMENTATION

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000

TRANSACTION LOG

http://support.microsoft.com/default.aspx?scid=kb;EN-US;873235 How to stop the transaction log of a SQL Server database from growing unexpectedly

http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375 A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

MEMORY PRESSURE

http://support.microsoft.com/default.aspx?scid=kb;EN-US;309256 How to troubleshoot SQL Server error 8645

http://support.microsoft.com/default.aspx?scid=kb;EN-US;907877 How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005

TEMPDB

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx Working with tempdb in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx TEMPDB Capacity Planning and Concurrency Considerations for Index Create and Rebuild

SQLOS - SCHEDULING

http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888

BOOKS ONLINE LINKS

http://msdn2.microsoft.com/en-us/library/ms188314.aspx Troubleshooting the Database Engine

http://msdn2.microsoft.com/en-us/library/ms138039.aspx Troubleshooting Integration Services

http://msdn2.microsoft.com/en-us/library/ms159135.aspx Troubleshooting Reporting Services

TOOLS

http://search.live.com/macros/sql_server_user_education/booksonline Link para efectuar uma pesquisa através do live.com nos Books Online

http://msdn2.microsoft.com/en-us/library/ms162833.aspx SQLdiag Utility

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en 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.

http://www.microsoft.com/downloads/details.aspx?familyid=da0531e4-e94c-4991-82fa-f0e3fbd05e63&displaylang=en SQL Server 2005 Best Practices Analyzer (CTP de Fevereiro 2007)

http://blogs.technet.com/beatrice/archive/2007/03/23/sql-server-2005-performance-troubleshooting.aspx SQL Server Profiler + System Performance Monitor

http://sqlblogcasts.com/files/folders/custom_reports/default.aspx Exemplo de um custom Report – TASKPAD view

http://www.microsoft.com/technet/support/ee/ee_advanced.aspx Events and Errors Message Center

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx SQL Server Best Practices

WEBCASTS

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)

WHITE PAPERS

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

http://www.microsoft.com/sql/techinfo/whitepapers/default.mspx White Papers General

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


published."





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


Publication





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


Next





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

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

Problem:

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

Table
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 ).



Cause:



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

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...

http://support.microsoft.com/kb/910070/en-us

Enabling Performance Counters in SQL Server 2000

Enabling Performance Counters in SQL Server 2000
This is an interesting problem we had in the Newsgroup. There are specific case where SQL Server's counters were missing. In this FAQ we assume that the installation of SQL Server 2000 is a default installation.

There are many reasons when the SQL Server counters will disappear, such as Permission, Server Crash, Registry Modification and Virus. To work around the symptom, please take the following actions:

1. Check if you have full permission to access the local folder: C:\Program Files\Microsoft SQL Server\MSSQL\ Make sure that you can access this folder or your current account is located in local administrator group.

2. Make sure there is no third-party application which add keys in Register to disable the performance counters.You can open the registry to check if there is a key named "Disable Performance Counters" valued "1" in:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]
If it exists, please delete it.

3. Under the register key:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]
Make sure the following keys with the value DO EXIST. If not, please create a new one and add the value.

# Note # : The "Last Counter", "Last Help", "First Help", "WbemAdapFileSignature", "WbemAdapFileTime", and "First Counter" values can vary between SQL Server installations.

"Library"=" C:\PROGRA~1\MICROS~3\MSSQL$~1\BINN\SQLCTR80.DLL "
"Collect"="CollectSQLPerformanceData"
"Open"="OpenSQLPerformanceData"
"Close"="CloseSQLPerformanceData"
"Last Counter"=dword:000016ce
"Last Help"=dword:000016cf
"First Help"=dword:000015bb
"WbemAdapFileSignature"=hex:d1,70,dc,f8,a7,75,5e,e4,9e,e6,dd,91,9e,cd,06,65
"WbemAdapFileTime"=hex:d6,34,9d,95,64,0d,c2,01
"WbemAdapFileSize"=dword:00008238
"WbemAdapStatus"=dword:00000000
"First Counter"=dword:000015ba

4. Unload the SQL Server Counters. In command line, execute "unlodctr MSSQLServer" (Without quotation, the same as below)

5. Reloading the SQL Server Counters
(a) In command line, locate the current folder to C:\Program Files\Microsoft SQL Server\MSSQL\BINN
(b) Execute "lodctr sqlctr.ini"

6. Stop SQL Server Services and Restart the Services
(a) In command line, execute "net stop mssqlserver"
(b) Execute "net start mssqlserver"
(Or you can perform it in Service Manager or in System Services)

7. Open Perforam Monitor to check if the counters appear
(a) In command line, execute "perfmon"
(b) Add and check if the counter appear


PS: Note this FAQ is based on the conversation in the sqlserver.setup newsgroup

IO is frozen for snapshot / IO is thawed

Following errors registered on a SQL Server Errorlog:

19-12-2006 23:49 :58.27 Database USEDDB:IO is thawed
19-12-2006 23:49 :52.04 Database USERDB:IO is frozen for snapshot

It seems the problem would caused by

1. EMC frame tools to create an SRDF to copy to our disaster site.
This occurs on SAN while the snapshot backups take place.
While databases are 'frozen' they are essentially offline and no users will be
able to connect.

2. NTBackup. This program has a commandline switch called /SNAP.
Whenever a ntbackup, even to backup 2 simple text files, all open files are frozen for a moment. Somehow, if your machine contains a SQL Server instance, a dump for all databases is created to a virtual device.

Also refer:
Error 800423f4 appears in the backup log file when you back up a volume by using the Volume Shadow Copy service in Windows Server 2003

The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database

Clustering Sites

http://www.clusterhelp.com/

http://www.msmvps.com/clustering

http://www.nw-america.com

Error: 5180, Severity: 22, State: 1

Error: 5180, Severity: 22, State: 1
Could not open FCB for invalid file ID 0 in database '%s'. Table or database may be corrupted.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276043

TargetServersRole

Use this "TargetServersRole" available on SQL Server 2000 SP3 and above to grant users who requires to view the all the jobs listed on SQL Server for a normal user.

Use msdb
EXEC sp_addsrvrolemember 'Corporate\HelenS', 'TargetServersRole'

Logshipping vs Database Mirroring

Applies to : SQL Server 2005

With Log Shipping:
Data Transfer: T-Logs are backed up and transferred to secondary server

Transactional Consistency: All committed and un-committed are transferred

Server Limitation: Can be applied to multiple stand-by servers

Failover: Manual Failover Duration: Can take more than 30 mins

Role Change: Role change is manual

Client Re-direction: Manual changes required

With Database Mirroring:
Data Transfer: Individual T-Log records are transferred using TCP endpoints

Transactional Consistency: Only committed transactions are transferred

Server Limitation: Can be applied to only one mirror server

Failover: AutomaticFailover

Duration: Failover is fast, sometimes <>

Role Change: Role change is fully automatic

Client Re-direction: Fully automatic as it uses .NET 2.0

BCP Error on SQLState = S1000, NativeError = 0

When trying on the below BCP Command
bcp master..syslogins out D:\MSSQL\Backup\PrimaryLogship\syslogins.dat -N -S SQLServer -U sa -P password

getting the error message as

SQLState = S1000, NativeError = 0
Error = [MS][ODBC SQL Server Driver] Unable to open BCP host data-file

Resolution:
The problem occured because of "Incorrect Folder Name"

Where to see Windows Error Message nos?

Server: Msg 3202, Level 16, State 1, Line 1
1) Status = 64 Write on 'c:\temp.dat' failed, status = 64
2) Status = 112 Write on 'c:\temp.dat' failed, status = 112

It can be found at:

D:\Documents and Settings\pz5qrt>net HELPMSG 112

There is not enough space on the disk.

A SQL Server cluster resource goes to a "failed" state when you try to bring the resource online in SQL Server 2000

When you try to bring a SQL Server cluster resource online for a virtual instance of Microsoft SQL Server 2000, you may notice the following behavior:

The SQL Server cluster resource goes to a "failed" state and does not come online.

You receive a combination of the following error messages on the computer that owns the SQL Server cluster resource.
Error message 1An event that is similar to the following is in the system event log:
Date: 08/05/2004 Time: 1:11:19 AM Source: ClusSvc Category: Failover Mgr Type: Error Event ID: 1069 User: N/A Computer: Description: Cluster resource 'SQL Server ()' in Resource Group '' failed. Error message 2An error message that is similar to the following is in the Cluster log file:
00000644.00000944::2003/11/30-18:11:30.360 SQL Server : [sqsrvres] Unable to read the 'VirtualServerName' property. Error: d.00000644.00000944::2003/11/30-18:11:30.360 SQL Server : [sqsrvres] OnlineThread: Error d bringing resource online.Error message 3Error messages that are similar to the following are in the SQL Server error log file:
2003-11-30 17:00:37.27 server Error: 17826, Severity: 18, State: 1 2003-11-30 17:00:37.27 server Could not set up Net-Library 'SSNETLIB'.. 2003-11-30 17:00:37.27 spid13 Starting up database 'SPB'. 2003-11-30 17:00:37.27 spid12 Starting up database 'BD_MTA'. 2003-11-30 17:00:37.27 spid14 Starting up database 'BD_SPF'. 2003-11-30 17:00:37.27 server Error: 17059, Severity: 18, State: 0 2003-11-30 17:00:37.27 server Operating system error -1073723998: .. 2003-11-30 17:00:37.27 server Unable to load any netlibs. 2003-11-30 17:00:37.27 server SQL Server could not spawn FRunCM thread.
CAUSE
The resource-specific registry keys that correspond to the SQL Server cluster resource that you are trying to bring online are missing. This problem also occurs if the values that correspond to the resource-specific registry keys are not correct.
RESOLUTION
Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.To resolve this problem, you must manually re-create the resource-specific registry keys that correspond to the SQL Server cluster resource. To do this, follow these steps:
1.
Click Start, click Run, type Regedit, and then click OK.
2.
In Registry Editor, locate and select the following registry key:HKEY_LOCAL_MACHINE\Cluster\Resources\\Parameters
3.
Create the following registry values in the Parameters registry key:For a default instance of SQL Server:
•
InstanceNameValue Name: InstanceNameValue Type: REG_SZ Value Data: MSSQLSERVER
•
VirtualServerNameValue Name: VirtualServerNameValue Type: REG_SZValue Data: For a named instance of SQL Server:
•
InstanceNameValue Name: InstanceNameValue Type: REG_SZ Value Data:
•
VirtualServerNameValue Name: VirtualServerNameValue Type: REG_SZValue Data:
4.
Quit Registry Editor.After you create the resource-specific registry keys, you can bring the SQL Server cluster resource online successfully.If you notice that a SQL Server Agent cluster resource cannot be brought online, you must create the same set of resource-specific keys that correspond to the SQL Server Agent cluster resource.

http://support.microsoft.com/?kbid=883732

SQL Server Proxy Error Messages

If the SQL proxy account not set up the below error message would appearMsg 50001, Level 1, State 50001xpsql.cpp: Error 997 from GetProxyAccount on line 472

When SQL Server startup account does not have the 'Local Admin' privilegesMsg 50001, Level 1, State 50001xpsql.cpp: Error 997 from GetProxyAccount on line 472

When the SQL Server Startup account does not have the"Replace Process level Token" Privileges on Local security policy ,
Msg 50001, Level 1, State 50001xpsql.cpp: Error 1314 from CreateProcessAsUser on line 504

When the 'SQL Sever proxy account does not exist or deleted from the Windows.Msg 50001, Level 1, State 50001xpsql.cpp: Error 1326 from LogonUserW on line 488