How big are your SQL Server database files? Where are they?

This is another quick piece that I run when I take on a new project.  Not too extensive, it just returns some brief details regarding your database files, sizes and locations.  Run it for one database, or every db on the server.  You might also consider using this within an Agent job, to report the file state to your DBA Team, on a recurring basis.

IF OBJECT_ID('usp_DatabaseDetails','P') <> 0
DROP PROCEDURE dbo.usp_DatabaseDetails
GO
CREATE PROCEDURE dbo.usp_DatabaseDetails (
@DBName VARCHAR(100) = NULL
)
AS
SET NOCOUNT ON;
/*
Returns database file/filegroup details for the given @DBName. Informational only, helps WHEN assessing new environment(s).

EXEC dbo.usp_DatabaseDetails @DBName = 'MINE' -- Details for the given @DBName
EXEC dbo.usp_DatabaseDetails @DBName = '*'    -- Details for all databases on the server
EXEC dbo.usp_DatabaseDetails @DBName = 'HELP' -- Ask for 'help' regarding the procedure function

Auth: Your Name
Date: XX/XX/XXX
*/
IF(@DBName IS NULL OR @DBName = 'HELP')
BEGIN   
    PRINT 'HELP for usp_DatabaseDetails'
    PRINT ''
    PRINT 'This procedure will return file/filegroup details for the given @DBName, in this format:'
    PRINT ''
    PRINT '1) Database Name'
    PRINT '2) Logical name for all the database files, mdf, ldf and ndf'
    PRINT '3) Physical SIZE of each files in megabytes (MB)'
    PRINT '4) Drive letter upon which each of file resides'
    PRINT '5) Filegroup type '
    PRINT '6) Filegroup location, with the full path'
  RETURN
END

IF(@DBName IS NULL)
BEGIN   
    SELECT
        DB_Name() [DatabaseName],
        SUBSTRING(name,1,50) [Logical Name],
        SIZE/128 [SIZE in MB],
        SUBSTRING(FileName,1,1) [Drive],
        CASE groupid WHEN 1 THEN 'Primary'
            WHEN 0 THEN 'Log' ELSE 'Secondary' END [FileGroup Type],
        SUBSTRING(FileName,1,255) [Location]
    FROM
        sysfiles
END

IF(@DBName IS NOT NULL AND @DBName <> '*')
BEGIN
    IF NOT EXISTS(SELECT name FROM master..sysdatabases WHERE name = @DBName)
    BEGIN
       IF(@DBName='HELP')
       BEGIN
           RETURN;
       END

       IF(@DBName <> 'HELP')
       BEGIN
          PRINT 'The given @DBName '''+LTRIM(@DBName)+''' is not a database on this server.'
          RETURN;
       END
   END

    EXEC('SELECT '''+@DBName+''' [DatabaseName], SUBSTRING(Name,1,50) [Logical Name],
    SIZE/128 [MB],SUBSTRING(FileName,1,1) [Drive],CASE groupid
    WHEN 1 THEN ''Primary''
    WHEN 0 THEN ''Log''
    ELSE ''Secondary''
    END [FileGroup Type],
    SUBSTRING(FileName,1,255) [Location]FROM '+@DBName+'..sysfiles')

END

IF(@DBName = '*')
BEGIN   
    EXEC sp_msforeachdb 'SELECT UPPER(''?'') [DatabaseName],SUBSTRING(Name,1,50)  
    [Logical Name],SIZE/128 [MB],SUBSTRING(FileName,1,1) [Drive],CASE groupid
    WHEN 1 THEN ''Primary''
    WHEN 0 THEN ''Log''
    ELSE ''Secondary''
    END [FileGroup Type],
    SUBSTRING(FileName,1,255) [Location]
FROM ?..sysfiles'  END
 

SET NOCOUNT OFF;
 



 

How to move your SQL Server transaction log?

Why would you move your transaction log file?  Maybe your tran log has outgrown the disk that it is on, and you need to move to another location to prevent failure.  Or, you may be moving the log to another physical drive, separate from the data, to improve your I/O performance.  Regardless of the reason, this can be done via SSMS (SQL Server Management Studio) or tSQL, and I will show you the tSQL approach in this tip.  Be sure to complete these steps beforehand:

  - Use sp_helpfile to note the current location, size and name of the existing files.
  - Determine the location and name of the new file location. 
  - Backup your database.

Ok.  This is how you move your tran log, adjusting your drive letter and directory names accordingly:
 
     /* Take exclusive access to your database. */
     ALTER DATABASE YourDatabaseName
     SET OFFLINE WITH ROLLBACK IMMEDIATE;

     /* In Windows Explorer, copy/paste your files to new location, delete the old files. */

     /* ALTER your database, use MODIFY FILE for new file location. */
     ALTER DATABASE YourDatabaseName
     MODIFY FILE (
     NAME='YourDatabaseName_Log',
     FILENAME='X:\NewDirectory\NewSubdirectory\YourDatabaseName_Log.ldf');

     /* Bring your database back online. */
     ALTER DATABASE YourDatabaseName
     SET ONLINE;
  
That's it!

But what about sp_detach_db and sp_attach_db?  The relocation of the transaction log can be done with sp_detach_db/sp_attach_db in SQL Server versions pre 2012.  These commands were deprecated, however, in v2005.  If you are in v2005 or v2008, the file relocation can be done using this method, again adjusting the drive letter and directory names accordingly:
 
    USE MASTER
    GO
    /* Take exclusive access to your database. */
    ALTER DATABASE YourDatabaseName
    SET SINGLE_USER
    GO

    /* Detach your database. */
    EXEC sp_detach_db 'YourDatabaseName'
    GO

     /* In Windows Explorer, copy/paste your files to new location, delete the old files. */

    /* Re-attach your database. */
    EXEC sp_attach_db 'YourDatabaseName',
    'X:\NewDirectory\NewSubdirectory\YourDatabaseName_Data.mdf',
    'X:\NewDirectory\NewSubdirectory\YourDatabaseName_Log.ldf'
    GO
  

When was your last backup run?

One of the first things I query when I take on a contract -- what type of backups are being run, and when?  On one of my recent contracts, no names mentioned, I found that no backups were being run at all!!  Run this little ditty on a single instance, or across multiple instances through your CMS.  It returns a helpful summary on the status of your backups.

  SET NOCOUNT ON;

  DECLARE @Results TABLE (
    ServerName VARCHAR(100) NULL,
    DatabaseName VARCHAR(100) NULL,
    RecoveryModel VARCHAR(100) NULL,
    LastFullBkupTime DATETIME NULL,
    DaysSinceLastFull INT NULL,
    DaysSinceLastDiff INT NULL,
    HoursSinceLastLogBkup INT,
    DBStatus VARCHAR (100) NULL,
    BkupFile VARCHAR(1000) NULL,
    Media INT
  )

  /* Get Server and database names. */

  INSERT @Results(ServerName,DatabaseName)
  SELECT CONVERT(VARCHAR,SERVERPROPERTY('ServerName')),a.name
  FROM master..sysdatabases a
  WHERE a.name <> 'tempdb'

  /* Last full bkup time and media. */

  UPDATE @Results
  SET
      LastFullBkupTime = b.backup_start_date,
      DaysSinceLastFull = DATEDIFF(dd,b.backup_start_date,GETDATE()),
      Media = b.media_SET_id
  FROM @Results a,(
    SELECT database_name, MAX(media_SET_id)media_SET_id, MAX(backup_start_date) backup_start_date
    FROM msdb..backupset
    WHERE TYPE = 'D'
    GROUP BY database_name) b
  WHERE
    a.DatabaseName = b.database_name

  /* Database status */

  UPDATE @Results
  SET DBStatus = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Status'))

 

  /* Recovery model */

  UPDATE @Results
  SET RecoveryModel = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Recovery'))

  /* Backup file location. */

  UPDATE d
  SET d.BkupFile = b.physical_device_name
  FROM @Results d, msdb..backupmediafamily b
  WHERE d.Media = b.media_SET_id
 
  /* Days since last diff bkup. */
  UPDATE d
  SET d.DaysSinceLastDiff = DATEDIFF(dd,b.backup_finish_date,GETDATE())
  FROM @Results d, (
     SELECT database_name,MAX(backup_finish_date) backup_finish_date
     FROM msdb..backupset
     WHERE TYPE  = 'I' 
     GROUP BY database_name) b
  WHERE d.DatabaseName = b.database_name

  /* Hours since last log dump. */

  UPDATE d
  SET d.HoursSinceLastLogBkup = DATEDIFF(hh,b.backup_finish_date,GETDATE())
  FROM @Results d, (
     SELECT database_name,MAX(backup_finish_date) backup_finish_date
     FROM msdb..backupset
     WHERE TYPE ='L' 
     GROUP BY database_name) b
  WHERE d.DatabaseName = b.database_name
  AND d.RecoveryModel <> 'SIMPLE'

  /* Bring it back for review. */

SELECT
    ServerName,
    DatabaseName,
    RecoveryModel,
    LastFullBkupTime,
    DaysSinceLastFull,
    DaysSinceLastDiff,
    HoursSinceLastLogBkup, 
    DBStatus,
    BkupFile,
    Media
FROM
    @Results

  SET NOCOUNT OFF;

 

How to rename your SQL Server

When you rename the computer for your SQL Server, the new name is automatically recognized during startup.  There are only a couple follow-up steps that you will need to run after you bring the machine up.  I have shown the rename for both a DEFAULT instance, and a named instance below. 

Please review each of these pointers BEFORE you attempt to rename the server:
 
   1.  If your renamed server is using Reporting Services, the SSRS instance may be unavailable after the rename.  In this
        case, you will ALSO need to perform a step within Reporting Services Configuration tool.  See here:    
             http://msdn.microsoft.com/en-us/library/ms345235.aspx
 
   2.  If your server is using database mirroring, you must turn off the mirroring BEFORE you perform the rename.  Afterward,
        you will re-establish the mirror with the new server name.
 
   3.  You cannot rename the server if it is involved in replication, UNLESS you are using log shipping with the replication.  In
        this case, the secondary computer in the log shipping solution can be renamed, if the primary fails.
 
   4.  Do NOT use the methods below if your server is part of a failover cluster.  See this method instead:    
            http://technet.microsoft.com/en-us/library/ms178083.aspx

To rename your SQL Server:

/* For a DEFAULT instance of SQL Server */ 

     EXEC sp_dropserver 'YourOldServerName';
     EXEC sp_addserver 'YourNewServerName', local; 

/* For a named instance of SQL Server */ 

     EXEC sp_dropserver '[YourOldServerName\InstanceName]';
     EXEC sp_addserver '[YourNewServerName\InstanceName]', local; 

That's it!  This method will work with everything from v2000 forward.  You can use this to check sys.servers, and confirm that @@SERVERNAME now reflects the new name:

    SELECT @@SERVERNAME;

Use sp_executesql or EXEC ?

There are many different thoughts out there on the use of sp_executesql vs EXEC.  Personally, I use sp_executesql rather than EXEC(), because it has better security, and it can have better performance.

First, this is because sp_executesql allows us to use parameterized statements.  EXEC/EXECUTE does not.  The parameterized statements do not expose us to the risk of SQL Injection.  Hence, the better security. 

Secondly, if we use sp_executesql to execute our statements that are called again and again, the Optimizer will reuse the execution plan.  And, we can achieve better performance from the cached query plans.

See this quick sp_executesql example, using AdventureWorks2012:


DECLARE
  @sqlCommand nvarchar(1000),
  @ColumnList varchar(75),
  @CountryRegionCode nvarchar(3)

SET @ColumnList = 'StateProvinceID, StateProvinceCode,CountryRegionCode,[Name]'
SET @CountryRegionCode = 'US'
SET @sqlCommand = 'SELECT ' + @ColumnList + ' FROM Person.StateProvince WHERE CountryRegionCode = @CountryRegionCode'

EXECUTE sp_executesql @sqlCommand, N'@CountryRegionCode nvarchar(3)', @CountryRegionCode = @CountryRegionCode

 

More information:  http://technet.microsoft.com/en-us/library/ms188001.aspx