Disk Space | DataGinger.com

Archive for the ‘Disk Space’ Category

SQL Server – How to Perform Striped Database Backup & Restore Using T-SQL Command

Posted in Backup, Disk Space, T-SQL, Transaction Log, tagged SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 on September 3, 2013| 1 Comment »

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB).

As shown in the picture below, striping is nothing but splitting one backup file to multiple backup files (maximum 64 files). However, these files may or may not be the same size (depends on the storage disks IO).

By Striping a backup we can:

Increase backup throughput and reduce the backup time window
Allow backups & restores to be written or to be read from all devices in parallel
Enable backup to different disks, thus distribute the space usage

Below are the simple T-SQL backup commands using AdventureWorks2012 sample database as an example.

T-SQL command for Striping a database backup

Note: In the below script, I used only Disk C to contain all the striped .bak files. However, we can direct to multiple disks if required

-- Striped Backups -- Backup to multiple files - 4 files in this case
BACKUP DATABASE [AdventureWorks2012]
TO 
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL command to restore from Striped database backup

--Restoring from striped backup -- from multiple files
RESTORE DATABASE [AdventureWorks2012] 
FROM  
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO

Also, we can apply the same striping concept on Log backups. Below is how we do it

T-SQL command for Striping transaction log backup

--Striped Log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn', 
DISK='C:\AdventureWorks2012_2.trn', 
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

Demo: Normal Backup Vs Striped Backup

Below are the results and screenshots from a live production environemnt. This once again proves striping backup files increase data transfer rate and reduce the time to backup

Results:

Backup Type	Time to Backup	Data Transfer Rate
Normal Backup (1 File)	537.6 Seconds	111.9 MB/Sec
Striped Backup (4 Files)	201.0 Seconds	299.3 MB/Sec

Screenshots:

However, the major downside of striping a backup is that if at-least one backup file is corrupt, restore operation cannot be performed using the other files.

Also, HERE is why I haven’t discussed striping backups using SSMS GUI.

Technical Reviewer(s): Hareesh Gottipati; Jaipal Vajrala

Read Full Post »

SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other

Posted in Disk Space, Files and FileGroups, T-SQL, Transaction Log, tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 on August 10, 2013| 17 Comments »

Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):

1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives

There can be even more secnarios like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command

Let us take the below scenario for step-by-step Demo:

Database: AdventureWorks2012
Current Files Location: C:\Disk1
New\Target Files Location: C:\Disk2

Step 1: Get the current database files Logical Name and Physical Location

USE master
GO
SELECT name AS LogicalFileName, physical_name AS FileLocation
, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('AdventureWorks2012');

Step 2: Take the Database offline

USE master
GO
ALTER DATABASE AdventureWorks2012 SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Note: In the above T-SQL query, I used WITH ROLLBACK IMMEDIATE option. Please be careful when using this option as it rolls back those incomplete transactions immediately. You may exclude this option, but have to wait till all the transactions are committed to take the database offline.

Step 3: Move the database files physically in the Windows OS to the new location

Step 4: Use ALTER DATABASE to modify the FILENAME to new location for every file moved

Only one file can be moved at a time using ALTER DATABASE.

USE master
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE 
( NAME = AdventureWorks2012_Data, 
FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path

USE master
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILE 
( NAME = AdventureWorks2012_Log, 
FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path

Step 5: Set the database ONLINE

USE master
GO
ALTER DATABASE AdventureWorks2012 SET ONLINE;

Step 6: Now, verify the database files Physical location

USE master
GO
SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('AdventureWorks2012');

Note: Same method can be used for moving files for any system or user defined database except for Resource database files

Read Full Post »

SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans

Posted in Backup, Disk Space, SQL Server Agent, tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 on August 7, 2013| 17 Comments »

Cleaning/deleting the old backup files is equally important as taking the backups. Doing so we can (i) Avoid overhead cost for the storage space, and (ii) Ensure and retain enough space on the disk for the next successful backup

Using ‘Maintenance Plans’ this task will be a cake walk for the DBAs who are having hard time deleting the old backup files manually!

Below is the step by step process with screenshots showing how to setup and Automate backup files cleanup task using Maintenance Plans in SQL Server.

Step 1: Connect to the SQL Server instance, expand ‘Management’ folder, right click on ‘Maintenance Plans’ and select ‘Maintenance Plan Wizard’

Step 2: ‘Maintenance Plan Wizard’ pops out, click Next

Step 3: On the ‘Maintenance Plan Wizard’ give a Name and Description. To schedule this as a job, select Change under ‘Schedule’

Step 4: ‘New Job Schedule’ pops out. Define a schedule. In my case I gave it to run Daily at 12:00:00 AM. Click ‘ok’ when done.

Step5: Make sure everything is correct and click Next

Step 6: Select the Maintenance Cleanup Task option and click Next

Step7: Here we only have one task, so nothing to order/re-order. Click Next

Step 8: This window is the heart for this task. Under ‘Delete files of the following type’ select Backup files. Under ‘File location’ select Search folder and delete files based on an extension. Give the path of the backup files location in Folder and “bak” (for native backups) in File extension. For Light Speed Backups you need to mention ‘lsb’.

Under ‘File age’ check the option Delete files based on the age of the file at task run time. Under ‘Delete files older than the following’ select a retention policy. I left the default 4 weeks. Click Next

Step 9: The wizard will write a report to a text file to the defined Folder location. You can leave the default location or chose a location of your choice. Click Next

Step 10: Click Finish

This should create the Maintenance Plan ‘Delete old backup files’ as shown in the screenshot below

Note: SQL Agent Services must be running in order to execute the above discussed Maintenance Plan.

Read Full Post »

SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information

Posted in Disk Space, Files and FileGroups, tagged SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 on June 28, 2013| 28 Comments »

From a recent conversation with a customer, I noticed even some of the experienced DBAs are not very clear about the terms “unallocated space” and “unused space” and by doing a quick Bing search, found there are many SQL friends out there trying to get a clear correlation between these SQL Server terms: unallocated space, unused space, and reserved.

Today, let us try to get a clear picture (infact, a picture!) of these terms using sp_spaceused system stored procedure and at the same time understanding results for database size information.

Execute the below query on AdventureWorks2012 sample database

USE [AdventureWorks2012]
GO
EXEC sp_spaceused
GO

Results:

Now, let us understand the above result sets, lets check by the column names

database_size: database size (data files + log files) = 205.75 MB
unallocated space: space that is not reserved for use by database objects (Space Available) = 14.95 MB
reserved: space that is reserved for use by database objects = 190.05 MB
data: total space used by data = 97016 KB/1024 = 94.74 MB
index_size: total space used by indexes = 88048 KB/1024 = 85.99 MB
unused: portion of the reserved space, which is not yet used = 9544 KB/1024 = 9.32 MB
used: let us coin this word to simplify things. say used = data + index_size = 94.74 + 85.99 = 180.73 MB

We now know what these columns are and the numbers they are returning. But, here is the real confusion part: How are they correlated with each other?

To answer this, below is the simple formulae I came up with to better interpret the results. I haven’t found this simple explanation clearly anywhere else on SQL space (trust me, you won’t)

(color coded to read easy)

used = data + index_size
reserved = used + unused
database_size = reserved + unallocated space + log space

To cross verify, we can substitute the result set we got from AdventureWorks2012 database in the above formula and confirm by checking the log file size. Alternatevely, we can calculate the log file size, which is not returned from the ‘sp_spaceused’ result set.

180.73 = 94.74 + 85.99 –>TRUE
190.05 = 180.43 + 9.32 –> TRUE
205.75 = 190.05 + 14.95 + log space

Hence log space = 205.75 – (190.05+14.95) = 0.75 MB (which is right!)

And, below is my small art in the same color coding as above to illustrate in a picture format. Of course, who doesn’t love pictures, and I am no exception -:)