Link to SQL Server's Restore and Recovery Documentation on MSDN

http://msdn.microsoft.com/en-us/library/ms191253.aspx

Could NOT open SSMS to connect to SQL Server - Problem was a lack of space on server

Today I needed to connect to a remote SQL Server as a co-worker was getting an error when she tried to connect with SSMS to our databases on that server.  As she was using RDC to connect to the server and using the local SSMS to attempt to connect to the databases, I tried using SSMS from my local system to connect directly to the SQL Server 2008 R2 on the Windows 2008 Server.  I got the following error.

The problem ended up being a lack of space of the C:\ drive on the actual server.  No where did we see that space was an issue.  However, in the future, when I get an error like the above I will go back and check the space no the server.

SQL Server Maintenance Plan Error - 'Agent XPs' component is turned off

I recently encountered the following error on a Windows 2012 Server running SQL Server 2012 when I attempted to open the maintenance plan.

‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

 To my knowledge the ‘Agent XPs’ components were not turned off by anyone intentionally.  The ‘Agent XPs’ option enables the SQL Server Agent extended stored procedures on a SQL Server Instance.  When not enabled the SQL Server Agent is not available in the SQL Server Management Studio Object Explorer.  When you use SQL Server Management Studio to start the SQL Server Agent service these extended stored procedures are enabled automatically.

 The error was resolved by performing the following.

(1)    Ran the following from SSMS in the SQL Query Window when connected to the master database.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE;

GO

Output in the Messages Window

Configuration option ‘show advanced options’ changed from 0 to 1.  Run the RECONFIGURE statement to install.

Configuration option ‘Agent XPs’ changed from 0 to 1.  Run the RECONFIGURE statement to install.

(2)    At this point I noticed my SQL Server Agent was down (I saw this in the SSMS Object Explorer ).  I double checked my SQL Server Agent Error Logs and discovered the agent had been down for three days.  I also validated that my backups had not run for that same period which made sense as the agent was down.  I started the agent via selecting it, right mouse click, start.

 

 

 

Upon further investigation I believe I have discovered the issue that caused this error.  Three days ago the Windows

SA changed the service account for the SQL Server, SQL Server (MSSQLSERVER) and the agent, SQL Server Agent

(MSSQLSERVER).  After he changed them he did restart the service, but never went back and validated the jobs,

maintenance plans, etc.  The database service and agent appeared to restart successfully as no errors were thrown to

him, but for some reason changing the service account caused issues for the agent and it did not start.

Working on re-configuring SQL Server Maintenance Plans responsible for full, differential, and transaction log backups, database consistency checks, file clean-up operations, etc.  One of the key questions has been trying to evaluate the impact of running transaction log backups during full or differential backups.  The first link is to an article that addresses this question.  The additional links are links to additional articles on backups, restores, etc.

http://www.sqlskills.com/blogs/paul/search-engine-qa-16-concurrent-log-and-full-backups/

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d4947a4-a1be-46af-bb76-aa62c7011e63/full-and-transaction-log-backup-sequence-question?forum=sqldisasterrecovery

http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

Eight Steps to Better Transaction Log Throughput.
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

SQL Server Pages and Extents Architecture (2008 R2)

Page – fundamental unit of storage in SQL Server

Extent – eight physically contiguous pages

Pages

·     page size is 8kb in SQL Server

·      SQL Server databases have 128 pages/mb  (1024k/8k=128)

·       each page begins with 96b header (header stores info about page like page number, page type, amount of free space, allocation unit ID the object that owns page, etc.)

·        page types

o   data – rows with data (except text, ntext, image,nvarchar(max), varchar(max), varbinary(max), xml [when text in row is set to ON]

o   index

o   text/image - text, ntext, image,nvarchar(max), varchar(max), varbinary(max), xml and variable length columns when data row exceeds 8kb (varhcar, nvarchar, varbinary, sql_variant)

o   GAM/SGAM (Global Allocation Map/Shared Global Allocation Map) – into about whether extents are allocated

o   page free space – info about page allocation and free space available on pages

o   index allocation map – info about extents used by a table or index

o   bulk changed map – info about extents modified by bulk operations (???) since the last

BACKUP LOG statement

o   differential changed map – info about extents that have changed since the last BACKUP DATABASE statement

·       data rows are put on the page serially following the header

·        row offset table starts at the end of the page and contains one entry for each row on the page recording how far the first byte of the row is from the start of the page (entries in the row offset table are in reverse order from the sequence of the rows on the page

 

 

 

·         Rows cannot space pages

·         Portions of a row may be moved off the row’s page(24b pointer on original page is maintained)

Log files DO NOT contain pages but contain a series of log records

 

Extents

·         basic unit by which space is managed

·         8 physically contiguous pages (64kb)

·         SQL Server databases have 16 extents per mb (1024kb / 64kb = 16)

·         Two types of extents

o   uniform

o   mixed

·         uniform extents – owned by a single object; all 8 pages in the extent can only be used by the owning object

·         mixed extents – shared by up to 8 objects; each of the eight pages in the extent can be owned by a different object

·         new tables and indexes are created as mixed extents; when those objects exceed 8 pages then it switches to use uniform extents for subsequent allocations ( if object has enough rows to generate 8 pages initially then it starts out using uniform extents)

SQL Server Logging Basics

Configuring SQL Server Maintenance plans to conduct database backups.  Good article on SQL Server logging at the following location.

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

SQL Server Backup Article

Configuring SQL Server Maintenance Plans to conduct database backups.  Good article on SQL server Backups at the following location.

http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx