[mssql] Write to Text File

CREATE PROCEDURE usp_WriteToTxtFile(
@pFileName varchar(255),
@pText     varchar(255))
AS
  DECLARE @iFS        int,
       @iOLEResult int,
       @iFileID    int,

  EXECUTE @iOLEResult = sp_OACreate 'Scripting.FileSystemObject', @iFS OUT
  IF @iOLEResult <> 0 PRINT 'Scripting.FileSystemObject'

  --Open a file
  EXECUTE @iOLEResult = sp_OAMethod @FS, 'OpenTextFile', @iFileID OUT, @pFileName, 8, 1
  IF @iOLEResult <> 0 PRINT 'OpenTextFile'

  --Write the input Text
  EXECUTE @iOLEResult = sp_OAMethod @iFileID, 'WriteLine', Null, @pText
  IF @iOLEResult <> 0 PRINT 'WriteLine'

  EXECUTE @iOLEResult = sp_OADestroy @iFileID
  EXECUTE @iOLEResult = sp_OADestroy @iFS
GO

http://www.motobit.com/tips/detpg_SQLWrFile/

[msqsl] Shrinking Database

SQL Server 2005 will reuse space that is made available by deleted rows. It records the status of extents with two maps, the Global Allocation Map (GAM) and the Shared Global Allocation Map (SGAM). A GAM will indicate if an extent is completely free, while an SGAM will identify partially free extents. Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page.

The Database Engine uses these maps to find free space for inserts, but it always tries to distribute data evenly in a database.

As databases age, records will be added and deleted, and this will leave empty pages within the database. This space can be reclaimed by 'shrinking' the database. There are two permutations here, removing unused space at the end of a database, and removing unused pages within a database. It is not clear if older versions of MSSQL will reuse deleted pages within a database. If they do not, then shrinking should be a regular maintenance task.
It is possible to shrink the whole database, or shrink individual files within the database. You can also shrink a database automatically by using a background schedule. Its possible to shrink a database with active users logged on, but performance may suffer if the database is busy. It is best to schedule a shrink at a quiet time.

There are some restrictions on database shrinking

• You cannot shrink a database or log while they are being backed up.
• You cannot shrink a database to be smaller than the size of the model database.
• If you are shrinking an entire database, you cannot make it smaller than its original size.
• If you shrink a multi-file database by individual files using the SHRINKFILE command, then you can make it smaller than its original size.
  
  

Setting up automatic database shrinking

You can set up a database to shrink automatically by using the ALTER DATABASE AUTO_SHRINK command, or by using the SQL Server Enterprise Manager (SSEM). Navigate through the SSEM until you reach the databases, then you right click your mouse on the database you want to shrink. Select 'ALL_TASKS' then 'Shrink Database'. You will see a summary of the database size, and the amount of space that can be recovered. If you click on the 'Schedule' radio button, then you get the option to schedule the shrink process. The screens are quite simple to follow, you can select how often, and at what time the shrinks will run. The SSEM is described in a bit more detail below.

Shrinking with database console commands (DBCC)

You can shrink a database or a file with the following DBCC commands -

DBCC SHRINKDATABASE  
    ( database_name [ , target_percent ] 
        [ , { NOTRUNCATE | TRUNCATEONLY } ] 
    )

DBCC SHRINKFILE 
    ( { file_name | file_id } 
        { [ , target_size ] 
            | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] 
        }
    )

A shrink operation without NOTRUNCATE or TRUNKATEONLY will consolidate all the pages to the front of the database or file, then release the unused space.
If you use the NOTRUNCATE option, then you consolidate all the pages, but you do not release the unused space, so the file is still the same size as before.
If you use the TRUNCATEONLY option, then you release the free space at the end of the file, but you do not do any consolidation of unused pages within the file.
If you specified a target_percent for space release, this is ignored for TRUNCATEONLY, but honored for NOTRUNCATE.

As the name suggests, the EMPTYFILE parameter moves all the data from a file into other files in the same filegroup. The file can then be dropped using the ALTER DATABASE statement.

Shrinking with the SQL Server Enterprise Manager

First, you need to get to the list of databases. Expand a server group and then expand a server. Expand Databases and you see a list of all databases defined to that server. Right-click on the database you wish to shrink, point to 'All Tasks', and then click on 'Shrink Database' to see the shrink database screen.

The top part of the shrink database screen shows the space allocated to the database, and the space free. You can use this to decide if shrinking will be worth while. The screen then has four options

1. The 'Maximum free space after shrinking' box allows you to define the percentage of free space you want left in the database after shrinking.
2. The 'Move pages to beginning of file before shrinking' will consolidate all empty pages within the file.
3. The 'Shrink databases based on this schedule' window is described above
4. The 'Shrink Files' button takes you to another screen, from which you can select individual database files. You use this screen to shrink the log file. There are options to compress the data within the file, truncate free space from the end of the file, shrink the file to a specified size, or shrink the file at a later date and time.

Shrinking Logfiles

There are limitations on how much space can be removed from a transaction log. Each transaction log file is divided logically into smaller segments called virtual log files. When you are truncating a log file, you can only remove entire virtual log files, and the smallest size for a transaction log is the space required for one virtual log file. You need to specify 'move pages to beginning of file before shrinking' to free up virtual log files, so they can be truncated.