SQL Server 2012 RCO

SQL Server 2012 RCO is avaiable for download.

http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

sys.dm_server_services

sys.dm_server_services DMV Returns information about the SQL Server and SQL Server Agent services in the current instance of SQL Server. Use this dynamic management view in SQL Server 2008 R2 SP1 and later versions(Denali aka SQL Server 2012) to report status information about these services.

Pausing and Resuming Database Mirroring

The database owner/dba can pause and later resume a database mirroring session at any time. Pausing preserves the session state while suspending mirroring. During bottlenecks, pausing might be useful to improve performance on the principal server.


When a session is paused, the principal database remains available. Pausing sets the state of the mirroring session to SUSPENDED, and the mirror database no longer keeps up with the principal database, causing the principal database to run exposed

How Pausing and Resuming Affect Log Truncation


Normally, when an automatic checkpoint is performed on a database, its transaction log is truncated to that checkpoint after the next log backup. While a database mirroring session remains paused, all of the current log records remain active because the principal server is waiting to send them to the mirror server. The unsent log records accumulate in the transaction log of the principal database until the session resumes and the principal server has sent the log records to the mirror server.

When the session is resumed, the principal server immediately begins sending the accumulated log records to the mirror server. After the mirror server confirms that it has queued the log record corresponding to the oldest automatic checkpoint, the principal server truncates the log of the principal database to that checkpoint. The mirror server truncates the redo queue at the same log record. As this process is repeated for each successive checkpoint, the log is truncated in stages, checkpoint by checkpoint.

Avoiding a Full Transaction Log


If the log fills up (either because it reaches its maximum size or the server instance runs out of space), the database cannot perform any more updates. To avoid this problem, you have two alternatives:

Resume the database mirroring session before the log fills up, or add more log space. Resuming database mirroring lets the principal server send its accumulated active log to the mirror server and puts the mirror database in the SYNCHRONIZING state. The mirror server can then harden the log to disk and start to redo it.

Stop the database mirroring session by removing mirroring.

Unlike pausing a session, removing mirroring drops all information about the mirroring session. Each partner server instance retains its own copy of the database. If the former mirror copy is recovered, it will have diverged from the former principal copy and be behind by the amount of time that has elapsed since the session was paused.

Recently modified Objects in sql server database

If you want to find a recently changed (or) modified objects list in sql server. The simplest way to find this below query.

SELECT * FROM sys.objects

WHERE DATEDIFF(D,modify_date, GETDATE()) < 2 -- (Last 2 days)
 
-- you can also filter stored procedure or tables etc
SELECT * FROM sys.objects

WHERE type='P' and DATEDIFF(D,modify_date, GETDATE()) < 2 -- (Last 2 days)

How to connect SQL Server using DAC on New Query Window(SSMS)

1. Open the new SSMS

2. Connection to Server window will popup , Please cancel it

3. click the NewQuery (Top Left corner)

4. Enter the following one of the option like  below  connection to Server popup window

Server Name : ADMIN:Servername
(OR)
Server Name : ADMIN:Servername\Instancename

Then you can run the below query to find connection

select * from sys.dm_exec_connections ec
join sys.endpoints ep on (ec.endpoint_id=ep.endpoint_id)
where ep.name='Dedicated Admin Connection'


Other option you can connect cmd prompt
c:\sqlcmd -S Servername -U sa -P 'xxxx' -A

SQL Server 2008 Service Pack 3 Cumulative Update 2 Released

Microsoft SQL Server 2008 Service Pack 3 (SP3) is now available for download. While keeping product changes contained, we have made significant investments to ease deployment and management of Service Packs. A few key enhancements for customers in Microsoft SQL Server 2008 Service Pack 3 are :

• Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3. In addition, we have increased the performance & reliability of the setup experience.
• In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows.
• Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled.
• Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped.
• Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor).
• Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan.

http://www.microsoft.com/download/en/details.aspx?id=27594

Unable restart SQL Server services

Error: 9954, Severity: 16, State: 1

Message
SQL Server failed to communicate with filter daemon launch service  (Windows error: The service did not start due to a logon failure.). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Solution:
If you have recently changed SQL Server service account password , Please make sure to update the password and restart the services this apply to all SQL Server Services components below.

SQL Server
SQL Servr Agent
SQL server Integration Services
SQL Server Analysis Services
SQL Server Reporting Services
SQL Server Browser
SQL Full-text Filter Daemon Lanuncer

SQL Server Code Name Denali CTP3

SQL Server Code Name "Denali" CTP3 is available to download from Microsoft web site

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

SSIS Log Analyzer

This video demonstrates how to use the SSIS Log Analyzer tool to analyze complex and verbose SSIS logs quickly and easily. You learn how to analyze logs manually by applying row and column filters or by using queries, view performance analysis of package tasks, and identify issues and possible solutions.

Web Link : http://technet.microsoft.com/en-gb/library/hh323822.aspx

SSIS Log Analyzer Tool available from codeplex site: http://ssisloganalyzer.codeplex.com/

Apple's visionary and creative genius Steve Jobs

How can I deploy SQL 2008 SSRS Report in to SQL 2005 SSRS Server

Error:
The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded. (rsInvalidReportDefinition) Get Online Help


You can not deploy  sql server 2008 report (rdl) into sql server 2005 report  server because RDL namespace are different for each version.

End of Mainstream Support for SQL Server 2005 and End of Service Pack Support for SQL Server 2008 SP1

Mainstream Support for SQL Server 2005 Service Pack 3 and SQL Server 2005 Service Pack 4 will end on April 12, 2011, and Service Pack Support for SQL Server 2008 Service Pack 1 will end on October 11, 2011. 

Product	Version	SP	Mainstream Support End Date	Extended Support End Date	Options / Notes
SQL Server	7.0	SP4	12/31/2005	01/11/2011	Technical support ends as of 01/11/2011; options for technical and/or hotfix support after this date: ð  Continue with self-help ð   Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2 ð  Custom support agreement
SQL Server	2000	SP4	04/08/2008	04/09/2013	Technical support continues till 04/09/2013, yet mainstream (hotfix) support ends as of 04/08/2008; options for hotfix support after 04/08/2008: ð   Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2 ð  Extended hotfix support agreement
SQL Server	2005	RTM (SP0)	07/10/2007	Not applicable	Technical support ends as of 07/10/2007; options for technical and/or hotfix support after this date: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2
SQL Server	2005	SP1	04/08/2008	Not Applicable	Technical support ends as of 04/08/2008; options for technical and/or hotfix support after this date: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2
SQL Server	2005	SP2	01/12/2010	Not Applicable	Technical support ends as of 01/12/2010; options for technical and/or hotfix support after this date: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2 ð  Custom support agreement
SQL Server	2005	SP3	04/12/2011	01/10/2012	Technical support continues till 01/10/2012, yet mainstream (hotfix) support ends as of 04/12/2011; options for hotfix support after 04/12/2011: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2 ð  Custom support agreement
SQL Server	2005	SP4	04/12/2011	04/12/2016	Technical support continues till 04/12/2016, yet mainstream (hotfix) support ends as of 04/12/2011; options for hotfix support after 04/12/2011: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2005 or SQL Server 2008 or SQL Server 2008 R2 ð  Extended hotfix support agreement
SQL Server	2008	RTM (SP0)	04/13/2010	Not Applicable	Technical support ends as of 04/13/2010; options for technical and/or hotfix support after this date: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2008 or SQL Server 2008 R2 ð  Custom support agreement
SQL Server	2008	SP1	10/11/2011	Not Applicable	Technical support ends as of 10/11/2011; options for technical and/or hotfix support after this date: ð  Continue with self-help ð  Upgrade to the latest supported service pack for SQL Server 2008 or SQL Server 2008 R2 ð  Custom support agreement
SQL Server	2008	SP2	See Options/Notes		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. Visit the Lifecycle page to find the support timelines for your particular product.
SQL Server	2008 R2	RTM (SP0)	See Options/Notes		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. Visit the Lifecycle page to find the support timelines for your particular product.

How to get the database property settings value

--DATABASEPROPERTYEX() Returns the current setting of the specified database option or property

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'SQLSortOrder') --0 = Database is using Windows collation
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoShrink')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoCreateStatistics')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoUpdateStatistics')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsFulltextEnabled')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsTornPageDetectionEnabled')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Recovery')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Version')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'UserAccess')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsPublished')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsMergePublished')
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsSubscribed')

How to import packages using Integration Services

There are two different way to import ssis packages either command prompt or SSIS.
Here i am going show you , How to import ssis package via SSIS.

1. connect to SQL Server Integration Services
2. Expand Stored Packages
3. Expand the MSDB
4. Create Folder called My SSIS Packages
5. Import Packages

6. Package Location : File system

7. Package Path : C:\SSMS_Export_to_Excel.dtsx
8. Protection Leve : Rely on the server storage and role for access control

9. Package is imported now as you can see

How to find the TornPageDetection/Checksum for databases in SQL 2005 & 2008

There are two different way to find checksum protection/TornPageDetection for the SQL server

--Part :1 (checksum)

select name,database_id, page_verify_option,page_verify_option_desc from sys.databases
go

-- Part :2 (IsTornPageDetectionEnabled)
select DATABASEPROPERTYEX ('master' ,'IsTornPageDetectionEnabled' )
1= TRUE

select DATABASEPROPERTYEX ('tempdb' ,'IsTornPageDetectionEnabled' )
0 = FALSE

The SQL Server Database Engine detects incomplete I/O operations caused by power failures or other system outages.
1 = TRUE
0 = FALSE

The package failed to load due to error 0xC0010014 "One or more error occurred

You are trying to import the SSIS package on SQL 2005 but SSIS package was created on SQL Server 2008 envrionments(SSIS).

Solution : Try to import ssis package on SQL Server 2008 with SSIS Manager.

How to Rename a Database in SQL Server

In sql 2005/2008 you can use ALTER DATABASE statment easily modify database name.

ALTER DATABASE Northwind MODIFY NAME = Northwind _Test

--The database name 'Northwind _Test' has been set.

 Back up the master database after you rename any database.

How to detect a Torn Page

SQL Server maintains suspect page information in a msdb database new system table : suspect_pages.When the database engine reads a database page containing a CHECKSUM or TORN PAGE (error 824), the page is considered suspect and Page ID number is recorded in the suspect_pages table.

SELECT db_name(database_id) DatabaseName , file_id , page_id , last_update_date
FROM msdb.dbo.suspect_pages
WHERE event_type=3

Torn pages can also be detected by reviewing the output from a DBCC CHECKDB command.

Granting SELECT permission on a table

USE [AdventureWorks]
GO
GRANT SELECT ON [dbo].[DatabaseLog] TO [RoleName] AS dbo
GO
sp_helprotect
--Other than dbo schema
GRANT SELECT ON [Person].[Contact] TO [RoleName] AS dbo
GO
sp_helprotect [Person.Contact]

Thanks for visiting this blog

More than 10000 users visited so for this SQL Server Blog, 

Could not allocate a new page for database 'TEMPDB' because of insufficient disk space

Error: 1101, Severity: 17, State: 10.


Message:
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Solution: Increase the data file( mdf) size. using SSMS

(OR)

USE MASTER

GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='tempdev', SIZE=1500MB)

How to find the scheduled subscriptions status on Report Server

If you deplyed reports on Report Server then you need to setup subscriptions to send an reports(pdf,excel) to users by email but how do you find scheduled reports send to user or not.

Use ReportServer
go
SELECT SubscriptionID,Description,LastStatus,EventType,
LastRunTime,DeliveryExtension,Version
FROM Subscriptions

SQL Server Code name DENALI CTP3

The future version SQL Server Denali is available here to download

http://www.microsoft.com/sqlserver/en/us/default.aspx

Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report the link is no longer valid

Always you should setup credentials stored securely in the report server for the report before you try to setup subscriptions to send report email with pdf format.

Report Manager, select your report.  Click the Properties tab, then select 'Data Sources' on the left side of the page.  Make sure the 'A custom data source' is the radio button selected, then click the radio button for 'Credentials stored securely in the report server'.  Type in your credentials to run the report and if it's a domain account, then check the box for 'Use as Windows credentials when connecting to the data source'.  Click Apply at the bottom and you should be able to create a subscription.

How to check AUTO_CREATE_STATISTICS is enabled for database

use master
go
SELECT name AS 'Name',
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
-- Also you can check list of statistics created on your database
use Mydb
go
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

When updating statistics with UPDATE STATISTICS or sp_updatestats, Microsoft recommend keeping
AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.

Find the datbase restore history for your server

-- Find the restore history for your database

use msdb
go
select rh.restore_history_id ,rh.restore_date, rh.user_name, rh.destination_database_name,
rh.restore_type,rf.restore_history_id, rf.file_number, rf.destination_phys_drive,rf.destination_phys_name
from restorehistory rh
join restorefile rf
on rh.restore_history_id = rf.restore_history_id
where rh.destination_database_name = 'mytestdb'
order by rh.restore_date desc

-- Please note Restore_Type value D = Full and L= Log

All SQL Server Backups taken Last time

-- All SQL Server Backups taken Last time
SELECT d.name AS DatabaseName,
MAX(b.backup_finish_date) AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D' -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model GROUP BY d.name
ORDER BY 2 DESC
--Show details of the successful Backup operations performed on this server in the past

SELECT d.name AS DatabaseName,
b.backup_finish_date AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type IN( 'D') -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model ORDER BY 2 DESC

How to copy RDL files from Report Server Manager

If you want to copy production reporting files rdl into testing server to deploy. The following steps are easiest method to do.

Solution:
Open your Report on Report Manager
Then Click properties of Report
Under the Report Definition you will see Edit and Update link
If you click on the Edit link you can Open or Save the RDL files

How can you find the stored procedure used by SQL Job

If you have hundreds of sql jobs scheduled  to run everyday in SQLserver.  How quickly you can find the stored procedure used by sql job command.

Here is simple solution to find it.

use msdb
go
select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%uspMyBook%'

RESTORE cannot operate on database because it is configured for database mirroring.

Msg 3104, Level 16, State 1, Line 1
You come to know mirror database not sync while, If you decided to break the mirror and re-setup mirror.

1. ALTER DATABASE AdventureDB SET PARTNER OFF ( You remove the mirror first)
2. Take the full backup from the principal server copy over to mirror
3. You will see mirror database is disconnected

RESTORE DATABASE AdventureDB WITH RECOVERY

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AdventureDB' because it is configured for database mirroring.
Use ALTER DATABASE to remove mirroring if you intend to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You found this above strange error.

4. You have reboot the SQL Server. Make sure sql server services are running after re-boot.
 -- Mirror Server restarted
5. Now re-run the command on mirror server
RESTORE DATABASE AdventureDB WITH RECOVERY
RESTORE DATABASE successfully processed 0 pages in 20.281 seconds (0.000 MB/sec).
-- Database is now accessible on mirror servers

6. Start restore database backup Full, Logs with norecovery mode
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.BAK' WITH NORECOVERY
 -- Restoring now
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.Log' WITH NORECOVERY, REPLACE, STATS = 5
If you have more than one log backup do the same
7. Ready resetup the mirror again.

Find Page Life Expectancy (PLE) value for default SQL Server instance

SELECT cntr_value AS [Page Life Expectancy], @@servername AS Server
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND [object_name] = 'SQLServer:Buffer Manager'

Note: If you get cntr_value is less than 300 then, that means

An average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure or missing indexes, or a cache flush.

error 3624, status 1, severity 20. Database mirroring has been suspended

The remote mirroring partner for database 'Adventuredb', encountered
error 3624, status 1, severity 20. Database mirroring has been suspended.
Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

when you see this error on sql server logs (or) if you notice your mirror database is suspended.

Solution:

First try to resume mirroring, If mirror become suspended again please use the below steps to resovle

1. Break mirror from Principal server
2. Take a full backup of mirrored database from Principal database server
3. Restored the full backup on mirror server with NORECOVERY
4. Take a Tranactional log backup of mirrored database from Principal database server
5. Restored the Log backup on mirror server with NORECOVERY
6. Re-setup mirroring between principal and mirror server.

Hope this will help

Monitor database mirroring servers

--Simple way to monitor database mirror servers

SELECT d.name as dbname, d.database_id,

@@servername as Principal_Server,
m.mirroring_role_desc,
m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device Operating system error 53

When you are in the situation to restore the database if you wrongly typed path name of backup device you will come accross the following error.

USE master
go
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

--Error
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\SQLSVR001\Backup\Adventureworks.BAK'. Operating system error
53(error not found).

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solution:
-- Make sure to include the correct path name of backup file
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\MSSQL\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

At the end you will see
Msg: RESTORE DATABASE successfully processed 760350 pages in 144.614 seconds (43.071 MB/sec).

Advanced Troubleshooting with Extended Events

Paul S.Randal has written wonderful article on Technet about Troubleshooting SQL Server using extended events in SQL Server 2008.

• Why troubleshooting is necessary
• Troubleshooting tools in SQL Server
• Overview and architecture of Extended Events
• Using Extended Events

Source : http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx

Restore the Database from the Backup file

-- PART 1
-- Find the Backup file details like Machinename, softwareBuild, collation, backupsize, BackupStartDate
USE MASTER
go
RESTORE HEADERONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE FILELISTONLY
FROM DISK = H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE VERIFYONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Restore the database from the backup file
-- eg. Development database refresh from production copy
RESTORE DATABASE AdventuresDb
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY, REPLACE, STATS = 10

------------------------------------------------------------------ PART 2
-- Find the Backup file orignal File path and Logical filename, Physical filename
USE MASTER
go
RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
-- Create/Restore the Database from the backup file with move to different locations
-- eg. Development database refresh from production copy

RESTORE DATABASE AdventuresDb
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY,
MOVE 'AdventuresDb_Data' TO 'D:\MSSQL\Data\AdventuresDb.MDF',  MOVE 'AdventuresDb_Log' TO 'E:\MSSQL\Log\AdventuresDb_log.LDF',
STATS = 10

-- sp_helpdb AdventuresDb