Symptom:
You install an instance of Microsoft SQL Server 2005 on a computer that has many processors. You upgrade to SQL Server 2005 Service Pack 3 (SP3) or to Microsoft SQL Server 2008. You run a query that uses the Like operator in the WHERE clause or that uses a string comparison function, such as the CharIndex() function. When you run the query many times on multiple connections at the same time, the CPU usage reaches 100 percent, and the server performance decreases significantly.
Then refer the Microsoft article and apply the cumulative update
Sunday, October 11, 2009
No Surface Area Configuration (SAC) Tool in SQL Server 2008
In SQL Server 2008, Surface Area Configuration (SAC) Tool has been added to the deprecated feature list.
So how to do the functionality of SAC configuring Service, Connection and features?
1. For Instance and Database Engine Features:
Select Facets on Properties of Instance / Database on SQL Server Management Studio.
2.For Connections, Protocols and StartUp options:
Use SQL Server Configuration Manager
3.SSAS Features:
It can be done at Property Settings
4.SSRS Features
It can be done by editing the RSReportServer.config file or by using the facets feature
So how to do the functionality of SAC configuring Service, Connection and features?
1. For Instance and Database Engine Features:
Select Facets on Properties of Instance / Database on SQL Server Management Studio.
2.For Connections, Protocols and StartUp options:
Use SQL Server Configuration Manager
3.SSAS Features:
It can be done at Property Settings
4.SSRS Features
It can be done by editing the RSReportServer.config file or by using the facets feature
Saturday, September 26, 2009
Feature Pack for Microsoft SQL Server 2005 and SQL Server 2008
Please have a look on the Feature pack of SQL Server 2005 and SQL Server 2008.
SQL Server 2005
SQL Server 2008
SQL Server 2005
SQL Server 2008
SQL Agent fails on SQL Server 2005
The error log from starting sqlagent is the following:
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[000] Unable to connect to server '\SQL2005'; SQLServerAgent cannot start
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[382] Logon to server '\SQL2005' failed (DisableAgentXPs)
[098] SQLServerAgent terminated (normally)
Resolution:
-----------
Client side setting would have messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no".
If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client). Just run the sqlncli.msi
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[000] Unable to connect to server '
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[382] Logon to server '
[098] SQLServerAgent terminated (normally)
Resolution:
-----------
Client side setting would have messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no".
If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client). Just run the sqlncli.msi
Monday, September 21, 2009
Microsoft SQL Server 2008 advantages over Oracle 11g
SQL Server compared to Oracle
Microsoft SQL Server 2008 advantages over Oracle 11g
* Better Performance and Scalability: SQL Data Services, Filtered Indexes, Advanced Sparse Columns, Column Prefix Compression
* Better Security: Module Signing using Certificate, and integrated updates and patching
* Better Business Intelligence (BI) tools: Optimized cube designers, on-demand processing and instance-based rendering, optimized integration with the 2007 Microsoft Office system
Find more on here
Microsoft SQL Server 2008 advantages over Oracle 11g
* Better Performance and Scalability: SQL Data Services, Filtered Indexes, Advanced Sparse Columns, Column Prefix Compression
* Better Security: Module Signing using Certificate, and integrated updates and patching
* Better Business Intelligence (BI) tools: Optimized cube designers, on-demand processing and instance-based rendering, optimized integration with the 2007 Microsoft Office system
Find more on here
Thursday, September 17, 2009
Get up to 25% off Select Microsoft Certification Exams
Get Your Voucher Code for your Discounted Exams
Find the exam of your choosing from the lists below and select "Get Voucher," complete the registration form and your discounted voucher code will be emailed to you. Once you have obtained your code, plan and schedule your next exam at http://www.prometric.com/Microsoft/default.htm.
http://www.prometric.com/microsoft/careeroffer.htm
* Step 1: Using the voucher number, schedule and pay for your initial exam via Prometrics's web site
o You will need to have your voucher number available during the registration process.
* Step 2: Take your exam.
Candidates must register, schedule and take their discounted exams by December 31
Find the exam of your choosing from the lists below and select "Get Voucher," complete the registration form and your discounted voucher code will be emailed to you. Once you have obtained your code, plan and schedule your next exam at http://www.prometric.com/Microsoft/default.htm.
http://www.prometric.com/microsoft/careeroffer.htm
* Step 1: Using the voucher number, schedule and pay for your initial exam via Prometrics's web site
o You will need to have your voucher number available during the registration process.
* Step 2: Take your exam.
Candidates must register, schedule and take their discounted exams by December 31
Wednesday, September 16, 2009
How to reset Internel Protocol TCP / IP
One of the components of the Internet connection on your computer is a built-in set of instructions called TCP/IP. TCP/IP can sometimes become damaged or corrupted. If you cannot connect to the Internet and you have tried all other methods to resolve the problem, TCP/IP might be causing it.
Use the command below:
netsh int ip reset c:\resetlog.txt
Reference : How to reset Internet Protocol (TCP/IP)
Use the command below:
netsh int ip reset c:\resetlog.txt
Reference : How to reset Internet Protocol (TCP/IP)
Monday, September 14, 2009
You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP
You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP or in Windows 2000 or in Windows Server 2003
You may be unable to use the following components at the same time in Microsoft Windows XP, in Microsoft Windows 2000, or in Microsoft Windows Server 2003 Standard Edition with Service Pack 1 (SP1):
* A Cisco virtual private network (VPN) client
* The Internet Protocol security (IPsec) implementation that is built into Windows XP, Windows 2000, and Windows Server 2003
In this situation, you cannot use a VPN tunnel to access resources on an enterprise network on which Domain and Server Isolation policies are deployed.
Refer this here
You may be unable to use the following components at the same time in Microsoft Windows XP, in Microsoft Windows 2000, or in Microsoft Windows Server 2003 Standard Edition with Service Pack 1 (SP1):
* A Cisco virtual private network (VPN) client
* The Internet Protocol security (IPsec) implementation that is built into Windows XP, Windows 2000, and Windows Server 2003
In this situation, you cannot use a VPN tunnel to access resources on an enterprise network on which Domain and Server Isolation policies are deployed.
Refer this here
Thursday, May 14, 2009
Do we need SSIS to create Maintenance Plan
Before SQL Server 2005 SP2 was released Maintenance plans were integreated with SSIS but after SP2 Microsoft has changed that and we do not need SSIS to be installed to run maintenance jobs. And it carried over to SQL Server 2008 too.
Monday, May 04, 2009
How to change the owner of a maintenance plan
Scenario 1:
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:
"Unable to determine if the owner (DOMAIN\xxx) of job has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."
Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.
The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.
/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
For SQL Server 2008:
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:
"Unable to determine if the owner (DOMAIN\xxx) of job
Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.
The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.
/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
For SQL Server 2008:
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'
Sunday, May 03, 2009
SQL Server 2000 Service account privileges
For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:
* Act as Part of the Operating System = SeTcbPrivilege
* Bypass Traverse Checking = SeChangeNotify
* Lock Pages In Memory = SeLockMemory
* Log on as a Batch Job = SeBatchLogonRight
* Log on as a Service = SeServiceLogonRight
* Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
* Act as Part of the Operating System = SeTcbPrivilege
* Bypass Traverse Checking = SeChangeNotify
* Lock Pages In Memory = SeLockMemory
* Log on as a Batch Job = SeBatchLogonRight
* Log on as a Service = SeServiceLogonRight
* Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
Monday, April 27, 2009
SQL 2005 Standard Edition on Cluster
SQL Server 2005 Standard Edition will support two-node MSCS,
but Windows 2003 Server Standard won't:
http://technet.microsoft.com/en-us/library/cc758523.aspx
but Windows 2003 Server Standard won't:
http://technet.microsoft.com/en-us/library/cc758523.aspx
Friday, April 10, 2009
Idera SQL Safe Backup Script
DECLARE @counter INT
DECLARE @maxCount INT
DECLARE @result INT
DECLARE @failed INT
DECLARE @dbname NVARCHAR(128)
DECLARE @backupSetName NVARCHAR(256)
DECLARE @backupSetDescription NVARCHAR(512)
SET @counter = 1
SET @maxCount = 1
SET @result = 0
SET @failed = 0
-- Create the temporary database table
IF object_id('tempdb..#databases') IS NOT NULL DROP TABLE #databases
CREATE TABLE #databases
(
id INT IDENTITY,
databaseName NVARCHAR(128),
backupSetName NVARCHAR(256),
backupSetDescription NVARCHAR(512)
)
-- Insert the databases to be backed up
INSERT INTO #databases VALUES ('User_DB1', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
INSERT INTO #databases VALUES ('User_DB2', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
SELECT @maxCount = MAX(id) FROM #databases
-- Loop through and backup each database
WHILE @counter <= @maxCount
BEGIN
SELECT @dbname = [databaseName], @backupSetName = [backupSetName], @backupSetDescription = [backupSetDescription] FROM #databases WHERE id = @counter
EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = 'D:\SQLData\MSSQL\BACKUP\IderaSQLBackups\USER_DB_BACKUPS\FULL\%instance%_%database%_%timestamp%.safe',@delete = '1days',@backupname = @backupSetName,@desc = @backupSetDescription,@verify = 1,@compressionlevel = 'ispeed',@threads = 3
IF (@result != 0) SET @failed = 1
SET @counter = @counter + 1
END
-- If one or more failures occurred, signal an error
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)
DECLARE @maxCount INT
DECLARE @result INT
DECLARE @failed INT
DECLARE @dbname NVARCHAR(128)
DECLARE @backupSetName NVARCHAR(256)
DECLARE @backupSetDescription NVARCHAR(512)
SET @counter = 1
SET @maxCount = 1
SET @result = 0
SET @failed = 0
-- Create the temporary database table
IF object_id('tempdb..#databases') IS NOT NULL DROP TABLE #databases
CREATE TABLE #databases
(
id INT IDENTITY,
databaseName NVARCHAR(128),
backupSetName NVARCHAR(256),
backupSetDescription NVARCHAR(512)
)
-- Insert the databases to be backed up
INSERT INTO #databases VALUES ('User_DB1', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
INSERT INTO #databases VALUES ('User_DB2', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
SELECT @maxCount = MAX(id) FROM #databases
-- Loop through and backup each database
WHILE @counter <= @maxCount
BEGIN
SELECT @dbname = [databaseName], @backupSetName = [backupSetName], @backupSetDescription = [backupSetDescription] FROM #databases WHERE id = @counter
EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = 'D:\SQLData\MSSQL\BACKUP\IderaSQLBackups\USER_DB_BACKUPS\FULL\%instance%_%database%_%timestamp%.safe',@delete = '1days',@backupname = @backupSetName,@desc = @backupSetDescription,@verify = 1,@compressionlevel = 'ispeed',@threads = 3
IF (@result != 0) SET @failed = 1
SET @counter = @counter + 1
END
-- If one or more failures occurred, signal an error
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)
Wednesday, April 08, 2009
SQL Server 2008 Service Pack 1 released
SQL Server 2008 Service Pack 1 (SP1) is now available at the following URL :
You can use these packages to upgrade any SQL Server 2008 edition.
SQL Server 2008 SP1 is primarily a roll-up of previous cumulative updates, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community. While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.
With this service pack, Microsoft has made investments to ease deployment and make management of service packs more efficient. Key improvements in SP1 include Slipstream, Service Pack Uninstall and Report Builder 2.0 click-once deployment.
• Slipstream allows administrators to install SQL Server 2008 and Service Pack 1 in a single instance. This decreases the total time for an installation, including a fewer number of reboots thereby increasing productivity and deployment availability.
• Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release. This feature also improves DBA productivity, reduces the cost of deployment and improves overall supportability
• Report Builder 2.0 Click Once improves the existing SQL Server end-user report authoring application by easing deployment to business users
For more information about SQL Server 2008 Service Pack 1, please review the Release Notes :
You can use these packages to upgrade any SQL Server 2008 edition.
SQL Server 2008 SP1 is primarily a roll-up of previous cumulative updates, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community. While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.
With this service pack, Microsoft has made investments to ease deployment and make management of service packs more efficient. Key improvements in SP1 include Slipstream, Service Pack Uninstall and Report Builder 2.0 click-once deployment.
• Slipstream allows administrators to install SQL Server 2008 and Service Pack 1 in a single instance. This decreases the total time for an installation, including a fewer number of reboots thereby increasing productivity and deployment availability.
• Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release. This feature also improves DBA productivity, reduces the cost of deployment and improves overall supportability
• Report Builder 2.0 Click Once improves the existing SQL Server end-user report authoring application by easing deployment to business users
For more information about SQL Server 2008 Service Pack 1, please review the Release Notes :
Sunday, March 15, 2009
Cluster Diagnostics and Verification Tool (ClusDiag)
Cluster Diagnostics and Verification Tool (ClusDiag) is a graphical tool that performs diagnostics tests on a pre-production server cluster and creates log files to help system administrators identify configuration issues prior to deployment in a production environment.
Download details: Cluster Diagnostics and Verification Tool (ClusDiag.exe)
Download details: Cluster Diagnostics and Verification Tool (ClusDiag.exe)
Friday, March 13, 2009
SQL 2005 SP2 templog is set to restricted and will not go back to unrestricted
It is only a presentation issue.
There's no such thing as "unlimited" in the end, since the max theoretical size
• for a data file is 32TB and
• for a log file is 2TB. 2,097,152 MB = 2 TB.
I.e., the value you see is the maximum theoretical size.
Although file size is displaying as "restricted", the MAXSIZE is indeed 2TB (2,097,152MB).
So, within the confines of capacity limitations, it is unlimited so to speak.
Working with tempdb in SQL Server 2005
There's no such thing as "unlimited" in the end, since the max theoretical size
• for a data file is 32TB and
• for a log file is 2TB. 2,097,152 MB = 2 TB.
I.e., the value you see is the maximum theoretical size.
Although file size is displaying as "restricted", the MAXSIZE is indeed 2TB (2,097,152MB).
So, within the confines of capacity limitations, it is unlimited so to speak.
Working with tempdb in SQL Server 2005
Sunday, March 01, 2009
Saturday, February 14, 2009
Automate SQL Server Profiler Trace
-- To create trace
exec @rc = sp_trace_create @TraceID output, 2, N'N:\SQLTrace\15FebTrace', @maxfilesize, NULL
## Note Option 2 here
--- To set trace status
exec sp_trace_setstatus @traceid, @status
where @status = 0 Stopped
1 Running
2 Closed, cleaned from server
---
To get the Traces running on the server
select * from ::fn_trace_getinfo(0)
---
To read trace content into a table
select * from ::fn_trace_gettable('N:\SQLTrace\15FebTrace.trc',default)
exec @rc = sp_trace_create @TraceID output, 2, N'N:\SQLTrace\15FebTrace', @maxfilesize, NULL
## Note Option 2 here
--- To set trace status
exec sp_trace_setstatus @traceid, @status
where @status = 0 Stopped
1 Running
2 Closed, cleaned from server
---
To get the Traces running on the server
select * from ::fn_trace_getinfo(0)
---
To read trace content into a table
select * from ::fn_trace_gettable('N:\SQLTrace\15FebTrace.trc',default)
Thursday, February 05, 2009
Terminal Server and Citrix Keyboard Shortcuts
Terminal Server and Citrix Keyboard Shortcuts
If working in an RDP or ICA Session the following are keyboard shortcut key combinations that map to the native Windows shortcut keystrokes.
CTRL+F3 Task Manager
CTRL+F2 Start Menu
Sunday, February 01, 2009
SQL Profiler buffers the trace data in temporary files on the client machine
SQL Profiler buffers the trace data in temporary files on the client machine, and if the drive on which these temporary files reside doesn't have ample free space, a Profiler trace (e.g. with the standard trace template) could deplete the drive of the free space rather quickly and end up disabling the trace.
System is low on disk space on drive 'C'. All SQL Server profiler functions are temporarily disabled.
The temporary files to buffer the profiler trace data were in directory C:\Documents and Settings\Siva\local settings\Temp\1, and the names of the temporary files had this pattern: Prf*.tmp. Upon exiting SQL Profiler, these temporary files would be removed automatically.
Work around:
To work around this problem, do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events.
You can use the following system stored procedures:
* sp_trace_create
* sp_trace_setevent
* sp_trace_setfilter
* sp_trace_setstatus
For more information about these system stored procedures, search for the stored procedures in SQL Server 2000 Books Online or in SQL Server 2005 Books Online.
Reference:
An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance
System is low on disk space on drive 'C'. All SQL Server profiler functions are temporarily disabled.
The temporary files to buffer the profiler trace data were in directory C:\Documents and Settings\Siva\local settings\Temp\1, and the names of the temporary files had this pattern: Prf*.tmp. Upon exiting SQL Profiler, these temporary files would be removed automatically.
Work around:
To work around this problem, do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events.
You can use the following system stored procedures:
* sp_trace_create
* sp_trace_setevent
* sp_trace_setfilter
* sp_trace_setstatus
For more information about these system stored procedures, search for the stored procedures in SQL Server 2000 Books Online or in SQL Server 2005 Books Online.
Reference:
An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance
Subscribe to:
Posts (Atom)