Backup job failure issue.

Backup job failed with below error:

Msg 3202, Level 16, State 2, Line 1
Write on "H:\Backups\XXXX\XXX_FULL_20110926.BAK" failed: 33(The process cannot access the file because another process has locked a portion of the file.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Resolution:

As per below image, check the Disk properties for ‘Compress drive to save disk space’ is checked or not.

If yes, please uncheck and execute the backup query, it will execute successfully.

Simultaneous running of both process (backup and drive compress process) cause the backup failure.

Large Log Ratio compared with data file size:

Large Log Ratio compared with data file size:

When log ratio is more than 2 times of data file size, how can we resolve to less size of log file than the data file as per database standard?

Run below query steps:

dbcc opentran

Output:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator

backup log dbname with truncate_only

(If you run this above query, you are recommended to take full back up next after above query, if not it will break LSN sequence) otherwise you will get below error message during transaction log backup job operation.

System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)

if above query not working means, follow the below query

use master

Go
alter database dbname
set recovery simple

alter database dbname

set recovery full

use dbname

dbcc shrinkfile(dbname_log,sizetoshrink)

That’s it.

Hope you will resolve large log ratio issue 

STANDARD #1

How to find the data files and log files for those not in the proper drives as per database standards?

Data file check

select name, physical_name from sys.master_files

where type = 0 and left(physical_name,12) <> 'A:\Data\SQL\'

Log file Check

select name,database_id, physical_name from sys.master_files

where type = 1 and left(physical_name,12) <> 'B:\Logs\SQL\'

If any records found for the above query,if so seems to be some of the datafiles and log files not following database standard.

We need to change mdf and ldf to the appropriate path.

How to stop the log shipping alert which sending the error message 14421?

How to stop the log shipping alert which sending the below error message?

Error Message:

Over the previous interval, there were 5 occurrences of the following event.

Logfile: Application
Event Type: Error
Event ID: 14421
Computer: XXXXXXXX
Source: MSSQL$SQL2008
Text:
The log shipping secondary database Servername \SQL2008.Databasename has restore threshold of 45 minutes and is out of sync.
No restore was performed for 9426 minutes. Restored latency is 8 minutes. Check agent log and logshipping monitor information.

Cause:

Database which involved in source as well in destination server for log shipping setup is not available. Because database dropped by somebody.

Resolution:

The database name entry is found in the below logshipping tables that are referencing the alert jobs LSAlert_ XXXXXXXX \SQL2008 and LSAlert_YYYYYYY\SQL2008 in both primary and secondary server.

Action Taken:

 By removing the record entry in the below log shipping tables corresponding to the log shipping database, Alerts stating above error message 14421 won’t come anymore.

Select * from msdb.dbo.log_shipping_monitor_primary
select * from msdb.dbo.log_shipping_monitor_secondary

Delete from msdb.dbo.log_shipping_monitor_primary where primary_database='logshippingdatabasename'

Delete from msdb.dbo.log_shipping_monitor_secondary where secondary_database='logshippingdatabasename'