SQL Questions & AnswersRunaway Log Files, SQL Server Instances, and More
Edited by Nancy Michell
Log File Overgrowth
Q I have a little 40MB database in SQL Server™ Express that I use for a Web site, but the log file has grown to over 1GB. How do I shrink the log file, and how do I prevent this from happening?
A First of all, read up on the recovery model in SQL Server Books Online. You probably want to use Simple Recovery (not Full) so that SQL Server automatically truncates your transaction log after checkpointing the database to disk. The trade-off is that in the event of disaster you won’t be able to recover the exact time of the failure because you won’t have a chain of log activity from the last backup to the point of the failure. You can still recover to the point of your last database backup though.
To change to simple recovery, you would issue a statement like this:
ALTER DATABASE <database_name> SET RECOVERY SIMPLE
Once you have converted the database to the simple recovery model, the transaction log will still contain data for transactions committed before the conversion. To free the space used by these transactions, execute the following statements:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE (<log_file_name>, TRUNCATEONLY)
Note that even with the simple recovery model, long-running transactions may cause the transaction log to become large. If necessary, you can track down active transactions using this statement:
SELECT * FROM sys.dm_tran_session_transactions
SQL Server Instances
Q What is the recommended number of instances of SQL Server to be loaded on a server and on a clustered or mirrored environment? I saw a TechNet webcast by Bryan Von Axelson indicating that four instances was the preferred limit per server.
A The webcast you refer to is "A Technical Overview of SQL 2005 High Availability Features" and the section mentioned dealt with clustering and instances. SQL Server 2005 can support up to 50 instances when it’s not clustered. When SQL Server is clustered, the maximum number of instances is 25, but this number can be further restricted by additional limitations imposed by clustering.
As to the maximum recommended number of instances, that is going to depend on the resources available on your server and the resources that each instance requires. Sometimes it is possible to just sum the individual resource requirements for CPU, memory, and I/O, and get a reasonably good idea of how many instances can fit. For more information on consolidating SQL Server, check out resources on SQL Server Consolidation.
Usually, if you have enough memory and disk space with SQL Server 2000, you can get about four instances comfortably, maybe one or two more if they are low-power consumption instances. Add much more than that and you can run into disk trouble.
SQL Server 2005 changes things a bit with mount point support, but the thought process involved isn’t different: if you get down to one node, one plus one still must equal two. If one plus one equals three, then your totaled-up instances have greater capacity than the node itself.
Generally speaking, one SQL Server instance will outperform two or more instances on the same hardware because there is some overhead for the instances themselves. This means that if your first instance is not hitting a performance bottleneck, having a second instance will always reduce the resources available to both instances because the second instance will maintain both the second copy of SQL Server itself and its own copies of the query plans for its data.
Installing and Rebooting SQL Server
Q Why does SQL Server require a separate installation of binaries (program files) for each instance and therefore a reboot?
A To help understand the reboot you are seeing, let’s step back a bit. With SQL Server 2000 and SQL Server 2005, you can perform a new instance installation in two ways: with shared files—tools, some system files, Microsoft Data Access Components (MDAC), Microsoft XML Core Services (MSXML), and so on—or with per-instance files, the files installed in the directory for this instance and not shared with anybody.
The way the post-installation reboot works is as follows: only the shared files can be locked during an installation of SQL Server. However, the shared files could be locked by any other instance or even an entirely different program. If any of the shared files are locked by another source at the instant of installation, then they are not installed at this time. Instead, they are placed on the PendingFileRenames (PFR) registry key at HKLM\System\CurrentControlSet\Control\SessionManager for installation after the next reboot. In this case, the installation is not completed until after a reboot.
Turning now to setup itself, if your existing shared files are the same or higher file versions than your new instance needs, you won’t need to replace them and therefore won’t have to reboot. But if the shared files the new instance needs are a higher version than the ones currently in place, then you will need to replace them. And if the existing instances or other services are using these files, resulting in these files being locked at this moment, you’ll have to reboot to replace them. Thus you can always install a SQL Server 2000 instance that is the same build or lower than existing instances without rebooting. The installation process for a higher build instance (major, minor, or hotfix build, which is also a minor version upgrade within the SQL Server 2000 and 2005 versioning schemes) will replace shared files and possibly need a reboot.
Also, there might be a previously executed piece of installation code that has left behind a separate PFR operation. Back in SQL Server 2000, the installation technologies did not include the ideal PFR handling features found today in Microsoft Installer (MSI) and update.exe-based installers. As such, if there are any PFR operations waiting when you start the SQL Server 2000 setup, it will block and tell you to reboot first. This requires a reboot before SQL Server 2000 is installed, while the other scenario requires a reboot after SQL Server 2000 is installed. You could need both.
To avoid problems you should always allow separate software installations to complete, even if this requires a reboot, before beginning the next setup unless you are completely certain that these two programs share no common files which could be locked.
SQL Server Resource Utilization
Q I plan to run multiple SQL Server instances on the same server. Should I configure each of the instances to use specific portions of the available OS resources or should I allow each instance to determine the resource it requires set an upper limit on OS resources?
Too Many Snapshots
Q I have a snapshot publication that is scheduled to generate its snapshot every Sunday at 4 A.M. I’ve noticed that each week there are two snapshots being generated—one immediately after the other. How can I get a look at the actual jobs that are running?">I have a snapshot publication that is scheduled to generate its snapshot every Sunday at 4 A.M. I’ve noticed that each week there are two snapshots being generated—one immediately after the other. How can I get a look at the actual jobs that are running?
A To see a list of possible snapshot jobs, query MSsnapshot_agents, sysjobs, or sysjobsteps. Perhaps there is a job without a matching snapshot agent. The following queries will help you determine what’s causing the problem.
To list of all snapshot agents, execute this query:
select * from distribution..MSsnapshot_agents
To get a one-to-one match to number of jobs, do this:
select * from msdb..sysjobs
To match agent to job, run the following query:
select j.*, s.* from distribution..
MSsnapshot_agents s join msdb..sysjobs j
on s.job_id = j.job_id
To see if the job has corresponding job steps, here’s the syntax:
select j.*, s.* from msdb..sysjobsteps s join
msdb..sysjobs j
on s.job_id = j.job_id
where s.subsystem = ‘Snapshot’
A Well, it depends. If there isn’t any resource contention and there is enough of each resource to go around, then there is usually no reason to set any caps. The OS and SQL Server will share the available resources without any problems. However, if there is any resource contention, then depending on the resource and what you want to happen, the way that SQL Server and the OS manage the contention might not give you the result you want. In this situation you should consider setting caps on the resources in dispute.
With memory, each SQL Server instance will take what memory it thinks it needs, up to the available physical memory. When there is memory contention, then sometimes one SQL Server instance won’t release memory as quickly as you might like. If you are in this situation you may need to configure maximum and minimum server memory settings to manually control this. As these are dynamic settings in SQL Server 2005, you can change them without a reboot and see an immediate effect.
The OS and each SQL Server instance does a great job of sharing the CPU between all threads. Sometimes this is what you want, but sometimes you actually want to allow all instances to have an equal share of the CPU, not a share based on how much work they have to get done. Think about a server with one instance running a decision support system (DSS) with a few very complex long-running queries, and another instance running an online transaction processing (OLTP) system.
The OLTP system needs an unfair share of the CPU to allow it to guarantee response times to its users. You have to make sure the DSS instance doesn’t consume all the resources and starve the other instances while executing its complex queries. To fix each instance’s share of resources, you can use CPU Affinity and affinitize SQL Server instances to specific CPUs, or use Windows System Resource Manager to manage the share of total CPU resources for each SQL Server instance.
With I/O the OS does a great job of managing contention. You can often help to avoid contention in the design you choose for the I/O system. You do this by laying out your data and log files onto onto different physical disks, each on a different I/O path.
In a clustered environment, there may be additional factors concerning what happens when an instance fails over. In a multiple instance failover cluster configuration you have to take into account not just the resource needs of the instances running on this node, but also the resource requirements of the instances that may fail over to this node. This is a more complex topic and is discussed at length in the white papers located at "SQL Server Consolidation on the 32-Bit Platform using a Clustered Environment" and "SQL Server Consolidation on the 64-Bit Platform".
Default and Named Instances
Q I understand that a default instance can run SQL Server 2000 and a named instance can run SQL Server 2005, but are there any issues in doing this? Can a default instance run SQL Server 2005 and a named instance run SQL Server 2000?
A You can mix versions of SQL Server (both SQL Server 2000 and SQL Server 2005) on the same machine running Windows®. The combination can be from the following: one default instance (either SQL Server 2000 or SQL Server 2005) plus everything else as a named instance (SQL Server 2000 or SQL Server 2005) up to the number of supported instances for that version of SQL Server, or all named instances for everything. In a clustered configuration, it is exactly the same—you can have only one default instance plus the supported number of named instances for that version of SQL Server on a cluster, or all named instances.
Antivirus Software and SQL Server 2000
Q What are the best practices for using antivirus software on servers running SQL Server 2000?
A If you’re using Windows Server System™ Reference Architecture (WSSRA), then SQL Server will be behind a firewall in a data center, so it’s protected by virtue of its location. Beyond that, to some extent, the usual recommendation is to set exclusions for the SQL data and log files, and any backups. Otherwise the antivirus software goes mad trying to scan the data and log files every time SQL Server does anything.
Knowledge Base article 309422 also has some antivirus information. However, many times DBAs don’t set exclusions properly and their CPU and I/O utilization goes through the roof. Since this is such a common mistake, it’s the first thing you should check when encountering a SQL Server performance issue.
If you have a dedicated machine for SQL Server which is not being used as a file server, has no shares, and so forth, adding antivirus software with no exclusions poses a greater risk. And on a cluster, besides excluding all SQL Server and Analysis Services file types such as .mdf, .nfg, and .ldf, you must exclude the MSCS directory on the quorum drive as well. It is especially important in a clustered configuration to exclude the SQL Server files. This is because if there is a failure and failover to another node in the cluster, it can take a long time to scan that node, thus preventing SQL Server from coming back online in a timely manner.
Sometimes antivirus software, especially if it’s not cluster-aware, can have strange effects and interact in a negative way with clusters. Be sure to check with your antivirus vendor to see if it is cluster-aware or not.
Additionally, complete understanding of when a full and incremental antivirus scan is performed during the maintenance window for a database server is essential. Typically these scans run in the same timeslot as the maintenance window. To avoid resource contention, you might work with the infrastructure support teams to lock down backups, antivirus scans, software patches, and other schedules. Then you could look at server activity over a 24-hour period to see user time, processing times, and maintenance window times.
Then you can see overlaps of processes and use this for planning. Be aware of floating antivirus scan schedules due to antivirus management server issues and backlogging of tasks. Audit the antivirus scan window periodically to ensure it is occurring when you think it is.
Exporting Data from Oracle
Q I want to export data from an Oracle 9i database and import it into SQL Server 2005. I’m considering SQL Server Migration Assistant (SSMA) for Oracle version 2.0. I want to export the data once a week and then import it into the SQL Server database at a later time.
A Did you consider using Oracle as a publisher in replication? (See the SQL Server 2005 Books Online article Configuring an Oracle Publisher.)
Otherwise the easiest way to go is to create a SQL Server Integration Services (SSIS) package, and to run it on a scheduled basis. SSMA moves the data from Oracle to SQL Server directly, without writing the data into a text file. You have to be actually connected to both Oracle and SQL Server during the data move, and you can’t export the data into a text file. You can select all or some of the tables to be migrated. Keep in mind that SSMA uses an INSERT statement to move the data, and so it’s usually slower than SSIS.
Multicore Proccesors and Hyperthreading
Q What are the advantages and disadvantages of using multicore processors and hyperthreading processors with SQL Server 2000 and SQL Server 2005?
A This question comes up quite frequently. The general rule for multicore systems is that they provide a benefit to performance without your having to do anything to get the benefit.
The story isn’t so clear for hyperthreading. Some folks will tell you that for zero administration reasons you should disable this. More experienced administrators should test with their workload to determine if hyperthreading might provide a benefit. Here the operating system should also be considered. Windows 2000 Server is less hyperthreading aware than is Windows Server 2003. Windows Server 2003 SP1 has further hyperthreading enhancements, providing the best results with this OS.
Thanks to the following Microsoft IT pros for their technical expertise: Dmitry Balin, Shaun Cox, Ernie DeVore, Tim Getsch, Allan Hirt, Ernest Ho, Matt Hostetler, Dimitris Kalyvas, Igor Leybovich, Kristal Lindstrom, Mauricio Lorenzon, John McClelland, Kevin Mineweaser, Maxwell Myrick, Matt Neerincx, Michael Nelson, Al Noel, Vijay Sirohi, Chris Skorlinski, Carl Speshock, Bryan Von Axelson, Frank Wiemer, Heiko Wiesner, Roger Wolter, and Steven Wort.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.