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;
Monday, January 13, 2014
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
- 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.
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
)
SELECT CONVERT(VARCHAR,SERVERPROPERTY('ServerName')),a.name
FROM master..sysdatabases a
WHERE a.name <> 'tempdb'
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
SET DBStatus = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Status'))
SET RecoveryModel = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Recovery'))
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
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'
ServerName,
DatabaseName,
RecoveryModel,
LastFullBkupTime,
DaysSinceLastFull,
DaysSinceLastDiff,
HoursSinceLastLogBkup,
DBStatus,
BkupFile,
Media
FROM
@Results
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 @ResultsSET
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 dSET 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:
EXEC sp_addserver 'YourNewServerName', local;
EXEC sp_addserver '[YourNewServerName\InstanceName]', local;
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
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
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.
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
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.
Subscribe to:
Posts (Atom)