Dirty Page, Checkpoint, Commit Tran

A dirty page is a page that has not yet been written to the disk. You can (and most often will) have many pages that are different in memory as opposed to the copy on disk. They are called dirty, because the application has "smudged" them with new data. Once they are written to disk and both the disk copy and the memory copy agree, then it is no longer dirty.




As for the Buffer cache, I thought that the procedure cache was separate from the buffer cache. At least in 6.5, it was the same pool, but different "sides". You had to define how much of your memory allocation was for the proc cache. SQL Server defaulted to 30%. Anyone who came into this business after SQL 7.0 doesn't remember a bit of that trivia ;-). Generally, I think of the buffer cahce as containing any data buffer, so that would be log as well as data. The log contains "data" of it's own. Usually page numbers, transaction id's, etc..



Checkpoints have to happen in full and simple modes. Otherwise, no one's database would ever come back from a reboot. Checkpoints are done asynchronously by one of the background processes (spids 1-15 or so). This is to keep the I/O of writing of data from interfering with your reading in other pages from disk. Checkpoints can be triggered by a number of things ranging from the manual issuing of the checkpoint command to a need for free buffers caused by a huge table scan. Oddly enough, a shutdown does not give a checkpoint. If you have long re-start times, you may want to consider issuing a checkpoint before a shutdown (assuming it is a planned shutdown).



A commit transaction will mark your transaction as "completed" in the trasnaction log. Remember: All data is written first to the transaction log, then to the data files. All data is updated in memory first, as it is faster, but the hope is that data makes it to disk eventually. If something does not make it into your transaction log, it has no business on your data file. On recovery (such as after reboot), SQL Server goes through the transaction log to make sure that the current copy of the data is consistent. If it is not consistent, then it is suspect.

SQL SERVER 2012 EDITIONS

SQL Server Enterprise edition

SQL Server BI edition (Business Intelligence edition)

SQL Server Standard edition

* The Web, Developer and Express editions will also be available in the SQL Server 2012 release.

 *Image Deleted...Shortly Uploading

 

Reboot from CMD

shutdown -r -t 00 -f /m \\computer_name or IP

Error 229: Execute Permission Denied on Object 'xp_availablemedia' Owner 'dbo'

To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.

More on http://support.microsoft.com/kb/323249

Cannot show requested dialog. (SqlMgmt) OR Cannot show requested dialog.

This property may not exist for this object, or may not be retrievable due to insufficient access rights

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somevalidlogin]

Invalid Name Space SSRS 2005

Path for MOF File

C:\Program Files\Microsoft SQL Server\90\Shared

Change Name of RS Instance name,

1. Go to above mentioned location

2. Open the MOF file in Notepad

3. Search below code and replace string to SQL Server NAME.
#pragma namespace ("\\\\.\\root\\Microsoft\\SQLSERVER")
instance of __Namespace
{
    Name = "MSSQLSERVER";
};

4.Save the file, and then exit Notepad.


5.Open a command prompt, and then move to the same folder.

6.Type mofcomp reportingservice.mof, and then press ENTER.


This will solve invalid name space issue

Max Replication Text Size

When SQL Server is installed, the Max Replication Size value is set by default to 65536. If a record is inserted into a published table that exceeds that value, an error will occur at the application.

The following error will be returned to the calling application:

__________________________________________________________________________________

    Server: Msg 7139, Level 16, State 1, Line 1

Length of text, ntext, or image data (200) to be replicated exceeds configured maximum 10.

The statement has been terminated

__________________________________________________________________________________

In order to prevent this from occurring, Max Text Replication Size needs to be overridden to the maximum allowed, which is 2147483647

Solution :- 

exec sp_configure 'max text repl size', 2147483647

reconfigure with override