Reporting Service Error - The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database

---

I ran into one issue while configuration of the reporting service in sql server and got the error to load the reports,

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)

Finally i got the solution to resolve this issue as following,
Go to the Report Service Configuration Option and delete the encrypted content.  Please take a backup of key before deleting it.

Sql Server Trigger

SQL Server Trigger

- A type of stored procedure that run automatically when an event occurs within database server.

3 Type of Trigger:

1) DML: Data Manipulation Language

   - Run on manipulation of data event such as Insert, Update or Delete

2) DDL: Data Definition Language

   - Run on manipulation of data structure or schema event such as CREATE, ALTER and DROP

3) Logon:

   - Run on user Logon event when user session is being established.

   
You can get more detail from Microsoft website, but our site article is meant cover practicle most used code that include tips and tricks to solve some usual problem.
Below is a sample of a DML trigger that does the following:
1) One trigger that handle event for all 3 Insert, Update, Delete
2) Answer common questions such as:
   1)How to distinguish which event is fired from Insert, Update or Delete?
       - You determine that through the two tables provided by trigger result:
           1) Inserted: stores all new record being inserted
           2) Deleted: stores all record being deleted
           NOTE:
               In an update action, SQL server would do 2 actions:
                   1) delete the original row
                   2) inserted a new updated row
               -> Therefore, in an update statement, you will see old record in Deleted table, and updated row in Inserted table
   2) How to know which column is updated?
       -> There are a SQL Server called COLUMNS_UPDATED() which return binary value that represents all columns within a table and tell you which one is updated based on its bit position being (0/1) -> 0: not updated, 1: updated
           Example: I have 5 columns, and my 3rd column is updated and others are not:
               COLUMNS_UPDATED() = 00100 = 4
               To know if 3 column is updated, you mask the binary with 4 and if the result greater than 0, then it's updated.
               (COLUMNS_UPDATED() & 4)>0
                   -> this will be true if 3 columns is updated
       -> The example above is too much work and it's still doesn't give you the column name or anything related to the column so you know right there.
           -> Therefore, we'll make use of a function called sys.fn_IsBitSetInBitmask(), buildin function to get the detail data you want

SQL Server Transaction Log Full Error

Error:

"The
transaction log for database "DatabaseName" is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in Sys.database on
"Source where sql statement is run from the code"

Cause:

SQL
Server trying to log the changes in database to its log file .LDF and the disk
space is out or it hits the limits allowed by SQL Server.

In
Sql Server Management Studio -> Right click on Database -> Select
Properties -> Select Files to find out where the .MDF and .LDF files are
stored

->
Look at the AutoGrouth column

Fix:

Backup
your DB and shrink the log file

USE
YourDatabase;

GO

ALTER
DATABASE YourDatabase SET RECOVERY SIMPLE;

GO

DBCC
SHRINKFILE (YourDatabase_Log, 10); --10MB

GO

ALTER
DATABASE AdventureWorks SET RECOVERY FULL;

GO

Good
Reference Link: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/do-not-truncate-your-ldf-files

Enable Remote Access to SQL Server 2008 R2 on Win 2008 R2

Enable Remote Access to SQL Server 2008 R2 hosted on Window Server 2008 R2

Grant User Access
To allow access to users in a Windows domain
1.Open the SQL Server Management Tool.
2.Log on to the instance of SQL Server. This step presumes that you are the administrator of the computer and the instance of SQL Server.
3.In Object Explorer, expand the Security node.
4.Right-click Logins and click New Login....
5.Type the name of the user to allow by using the domain\username format.
6.Click Search and use the dialog to confirm that the user is a Windows authenticated user. Then close the New Login dialog.
7.In Object Explorer, expand the Databases node.
8.Expand the node of the database that you want to grant access to.
9.Expand the Security node of the database.
10.Right-click the Users node and click New User.
11.In the User name box, type in a name for the user.
12.In the Login name box, type the name of the user by using the domain\username format.
13.In the Role Members list box, select the role that you want to grant the user for the database. Common options are db_dataread and db_datawriter.

Enable a Port
1.On the Start menu, click Run, type WF.msc, and then click OK.
2.In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3.In the Rule Type dialog box, select Port, and then click Next.
4.In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
5.In the Action dialog box, select Allow the connection, and then click Next.
6.In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7.In the Name dialog box, type a name and description for this rule, and then click Finish.

Set a Protocol
This is also a one-time procedure that you perform on the computer that hosts the instance of SQL Server to indicate which protocol to use when communicating with remote clients. This procedure uses the TCP/IP protocol.
To set the protocol
1.Open the SQL Server Configuration Manager application. This is found in the Configuration Tools folder of the Microsoft SQL Server 2008 folder.
2.Expand the SQL Server Network Configuration Manager node.
3.Expand the SQL Server Network Configuration node.
4.Click Protocols for MSSQLSERVER.
5.Right-click TCP/IP and click Enable.

After setting the protocol you must restart the SQL Server service.
To restart the SQL Server service
1.In the SQL Server Configuration Manager application, click the SQL Server Services node.
2.Right-click SQL Server (MSSQLSERVER) and click Restart.

Filegroup Backups

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

view plaincopy to clipboardprint?

1. BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY'   
2. TO DISK = 'c:\Adventureworks.BAK'  

Msg 3004, Level 16, State 1, Line 1

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.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -

view plaincopy to clipboardprint?

1. ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT  
2. GO  
3.   
4. BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY'   
5. TO DISK = 'c:\Adventureworks.BAK'  

Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.

Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.

BACKUP DATABASE...FILE= successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).

Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...

view plaincopy to clipboardprint?

1. DECLARE @backupPath VARCHAR(500)  
2. SET @backupPath = 'd:\sqlbackups\'   
3.   
4. DECLARE @backuptimestamp VARCHAR(30)  
5. SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')  
6.   
7. SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND  
8. FROM sys.filegroups  

view plaincopy to clipboardprint?

1.    

NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

Read more: http://sqlsolace.blogspot.com/search/label/backups#ixzz2EpPK313j

SQLServer 2008R2 SSAS NOT STARTING

  I would request you to follow the below steps incase SQL Server 2008 R2 Analysis Services fails to start again.

1.       Change Logon On for SQL Server 2008 R2 Analysis Services in SQL Server Configuration Manager from NT logon to Local System

2.       Clear the Application log in the event viewer without fail as this is very important.

3.       Restart SQL Server 2008 R2 Analysis Services using SQL Server Configuration Manager

If you are new to SQL Server Configuration Manager then refer the following article Overview of SQL Server Configuration Manager to know more about SQL Server Configuration Manager.

Hope that Helps!

A plan for a backup server for our production server running SQL Server. Should I use log shipping or clustering?

A plan for a backup server for our production server running SQL Server. Should I use log shipping or clustering?

http://www.sql-server-performance.com/faq/cluster_log_shipping_p1.aspx

Most of the production SQLServersI have run across don't have any easy or quick way to failover to a working server should they fail.

Generally, most people hope their production SQL Server never fails.And in the back of their minds, they think they know what to do to recover should the worst happen, but for the most part, they don't have any formal plans, nor have they tested how they actually plan to failover.

In these cases, you can assume that it might take a day or longer for them to get back into production should their SQL Server fail.

If your production SQL Server is not critical to the success of your business, and you can afford to be down a day or two, then not having a formal failover plan is not much of a problem

On the other hand, if your production SQL Server is critical to the success of your organization, then you must have a tested failover plan in place.

Two popular ways to implement a failover plan is to either implement SQL Server log shipping or clustering. Which of these is your best choice?

The question you have to ask yourself is what you mean by best?

If by best, you mean a failover solutionthat fails over automatically and quickly (a minute or so), the SQL Server clusteringis the answer.

If by best you mean a failover solution is that somewhat easy to implement and less expensive, then log shipping is the answer.

Unfortunately, there is no best answer. You have to wade through the pros and cons and decide for yourself what is the best compromise.

Here are the major pros and cons of log shipping vs. clustering.


Pros of Log Shipping
Relatively speaking, less expensive.
Relatively speaking, less work to set up.
Relatively speaking, less on-going maintenance.
Requires less experienced DBAs and networkadministrators.

Cons of Log Shipping

Failover is not quick, may take from 15 - 60 minutes or more of downtime.
Failover is not automatic, it must be manually implemented.
Failed over server must be renamed to the name of the failed server.
Failing back to the original server, once it is fixed, is a manual process that can require 15 - 60 minutes or more of downtime.

Pros of Clustering

Failover is quick, usually resulting in less than minute or two of downtime.
Failover is automatic, no manual intervention is required.
Clustering doesn't require any servers to be renamed.
Failing back is quick, usually resulting in less than minute or two of downtime.

Cons of Clustering

More expensive than log shipping.
Requires more set up than log shipping.
Requires more on-going maintenance.
Requires more experienced DBAs and network administrators.

Troubleshoot Suspect Database Issue

Troubleshoot Suspect Database Issue

Problem

How to troubleshoot suspect database problem? How to bring it back online? What are the do you need to perform once the database is online?

Solution

Step 1: Bring the database online using below script
USE Master
GO

– Determine the original database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO

– Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 24
WHERE [Name] = ‘SuspectedDatabaseName’
GO

– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO

– Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

Step 2: Check for database corruption. This is very important step please execute it.

DBCC CHECKDB – Validate the overall database integrity
DBCC CHECKCATALOG – Validate the system catalog integrity
DBCC CHECKTABLE – Validate the integrity for a single table

Step 3: To resolve the corruption issue, please execute below commands

Drop and Recreate Index(es)
Move the recoverable data from an existing table to a new table

Update statistics
DBCC UPDATEUSAGE
sp_recompile

Step 4: Repeat Step 2 to validate all the corruption occurred

How to take database out of emergency mode?

How to take database out of emergency mode?

IN SQL Server 2005/2008

ALTER DATABASE sqldbpool
SET online

How to Check When SQL Server was installed?

How to Check When SQL Server was installed?

NT AUTHORITY\SYSTEM login gets created when we install SQL Server.

By querying sys.syslogins, we would get an idea of the SQL Server Installation date time.

SELECT createdate AS sql_server_install_date
FROM sys.syslogins
WHERE sid = 0×010100000000000512000000

SELECT * FROM
sys.syslogins
WHERE sid = 0×010100000000000512000000

How to change SQL Server Instance Name?

How to change SQL Server Instance Name?

First collect the output of the current instance configuration

sp_helpserver
select @@servername

Execute the below query to change the instance name:

sp_dropserver ‘old_name’
go
sp_addserver ‘new_name’,‘local’
go

Verify sql server instance configuration by running below queries:

sp_helpserver
select @@servername

Configuring Log Shipping between SQL Server 2005 & 2008

Configuring Log Shipping between SQL Server 2005 & 2008

In the step below you need to restore the database either in Norecovery \ standby mode only.

If you restore the database in recovery then consecutive log backup cannot be restored.
Restore with Recovery

Try the below steps.

Take a full backup of the database in the primary server (SQL 2k5)
Copy the backup to the secondary server (SQL 2k8)
Restore the database with Norecovery (SQL 2k8)
Take a tail backup of the database in the primary server(SQL 2k5), this could be small in size
Copy and restore the tlog backup in the secondary server with either NORECOVERY \ STANDBYMODE(SQL 2k8)
Configure log shipping between primary (SQL 2k5) to secondary server (SQL 2k8) and make sure its working fine.
Once log shipping is in place, move all the logins from primary (SQL 2k5) server to secondary server (SQL2k8), this step ensures that the ID used in application exists in the secondary server with same SID & password. You can either use "Transfer login SSIS task" or "this KB article"
Now logins are all created in the secondary server with the same SID, now we can start failover.
Shutdown the application \ services etc
Disable all log shipping jobs both in primary and secondary servers
Take a tlog backup and make the database in readonly (so that no further transactions is happened)mode in the primary server (SQL 2k5)
Copy and restore the tlog backup in the secondary server (SQL 2k8) with RECOVERY (so that appliation can make changes to the db)
Change the connection string \ ODBC entry etc to point the application to the secondary server (SQL 2k8) and make sure its working fine.
Once the application started working and updates will be sent to the secondary server (SQL2k8), you can start inplace upgrade of your primary server to SQL server 2008.
Check these steps and let me know if it works!

How many databases can be mirrored on a single instance of Microsoft SQL Server?

How many databases can be mirrored on a single instance of Microsoft SQL Server?

It is frequently asked by the DBAs or SystemAdmins or Customer that how many databases can be mirrored on a single instance of Microsoft SQL Server?

Answer of the above question is you can configure 10 databases for 32-bit operating system.

On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

For 64-Bit Operating system you can mirror more than 10 databases depending on the number of processors and worker threads.

Many companies has deployed more that 10 Databases as mirrored.

Setup a low disk space alert for SQL Server drives

Setup a low disk space alert for SQL Server drives

This is the pretty easy and clean script that you may use to check periodically your database server drive space.

You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if any of drive has less than 15% or 20% threshold value.

-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))

-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives

-- Declare variables
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT

-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from ##space where tspace is null)>0

begin
select top 1 @dletter = dletter + ':\',@fspace = fspace from ##space where tspace is null
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
UPDATE ##space SET tspace = CAST(@drsize AS BIGINT) WHERE lower(dletter) + ':\' = lower(@dletter)

EXEC master.dbo.sp_OADestroy @oDrive
end

EXEC master.dbo.sp_OADestroy @oFSO

update ##space set percentfree = fspace/((tspace/1024.0)/1024.0)*100

-- Select your data

select [Drive] = dletter ,
[Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
[Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
[% Free]= percentfree
from ##space

-- Drop temporary table

drop table ##space

Find the database restore history

Find the database restore history

When ever we restored database from the backup file the sql server stored information on msdb database table restorehistory.

SELECT restore_date ,destination_database_name
FROM msdb..RestoreHistory 
ORDER BY restore_date DESC

How to find SQL Server information

How to find SQL Server information

Run the extended procedure to get sql server internal information
xp_msver

we can use parameter to get specific information
xp_msver 'ProductName', 'ProductVersion', 'Language', 'Platform', 'WindowsVersion', 'PhysicalMemory', 'ProcessorCount'

Where is the Surface Area Configuration tool in SQL Server 2008

Where is the Surface Area Configuration tool in SQL Server 2008

Problem

Suppose we have just installed a new instance of SQL Server 2008 and can't find the Surface Area Configuration tool that we used to use in SQL Server 2005.

How can we manage the Database Engine features in SQL Server 2008?

Solution

Microsoft has removed the Surface Area Configuration tool that was shipped in SQL Server 2005.

The Services and Connections that were once managed in Surface Area Configuration tool should now be managed using the SQL Server Configuration Manager.

So how do we manage the Database Engine features?

We can now manage the Surface Area Configuration for the Database Engine using Policy Based Management.

A new Surface Area Configuration Facet in SQL Server Management Studio exposes the properties necessary to make required changes.

First, right-click on the instance name that you would like to configure in SQL Server Management Studio and select "Facets" from the context menu as shown below.

This will display the View Facets dialog box that will provide us with a drop down of all the available facets that can be configured for the instance.

Select "Surface Area Configuration" from the Facet drop down to display the properties exposed by that facet.

Make the appropriate changes and select OK to apply them to the server.

One interesting feature is the ability to make the appropriate changes for our organization and export them to create a policy using the "Export Current State as Policy..." button.

This allows us to configure this once and use this policy throughout your enterprise.

TSQL Query to find the DB Maintenance status

TSQL Query to find the DB Maintenance status

Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?

Here is the answer!

The query listed below can be used to find the Percentage of work completed for the following commands:

1. ALTER INDEX REORGANIZE
2. AUTO_SHRINK option with ALTER DATABASE
3. BACKUP DATABASE
4. DBCC CHECKDB
5. DBCC CHECKFILEGROUP
6. DBCC CHECKTABLE
7. DBCC INDEXDEFRAG
8. DBCC SHRINKDATABASE
9. DBCC SHRINKFILE
10. RECOVERY
11. RESTORE DATABASE,
12. ROLLBACK
13. TDE ENCRYPTION

We have to replace the where condition filter based on the requirement.

SELECT command,
            sqltext.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests Req
CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) sqltext
WHERE req.command in ('DBCC CHECKDB')

On SQL Server Management Studio, How to list only the database, which user has access ?

On SQL Server Management Studio, How to list only the database, which user has access ?

The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.

The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.

Hence by default the SSMS will show all the databases on SSMS.

If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,

DENY VIEW ANY DATABASE 
TO login_name

When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.

Setting up an OLE DB connection to a SQL Server database

Setting up an OLE DB connection to a SQL Server database

SQL SERVER ASP/OLE DB connections not only offer greater performance than ODBC (DSN) , but also allow some additional functions and have the benefit that you do not need to wait for your web hosting provider to set up a DSN on your server.

First create a new text file, by default windows will name it New Text Document.txt, rename the file NewConnection.udl, or whatever you wish, the important thing is to change the file extension to .udl (Universal Data Link). Windows will give you a warning message about renaming file extensions, click Yes to proceed.

Double click the newly created .udl file, this will open the Data Link Properties box shown below:



Select Microsoft OLE DB Provider for SQL Server from the list shown and click the Next>> button.

To connect to a SQL Server database on your local PC, select or type in the server name in the text box number 1.
Next click the relevant radio button and select whether you wish to use Windows NT integrated security or a specific user name and password. Finally select the database on the SQL Server to which you wish to connect.



Click the Test Connection button to confirm that everything is working correctly and click OK. Now right click the .udl file and select Open with Notepad.

If you selected Windows NT Integrated security you should see something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDB;_
       Data Source=YOUR_LOCAL_SERVER_NAME

If you selected Use a specific user name and password, you will see something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourUserID;_
       Initial Catalog=YourDB;Data Source=YOUR_LOCAL_SERVER_NAME

You can now copy and paste the connection string wherever it's required, for example:

<%
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;_
    PersistSecurity Info=False;Initial Catalog=_
    YourDB;_Data Source=YOUR_LOCAL_SERVER_NAME
strInsertCommand = "INSERT INTO tableName(TextField, NumericField)_
    VALUES('ABCDE', 12345);"
commInsert.Execute(strInsertCommand) ' Execute the insert command
commInsert.Close()
Set commInsert = Nothing
%>


Connecting to the remote database

Connecting to a remote SQL Server database is almost identical to connecting to a local database.

Create a .udl (Universal Data Link) file as in the first section, select Microsoft OLE DB Provider for SQL Server from the list and click Next, as before.

The details required to make the connection should be given to you by the host provider or your server administrator. Type in the server name in the text box, number 1, this may be an IP address as shown or a domain address like http//YourServer.com. Next enter the user name and password supplied by the host provider or administrator. Finally select your database on the SQL Server to which you wish to connect.



Open the .udl file with notepad, the connection string will appear similar to this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourUserID;_
                          Initial Catalog=YourDB;Data Source=YOUR_REMOTE_SERVER_NAME

CHECKPOINT in SQL Server

CHECKPOINT in SQL Server

 What is CHECKPOINT?

As per BOL definition “ Writes all dirty pages for the current database to disk.
Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.
Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk”
This minimizes the active portion of the log that must be processed during a full recovery of a database.

What are the Events that causes CHECKPOINT?

1. Before Database Backup
Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

2. Active Log exceeds recovery interval
The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

3. The log becomes 70 percent full, and the database is in log-truncate mode.
A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

• BULK_LOG Recovery: A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.
• An ALTER DATABASE statement is executed that adds or deletes a file in the database

4. Stopping a SQL Server issues a checkpoint in each database on the server

5. CHECKPOINT: A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.

6. Database Shutdown An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

How to find who dropped the database? Is that logged on SQL Server?

How to find who dropped the database? Is that logged on SQL Server?

Are you one looking to find answer for questions like below.,
· How to find who dropped a database?
· How to find who created the database?
· How to find who altered the database?
· How to find who altered the database configurations?
· How to find who dropped the schema?
· How to find who altered the schema?
· How to find who altered the server configuration?
· How to find who modified the login?
· How to find who modified the table?
If it is then,  are you wondering is that possible on SQL Server?
Yes, SQL Server can beginning from version SQL Server 2005 onwards.

That is where SQL Server Default trace come into picture.

What is SQL Server Default Trace?
Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.

To get the default trace options
 

SELECT * FROM ::fn_trace_getinfo(default)

 
To know what is audited on default trace
SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description

  FROM ::fn_trace_geteventinfo(1) Trc

    JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

    JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id




Example:
 
Below is the screenshot  “Schema Changes History” of Standard Reports at Server Level.

See that “it shows the event of Drop and Alter of the database

 

The same can be queried from SQL Server Default Trace like;

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

 
Usage

Default trace can be queried to find answers on

1. To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
2. To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
3. To find Login creation , modifications and failures
4. To find performance issues like missing of statistics, join predicate, hash or sort warnings
5. Database file growth or shrinkage
6. DBCC operations

 
Points to ponder

1. Default trace is a Server Side trace and it is enable by default.
2. Default trace properties can not be modified, other than enabling and disabling of this trace.
3. Default trace by default enabled and stored on the “Log” folder
   
   
4. Default trace file will be created newly on start of the SQL Server service.
5. A single trace file can store  up to 20 MB, when it is full new file will be created.
6. SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
7. Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.

SQL Server 2005

	Patch Level	PSS Only	Link	Build	Version
	2005 SP4 + Q2494123		GO	5,292	9.00.5292
	2005 + SP4 Cumulative Update 3		GO	5,266	9.00.5266
	2005 + SP4 Cumulative Update 1		GO	5,254	9.00.5254
	2005 SP4 + KB2494120		GO	5,057	9.00.5057
	2005 + SP4 RTM		GO	5,000	9.00.5000
	2005 + SP4 CTP		GO	4,912	9.00.4912
	2005 SP3 + Q2494112		GO	4,340	9.00.4340
	2005 SP3+Q2438344 (Cumulative HF15)		GO	4,325	9.00.4325
	2005 SP3+Q2438344 (Cumulative HF13)		GO	4,315	9.00.4315
	2005 SP3+Q2345449 (Cumulative HF12)		GO	4,311	9.00.4311
	2005 SP3+Q2258854 (Cumulative HF11)		GO	4,309	9.00.4309
	2005 SP3+Q983329 (Cumulative HF10)		GO	4,305	9.00.4305
	2005 SP3+Q980176 (Cumulative HF9)		GO	4,294	9.00.4294
	2005 SP3+Q978915 (Cumulative HF8)		GO	4,285	9.00.4285
	2005 SP3+Q978791		GO	4,278	9.00.4278
	2005 SP3+Q976951 (Cumulative HF7)		GO	4,273	9.00.4273
	2005 SP3+Q974648 (Cumulative HF6)		GO	4,266	9.00.4266
	2005 SP3+Q972511 (Cumulative HF5)		GO	4,230	9.00.4230
	2005 SP3+Q970279 (Cumulative HF4)		GO	4,226	9.00.4226
	2005 + Q971409		GO	4,224	9.00.4224
	2005 SP3+Q967909 (Cumulative HF3)		GO	4,220	9.00.4220
	2005 SP3+Q967101		GO	4,216	9.00.4216
	2005 SP3+Q961930 (Cumulative HF2)		GO	4,211	9.00.4211
	2005 SP3+Q959195 (Cumulative HF1)		GO	4,207	9.00.4207
	2005+SP3 (Q970892)		GO	4,053	9.00.4053
	2005+SP3 (Q955706)		GO	4,035	9.00.4035
	2005 SP2 + Cumulative Update 17		GO	3,356	9.00.3356
	2005 SP2+Q216793 (Cumulative HF16)		GO	3,355	9.00.3355
	2005 SP2+Q972510 (Cumulative HF15)		GO	3,330	9.00.3330
	2005 SP2+Q970278 (Cumulative HF14)		GO	3,328	9.00.3328
	2005 SP2+Q948567 / 961648		GO	3,327	9.00.3327
	2005 SP2+Q967908 (Cumulative HF 13)		GO	3,325	9.00.3325
	2005 SP2+Q969142		GO	3,320	9.00.3320
	2005 SP2+Q967199		GO	3,318	9.00.3318
	2005 SP2+Q962970 (Cumulative HF12, available viarequest.)		GO	3,315	9.00.3315
	2005 SP2+Q960090		GO	3,310	9.00.3310
	2005 SP2+Q962209		GO	3,303	9.00.3303
	2005 SP2+Q961479 / 961648		GO	3,302	9.00.3302
	2005 SP2+Q958735 (Cumulative HF11, avail. viarequest.)		GO	3,301	9.00.3301
	2005 SP2+Q959132		GO	3,295	9.00.3295
	2005 SP2+Q956854 (Cumulative HF10, avail. viarequest.)		GO	3,294	9.00.3294
	2005 SP2+Q956889		GO	3,291	9.00.3291
	2005 SP2+Q937137		GO	3,289	9.00.3289
	2005 SP2+Q953752 / 953607 (Cumulative HF9, avail. via request or by clicking here.)		GO	3,282	9.00.3282
	2005 SP2+Q955754		GO	3,261	9.00.3261
	2005 SP2+Q954950		GO	3,260	9.00.3260
	2005 SP2+Q954669 / 954831		GO	3,259	9.00.3259
	2005 SP2+Q951217 (Cumulative HF8, avail. viarequest.)		GO	3,257	9.00.3257
	2005 SP2+Q954054		GO	3,253	9.00.3253
	2005 SP2+Q952330		GO	3,244	9.00.3244
	2005 SP2+Q951190		GO	3,242	9.00.3242
	2005 SP2+Q951204		GO	3,240	9.00.3240
	2005 SP2+Q949095 (Cumulative HF7, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,239	9.00.3239
	2005 SP2+Q950189		GO	3,235	9.00.3235
	2005 (QFE) SP2+Q941203 / 948108		GO	3,233	9.00.3233
	2005 SP2+Q949959		GO	3,232	9.00.3232
	2005 SP2+Q949687/949595		GO	3,231	9.00.3231
	2005 SP2+Q949199		GO	3,230	9.00.3230
	2005 SP2+Q946608 (Cumulative HF6, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,228	9.00.3228
	2005 SP2+Q947463		GO	3,224	9.00.3224
	2005 SP2+Q945640 / 945641 / 947196 / 947197		GO	3,222	9.00.3222
	2005 SP2+Q942908 / 945442 / 945443 / 945916 /944358		GO	3,221	9.00.3221
	2005 SP2+Q941450 (Cumulative HF5, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,215	9.00.3215
	2005 SP2 (KB N/A, SQLHF Bug #50002118)		N/A	3,209	9.00.3209
	2005 SP2+Q944902		GO	3,208	9.00.3208
	2005 SP2+Q944677		GO	3,206	9.00.3206
	2005 SP2 (KB N/A, SQLHF Bug #50001708/50001999)		N/A	3,205	9.00.3205
	2005 SP2 (KB N/A, SQLHF Bug #50001951/50001993/50001997/50001998/50002000)		N/A	3,203	9.00.3203
	2005 SP2+Q941450 (Cumulative HF4, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,200	9.00.3200
	2005 SP2 (KB N/A, SQLHF Bug #50001812)		N/A	3,195	9.00.3195
	2005 SP2+Q940933		GO	3,194	9.00.3194
	2005 SP2+Q939562 (Cumulative HF3, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,186	9.00.3186
	2005 SP2+Q940128		GO	3,182	9.00.3182
	2005 SP2+Q939942		GO	3,180	9.00.3180
	2005 SP2+Q938243		GO	3,179	9.00.3179
	2005 SP2 (KB N/A, SQLHF Bug #50001193/5001352)		N/A	3,178	9.00.3178
	2005 SP2+Q939563 / 939285		GO	3,177	9.00.3177
	2005 SP2+Q936305 /938825 (Cumulative HF2, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,175	9.00.3175
	2005 SP2+Q937745		GO	3,171	9.00.3171
	2005 SP2+Q937041/937033		GO	3,169	9.00.3169
	2005 SP2+Q936185 / 934734		GO	3,166	9.00.3166
	2005 SP2+Q932610/935360/935922		GO	3,162	9.00.3162
	2005 SP2+Q935356/933724(Cumulative HF1, avail. via PSS only - must supply KBID of issue to resolve in your request)		GO	3,161	9.00.3161
	2005 SP2+Q934459		GO	3,159	9.00.3159
	2005 SP2+Q934226		GO	3,156	9.00.3156
	2005 SP2+Q933549 /933766/933808/933724/932115/933499		GO	3,155	9.00.3155
	2005 SP2+Q934106 / 934109 / 934188		GO	3,154	9.00.3154
	2005 SP2+Q933564		GO	3,153	9.00.3153
	2005 SP2+Q933097 (Cumulative HF1)		GO	3,152	9.00.3152
	2005 SP2+Q970895		GO	3,080	9.00.3080
	2005 SP2+Q960089		GO	3,077	9.00.3077
	2005 SP2+Q954606 (GDR)		GO	3,073	9.00.3073
	2005 (GDR) SP2+Q941203 / 948109		GO	3,068	9.00.3068
	2005 SP2+Q934458		GO	3,054	9.00.3054
	2005 SP2+Q933508		GO	3,050	9.00.3050
	2005 SP2+Q933508 (use this if SP2 was applied prior to 3/8)		GO	3,043	9.00.3043
	2005 'Fixed' SP2 (use this if SP2 was NOT applied yet - orig. RTM removed)		GO	3,042	9.00.3042
	2005 SP2 CTP (December) - Fix List		GO	3,033	9.00.3033
	2005 SP2 CTP (November)		GO	3,027	9.00.3027
	2005 SP1+Q929376		GO	3,026	9.00.3026
	2005 SP1+Q948344		GO	2,249	9.00.2249
	2005 SP1+Q933573		GO	2,245	9.00.2245
	2005 SP1+Q944968		GO	2,243	9.00.2243
	2005 SP1+Q943389/943388		GO	2,242	9.00.2242
	2005 SP1+Q940961		GO	2,239	9.00.2239
	2005 SP1+Q940719		GO	2,237	9.00.2237
	2005 SP1+Q940287 / 940286		GO	2,236	9.00.2236
	2005 SP1+Q937343		GO	2,234	9.00.2234
	2005 SP1+Q933499/937545		GO	2,233	9.00.2233
	2005 SP1+Q937277		GO	2,232	9.00.2232
	2005 SP1+Q934812		GO	2,231	9.00.2231
	2005 SP1+Q936179		GO	2,230	9.00.2230
	2005 SP1+Q935446		GO	2,229	9.00.2229
	2005 SP1+Q934066/933265		GO	2,227	9.00.2227
	2005 SP1+Q933762/934065934065		GO	2,226	9.00.2226
	2005 SP1+Q932990 / 933519		GO	2,224	9.00.2224
	2005 SP1+Q932393		GO	2,223	9.00.2223
	2005 SP1+Q931593		GO	2,221	9.00.2221
	2005 SP1+Q931329 / 932115		GO	2,219	9.00.2219
	2005 SP1+Q931843 / 931843		GO	2,218	9.00.2218
	2005 SP1+Q931821		GO	2,216	9.00.2216
	2005 SP1+Q931666		GO	2,215	9.00.2215
	2005 SP1+Q929240 / 930505 / 930775		GO	2,214	9.00.2214
	2005 SP1+Q930283 / 930284		GO	2,211	9.00.2211
	2005 SP1+Q929278		GO	2,209	9.00.2209
	2005 SP1+Q929179 / 929404		GO	2,208	9.00.2208
	2005 SP1+Q928394 / 928372 / 928789		GO	2,207	9.00.2207
	2005 SP1+Q928539 / 928083 / 928537		GO	2,206	9.00.2206
	2005 SP1+Q927643		GO	2,202	9.00.2202
	2005 SP1+Q927289		GO	2,201	9.00.2201
	2005 SP1+Q926773 / 926611 / 924808 / 925277 /926612 / 924807 / 924686		GO	2,198	9.00.2198
	2005 SP1+Q926285/926335/926024		GO	2,196	9.00.2196
	2005 SP1+Q926240		GO	2,195	9.00.2195
	2005 SP1+Q925744		GO	2,194	9.00.2194
	2005 SP1+Q924954/925335		GO	2,192	9.00.2192
	2005 SP1+Q925135		GO	2,191	9.00.2191
	2005 SP1+Q925227		GO	2,190	9.00.2190
	2005 SP1+Q925153		GO	2,189	9.00.2189
	2005 SP1+Q923849		GO	2,187	9.00.2187
	2005 SP1+Q929404 / 924291		GO	2,183	9.00.2183
	2005 SP1+Q923624/923605		GO	2,181	9.00.2181
	2005 SP1+Q923296 / 922594		GO	2,176	9.00.2176
	2005 SP1+Q922578 /922438 / 921536 / 922579 /920794		GO	2,175	9.00.2175
	2005 SP1+Q922063		GO	2,174	9.00.2174
	2005 SP1+Q920974/921295		GO	2,167	9.00.2167
	2005 SP1+Q919636 / 918832/919775		GO	2,164	9.00.2164
	2005 SP1+Q919611		GO	2,156	9.00.2156
	2005 SP1+builds 1531-40 (See Q919224 before applying!)		GO	2,153	9.00.2153
	2005 SP1+.NET Vulnerability fix		N/A	2,050	9.00.2050
	2005 SP1 RTM		GO	2,047	9.00.2047
	2005 SP1 CTP		GO	2,040	9.00.2040
	SP1 Beta		N/A	2,029	9.00.2029
	2005 RTM+Q932556		GO	1,561	9.00.1561
	2005 RTM+Q926493		GO	1,558	9.00.1558
	2005 RTM+Q926292		GO	1,554	9.00.1554
	2005 RTM+Q922804		GO	1,551	9.00.1551
	2005 RTM+Q917887/921106		GO	1,550	9.00.1550
	2005 RTM+Q918276		GO	1,547	9.00.1547
	2005 RTM+Q917905/919193		GO	1,545	9.00.1545
	2005 RTM+Q917888/917971		GO	1,541	9.00.1541
	2005 RTM+Q917738		GO	1,539	9.00.1539
	2005 RTM+Q917824		GO	1,538	9.00.1538
	2005 RTM+Q917016		GO	1,536	9.00.1536
	2005 RTM+Q916706		GO	1,534	9.00.1534
	2005 RTM+Q916086		GO	1,533	9.00.1533
	2005 RTM+Q916046		GO	1,532	9.00.1532
	2005 RTM+Q915918		GO	1,531	9.00.1531
	2005 RTM+Q915112 / 915306 / 915307/ 915308		GO	1,528	9.00.1528
	2005 RTM+Q913494		GO	1,519	9.00.1519
	2005 RTM+Q912472/913371/913941		GO	1,518	9.00.1518
	2005 RTM+Q912471		GO	1,514	9.00.1514
	2005 RTM+Q911662		GO	1,503	9.00.1503
	2005 RTM+Q915793		GO	1,502	9.00.1502
	2005 RTM+Q910416		GO	1,500	9.00.1500
	2005 RTM+Q932557		GO	1,406	9.00.1406
	2005 RTM			1,399	9.00.1399
	September CTP Release		N/A	1,314	9.00.1314
	June CTP Release		N/A	1,187	9.00.1187
	April CTP Release		N/A	1,116	9.00.1116
	March CTP Release (may list as Feb.)		N/A	1,090	9.00.1090
	December CTP Release		N/A	981	9.00.981
	October CTP Release		N/A	951	9.00.951
	Internal build (?)		N/A	917	9.00.917
	Beta 2		N/A	852	9.00.852
	Internal build (?)		N/A	849	9.00.849
	Internal build (?)		N/A	844	9.00.844
	Express Ed. Tech Preview		N/A	836	9.00.836
	Internal build (IDW4)		N/A	823	9.00.823
	Internal build (IDW3)		N/A	790	9.00.790
	Internal build (IDW2)		N/A	767	9.00.767
	Internal build (IDW)		N/A	747	9.00.747
	MS Internal (?)		N/A	645	9.00.645
	Beta 1