Database fails to generate a Checkpoint

This seems to be one of the many strange errors within SQL Server that you may encounter. You might’ve lately restored this DB from the server where Replication flag would have been enabled – That’s the closest of the scenarios which happened in my case. Although system DB’s were not restored as part of the exercise.

So, here’s what the error message will scare you with:
One or more recovery units belonging to database ‘mydatabase’ 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.

The log scan number (643555:241:0) passed to log scan in database ‘ mydatabase ‘ 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

Couple of options to start with:

  1. Check within the sys.databases table and see if the log_reuse_wait_desc is marked with Replication. If so, then we must work to get rid of it.
    SELECT name, log_reuse_wait_desc FROM sys.databases where log_reuse_wait_desc = ‘Replication’
  2. You may execute this stored procedure to remove replication from this DB if you’re sure that it’s not a replication participating DB.
    EXEC sp_removedbreplication ‘mydatabasename’
  3. After executing #2, if DB entry is still showing up in sys.databases table within log_reuse_wait_desc as ‘Replication’ then try marking the transactions as replicated using this command:
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
  4. After executing #3, if you’re still hitting up this error, then advise would be to install Replication component using the setup.exe and put the DB into publication (Use any dummy table) and then remove replication using the same command as mentioned in #2.

Off course, prior going into this series of steps you’ll have to verify a certain things respective to your environment:

  • Ensure that database integrity is rightly checked and validated.
  • Error log is rightly checked and there are no more errors available there, apart of this one.
  • Server resources are available in plenty.
  • Clean restoration was performed earlier while bringing this DB online.

Hope this helps!

Happy leaning.

Aman Kharbanda

Backup/Restore IO Size

By default, SQL Server will use jumbo sized IOs (typically of 1MB) to perform backup and restore operations. These can adversely affect the IO latency for your array. Whenever possible, use MAXTRANSFERSIZE=65536 on your BACKUP or RESTORE statements. This could make your backups and restore take a tad longer, so test accordingly. If you are leveraging Transaction Log Shipping, you might have no way to tune this, as sqllogship.exe doesn’t offer a means to tune this parameter. If log shipping backups/restores are affecting your performance, considering rolling out scripts to perform log shipping manually.

If you are not achieving good throughput from your backup/restore operations after using the MAXTRANSFERSIZE=65536 parameter, considering bumping up the number of buffers available to the process by using the BUFFERCOUNT parameter. In our testing, a 1.5TB database backed up in 24 minutes, 38 seconds at default settings, and 25 minutes, 6 seconds with 64Kb IO block size and BUFFERCOUNT=2048.

Happy Learning.
Aman Kharbanda




SQL Server Performance Dashboard

Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SSMS. These reports will allow the DBA’s to quickly identify whether there is a current bottleneck on their system and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:

– CPU bottlenecks (and what queries are consuming the most CPU)
– IO bottlenecks (and what queries are performing the most IO)
– Index recommendations generated by the query optimizer (missing indexes)   
– Blocking
– Latch contention

The information captured in the reports is retrieved from the DMV’s. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring the DB server.

Here are the detailed steps of Installation/Configuration of Performance Dashboard-

(a) Installing SQL Server 2012 Performance Dashboard Reports

 The following steps will install the Performance Dashboard Reports-

 1) Open the below link in your web browser and download SQL Server 2012 Performance Dashboard Reports:–pd     
2) Navigate to the location where you saved the SQLServer2012_PerformanceDashboard.MSI file and double-click it. You will receive a welcome screen. Click ‘Next’.
3) The next screen will present you with the license agreement. Read the agreement, select ‘I accept the terms in the license agreement’, and click ‘Next’.
4) The next screen will ask you the required registration information. Enter the details and click ‘Next’.
5) You will be asked to choose the program features for installation. Go with the default selection and click ‘Next’.
6) On the next screen, click ‘Install’ to begin the installation process. You can choose to click ‘Back’ button to make any other changes to your installation settings.
7) Click ‘Finish’ to exit the installation wizard. Now, you have successfully installed SQL Server 2012 Performance Dashboard Reports.

(b) Configuring SQL Server 2012 Performance Dashboard Reports

 After finishing the installation, it is necessary to configure the SQL Server to use the Performance Dashboard toolset you have just installed. The default installation directory for SQL Server 2012 Performance Dashboard Reports is ‘C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\’. Navigate to this location, search for the ‘setup.sql’ file, double-click it to open the Performance Dashboard Reports in SQL Server Management Studio. Next, establish connection to the SQL Server Database Engine instance where you need to install and access the reports.

(c) Using SQL Server 2012 Performance Dashboard Reports

 After you have successfully installed Performance Dashboard Reports, you need to consider the following-

  • You need to ensure that all functions and procedures accessed by the queries in reports must be present in all instances of SQL Server for which you need to carry out performance analysis. You need to search for the ‘setup.sql’ file after navigating to the installation directory and run the script. Close the window after the execution is finished.

Go to Object Explorer in SQL Server Management Studio, right-click on the server instance, and select ‘Reports’ → ‘Custom Reports’. In the ‘Open File’ dialog box, select the ‘performance_dashboard_main’ report file and click ‘Open’. You will be displayed a warning dialog box stating that you are about to run a custom report. Click ‘Run’ in this dialog box to open the ‘performance_dashboard_main.rdl’ file.

Sample reports are pasted here for your further reference.

Happy Learning.

Aman Kharbanda