Thursday, April 12, 2012

Free SQL Server command line tool "SQLS*Plus"

There is a free SQL Server command line tool "SQLS*Plus" (on http://www.memfix.com ) - exactly like SQL*Plus for SQL Server.
It is very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, parameters, batch processing, etc

Moving the system database "tempdb"

If the system database "tempdb" is defined on several data files as recommended in general, each data file is moved by a ALTER DATABASE statement.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_data, FILENAME = 'H:\MSSQL\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_01, FILENAME = 'I:\MSSQL\tempdb_01.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_02, FILENAME = 'J:\MSSQL\tempdb_02.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_03, FILENAME = 'K:\MSSQL\tempdb_03.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_log, FILENAME = 'L:\MSSQL\templog.ldf');
GO


The output of each statement is a message that the system catalog is modified and that the new path will be used after a restart of the database instance.

The file "tempdbXXXX" has been modified in the system catalog. The new path will be used the next time the database is started.




Please notice that you have to remove the old tempdb files manually after the restart.


If the size of the files is configured before the move, a side benefit is de-fragmented files.

Modify SQL Agent Errorlog path and Default Database Path

If you are moving databases to a new drive/volume or another server (different drive letter), you will find it useful to also change the SQL Server Agent log path as well as the Database Default Locations path. If you do not change the SQL Agent log path, SQL Agent will fail to start. If you do not modify the Database Default Locations path, creating a database may fail, if the original drive letter\path does not exist any more.

To change the SQL Agent log path:

1. In SQL Server Management Studio expand SQL Server Agent

2. Right-click on Errorlogs
3. Click on Configure
4. Change the Error Log File path to the new desired location

To change the Database Default Locations path

1. In SQL Server Management Studio right-click on the SQL Server name
2. Choose Prooperties
3. Choose Database Settings
4. Under "Database Default Locations" modify the Data and Log paths to the new, desired location

TSQL Script to return files and Free Space

Here is the script

 

;WITH c AS (
SELECT  g.name AS [FileGroupName]
       ,s.type_desc
       ,s.physical_name AS [FileName]
       ,s.size * CONVERT(FLOAT, 8) AS [Size]
       ,CAST(CASE s.type
               WHEN 2 THEN 0
               ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)
             END AS FLOAT) AS [UsedSpace]
FROM    sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ( ( s.type = 2
                                        OR s.type = 0
                                      )
                                      AND s.database_id = DB_ID()
                                      AND ( s.drop_lsn IS NULL )
                                    )
                                    AND ( s.data_space_id = g.data_space_id )
)
SELECT *, [size] - [UsedSpace] RemainingSpace
FROM c