Several times when you open Windows event log you may find some supoer socket related errors for SQL server, you may be amused that everything is working fine then why such errors, microsoft has provided a very good article on such issues, this error occur when you have configured SQL services to run under domain service account. Please find article review below, Error message/Symptoms: When SQL Server starts on a computer that is running Microsoft […]

Here is a very useful article by Vipul T. Shah via his blog http://blogs.technet.com/b/vipulshah/archive/2010/06/07/redirecting-sql-client-connection-to-different-instance-of-sql-who-is-listening-to-1433-default-port-without-altering-client-connectionstring.aspx Excerpt from his article, Scenario: Current Setup: SQL Server 2005 Default instance – PRODServer listening at default port 1433 Client Connection String using SQL Native Client – Provider=SQLNCLI ; Server=PRODServer; Database=PRODDB; Trusted_Connection=Yes; with TCP/IP protocol with default port 1433 Now – Upgrade Happens… a side-by-side upgrade on a same server. New Setup: SQL Server 2008 Named Instance – PRODServer\NewPRO Now if […]

I was going through some posts related to SQL Server capacity and came across this link – http://technet.microsoft.com/en-us/library/ms143432(SQL.100).aspx. Its amazing to see the max capacity specification for SQL Server. These are for SQL Server 2008, but they haven’t changed from SQL Server 2005’s limit. I would love to see systems with near capacity in below specifications: SQL Server Database Engine object Maximum sizes/numbers SQL Server (32-bit) Maximum sizes/numbers SQL Server (64-bit) Database size 1,048,516 terabytes […]

Today faced an issue that SQL server agent was not starting up, after investigation found that issue is due to AWE and lock pages in memory settings , found a very useful article at following link It says,  I changed my SQL Server Agent service account to a domain account using SQL Server Configuration Manager (should always use this tool to manipulate service accounts), but then could not start the SQL Server Agent service. This […]

Today after searching for possible causes of OLEDB timeout error faced by an application came across various links and lastly found a useful link  Article states:  I have heard many people saying that their web site is chugging along, and suddenly they get this error:    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)  [Microsoft][ODBC SQL Server Driver]Timeout expired   or    Microsoft OLE DB Provider for SQL Server error ‘80040e31’  Timeout expired   This […]

Today while debugging a C: drive disk space issue found that in certain service account location C:\Users\\AppData\Local\Temp there are several “*.CACHE” files. They come named as follows: “machine-name_instance.SQL-Server-name_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_#.cache” where: “machine-name” is the server’s Windows computer name; “instance.SQL-Server-name” is the MS SQL Server 2008 server and instance name; “#” is some unique 1-2 digit identifier. these were created by Data collector functionality of SQL server 2008, when you do not specify specified cache file patch in […]

While investigating a cluster resource offline issue, came across following three errors in windows system event log, cluster log and SQL error log respectively, Error message 1 Source: ClusSvc Category: Failover Mgr Type: Error Event ID: 1069 User: N/A Computer: < Computer Name > Description: Cluster resource ‘SQL Server ()’ in Resource Group ‘‘ failed. Error message 2 Error in the Cluster log file, SQL Server < SQLServer >: [sqsrvres] Unable to read the ‘VirtualServerName’ […]

Today, while doing some maintenance plan modification encountered a unique issue, recieved following error while modifying/opening any maintenance plan or job, Error Screenshots: Job error: Maintenance Plan error: Later on investigation found that this is a known bug with SQL Server 2005 SP2 that if SQL Server name exceeds 30 chars then this issue will occur, for e.g. if SQL server name is dazsqlmper234_1234\dbajsasalsa_456 Issue: SQL Agent Jobs and Maint plans are not editable when […]

I was struggling to identify the DTS package name from the job syntax which looks like, DTSRun /~Z0x5B431B25562BD74…2AE391DF759D0B3B09EEAB94B687E05E66CE7307C6304204 this can be simulated by right clicking the DTS Package (SQL 2000) and schedule a job. If you schedule a package to run that way, the SQL Job it creates will have an encrypted name for DTS package in SQL job step – similar to what you see above. Found a solution to this at following link […]

A very good read of 59 pages by Paul S. Randal available at following link: http://www.sqlskills.com/blogs/paul/CommonSQLServerMyths.pdf Do give it a read, it talks about very common myths which an amateur or even professional DBA’s have.