How to Trouble Shoot Transaction Log file Growth in SQLServer

How to Trouble Shoot T-Log file Growth============================ Enable the following TF for checkpoint:- TRACEFLAG 3512Description =’Write info on log space used at checkpoint time to errorlog’ TRACEFLAG 3502Description =’Send checkpoint state changes to errorlog’ TRACEFLAG 3504Description =’Send checkpoint summary to errorlog ,Displays number of pages written and other stats’  Make sure this TF 3505 is not enabled…

0

Unable to run SQLAgent job’s in SQL server 2000 Service Pack 4

Unable to run SQLAgent job’s in SQLServer 2000 Service Pack 4   In SQL Server 2000 Service pack 4. When we try to run a job, the job doesn’t run. We won’t see any failure messages either. When we click the job properties and then click on “Job History” it gives a message stating “No History…

0

How to add an IP Address when we Add new NIC to node where SQLServer2005 instance is running.

The only possible way to add an IP to SQLServer2005 after adding new NIC to the node where SQLServer2005 is installed (or) After adding second ip address to existing NIC is to either manually edit the registry or run Service pack/Hotfix setup again.   The only possible way to add an IP to SQLServer2005 after…

6

Different Status bits of sysdatabases in SQLServer

Sysdatabases.status and sysdatabases.status2 Bit of Sysdatabases in SQLServer======================================================================== <Script1> declare @status int;declare @status2 int; Set  @status=’65544′  –Replace your DB status here set @status2=’1090520064′ –Replace your DB status2 here SELECT CASE (@status & 1) WHEN 1 THEN 1 ELSE 0 END AS autoclose, CASE (@status & 4) WHEN 4 THEN 1 ELSE 0 END AS selectintobulkcopy,…

5

Installation of SQLServer2008 fails (The registry key SYSTEMCurrentControlSetServicesRsFx0102InstancesShares is missing)

Installation of SQLServer2008 might fail with below error    Detailed results:  Feature:                       Database Engine Services  Status:                        Failed: see logs for details  MSI status:                    Passed  Configuration status:          Failed: see details below  Configuration error code:      0xCD263ADC@1306@30  Configuration error description: The registry key SYSTEM\CurrentControlSet\Services\RsFx0102\InstancesShares is missing.  Configuration log:             C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090410_153833\Detail.txt   To resolve this issue:…

3

Installation of SQLserver2008 cluster fails on windows2008.(The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

Installation of SQLserver2008 cluster might fail on windows2008 with error mentioned below The cluster resource ‘SQL Server’ could not be brought online.  Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)   Root Cause This problem occurs because of a new security feature named…

12

How to monitor the Session and query which Consumes Tempdb.

The total space used by tempdb equal to the User Objects plus the Internal Objects plus the Version Store plus the Free Space. Use the Below Quer to Track what is consuming Space in Temp Db.=============================================================== SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],SUM(version_store_reserved_page_count) AS [version store pages used],(SUM(version_store_reserved_page_count)*1.0/128) AS [version store…

2

Error 1934 returned when you run Update Statistics from a job against table that has index on computed column

When you choose to update statistics in your Maintenance Plan, and you have index on “computed column” in that database, the maintenance plan will fail with the following error: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’   Root Cause:…

3

Using DMVs to find out the index usage history- SQLServer Index Usage

  SQLserver 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation. Using DMVs to find out the index usage history Over a period of time, you could create a lot of indexes on…

1