Archive

Archive for the ‘Backup and Recovery’ Category

SQL server 2012 Backup issue and resolution

January 26, 2013 Leave a 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

Advertisements
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

Backup Issue if Full text search is enable

May 5, 2012 1 comment

SQL Error Message:
The backup of the file or filegroup “sysft_ftcat_documentindex” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. [SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)

Resolution

workaround:

Initially you need to check the D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTData is exist if it is not exist then create the FTData folder

Afterwards:

Run the following query on database.

ALTER FULLTEXT CATALOG ftcat_documentindex REBUILD;

Some useful Full text command:

In the database that uses the full text catalog run:

SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0

That will give you a list of the tables which use it, then you can mark each table not to use full text:

Categories: Backup and Recovery