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)