Wednesday, March 25, 2015

Error: 3041, Severity: 16, State: 1 ;Backup detected log corruption in database DatabaseName. Context is FirstSector

There is a Transaction log backup scheduled on my production server. On one bad day the Transaction log backup sql agent job throwing error and the error was like below. And I have found this error in the ERROR path(Eg:C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG)
Error: 3041, Severity: 16, State: 1   
Backup detected log corruption in database DatabaseName. Context is FirstSector. LogFile: 2 'E:\MSSQL\DATA\DatabaseName.ldf' VLF SeqNo: x541af1 VLFBase: x42e20000 LogBlockOffset: x43785c00 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x6320656c LogBlock.StartLsn.
Blk: x6f6e6e61 Size: x656e

And the steps I have done to fix this issue is:
However Transaction log backup is not working.
1) Take the database full backup
2) Change the Data base recovery model from FULL to Simple(breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up)
4) Again change the recovery model from Simple to FULL
5) Again take FULL Backup in order to successful backups of Transaction logs

After doing above steps my transaction log backup started working successfully.

Tuesday, February 17, 2015

How to find which session is causing lock

By running the below query we can find that which query is causing to the locking.

SELECT lok.resource_type






















FROM sys.dm_tran_locks lok

JOIN sys.dm_os_waiting_tasks wat

ON lok.lock_owner_address = wat.resource_address


Thursday, February 12, 2015

How to change the server name of SQL Server:

If you are trying to change the name of the server in a production environment you need to look at the below steps. Please check whether Replication, Log shipping, Mirroring is installed. If that is the case, you should be cautious before you are running this script. You need to disable all these before you are going to run the below command. And also ensure that you have a backup of all the databases available. And follow the below steps.

If you are trying to change the "Default Instance" you can run the below command.


Wednesday, February 4, 2015


Here Demo is a database name ,that I created in my local instance.
This value will show you the old value if you are
trying to update a value with BEGIN TRAN and you are trying to see the result in other session.
While doing this ensure thatno other seession is opened on the database.Kill other database sessions
before you are trying to change the database isolation. This value will show you the old value if you are
trying to update a value with BEGIN TRAN and you are trying to see the result in other session.If this isolation
SET READ_COMMITED_SNAPSHOT ON you can use WITH(Tablock) table lock on the table. Until you update this table,you
can not update another value in the same table.READ COMMITTED means internally it si READ_COMMITTED_SNAPSHOT_ON/OFF.
SET READ_COMMITTED_SNAPSHOT ON --(/*2005 on wards*/)
SET Val3='Ramesh'
This will show you the old value till BEGIN TRAN completes with COMMIT OR rollback.
As per BOL:Sepcifies that statements can not read data that has been modified but not yet committed by other transactions
and that no other transaction can not modify data that has been read by the current transaction completes.
USE Demo
 Statements can not read the data that has been modifed but not yet committed by other transaction
 No other transaction can modify data that has been read by the current transactions until current transaction completes
 Other transactions can not insert rows with key values that would fail in the range of keys
 read by any statements in the current transaction untill current transaction completes
USE Demo
  Session 1
SET Val3='Ramesh'
  Session 2
  This will not complete till first session completes on ISOLATION LEVEL SERIALIZABLE
SET Val3='Madhu'

Tuesday, November 25, 2014

CTRL+R not working in SQL Server 2012 and 2014 Management tool

Please follow the below instructions.

Select "Tools", "Customize..."
- Click "Keyboard..."
- In the list window, scroll down and select "Window.ShowResultsPane"
- Under "Use new shortcut in:", select "SQL Query Editor"
- Place your cursor in the "Press shortcut keys:" input area and press Ctrl+R
- Click "Assign", then "OK"


Friday, November 21, 2014

SQL Data page and Extent.

Data Page:  The size of the data page is 8KB. Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page. And each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

Extent:  Extents are the basic unit in which space is managed. An extent is eight contiguous pages, or 64 KB. This means SQL Server database have 16 extents per megabyte (1MB). To make its space allocation efficient SQL Server does not allocate whole extents to table with small amounts of data. SQL Server has two types of events.
  1 Page->8KB
  8 Pages->8*8=64KB(One Extent)


Friday, September 12, 2014

RESTORATION OF ReportServer and ReportServerTempdb

I just want to share one of my experiences as DBA. One day my boss gave me a task of restoration of ‘Reportserver’ and ‘ReportServerTempdb’ databases. This is in SQL Server 2008R2. What I did is I tried to restore the database and I got an error saying that “The database is already in Use”. So I thought there might be other sessions are open on this database. So I ran sp_who2 stored procedure and kill all  the sessions which are connecting to ‘Reportserver’ database and try to restore the database as usual. But this time also I faced the same problem.

So I thought this time I will take the database in single user mode and try to restore the same. I failed in this attempt also. What I realized after some time was “Reporting Services” are running and this is stopping me to restore the database. I stopped this “Reporting Service” and restored the two databases with in one minute

After few days I got an error on "ReportServerTempdb" database the error details are as below.
"An error occurred with in the report server database. This may be due to a connection failure,timeout or lowdisk condition with in the database(rsReportServerDatabaseError) the transaction log for database "ReportServerTempdb" is full.To find out why space in the log can not be reused,see the log_reuse_wait_desc columns sys.database.

In order to troubleshoot the above issue I followed the below instructions. Before going into details let me explain about the "ReportserverTempdb" database.

Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server.Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.
If you back up the temporary database and subsequently restore it, you should delete the contents. Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents.
If you delete the temporary database, you can create a new database, and then run the Catalogtempdb.sql script to add the table structure. The temporary database must have the same root name as the primary report server database. And the Catalogtempdb .sql script available in the below path
 \Program Files\Microsoft SQL Server\MSRS10. < instancename > \Reporting Services\ReportServer folder.
Now I can explain about the troubleshoosing stpes. First I ran the below script

Please find the results

And I also found that the Recovery model of a ReportServerTempdb is showing in 'FULL'. But this is not necessary and I changed this to 'SIMPLE'. After changing that to Simple log has been reduced to

Now the reports are available for access.