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.

Cheers!
Aman Kharbanda

Advertisements

What’s and How’s of Columnstore Indexes

A ColumnStore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data is relevant or not, column store queries need only to search much lesser number of the columns. This means major increases in search speed and hard drive use.
Column oriented storage is the data storage of choice for data warehouse and business analysis applications. It works well for mostly read-only queries that perform analysis on large data sets. Column oriented storage allows for a high data compression rate and as such it can increase processing speed primarily by reducing the IO needs. SQL Server allows for creating column oriented indexes known as ColumnStore Indexes and thus brings the benefits of this highly efficient BI oriented indexes in the same engine that runs the OLTP workload. With SQL2016, a rowstore table can have one updateable nonclustered columnstore index. Previously, the nonclustered columnstore index was read-only. Columnstore supports index defragmentation by removing deleted rows without the need to explicitly rebuild the index.

How to change data in a Non-Clustered Index (Applies to SQL2012/4):
Basically,  once you create a non-clustered columnstore index on a table, you cannot directly modify the data in that table. A query with INSERT, UPDATE, DELETE, or MERGE will fail and return an error message like this:

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

Disabling, rebuilding the index or dropping and then recreating the index is one probable solution but this can be an expensive process especially in mid of our business day. Another workaround here is to create partitioning and make use of staging tables. This will not require you to disable the columnstore indexes and you’ll still be able to update your data.

How to ignore Column Store Indexes if your query performance takes a hit?
There may be some cases when columnstore index is not ideal and needs to be ignored the same. You can use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to ignore the columnstore index. SQL Server Engine will use any other index which is best after ignoring the columnstore index.

Hope this helps!

Happy Learning!
Aman Kharbanda

 

 

 

 

How to backup or restore your SSRS encryption key using command line tools?

SSRS keys can be backed up and restored in 2 easier ways. One, offcourse using the GUI panel of Reporting Configuration Manager and the other one is command line tool by executing rskeymgmt utility.

The rskeymgmt utility can be found in the binn sub-directory of your SQL Server install directory. Opening the command prompt and navigating to this directory, we can run rskeymgmt -? to get a list of  arguments and additionally some example commands.

key managment help

To backup the key, issue this command within cmd prompt: rskeymgmt.exe -e -f D:\TestBackup\SSRSKeybackup -p Yourownpwd -i MSSQLServer

While, to restore, use this command: rskeymgmt.exe -a -f D:\TestBackup\SSRSKeybackup -p Yourownpwd -i MSSQLServer

 

Happy Learning!

Cheers!
Aman Kharbanda

Why is it important to backup SSRS keys?

In the event you restore a SSRS database to a new server, the encryption keys will need to be loaded onto the new server in order to allow that server to read and utilize all of the items noted in the below list.

Otherwise an error will result when attempting to navigate to the Report Server.
Also, your embedded data sources would be unreadable if you add a new key.

Of course you could recreate a SSRS key on the new server and then redeploy all the data sets, data sources, and reports. In that situation though, you would still have to recreate all the folders and more importantly, the security for those folders (and related reports). An easier alternative is the backup and restore the SSRS key. Before digging deep into that, lets understand what gets encrypted within SSRS:

  • Credentials used to connect to the Report Server database itself.
  • The actual symmetric key used by SSRS to encrypt data.
  • Data source credentials which are stored in the database in order to connect to external databases and data sources.
  • The unattended user account information which is used to connect to a remote server in order get external images or data.

 

Happy Learning!

Cheers!
Aman Kharbanda

Listing out connection strings of all SSRS Shared Data Sources

Moving a Report database to an other SQL Server can be tricky at times especially when there are multiple data sources around and which one should be rightly changed afterwards.
With this query for ReportServer database you get the connection string of all Shared Data Sources to document the usage or to search for a specific server/database.

–Listing out connection strings of all SSRS Shared Data Sources
;WITH XMLNAMESPACES  — XML namespace def must be the first in with clause.
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource’
,’http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’
AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
,SDS.[Path]
,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5)     — 5 = Shared Datasource

SELECT CON.[Path]
,CON.SharedDsName
,CON.ConnString
FROM
(SELECT SDS.[Path]
,SDS.SharedDsName
,DSN.value(‘ConnectString[1]’, ‘varchar(150)’) AS ConnString
FROM SDS
CROSS APPLY
SDS.DEF.nodes(‘/DataSourceDefinition’) AS R(DSN)
) AS CON
— Optional filter:
— WHERE CON.ConnString LIKE ‘%Initial Catalog%=%TFS%’
ORDER BY CON.[Path]
,CON.SharedDsName;

 

Happy Learning!

Cheers!
Aman Kharbanda

Roles to be added while moving a Report Server Database

Penning down this today as I recently saw my friends struggling to connect with Reporting server after migrating the report server database.

Reporting Services uses a predefined DB role called RSExecRole to grant report server permissions to the report server DB. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases.

After you create the roles, you can move the report server database to new SQL Server instance.

T-SQL for adding the roles explicitly within the new instance:

use [master]
GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_is_starting] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
use [msdb]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole]
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole]

Happy learning!

Cheers!
Aman Kharbanda

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.

Quite often we see such errors as these while moving the system DB’s across drives or the restore failing for system DB (Model). SQL Server won’t be starting for you at this point in time which could be frustrating. Lifeline for us? The errorlog to indicate what actually is happening at the backend, and this is where you shall be reading the error as subjected.

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.
2016-11-26 06:19:37.23 spid12s     Error: 927, Severity: 14, State: 2.
2016-11-26 06:19:37.23 spid12s     Database ‘model’ cannot be opened. It is in the middle of a restore. 

First things first, start SQL Server in a minimally configured mode-
“sqlservr.exe -sInstanceName /T3608 -m” and once service is started, establish a connection with SQL Server via SQLCMD – “sqlcmd -E -S servername/instancename,port”

“1>” means we are connected to SQL Instance and then execute the below command (hit enter at end of each line)

1>Restore database Model with recovery
2>Go
RESTORE DATABASE successfully processed 0 pages in 0.0410 seconds (0.000MB/Sec)

Kabooom! You’re done. Startup your SQL service and proceed with your tasks.

Happy learning!

Cheers!
Aman Kharbanda