Important support changes for SQL Server 2000 and SQL Server 2005

There are some important support changes coming up for Microsoft SQL Server 2000 and SQL Server 2005. We want to make sure you know what’s happening so you can avoid the risk of running unsupported products and choose the right version of SQL Server for your business.

What is happening:

SQL Server 2000

On 4/9/2013, Extended Support for SQL Server 2000 will come to an end, and SQL Server 2000 will no longer be supported.

After this date:

·         Updates to this software will stop and so you will no longer receive patches including security updates.

·         Self-Help Online Support will be available for a minimum of 12 months.

SQL Server 2005

On 4/12/2011, SQL Server 2005 will transition from Mainstream Support to Extended Support, which includes:

·         Paid support (charged on an hourly basis per incident). Customers will no longer receive no-charge incident support and warranty claims, and won’t be able to request design changes or features.

·         Security update support at no additional cost.

·         Non-security related hotfix support will require a separate Extended Hotfix Support Agreement to be purchased within 90 days of the end of Mainstream Support – July 11th, 2011.

Microsoft Support Lifecycle Policy

The Microsoft Support Lifecycle policy took effect in October 2002, and applies to most products currently available through retail purchase or volume licensing and most future release products.

Through the policy, Microsoft will offer a minimum of:

·         10 years of support (5 years Mainstream Support and 5 years Extended Support) at the supported service pack level for Business and Developer products

·         5 years Mainstream Support at the supported service pack level for Consumer/Hardware/Multimedia products

·         3 years of Mainstream Support for products that are annually released (for example, Money, Encarta, Picture It!, and Streets & Trips)

Phases of the Support Lifecycle

Your options in moving forward:

SQL Server 2000

·         Upgrade to a supported version of SQL Server.

·         Find out more about a Custom Support Agreement (CSA).

·         Run SQL Server 2000 unsupported with access to Self-Help Online Support only (not recommended)

SQL Server 2005

·         Remain on SQL Server 2005 with Extended Support. You can still open support incidents and receive support, however you will not be able to request specific fixes.

·         Remain on SQL Server 2005 with Extended Support and purchase Extended Hotfix Support. You can then open support incidents and request specific fixes.

·         Upgrade to a supported version of SQL Server

Get more details on how to stay supported along with other important resources at: http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx

Source: Microsoft SQL Server Team Blog

...Happy SQLing
Thanks for Visiting and Sharing your Views

SQL Server Developer Tools "Juneau" CTP3 bits available now!

Are you a wanted Developer?
Take a look at the Pre-release SQL Server developer tools from Denali CTP3.
Donot forget to Provide your feedback to Microsoft.

http://www.microsoft.com/web/gallery/install.aspx?appid=JUNEAU10


...Happy SQLing
Thanks for Visiting and Sharing your Views

SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE!

Denali's Community Technology preview 3 Bits are now available. Start downloading and have fun with Denali (the most hiped and awaited product from Microsoft SQL Server team)

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

...Happy SQLing
Thanks for Visiting and Sharing your Views

Unable to Shrink Log File?

Log File Can only be Shrunk when :-

1) There are no Open Transactions in the Database

USE DB
DBCC OPENTRAN

2) When the Log File is not in use

DBCC LOGINFO
Verify that the File status <> 2 for all the Virtual log files.
 Backup the Log file to mark the file status to inactive.

3) Check if the Database is involved in Transactional Replication
If your database is involved in Transactional replication and you see there are no undelivered transactions, then execute the below

USE [database]
go
exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
go
DBCC Shrinkfile ('logfilename')

Increase in the Virtual Log files and Size will also lead to fail replication from delivering the transactions to the Subscriber.

...Happy SQLing
Thanks for Visiting and Sharing your Views

Review CPU Utilization history from SQL Server

There will be at times where we would be wondering as what happened in the history that caused the SQL Server slow down or poor performance.
one such clue to look at initially is the CPU pressure.

using the Below Query we can pull the CPU Utilization history for the last 30 minutes.
You can see CPU Utilized by SQL Server and Other Windows Processes.

DECLARE @ts_now bigint SET @ts_now = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); SELECT
SystemIdle
100 TOP(50) SQLProcessUtilization AS [SQL Server Process CPU Utilization], AS [System Idle Process], - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] FROM ( SELECT
record record.value('(./Record/@id)[1]', 'int') AS record_id, .value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS
record [SystemIdle], .value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS x ) AS y ORDER BY record_id DESC;

...Happy SQLing
Thanks for Visiting and Sharing your Views

Generate Scripts of all SQL Server Objects

...Happy SQLing
Thanks for Visiting and Sharing your Views

Identify bad / Unused Indexes

While tuning the performance of a query or Stored Procedure, we might want to first look at the bad and required indexes.

Bad Indexes:- There are indexes that are not being used and are resulting to bad execution plan.
These indexes are noted as Unused Indexes. Below Query helps you to find the unused indexes based on the last Usage Statistics. basically Indexes that are not used for Seek / Scan operations and used under inserts/updates in the Columns are treated as Unused Indexes.

FOR A SINGLE DATABASE

SELECT DB_NAME() AS [Database], o.name AS Table_name, i.name AS index_name, i.type_desc, SUM(s.used_page_count) * 8   AS IndexSizeKB, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement

FROM sys.indexes i JOIN sys.dm_db_partition_stats  AS s ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON  i.object_id = o.object_id

LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id

AND i.index_id = u.index_id AND u.database_id = DB_ID()

WHERE o.type <> 'S' and isnull(u.user_updates,0) > 0and i.type_desc <> 'HEAP' AND U.user_seeks < 100

GROUP BY

o.name , i.name, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, i.name, o.name

ORDER BY

(convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, user_updates desc, o.name, i.name

RUN THIS IF REQUIRED FOR FOR ALL DATABSES

 declare @db_name varchar(100), 

@str varchar(5000) , @str1 varchar(5000)

DECLARE cur_user_db CURSOR FOR 

 SELECT name  

 FROM master..sysdatabases  

 where name not in ('tempdb', 'model', 'master', 'msdb', 'maintenance', 'distribution', 'litespeedlocal', 'litespeedcentral') 

        and databasepropertyex(name,'Updateability') = 'READ_WRITE'  

        and databasepropertyex(name,'Status') = 'ONLINE' 
        and databasepropertyex(name,'Useraccess') = 'MULTI_USER' 

OPEN cur_user_db 

FETCH NEXT FROM cur_user_db into @db_name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

set @str = 'use [' + @db_name + ']' + char(13) + char(10) +

'SELECT DB_NAME() AS [Database], o.name AS Table_name, i.name AS index_name, i.type_desc, SUM(s.used_page_count) * 8   AS IndexSizeKB, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, ''Drop index '' + i.name + '' on '' + o.name as DropIndexStatement

FROM sys.indexes i

JOIN sys.dm_db_partition_stats  AS s ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

JOIN sys.objects o ON  i.object_id = o.object_id

LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id         

AND i.index_id = u.index_id AND u.database_id = DB_ID()

WHERE o.type <> ''S'' and isnull(u.user_updates,0) > 0and i.type_desc <> ''HEAP'' AND U.user_seeks < 100

GROUP BY

o.name , i.name, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,

u.last_user_seek, u.last_user_scan, i.name, o.name

ORDER BY

(convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, user_updates desc, o.name, i.name'

exec (@str) 

FETCH NEXT FROM cur_user_db into @db_name 

END 

CLOSE cur_user_db 

DEALLOCATE cur_user_db

The Query also provides the Command to drop the Unused Indexes. So its easy to remove them.

...Happy SQLing
Thanks for Visiting and Sharing your Views