Visual Basic Q&A

Symptoms
In Microsoft SQL Server 2000 and in MicrosoftSQL Server 2005, you can add, delete, and rename the data files and the transaction log files. This article explains how to consolidate physical files, and how to rename the logical file name in SQL Server 2000 and in SQL Server 2005.
Resolution

Consolidate Physical Files You may have to consolidate files or reduce the number of physical files for a server that is running SQL Server 2000 or SQL Server 2005. To reduce the number of physical files, you can delete the files. To consolidate the files, you can purge inactive transactions.
Deleting FilesDeleting a data or a transaction log file removes the file from the database. However, you cannot remove a file from a database if the file contains data or transaction log information. You can only remove a file if the file is empty. If you have data that you want to keep, you can migrate the data from a data file to other files in the same filegroup. To migrate the data, you can use a DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Then, SQL Server no longer allows data to be inserted in the file, and you can delete the file by using an ALTER DATABASE statement.
You cannot migrate the transaction log data from one log file to another to delete a transaction log file.
Purging Inactive TransactionsTo purge inactive transactions from a transaction log file, you must truncate or back up the transaction log. When a transaction log file no longer contains any active or inactive transactions, you can remove the log file from the database. To remove the log file from the database, use these steps: To shrink data or information in a file and to make the file empty, run the following Transact-SQL statement:

DBCC SHRINKFILE (‘<logical file name>’, EMPTYFILE ) To delete a file from a database, run the following Transact-SQL statement:

ALTER DATABASE <Database name>REMOVE FILE <logical file name>
IMPORTANT After you add or delete files, create a database backup immediately. You must create a full database backup before you create a transaction log backup.
Rename Logical File NameTo modify the logical name of a data file or a log file, specify the logical file name you want to rename by using the Name parameter, and then specify the new logical name for the file by using the NewName parameter. To rename the logical file, run the following Transact-SQL statement:

ALTER DATABASE <Database name>MODIFY FILE( NAME = <current_logical_name>, NEWNAME = <new_logical_name>)

Symptoms
In Microsoft Visual Studio .NET or Visual Studio 2005, one or more columns in the Results pane appear to be empty when you retrieve data from a Microsoft SQL Server 2000 database table. This problem occurs when the following conditions are true:The database table contains a column of type CHAR, NCHAR, VARCHAR, or NVARCHAR.The column contains data that has more than 900 characters.
Resolution
The cells of the Results pane in Visual Studio .NET or Visual Studio 2005 cannot display data that has more than 900 characters.