Readers' Tips and Tricks - 25 Apr 2000

Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.

Accessing Appropriate Table View

Claude Seidman's article, "Creating Horizontally Partitioned Views" (April 2000), explains well the way partitioning works. But for partitioned views to work properly, you can't use a function in the column-check expression. In the article, the author uses the month and year functions. The author shows how to access all the tables within the view. However, if you properly set up partitioning, you access only the appropriate table through the view. Listing 1 shows an example SQL statement that correctly demonstrates the behavior. The author has fallen into the same trap that I did when playing with this feature.

—Tony Rogerson
[email protected]

Safely Changing SQL Server's Service Account

When you use the Control Panel Services applet to change the service account that SQL Server 7.0 runs under, beware. You might need to change the server account from a local system account to a domain user account, for example, to enable email capability with a Messaging API (MAPI)-compliant email system. But using the Services applet to change the account causes Full-Text Search to stop working.

To safely change the service account SQL Server runs under, select the database's Properties sheet, then the Security tab. In the Startup Service Account box, make the appropriate account selection. If you want to change the account to a domain user, you must be logged in as the account that you're changing SQL Server to run under (i.e., domainuser _account). If you're not logged in as the domain user that you're setting the account to, you'll receive the following error message:

A required privilege is not held by the client.

If you've already changed the account under the Services applet and Full-Text Search isn't working, follow the above procedures, then rebuild the full-text index for the table.

—Lori Hughes
[email protected]

Shrinking an Active Log File in SQL Server 7.0

When SQL Server 7.0 marks a log file active, shrinking that file can be difficult. For example, let's say your SQL Server 7.0 database has a 1.7GB log file with only 20MB of data and you want to truncate all committed records. You try to shrink the log file by issuing the following statement:

USE MASTERGOBACKUP LOG databasename WITH TRUNCATE_ONLYGOUSE databasenameDBCC SHRINKFILE (logfilename, TRUNCATEONLY)

But this technique doesn't work. To investigate the problem, you first check your log files' status by issuing the following statement:

USE databasenameGODBCC LOGINFO

As you can see from the results in Figure 1, the last file's status is 2 (active); and SQL Server 7.0 won't let you truncate active log files.

To shrink a log that SQL Server has marked active, you need to rebuild the log at the server console. First, back up the database. Next, put the database in single-user mode to make sure the database can't process a transaction; in Enterprise Manager, just right-click the name of the database, select Properties, then the Options tab, and select the Single User check box. You can then bring the database back up in emergency mode by executing the following statement from Query Analyzer:

USE MASTERGOUpdate sysdatabases set status = 32768 where name = 'databasename'

Now, stop and restart the server from Enterprise Manager by right-clicking the name of the server and selecting Stop/Start.

To rebuild the transaction log, issue the following statement in Query Analyzer:

dbcc rebuild_log('databasename',   'path for a new ldf file, see eg below')/* eg d:MSSQL7LOGMyNewLog_Log0.ldf */

This statement brings up the following message:

Warning: The log for database 'databasename' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can now set the database back to normal mode by issuing the following statement in Query Analyzer:

Update sysdatabases set status = 0  where name = 'databasename'

This update statement will return the result: 1 row(s) affected. When you receive this message, stop and restart the server again.

To avoid this truncation problem in the future, set up the database log to use two files. In Enterprise Manager, right-click the name of the database you want and choose Properties. On the Log tab, give the first log file a minimum file size and set it so that it won't auto grow. Then, add another file to the log and set this second file to auto grow. With these two new log files replacing the original file that SQL Server wouldn't let you truncate, you can delete the original file (in this example, the 1.7GB file) from your hard drive.

Note that this method is an undocumented and unsupported approach to shrinking log files. Microsoft's SQL Server 2000 group has received a feature request about shrinking log files. But until Microsoft releases SQL Server 2000, when you create a new database, you can create log files as this tip describes.

—Chunyen "Christine" Chang
[email protected]

Corrections to this Article: