Monday, January 13, 2014

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
 
 

Friday, January 10, 2014

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;

 

Thursday, January 9, 2014

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

 

Monday, December 23, 2013

Backup your databases... where?

Backing up my databases locally has been in my build list for a very long time.  Why?  Well, primarily for manageability.  I had to know that my backups were always good, and I did not want to introduce any other factors, such as network queues or interruptions.  I always told myself to back them up local first, and go from there.

Today I ran into a blog that suggested it may be time to revisit this practice. 

First and foremost, what are the backups for?  Recovery.  Aren't they?  If I backup the databases locally, and my server goes down, don't I need to need to recover the hardware first, before I can get to my database .bak file(s).  I am almost embarrassed to admit I have not looked at it from this perspective before. 

What about moving data around production networks intraday?  I don't know about you, but I have worked on some very secure AND controlled networks.  In many cases, it was often frowned upon to move backup files from production to DEV/QA intraday.  Not only the network, but the production resource itself!  During certain hours, these machines are locked down.  It would be much easier to access last night's backup, if it was written to a network share, rather than the production server.

There are a few other reasons, but I believe these two are the most pointed, obvious reasons to consider writing your backups to a network share, rather than the local disk. 

Where is SQL Server 2012 Books Online?

I have run quite a few v2012 installations recently, and I wanted to post a couple of quick notes for my readers.  The installation is still rather lengthy, but it is all pretty straightforward.  The features do appear differently throughout the install, in comparison to v2008.  You will see those differences noted here:       http://technet.microsoft.com/en-us/library/bb500459.aspx
 
One notable difference --  Books Online is gone!  If you've managed SQL Server for as long as I have, you may also find this a little odd.  In short, Microsoft has moved to a new Help technology.  The documentation is no longer included in the installation, and it must be either viewed online, or downloaded as a local help collection.   
    http://technet.microsoft.com/en-us/library/ms166020.aspx 
 
Fortunately, you can still install BOL as you've used it before, as written here by Kumar Vivek: 
 
Lastly, this topic describes the differences of v2012 at the level of each component, such as the installation, SSAS, SSIS, SSRS, etc.