Archive

Archive for the ‘Backup and Recovery’ Category

Capacity Planning using SQL Server Backup History


Problem Statement: Storage is always been one of the IT infrastructure component, where organization invest in a lot. Optimal utilization of storage is one of the key factors on saving cost. We should have fair details on the current storage requirement and also ability to forecast the tentative growth. This is critical, so that we don’t end up wasting storage by allocation extra, where the data is not growing and having trouble in adding storage where data is growing.
SQL Server is used for lot of application as its database in any organization. The same storage story apply to SQL Server databases as well. When any new database is created, we may predict the storage requirement depending on may be a baseline of the applications design or just leave it to grow with know the growth. For the forecast we may use tools that may help in understanding the growth. Then there is no issue in forecasting the growth and allocating the storage accordingly. What if we don’t have any such tools in place to provide the data growth history details of database or its file?
Solution: In any stable and healthy environment we maintain a good backup strategy. Let us assume that, we have daily backup in place for a database called Test and we maintain the history of this backup in msdb database. This Test database have multiple data file residing on multiple drive.
We can perform the below steps to build the database/data file growth report.

1. Gather the data from backup history. The below query will help in fetching these details.
select a.backup_set_id,b.backup_finish_date, b.database_name,
a.physical_name, sum(a.file_size/128/1024) as fizeSizeinMB from msdb..backupfile a, msdb..backupset b
where a.backup_set_id = b.backup_set_id
and b.database_name in (‘Test’)
and b.type = ‘D’
and a.file_type = ‘D’
group by a.backup_set_id,b.backup_finish_date, b.database_name,
a.physical_name
order by b.backup_finish_date desc

2. Copy the data in excel sheet.

3. Make use of Pivot and graph to represent the data growth. We will not discuss on how to work with excel here, but refer the excel file which will help in better understanding here.

The above easy step will provide the pattern of data file growth within a database. You can now analyze the data with slice and dice in excel.

Categories: Backup and Recovery

SQL server 2012 Backup issue and resolution

January 26, 2013 1 comment

We have received the below failure for one database at SQL server 2012 64 bit enterprise edition

 

Error:

A database from an earlier version of SQL Server becomes unusable when you attach it to an instance of SQL Server 2012

 

Msg 5901, Level 16, State 1, Line 1

 

One or more recovery units belonging to database AdventureWorksLT2008 failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

 

Msg 9003, Level 20, State 15, Line 1

The log scan number (43607:149:1) passed to log scan in database AdventureWorksLT2008 ‘ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

 

Workaround:

 

http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_SNAC_CU2_2703275_11_0_2325_x64/11.0.2325.0/free/453121_intl_x64_zip.exe)

 

Need to be downloading and update the service pack

 

I have attached the below four  link which indicate the KB article for Microsoft® SQL Server® 2012 issue and resolution. All the links are specially for 64 bit edition.

 

Package:

———————————————————–

———————————————————–

KB Article Number(s): 2703275

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_SNAC_CU2_2703275_11_0_2325_x64/11.0.2325.0/free/453121_intl_x64_zip.exe)

 

———————————————————–

KB Article Number(s): 2550375, 2554905, 2567366, 2658474, 2665649, 2667211, 2673997, 2675233, 2675522, 2677198, 2678796, 2679342, 2682488, 2682819, 2683304, 2685132, 2685743, 2688968, 2689336, 2689903, 2693652, 2698150, 2699163, 2699820, 2699949, 2699968, 2702015, 2702879, 2703152, 2703275, 2705236, 2705571, 2707166, 2708594, 2710782, 2711145, 2711315, 2711683, 2714785, 2717234, 2717239, 2719217, 2720619, 2720673, 2720678, 2720690, 2720961, 2721122, 2721610

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_PPExcel_CU2_2703275_11_0_2325_x/11.0.2325.0/free/449400_intl_x64_zip.exe)

 

———————————————————–

KB Article Number(s): 2550375, 2554905, 2567366, 2658474, 2665649, 2667211, 2673997, 2675233, 2675522, 2677198, 2678796, 2679342, 2682488, 2682819, 2683304, 2685132, 2685743, 2688968, 2689336, 2689903, 2693652, 2698150, 2699163, 2699820, 2699949, 2699968, 2702015, 2702879, 2703152, 2703275, 2705236, 2705571, 2707166, 2708594, 2710782, 2711145, 2711315, 2711683, 2714785, 2717234, 2717239, 2719217, 2720619, 2720673, 2720678, 2720690, 2720961, 2721122, 2721610

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/SQLServer2012_RTM_CU2_2703275_11_0_2325_/11.0.2325.0/free/449398_intl_x64_zip.exe)

 

———————————————————–

KB Article Number(s): 2550375, 2554905, 2567366, 2658474, 2665649, 2667211, 2673997, 2675233, 2675522, 2677198, 2678796, 2679342, 2682488, 2682819, 2683304, 2685132, 2685743, 2688968, 2689336, 2689903, 2693652, 2698150, 2699163, 2699820, 2699949, 2699968, 2702015, 2702879, 2703152, 2703275, 2705236, 2705571, 2707166, 2708594, 2710782, 2711145, 2711315, 2711683, 2714785, 2717234, 2717239, 2719217, 2720619, 2720673, 2720678, 2720690, 2720961, 2721122, 2721610

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_DAC_CU2_2703275_11_0_2325_x64/11.0.2325.0/free/449402_intl_x64_zip.exe

Categories: Backup and Recovery

Is it possible that SQL 2005 backup can restore in SQL 2000?


As per Microsoft there is no workaround. SQL Server 2005 does not support downgrades or restores to SQL Server 2000. But if server contains SQL 2000 and 2005 both instances then we can restore the SQL 2005 backup on SQL 2000 instance.

Or

There is no “Restore” functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click “script all objects…”, hit next
select any options you want, specifically changing “script for server version” to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export…
set source and hit next
set destination and hit next
select “copy data from one or more tables”, hit next
select all, check “optimize for many tables” and “run in a transaction”
you may have to edit each table mapping and check “enable identity insert”

Categories: Backup and Recovery

Very interesting feature of Backup compression


I come across very interesting feature of Backup compression.

If you have taken the backup of the database by TSQL statement like with compress command that time the compress is workout but the size is different. If you would like to take the full Backup compress futures then enable advance option and set the server level setting for backup compression on. After On the option then if take the backup then the size is different.

I have taken Adventure database backup without the server settings option that time the size is 135235 KB but the database is backed after enable the option then the size is 34906 kb.

 

To enable the option
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
GO

Categories: Backup and Recovery

Proactive Monitoring method Database Log space status to avoid log file full error


As per the customer requirement we have to make a script that notified us whenever a database (including tempdb) has less than 50% unused transaction log space. We monitored for this condition proactively discover long running, transaction log consuming queries. Such queries can take a long time to rollback and can sometimes be a result of a user error – for example an accidental delete of many records. The DBA at this customer knew he had to take action when log space was low, to investigate such potential user error.

With the monitoring scripts used by us the alarm went off regularly on tempdb. Because of the checkpoint behavior described above, unused transaction log space is only reclaimed at 70% log usage of tempdb. The customer still wanted to headroom of the 50% limit, so he had to be able to distinguish between the “by design” checkpoint at 70% and a troublemaking, long running transaction preventing log space from being reclaimed.

The solution for the customer was to monitor to the column log_reuse_wait_desc in sys.databases in combination with the unused transaction log space. If this column has the value different than CHECKPOINT and NOTHING, the log usage is a result of a long running log space consuming transaction, in tempdb.

The script below illustrates how this is done:

SELECT name
, db.log_reuse_wait_desc
, ls.cntr_value AS size_kb
, lu.cntr_value AS used_kb
, CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
AS used_percent
, CASE WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5 THEN
CASE
/* tempdb special monitoring */
WHEN db.name = ‘tempdb’
AND log_reuse_wait_desc NOT IN (‘CHECKPOINT’, ‘NOTHING’) THEN ‘WARNING’
/* all other databases, monitor foor the 50% fill case */
WHEN db.name <> ‘tempdb’ THEN ‘WARNING’
ELSE ‘OK’
END
ELSE ‘OK’ END
AS log_status
FROM sys.databases db
JOIN sys.dm_os_performance_counters lu
ON db.name = lu.instance_name
JOIN sys.dm_os_performance_counters ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’
AND ls.counter_name LIKE ‘Log File(s) Size (KB)%’

Categories: Backup and Recovery

Quick way to handle “Transaction log file full” issue


In our day to day work life very frequently we are getting the error that transaction log grows unexpectedly or becomes full. In most of the production system it is happening frequently.

Errors from SQL server error log:

Error: 9002, Severity: 17, State: 2.

The transaction log for database ‘test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

If you get an error “Error: 9002, Severity: 17, State: 2.” like this go and check the log_reuse_wait_desc column in sys.databases by running below command and see what value it returns.

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases

log_reuse_wait_desc “NOTHING” means there is no open transaction. If any other value is mentioned then we should take the transaction log backup for the respective database.

Script To shrink the all user database if database associate with single log file:

sp_msforeachdb “use [?]; dbcc shrinkfile(2,TRUNCATEONLY) WITH NO_INFOMSGS ”

Note:The command will work fine only if every user database only have single log file.

If we are using multiple log files in different drive, then it is bit tough to quickly shrink the all user database log files together. It is very hectic. Please use the below command file to shrink all user database log file at the same time.

Script if databases have multiple log files:

DECLARE @DBName varchar(255)

DECLARE @DBLogicalFileName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR

select DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.name

from sys.master_files MaTableMasterFiles

where

– ONLINE

MaTableMasterFiles.state = 0

– Only look at databases to which we have access

and has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1

– Not master, tempdb or model

and db_name(MaTableMasterFiles.database_id) not in (‘Master’,’tempdb’,’model’)

AND type_desc LIKE ‘log’

group by MaTableMasterFiles.database_id, MaTableMasterFiles.name

order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (‘Use [‘ + @DBName + ‘] DBCC SHRINKFILE (“‘ + @DBLogicalFileName + ‘”)’)

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName

END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

Categories: Backup and Recovery

Recovering the master database in SQL 2008 environment


If the master database is corrupted or damaged, SQL Server won’t start. Attempting to start SQL Server will have no effect. Attempting to connect to the instance with Management Studio will invoke a warning that the server does not exist or that access is denied. The only solution is to first rebuild the master database using the command-line setup (as shown next), reapply any SQL Server updates, start SQL Server in single-user mode, and restore the master database.

1. Rebuild the master database using the following command-line setup:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=”<instance name>”

/SQLSYSADMINACCOUNTS=”<DomainName\UserName >” /SAPWD=”<password>”

■ setup.exe is either from your original installation media or the ‘‘local’’ setup.exe as found in the 100\Setup Bootstrap\Release directory.

■ The /QUIET switch suppresses all error messages.

■ The /ACTION=REBUILDDATABASE switch rebuilds all the system databases.

■ The /INSTANCENAME switch specifies the name of your SQL Server named instance. Use MSSQLServer for ‘‘<instance_name>’’ for the default instance.

■ The /SQLSYSADMINACCOUNTS switch corresponds to the currently logged in domain user running this rebuild process. The user must be a member of the SQL Server instance’s sysadmin server role.

■ The /SAPWD switch is used to indicate a new SA password if you configured SQL Server for mixed authentication.

2. Run the following from the command prompt to start a default instance of SQL Server in single-user mode:

sqlservr.exe -m

To start a named instance of SQL Server in single-user mode, run the following:

sqlservr.exe -m -s <instancename>

3. Reapply any SQL Server updates, service packs, and hot fixes that were previously applied to the SQL Server.

4. Restore the master database as you would a user database.

Note: Rebuilding the master database rebuilds the msdb and model databases too, so after rebuilding the databases restore the system databases (master, msdb, model) from the most recent good backup.

Categories: Backup and Recovery