aiDeeva.com

Category: High Availability & DR

  • Shrink Log File

    Just a few days back I received an issue regarding disk size is out of space. I have tried to shrink the database using DBCC ShrinkFile but there isn’t any success.

    I have follow below steps to resolve this issue and it really works.

    I have check the below query for

    Select name,log_reuse_wait_desc from sys.databases

    1. There was another database which is waiting log backup to release the space
    2. There was one database who is waiting for Replication to release the space

    Database who is waiting for log backup to release the log space

    I have taken the log backup two times and executed the database shrink file command to release the space and it has reclaimed 40GB space from transaction log file.

    Database who is waiting for Replication to release the log space

    This database is in Simple recovery mode and there isn’t any replication enable on this. I have executed the DBCC OPENTRAN command to see any active transaction. I have executed the DBCC OpenTran and it has provided me the below result.

    Oldest active transaction:

        SPID (server process ID): 101

        UID (user ID) : -1

        Name          : INSERT

        LSN           : (999:138204:2)

        Start time    : OCT  13 2009  1:34:47:827PM

        SID           : 0x88d52e4051a71143adee5dc7b6619f8a

    Replicated Transaction Information:

            Oldest distributed LSN     : (890:2091888:1)

            Oldest non-distributed LSN : (896:2784855:1)

    I don’t know the exact reason what happened internally. But from the output it seems that there is unmark distributed transaction. So I have executed Sp_Repldone command to unmark the LSN

     

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

     Than I have executed the DBCC ShrinkFile command and it has reclaimed the 400GB space

  • SQL Server 2005 Backup Error Messages

    MSSQL 2005 Backup or its related Error Messages

    Severity level 10 messages are informational and indicate problems caused by mistakes in the information you have entered. Severity levels from 11 through 16 are generated by the user, and can be corrected by the user.

    Severity levels from 17 through 25 indicate software or hardware errors (Please find the below update error messages).

    We have to inform the system administrator whenever problems that generate errors with severity levels 17 and higher than it. The system administrator must resolve these errors and track their frequency. When a level 17, 18, or 19 errors occur, we can continue working, although we might not be able to execute a particular statement.

    Error Messages

    select message_id, severity, [text] from sys.messages where language_id = 1033 and severity <> 10 and  [text] like ‘%backup%’

    Msg Id

    Severity

    Error Message

    20622

    11

    		 Replication database option -sync with backup- cannot be set on the publishing database because the database is in Simple Recovery mode_

    1411

    16

    		 The remote copy of database -_*ls has not had enough log backups applied to roll forward all of its files to a common point in time_

    1475

    16

    		 Database mirroring cannot be enabled because the -_*ls database may have bulk logged changes that have not been backed up_ The last log backup on the principal must be restored on the mirror_

    1478

    16

    		 The mirror database, -_*ls, has insufficient transaction log data to preserve the log backup chain of the principal database_  This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database_

    1833

    16

    		 File _ls- cannot be reused until after the next BACKUP LOG operation_

    1931

    16

    		 The SQL statement cannot be executed because filegroup __*ls- is offline_ Use the sys_database_files or sys_master_files catalog view to determine the state of the files in this filegroup and then restore the offline file(s) from backup_

    2505

    16

    		 The device __*ls- does not exist_ Use sys_backup_devices to show available devices_

    2515

    16

    		 Page -S_PGID, object ID -d, index ID -d, partition ID -I64d, alloc unit ID -I64d type -_*ls) has been modified but is not marked modified in the differential backup bitmap_

    2516

    16

    		 Repair has invalidated the differential bitmap for database -_*ls_ The differential backup chain is broken_ You must perform a full database backup before you can perform a differential backup_

    2517

    16

    		 Bulk-logging has been turned on for database -_*ls_ To ensure that all data has been secured, run backup log operations again_

    3002

    16

    		 Cannot BACKUP or RESTORE a database snapshot_

    3004

    16

    		 The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model_ Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS_

    3006

    16

    		 The differential backup is not allowed because it would be based on more than one base backup_ Multi-based differential backups are not allowed in the simple recovery model, and are never allowed for partial differential backups_

    3007

    16

    		 The backup of the file or filegroup -ls 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_

    3008

    16

    		 The specified device type is not supported for backup mirroring_

    3009

    16

    		 Could not insert a backup or restore history/detail record in the msdb database_ This may indicate a problem with the msdb database_ The backup/restore operation was still successful_

    3010

    16

    		 Invalid backup mirror specification_ All mirrors must have the same number of members_

    3011

    16

    		 All backup devices must be of the same general class (for example, DISK and TAPE)_

    3016

    16

    		 Backup of file _ls- is not permitted because it contains pages subject to an online restore sequence_ Complete the restore sequence before taking the backup, or restrict the backup to exclude this file_

    3021

    16

    		 Cannot perform a backup or restore operation within a transaction_

    3023

    16

    		 Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized_ Reissue the statement after the current backup or file manipulation operation is completed_

    3024

    16

    		 You can only perform a full backup of the master database_ Use BACKUP DATABASE to back up the entire master database_

    3033

    16

    		 BACKUP DATABASE cannot be used on a database opened in emergency mode_

    3035

    16

    		 Cannot perform a differential backup for database -ls, because a current database backup does not exist_ Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option_

    3038

    16

    		 The file name -ls is invalid as a backup device name_ Reissue the BACKUP statement with a valid file name_

    3039

    16

    		 Cannot perform a differential backup for file _ls- because a current file backup does not exist_ Reissue BACKUP DATABASE omitting the WITH DIFFERENTIAL option_

    3041

    16

    		 BACKUP failed to complete the command -_*ls_ Check the backup application log for detailed messages_

    3043

    16

    		 BACKUP _ls- detected an error on page (-d:-d) in file _ls-_

    3044

    16

    		 Invalid zero-length device name_ Reissue the BACKUP statement with a valid device name_

    3045

    16

    		 Differential BACKUP or RESTORE is not supported on the FAT file system_ The path -_*ls is not usable_

    3046

    16

    		 Inconsistent metadata has been encountered_ The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option_

    3047

    16

    		 The BackupDirectory registry key is not configured correctly_ This key should specify the root path where disk backup files are stored when full path names are not provided_ This path is also used to locate restart checkpoint files for RESTORE_

    3048

    16

    		 BACKUP LOG WITH TRUNCATE_ONLY cannot operate on database _ls- because it is configured for database mirroring_

    3049

    16

    		 BACKUP detected corruption in the database log_ Check the errorlog for more information_

    3050

    16

    		 SQL Server could not send the differential information for database file _ls- of database _ls\\-ls- to the backup application because the differential information is too large to fit in memory, and an attempt to use a temporary file has failed_

    3051

    16

    		 BACKUP LOG was unable to maintain mirroring consistency for database _ls-_ Database mirroring has been permanently suspended_ To resume mirroring, the existing mirroring configuration must be dropped and then re-established_

    3054

    16

    		 Differential file backups can include only read-only data for databases using the simple recovery model_ Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS_

    3103

    16

    		 A partial restore sequence cannot be initiated by this command_ To initiate a partial restore sequence, use the WITH PARTIAL clause of the RESTORE statement and provide a backup set which includes a full copy of at least the primary data file_ The WITH PARTIAL clause of the RESTORE statement may not be used for any other purpose_

    3106

    16

    		 The filegroup -ls is ambiguous_ The identity in the backup set does not match the filegroup that is currently defined in the online database_ To force the use of the filegroup in the backup set, take the database offline and then reissue the RESTORE command_

    3107

    16

    		 The file -ls is ambiguous_ The identity in the backup set does not match the file that is currently defined in the online database_ To force the use of the file in the backup set, take the database offline and then reissue the RESTORE command_

    3109

    16

    		 Master can only be restored and fully recovered in a single step using a full database backup_ Options such as NORECOVERY, STANDBY, and STOPAT are not supported_

    3116

    16

    		 The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore_

    3117

    16

    		 The log or differential backup cannot be restored because no files are ready to rollforward_

    3118

    16

    		 The database -ls does not exist_ RESTORE can only create a database when restoring either a full backup or a file backup of the primary file_

    3120

    16

    		 This backup set will not be restored because all data has already been restored to a point beyond the time covered by this backup set_

    3121

    16

    		 The file -ls is on a recovery path that is inconsistent with application of this backup set_ RESTORE cannot continue_

    3123

    16

    		 Invalid database name __*ls- specified for backup or restore operation_

    3125

    16

    		 The database is using the simple recovery model_ The data in the backup it is not consistent with the current state of the database_ Restoring more data is required before recovery is possible_ Either restore a full file backup taken since the data was marked read-only, or restore the most recent base backup for the target data followed by a differential file backup_

    3129

    16

    		 The contents of the file -ls are not consistent with a transition into the restore sequence_ A restore from a backup set may be required_

    3135

    16

    		 The backup set in file _ls- was created by -hs and cannot be used for this restore operation_

    3136

    16

    		 This differential backup cannot be restored because the database has not been restored to the correct earlier state_

    3143

    16

    		 The data set on device _ls- is not a SQL Server backup set_

    3144

    16

    		 File __*ls- was not backed up in file -d on device _ls-_ The file cannot be restored from this backup set_

    3147

    16

    		 Backup and restore operations are not allowed on database tempdb_

    3154

    16

    		 The backup set holds a backup of a database other than the existing _ls- database_

    3155

    16

    		 The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created_

    3159

    16

    		 The tail of the log for the database -ls has not been backed up_ Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose_ Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log_

    3168

    16

    		 The backup of the system database on the device -ls cannot be restored because it was created by a different version of the server (-ls) than this server (-ls)_

    3169

    16

    		 The database was backed up on a server running version -ls_ That version is incompatible with this server, which is running version -ls_ Either restore the database on a server that supports the backup, or use a backup that is compatible with this server_

    3173

    16

    		 The STOPAT clause provided with this RESTORE statement indicates that the tail of the log contains changes that must be backed up to reach the target point in time_ The tail of the log for the database -ls has not been backed up_ Use BACKUP LOG WITH NORECOVERY to back up the log, or use the WITH REPLACE clause in your RESTORE statement to overwrite the tail of the log_

    3178

    16

    		 File -ls is not in the correct state to have this differential backup applied to it_

    3180

    16

    		 This backup cannot be restored using WITH STANDBY because a database upgrade is needed_ Reissue the RESTORE without WITH STANDBY_

    3182

    16

    		 The backup set cannot be restored because the database was damaged when the backup occurred_ Salvage attempts may exploit WITH CONTINUE_AFTER_ERROR_

    3183

    16

    		 RESTORE detected an error on page (-d:-d) in database -ls as read from the backup set_

    3185

    16

    		 RESTORE cannot apply this backup set because the database is suspect_ Restore a backup set that repairs the damage_

    3186

    16

    		 The backup set has been damaged_ RESTORE will not attempt to apply this backup set_

    3187

    16

    		 RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information_

    3189

    16

    		 Damage to the backup set was detected_

    3190

    16

    		 Filegroup _ls- cannot be restored because it does not exist in the backup set_

    3195

    16

    		 Page -S_PGID cannot be restored from this backup set_ RESTORE PAGE can only be used from full backup sets or from the first log or differential backup taken since the file was added to the database_

    3196

    16

    		 RESTORE master WITH SNAPSHOT is not supported_ To restore master from a snapshot backup, stop the service and copy the data and log file_

    3201

    16

    		 Cannot open backup device _ls-_ Operating system error -ls_

    3204

    16

    		 The backup or restore was aborted_

    3205

    16

    		 Too many backup devices specified for backup or restore; only -d are allowed_

    3206

    16

    		 No entry in sysdevices for backup device __*ls-_ Update sysdevices and rerun statement_

    3207

    16

    		 Backup or restore requires at least one backup device_ Rerun your statement specifying a backup device_

    3208

    16

    		 Unexpected end of file while reading beginning of backup set_ Confirm that the media contains a valid SQL Server backup set, and see the console error log for more details_

    3214

    16

    		 Too many backup mirrors are specified_ Only -d are allowed_

    3215

    16

    		 Use WITH FORMAT to create a new mirrored backup set_

    3218

    16

    		 Backup mirroring is not available in this edition of SQL Server_ See Books Online for more details on feature support in different SQL Server editions_

    3227

    16

    		 The backup media on -ls is part of media family -d which has already been processed on -ls_ Ensure that backup devices are correctly specified_ For tape devices, ensure that the correct volumes are loaded_

    3231

    16

    		 The media loaded on -ls is formatted to support -d media families, but -d media families are expected according to the backup device specification_

    3232

    16

    		 The volume mounted on -ls does not have the expected backup set identity_ The volume may be obsolete due to a more recent overwrite of this media family_ In that case, locate the correct volume with sequence number -d of media family -d_

    3239

    16

    		 The backup set on device _ls- uses a feature of the Microsoft Tape Format not supported by SQL Server_

    3240

    16

    		 Backup to mirrored media sets requires all mirrors to append_ Provide all members of the set, or reformat a new media set_

    3242

    16

    		 The file on device _ls- is not a valid Microsoft Tape Format backup set_

    3249

    16

    		 The volume on device _ls- is a continuation volume for the backup set_ Remove it and insert the volume holding the start of the backup set_

    3255

    16

    		 The data set on device _ls- is a SQL Server backup set not compatible with this version of SQL Server_

    3256

    16

    		 The backup set on device _ls- was terminated while it was being created and is incomplete_ RESTORE sequence is terminated abnormally_

    3258

    16

    		 The volume on the device -ls is not part of the media set that is currently being processed_ Ensure that the backup devices are loaded with the correct media_

    3259

    16

    		 The volume on device _ls- is not part of a multiple family media set_ BACKUP WITH FORMAT can be used to form a new media set_

    3265

    16

    		 The login has insufficient authority_ Membership of the sysadmin role is required to use VIRTUAL_DEVICE with BACKUP or RESTORE_

    3266

    16

    		 The backup data at the end of -ls is incorrectly formatted_ Backup sets on the media might be damaged and unusable_ To determine the backup sets on the media, use RESTORE HEADERONLY_ To determine the usability of the backup sets, run RESTORE VERIFYONLY_ If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets_

    3268

    16

    		 Cannot use the backup file _ls- because it was originally formatted with sector size -d and is now on a device with sector size -d_

    3273

    16

    		 The BUFFERCOUNT parameter must supply a value that allows at least one buffer per backup device_

    3276

    16

    		 WITH SNAPSHOT can be used only if the backup set was created WITH SNAPSHOT_

    3280

    16

    		 Backups on raw devices are not supported_ _ls- is a raw device_

    4202

    16

    		 BACKUP LOG is not possible because bulk logged changes exist in the database and one or more filegroups are unavailable_

    4208

    16

    		 The statement -hs is not allowed while the recovery model is SIMPLE_ Use BACKUP DATABASE or change the recovery model using ALTER DATABASE_

    4212

    16

    		 Cannot back up the log of the master database_ Use BACKUP DATABASE instead_

    4214

    16

    		 BACKUP LOG cannot be performed because there is no current database backup_

    4218

    16

    		 Bulk-logged operations exist in the database_ Perform a BACKUP LOG_

    4305

    16

    		 The log in this backup set begins at LSN -_*ls, which is too recent to apply to the database_ An earlier log backup that includes LSN -_*ls can be restored_

    4311

    16

    		 RESTORE PAGE is not allowed from backups taken with earlier versions of SQL Server_

    4312

    16

    		 This log cannot be restored because a gap in the log chain was created_ Use more recent data backups to bridge the gap_

    4319

    16

    		 A previous restore operation was interrupted and did not complete processing on file _ls-_ Either restore the backup set that was interrupted or restart the restore sequence_

    4320

    16

    		 The file -ls was not fully restored by a database or file restore_ The entire file must be successfully restored before applying this backup set_

    4326

    16

    		 The log in this backup set terminates at LSN -_*ls, which is too early to apply to the database_ A more recent log backup that includes LSN -_*ls can be restored_

    4327

    16

    		 The log in this backup set contains bulk-logged changes_ Point-in-time recovery was inhibited_ The database has been rolled forward to the end of the log_

    4330

    16

    		 This backup set cannot be applied because it is on a recovery path that is inconsistent with the database_ The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point_ Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time_  For more information about recovery paths, see SQL Server Books Online_

    4338

    16

    		 The STOPAT clause specifies a point too early to allow this backup set to be restored_ Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point_

    4341

    16

    		 This log backup contains bulk-logged changes_ It cannot be used to stop at an arbitrary point in time_

    4343

    16

    		 The database has been rolled forward to the end of this backup set and beyond the specified point in time_ RESTORE WITH RECOVERY can be used to accept the current recovery point_

    4348

    16

    		 The online restore to database _ls- failed_ It may be appropriate to perform an offline restore instead_ An offline restore is initiated by using BACKUP LOG WITH NORECOVERY_

    4349

    16

    		 The log in this backup set begins at LSN -_*ls, which is too recent to apply to the database_ This restore sequence needs to initialize the log to start at LSN -_*ls_ Reissue the RESTORE LOG statement using an earlier log backup_

    4351

    16

    		 Backups taken on earlier versions of SQL Server are not supported by fn_dump_dblog_

    5173

    16

    		 One or more files do not match the primary file of the database_ If you are attempting to attach a database, retry the operation with the correct files_  If this is an existing database, the file may be corrupted and should be restored from a backup_

    5250

    16

    		 Database error: -ls page -S_PGID for database __*ls- (database ID -d) is invalid_  This error cannot be repaired_  You must restore from backup_

    7694

    16

    		 Failed to pause catalog for backup_ Backup was aborted_

    7695

    16

    		 Operation failed_ Full-text catalog backup in progress_ Retry after backup operation has completed_

    8309

    16

    		 BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated_ The simple recovery model should be used to automatically truncate the transaction log_

    8463

    16

    		 Failed to read the message body while marshaling a message_ This message is a symptom of another problem_ Check the SQL Server error log and the Windows event log for additional messages and address the underlying problem_ If the problem persists, the database may be damaged_ To recover the database, restore the database from a clean backup_ If no clean backup is available, consider running DBCC CHECKDB_ Note that DBCC CHECKDB may remove data to repair the database_

    8477

    16

    		 An internal Service Broker error occurred (error = 0x-08x)_ This error indicates a serious problem with SQL Server_ Check the SQL Server error log and the Windows event logs for information pointing to possible hardware problems_ The database may have been damaged_ To recover the database, restore the database from a clean backup_ If no clean backup is available, consider running DBCC CHECKDB_ Note that DBCC CHECKDB may remove data to repair the database_

    8930

    16

    		 Database error: Database -d has inconsistent metadata_ This error cannot be repaired and prevents further DBCC processing_ Please restore from a backup_

    9715

    16

    		 The conversation endpoint with conversation handle _ls- is in an inconsistent state_  Check the SQL Server error logs and the Windows event logs for information on possible hardware problems_  To recover the database, restore the database from a clean backup_  If no clean backup is available, consider running DBCC CHECKDB_ Note that DBCC CHECKDB may remove data_

    14052

    16

    		 The @sync_type parameter value must be automatic, none, replication support only, initialize with backup, or initialize from lsn_

    14418

    16

    		 The specified @backup_file_name was not created from database _s-_

    14419

    16

    		 The specified @backup_file_name is not a database backup_

    14420

    16

    		 The log shipping primary database -s_-s has backup threshold of -d minutes and has not performed a backup log operation for -d minutes_ Check agent log and logshipping monitor information_

    14450

    16

    		 The specified @backup_file_name was not taken from database _s-_

    14451

    16

    		 The specified @backup_file_name is not a database backup_

    15012

    16

    		 The device _s- does not exist_ Use sys_backup_devices to show available devices_

    15044

    16

    		 The type -s is an unknown backup device type_ Use the type disk or tape_

    15061

    16

    		 The add device request was denied_ A physical device named -s already exists_ Only one backup device may refer to any physical device name_

    17208

    16

    		 -s: File _s- has an incorrect size_ It is listed as -d MB, but should be -d MB_ Diagnose and correct disk failures, and restore the database from backup_

    17557

    16

    		 DBCC DBRECOVER failed for database ID -d_ Restore the database from a backup_

    18204

    16

    		 -s: Backup device _s- failed to -s_ Operating system error -s_

    18210

    16

    		 -s: -s failure on backup device _s-_ Operating system error -s_

    18273

    16

    		 Could not clear _s- bitmap in database _s- because of error -d_ As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup_ This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary_ Typically, the cause of this error is insufficient resources_ Investigate the failure and resolve the cause_ If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups_

    18781

    16

    		 The value of the @backupdevicetype parameter must be one of -logical-, -disk-, or -tape-_

    18782

    16

    		 Could not locate backup header information for database _s- in the specified backup device_

    18786

    16

    		 The specified publication does not allow initialize with backup_

    18787

    16

    		 Support for initialize with backup can not be enabled for Snapshot publications_

    18790

    16

    		 Support for initialize with backup can not be enabled for non-SQL Server Publishers_

    18846

    16

    		 Possible inconsistent state in distribution db: dist_backup_lsn {-08lx:-08lx:-04lx}, dist_last_lsn {-08lx:-08lx:-04lx}_

    21333

    16

    		 A generation was expected to be found in -s_dbo_MSmerge_genhistory, but was not there_ If this database is a subscriber, a reinit is recommended_ If it is a publisher, a restore from backup is recommended_

    21397

    16

    		 The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor_ Retry the operation again with a more up-to-date log, differential, or full database backup_

    21407

    16

    		 The entire publication must be subscribed at once when setting up an -initialize with backup- subscription_

    1522

    20

    		 Sort operation failed during an index build_ The overwriting of the allocation page in database __*ls- was prevented by terminating the sort_ Run DBCC CHECKDB to check for allocation and consistency errors_ It may be necessary restore the database from backup_

    7909

    20

    		 The emergency-mode repair failed_You must restore from backup_

    9003

    20

    		 The log scan number -S_LSN passed to log scan in database __*ls- 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_

    3301

    21

    		 The transaction log contains a record (logop -d) that is not valid_ The log has been corrupted_ Restore the database from a full backup, or repair the database_

    3313

    21

    		 During redoing of a logged operation in database __*ls-, an error occurred at log record ID -S_LSN_ Typically, the specific failure is previously logged as an error in the Windows Event Log service_ Restore the database from a full backup, or repair the database_

    3314

    21

    		 During undoing of a logged operation in database __*ls-, an error occurred at log record ID -S_LSN_ Typically, the specific failure is logged previously as an error in the Windows Event Log service_ Restore the database or file from a backup, or repair the database_

    3315

    21

    		 During rollback, the following process did not hold an expected lock: process -d with mode -d at level -d for row -S_RID in database __*ls- under transaction -S_XID_ Restore a backup of the database, or repair the database_

    3316

    21

    		 During undo of a logged operation in database __*ls-, an error occurred at log record ID -S_LSN_ The row was not found_ Restore the database from a full backup, or repair the database_

    3417

    21

    		 Cannot recover the master database_ SQL Server is unable to run_ Restore master from a full backup, repair it, or rebuild it_ For more information about how to rebuild the master database, see SQL Server Books Online_

    3431

    21

    		 Could not recover database __*ls- (database ID -d) because of unresolved transaction outcomes_ Microsoft Distributed Transaction Coordinator (MS DTC) transactions were prepared, but MS DTC was unable to determine the resolution_ To resolve, either fix MS DTC, restore from a full backup, or repair the database_

    3443

    21

    		 Database __*ls- (database ID -d) was marked for standby or read-only use, but has been modified_ The RESTORE LOG statement cannot be performed_ Restore the database from a backup_

    3456

    21

    		 Could not redo log record -S_LSN, for transaction ID -S_XID, on page -S_PGID, database __*ls- (database ID -d)_ Page: LSN = -S_LSN, type = -ld_ Log: OpCode = -ld, context -ld, PrevPageLSN: -S_LSN_ Restore from a backup of the database, or repair the database_

    9004

    21

    		 An error occurred while processing the log for database __*ls-_  If possible, restore from backup_ If a backup is not available, it might be necessary to rebuild the log_

  • How to find database restore history from MSDB?

    MSDB database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. Again SQL Server is using MSDB for storing the history of backup, restore, log-shipping details and more…

    You can query the MSDB as below to get the details about the SQL Server restore history.

    SELECT TOP 10 *
FROM restorehistory WITH (nolock)WHERE (destination_database_name = 'Database Name')ORDER BY restore_date DESC
All Databases

SELECT TOP 10 * FROM restorehistory WITH (nolock)ORDER BY restore_date DESC

  • DRBD, Heartbeat and MySQL

    The easiest solution to implement clustering in MySQL is DRBD and Heartbeat.

    DRBD: The Distributed Replicated Block Device (DRBD) is a software-based, shared-nothing, replicated storage solution mirroring the content of block devices (hard disks, partitions, logical volumes etc.) between servers.

    DRBD mirrors data

    • In real time. Replication occurs continuously, while applications modify the data on the device.
    • Transparently. The applications that store their data on the mirrored device are oblivious of the fact that the data is in fact stored on several computers.
    • Synchronously or asynchronously. With synchronous mirroring, a writing application is notified of write completion only after the write has been carried out on both computer systems. Asynchronous mirroring means the writing application is notified of write completion when the write has completed locally, but before the write has propagated to the peer system

    You can download DRDB from below site

    http://www.drbd.org/download/packages/

  • SQL Server Execution Context and Security

    What is Execution Context?

    Whenever User log on or connects to SQL Server, it will create the User Session. Whenever that user executes the statement SQL Server uses that session’s user id, permission, password to execute the query. That session is called execution context.

     

    Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

     

    What is Principals in SQL Server 2005?

    Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

     

    -> Windows level

    -> SQL Server level

    -> Database level 

    Windows-level principals

    • Windows Domain Login
    • Windows Local Login

    SQL Server–level principal

    • SQL Server Login

    Database-level principals

    • Database User
    • Database Role
    • Application Role

    What are SQL Server Securables?

    SQL Server Securables contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

    ·         Server: It includes server roles, logins, etc.

    ·         Database: It includes database users, application roles, database roles, etc.

    ·         Schema: It includes various database objects such as tables, views, procedures, etc.

    The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

     

    What is the use of the Public database role in SQL Server?

    Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable. 

    Explain Certificate based SQL Server Logins/Principals?

    Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

    • ##MS_SQLResourceSigningCertificate##
    • ##MS_SQLReplicationSigningCertificate##
    • ##MS_SQLAuthenticatorCertificate##
    • ##MS_AgentSigningCertificate##
    • ##MS_PolicyEventProcessingLogin##
    • ##MS_PolicySigningCertificate##
    • ##MS_PolicyTsqlExecutionLogin##