Data and Analytics

While searching over the internet, today I found this official document ( http://msdn.microsoft.com/en-us/library/ms143432.aspx ) from Microsoft. This document clearly mentions maximum capacity of SQL Server for possibly all features. On a personal note, I never faced a situation where any feature of SQL Server really “maxed-out” but received so many questions from others asking “what is the maximum database size of a SQL Server database etc…” So I thought to post it for as a quick reference. Hopefully this will be useful information to all.

This is a simple trick which should not be used unless you have no other option to recover the log file. Suppose you need to attach a database and can not find the associate log files, you can still attach the database and bring it online with rebuilding the log files. Follow below syntax for performing this operation: USE [master] GO CREATE DATABASE [Database] ON ( FILENAME = N ' [Datafile1 Complete Path] ' )   FOR ATTACH_REBUILD_LOG GO Alternately if you prefer using GUI to attach a database, simply remove the log file while attaching the database. SQL Server will rebuild the log file in that case. However please be advised that if your database was not cleanly shutdown before detachment, then attempting to rebuild log file will not bring the database online. 

We discussed log shipping as a good old DR solution. So when it is in action, there should be an operational procedure in place to do role change quickly and effectively. I always follow/recommend below steps for performing a role change in log shipping. Assumption: All the logins and server objects (like linked server, SSIS/DTS etc) in the secondary instance are already in sync with primary instance. This must be an ongoing activity and responsibility of DBA. Steps: 1. Copy all un-copied backup files from primary server to secondary server. (From where the logs are being applied to secondary instance) 2. If possible and required, backup the tail log of primary database. At this stage I prefer to use “no recovery” clause to ensure that primary database surely goes to an unusable state and be prepared to accept transactional log. Then copy the tail log backup to secondary server. At this point, if primary is online and log shipping is working, you should stop it. (I prefer to re

Sometime we face a situation where users of a database want us to take an ad-hoc full backup. This can create lots of constraints from Infrastructure point of view. For example taking a full backup breaks the backup sequence and hence following differential backups or log backups consider the ad-hoc backup as the latest full backup till next automated full backup is taken. This can add unnecessary confusion and complexity of the established backup protocol for the database. This issue is resolved from SQL Server 2005 onwards where we can take a full (and log also) backup with out breaking the conventional “backup sequence”.  This new technology is called “Copy only backup”. You can use copy only backup using one of the following way: TSQL (Applicable for SQL 2005 onwards) -- Note: All other options of backup is supported BACKUP DATABASE 'Database which is backed up' TO   DISK = 'Complete path of backup file' WITH   COPY_ONLY GO Management Studio (Appli

Log shipping is still one good old high availability mechanism used in many infrastructures as a reliable and realistic solution. So any one who works with SQL Server should be familiar with ins-and-outs of this technology. Basically log shipping follows a very simple protocol of backing up logs of primary database, ship the log files to a shared location and then continuously keep applying the log files to secondary database. Setting up log shipping is a standard feature available in following versions of SQL Server: SQL 2005 –- Workgroup, Standard, Enterprise SQL 2008 – Web, Workgroup, Standard, Enterprise SQL 2000 – Enterprise While I was planning to write a bit more on this technology and step on how to setup logs hipping in a SQL 2005 environment, I came across to an excellent article of Hilary Cotter. Please refer to http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309728_mem1,00.html for detailed steps to set-up log shipping and some very important informatio

This is a very straightforward process. Please follow below steps: Execute sp_helpdb on the database (which needs to be moved) to find the logical file names and complete path of all the files associated with the database.. sp_helpdb databasename  -- databasename  is the database which will be moved Once you identified all the logical files and physical location of the all log and data files, update their new location in system catalog using below code: ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ) Note: You have to execute this code seperately for each data/log files you will move. Now take the database offline using below code: ALTER DATABASE   databasename   SET OFFLINE WITH ROLLBACK IMMEDIATE Physically move the files to new location  Bring the database online using below code: ALTER DATABASE   databasename   SET ONLINE At this point, database is physically moved to new

We know about the benefits of using cluster technology as high availability solutions of SQL Server. One of the major fascinating advantages clustering technology offers is the nodes of the cluster can be residing in geographically dispersed location. Unlike a local cluster, a geographically dispersed Cluster requires multiple storage arrays. At least one storage arrays must be present at each site to ensure that in the event of failure at one site, the remaining site will have local copies of the data. Also the nodes of a geographic cluster are connected to storage in such a way that when there is a failure at one site or a failure in communication between sites, the functioning nodes can still connect to storage at their own site. From SQL Server perspective, setting up and managing a Geographically Dispersed Cluster is not different than setting up and managing a normal cluster as long as the cluster is set up in the similar way a locally connected cluster would have been se

While executing sp_helpdb, you may receive an error similer to below: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot insert the value NULL into column 'owner', table 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________00010001D881'; column does not allow nulls. INSERT fails. The statement has been terminated. This error is thrown because one of the database does not have owner defined with it and hence sp_helpdb can not work on that database. This error can be thrown if you are using sp_helpdb [DatabaseName] to get properties of that particular database or sp_helpdb to get properties of all databases residing on that SQL instance. As the internal table used by sp_helpdb does not allow value of owner column as “Null”, this error is thrown. Solution: Resolving this is very straight forward. Identify the database/s whioh have the owner missing and then execute below code to as

You may experience error message while running a distributed query from SQL 2005 64 bit client instance to SQL 2000 or lower version’s 32 bit server instance. Possible error message varies on the version of 32 bit server as below: If the 32-bit SQL Server 2000 server has not been upgraded to SQL Server 2000 Service Pack 3 (SP3) or SQL Server 2000 Service Pack 4 (SP4), you receive the following error message: The ODBC catalog stored procedures installed on server are version ; version or later is required to ensure proper operation. Please contact your system administrator. You receive following error message if the following conditions are true: 1. SQL Server 2000 SP3 or SQL Server 2000 SP4 is installed on the 32-bit SQL Server 2000 server, or you use the linked SQL Server 7.0 server. 2. The versions of the system stored procedures on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server are different from the service pack version that is installed on the server.

There is a feature called “Index Hints” which is sometime used by developers. Basically if you use this feature, you are asking SQL Server to use the specific index in the execution plan instead of the index chosen by SQL Server. I often found it as a short term solution where someone created an index and then forced SQL Server to use it for immediate performance improvement. While this can give you some immediate benefits, this comes with following costs: • If a better index is added in future, SQL server still will not pick it. So basically the code with forced index will be out of scanner of SQL Server for planning a better execution plan. •If the index which is forced gets dropped, your code will be in deep trouble. Moreover if you really do not remember the code where you forced index hint and try to take a generic approach of performance troubleshooting of SQL Server (like updating statistics etc), you will not be benefited. • Every time Stored procedure/application needs

Most of us saw two tiny files named mssqlsystemresource.mdf and mssqlsystemresource.ldf in our SQL Server (typically in the same directory where other system database files are residing) but probably never had to work on them. So as per me, these files (and of course which means resource database itself) is some how less understood by many of us compared to other systems database. Resource database is actually another system databases present in SQL 2005 onwards which is a very vital part of SQL Server. It is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects (eg. sys.objects) physically reside in the   Resource   database, but they logically appear in the   sys   schema of every database. The   Resource database does not contain user data or user metadata. One of the major advantages of Resource   database is upgrading to a new version of SQL Server an easier and faster. In earlier versions of SQL Server, upgradin

This is one of the common questions I experienced from many folks when they attempt to connect to a SQL instance using Port Number instead of Instance Name. Irrespective of version of SQL Server, you can run the SQL Server on a TCP/IP port and can use SQL Server’s IP and port number combination instead of using typical SQLServername\Instancename to connect to it. For example, SQL instance running on Port number 1193 of 192.42.10.45 can be connected as 192.42.10.45, 1193

Today morning I was looking for an easy step by step guide to install SQL 2005 cluster on Windows 2003. My search landed me to one of Brad’s article describing the installation process very easily with screen prints. This article also includes the recommended tests to be performed on the cluster before using it in real life. So if you are looking for a documented guide to install SQL 2005 cluster on a Windows 2003 OS, you should visit here . (URL: http://www.sql-server-performance.com/articles/clustering/cluster_sql_server_2005_p1.aspx )

Recently I am working on a old SQL2000 instance which is running 2 OLTP applications on a 4GB Ram.  Everything was happy around that instance till some new .net deployment. After the new code went in live, end users intermediately started getting weird errors from their browser reporting typical .net stack dump. Unfortunately the new deployment was a vital part of the application which can not be removed. Hence backend analysis became a necessity and I was summoned. Very first thing I observed in SQL log is series of errors message throughout the day which complains about memory like below: Buffer Distribution:  Stolen=<> Free=<> Procedures=<> WARNING:  Failed to reserve contiguous memory of Size= <>. Dynamic Memory Manager:  Stolen=<> OS Reserved=<> Procedure Cache:  TotalProcs=<> TotalPages=<> InUsePages=<> Buffer Counts:  Commited=<> Target=<> Hashed=<> Insufficient memory available.. Error: 17803, Sev

Why should I use Checksum on my database? Or rather should I switch it off and gain better performance? This is a very common ambiguity while some one tries to do a trade off between performance and a very useful feature of SQL Server. First step is to understand CHECKSUM itself to answer this question. CHECKSUM is new feature in SQL2005 that provides a stronger mechanism than torn-page to detect any corruptions in IO/Disc subsystem which may eventually cause loss of data/corrupt a database. Between the time a database page is written to the disk and subsequently read by SQL Server, it may get corrupted for reasons that are outside the control of SQL Server. Checksum mechanism allows SQL Server to detect the database page corruption under such situations.     So the advantage of having this mechanism on is to detect any potential issue with I/O (like page corruption where data file is residing) beforehand and then take reactive steps to minimize the hazards of possible data loss.

All of us are now using 64 bits SQL Server for almost every new configuration. While running SQL Server on 64 bits is surely better for many reasons, we should actually invest some time to decide actually which one will be best for new environment if you are migrating old 32 bits databases. There is no “General rule of migration” which can conclude that choosing a 64 bit system is always best idea over a 32 bit system. When you move to a 64-bit environment, you pay some costs. Many data types (like register) that previously took 32 bits will now take 64 bits. Because the data takes more space, it offers greater range and precision.    The main advantage a 64-bit environment brings to SQL Server is a vastly greater address space, which means SQL Server can directly address far more RAM compared to a 32 bit system. On the other hand, in a 32-bit system, you can address up to 4 GB of RAM. Of this 4 GB, Windows by default reserves 2 GB for the operating system. This means that when all