RSS

Monthly Archives: October 2011

Shrink Database Log File

http://platform.twitter.com/widgets/hub.1326407570.html

Shrink Database Log File

Only use this script for SQL Server development servers!
Script must be executed as sysadmin
This script will execute the following actions on all databases

– set recovery model to [Simple]
– trucate log file
– shrink log file
– Set recovery model to Simple

use [master]
go

— Declare container variabels for each column we select in the cursor
declare @databaseName nvarchar(128)

— Define the cursor name
declare databaseCursor cursor
— Define the dataset to loop
for
select [name] from sys.databases where database_id>4

— Start loop
open databaseCursor

— Get information from the first row
fetch next from databaseCursor into @databaseName

— Loop until there are no more rows
while @@fetch_status = 0
begin
print ‘Setting recovery model to Simple for database [‘ + @databaseName + ‘]’
exec(‘alter database [‘ + @databaseName + ‘] set recovery Simple’)

checkpoint

Print ‘checkpoint’

print ‘Shrinking logfile for database [‘ + @databaseName + ‘]’
exec(‘
use [‘ + @databaseName + ‘];’ +’

declare @logfileName nvarchar(128);
set @logfileName = (
select top 1 [name] from sys.database_files where [type] = 1
);
dbcc shrinkfile(@logfileName,1);
‘)
print ‘Setting recovery model to FULL for database [‘ + @databaseName + ‘]’
exec(‘alter database [‘ + @databaseName + ‘] set recovery Full’)
— Get information from next row
fetch next from databaseCursor into @databaseName
end

— End loop and clean up
close databaseCursor
deallocate databaseCursor
go

 
 

Create view By cursor

Create view By cursor

now my manager ask me to make views in database ((A)) from all tables already exist  in database ((B)) that’s hard task to catch tha tabel one by one and make create view XXXX as select * from Table_name . this operation very hard and will take a long time . so my friend MR/Mohamed Osman make Script dynamic to make this Operation .

Run this script on the database (B) where there are tables and the database you want to make the views select from there put the name of this DB in prameter name @DBNAME

— =============================================
— Create View dynamic
— Created by Mohamed osman
— =============================================
DECLARE @DBNAME AS NVARCHAR(100)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
SET @DBNAME =’GEN2010.DBO.’ —— views will select from this database
DECLARE @RESULT AS NVARCHAR (MAX)

DECLARE V CURSOR
READ_ONLY
FOR SELECT  ‘Create View ‘ + name + ‘ AS SELECT * FROM  ‘ + @DBNAME +  name
FROM sys.objects AS so WHERE so.type = ‘U’
DECLARE @name varchar(MAX)
OPEN V

FETCH NEXT FROM V INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

DECLARE @message varchar(max)
SELECT @message =  @name+ @NewLineChar +’GO’
PRINT @message
END
FETCH NEXT FROM V INTO @name
END

CLOSE V
DEALLOCATE V
GO

 
 

Change data capture (CDC) in SQL Server 2008

Introduction
Change data capture or (CDC) is very helpful feature in SQL Server 2008
Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change table” The SQL Server DBA can then easily monitor the activity for the logged table using these new audit tables .(Really i love this feature very nice microsoft)
Enabling Change Data Capture
CDC Enabling have two steps one on Database level and one on Tables level before Enabling CDC you must be Check CDC is enabled in any Database so Run the Query to check whether it is enabled for any database.

USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

After you run this script you will see your all database name and id and you will know  from this column  (IS_CDC_ENABLED) if CDC  feature is enabled in any Database or not enabled  (is_cdc_enabled = 0 ) that’s mean the CDC not enabled in this Database (is_cdc_enabled = 1) that’s mean the CDC is enabled in this Database
Know lets Create Database and tables To Enable the CDC

1.Create Database CDC
Create Database CDC
GO
2- Create Table
use CDC
go
2-Create Table Employee
(Emp_ID int Primary Key, Emp_Name Nvarchar(50),Emp_Job Nvarchar(50), Emp_Salary bigint )
GO
USE CDC
INSERT INTO dbo.Employee Values
(1,’Mostafa Elmasry’ , ‘DBA’ , ‘1000’),
(2,’Mohamed Omar’ , ‘Web Developer’ , ‘1500’),
(3,’Mohamed Osman’ , ‘Web Developer’ , ‘2000’)

Enabling Change Data Capture on a Database

USE CDC
GO
EXEC sys.sp_cdc_enable_db
GO

After run this Script the SQL Server will create five tables in Database CDC under System tables this table’s help you to know some Info about CDC in your database and Tables. Let’s See How

cdc.captured_columns (This table returns result for list of captured column)
-cdc.change_tables (This table returns list of all the tables which are enabled for capture)
-cdc.ddl_history (This table contains history of all the DDL changes since capture data enabled)
-cdc.index_columns (This table contains indexes associated with change table)
-cdc.lsn_time_mapping (This table maps LSN number (for which we will learn later) and time.)

Enabling Change Data Capture on one or more Database Tables

Before this steps must be known CDC is enabled in any tables in Database CDC or no let’s see how to know this information

USE CDC
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO

the result will be rwo column name (all tables name in database CDC) , is_tracked_by_cdc ( if this column = 0 that,s mean the CDC feature not enable in this table else CDC feature is enabled in this table name )

Enable CDC on table name Employee
—Syntax   (ياريت نركز شويه فى الخطوه دى لانها مهمه جدا)

USE Database_name
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’Schema_Name’, —- Mandatory
@source_name = N’Table_Name’,    —- Mandatory
@role_name = N’Role_Name’or NULL, —- Mandatory
@supports_net_changes = 0 or 1,
@capture_instance = N’CDC Instance Name’,
@index_name = Null or Index_Name,
@captured_column_ list = Column_Name,
@filegroup_name = FileGroup_name ,
@partition_switch = TRUE or FALSE
GO

•@source_schema is the schema name of the table that you want to enable for CDC
•@source_name is the table name that you want to enable for CDC
•@role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn’t exist. You can add users to this role as required; you only need to add users that aren’t already members of the db_owner fixed database role.
•@supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
•@capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
•@index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
•@captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
•@filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
•@partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed; i.e. allowing you to enable partitioning (TRUE or FALSE).

Now before try to enabled CDC on Table name ‘Employee’ Must be Check your SQL Server Agent is enabled because after you run the script The SQL Server will Create 2 jobs in your SQL Agent and SQl Server will create new table on your database CDC under System Tables this table name will be ‘@capture_instance+CT’ = ‘cdc.Employee_CDC_CT’ run the Script to enable CDC on table Employee and then check the jobs and new table. (خليك فاكر الكلمتين دول كويس)

USE CDC
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’Employee’,
@role_name     = NULL ,
@capture_instance = ‘Employee_CDC’
GO

Now go to your SQL server agent tocheck the two jobs you will see

1.cdc.AdventureWorks_capture
When this job is executed it runs the system stored procedure sys.sp_MScdc_capture_job. The procedure sys.sp_cdc_scan is called internally by sys.sp_MScdc_capture_job. This procedure cannot be executed explicitly when a change data capture log scan operation is already active or when the database is enabled for transactional replication. This system SP enables SQL Server Agent, which in facts enable Change Data Capture feature.
2.cdc.AdventureWorks_cleanup
When this job is executed it runs the system stored procedure sys.sp_MScdc_cleanup_job. This system SP cleans up database changes tables .
then now go to CDC database > tables > system tables > you will see new table name ‘cdc.Employee_CDC_CT’ if you change the @capture_intance in your script (Enable CDC on table) when you enable the CDC  if you change it from ‘Employee_CDC’ to ‘Emp’ the table name will be ‘cdc.Emp_CT’ instead of ‘cdc.Employee_CDC_CT’

Now let’s Check the New table in System tables you will see there are five additional columns into the mirrored original table (cdc.Employee_CDC_CT)
• __$start_lsn
• __$end_lsn
• __$seqval
• __$operation
• __$update_mask
There are two Column which are very important to us is __$operation and __$update_mask
Column _$operation contains value which corresponds to DML Operations Following is quick list of value and its corresponding meaning.
• Delete Statement = 1
• Insert Statement = 2
• Value before Update Statement = 3
• Value after Update Statement = 4
The column _$update_mask I will explain it after the example no one (لاتقلق كله جااااى فى الطريق)

Example of Change Data Capture

USE CDC
GO
SELECT *
FROM dbo.Employee
GO
USE CDC
GO
SELECT *
FROM cdc.Employee_CDC_CT
GO

You will see 3 rows in The original Table Employee and no data in table CDC.Employee_CDC_CT, whereas the table CDC.Employee_CDC_CT is totally empty. This table will have entries after an operation on the tracked table

Insert Operation

use CDC
INSERT INTO dbo.Employee Values
(4,’khaled Jmael’ , ‘IT’ , ‘3000’)
Go

Now try to check the table name ‘CDC.Employe_cdc_CT’ you will see

Update Operation

USE CDC
UPDATE DBO.Employee SET Emp_Job = ‘Technical Support’ where Emp_ID = 1
GO

Check now cdc.Employee_cdc_CT you will see the value before update and after update let’s see

Delete operation

USE CDC
DELETE FROM DBO.Employee WHERE Emp_ID = 1
GO

Check now cdc.Employee_cdc_CT you will see the value deleted from dbo.Employee

The summary
To Enable Change Data Capture (CDC) Feature in SQL server 2008 :
1- Check if CDC feature is enabled in your database or no .
2- Enable CDC feature in your database . (if not enabled)
3- Check the five new table under system tables in your database .
4- Check in if CDC enabled in any tables in your database or no .
5- Check the SQL Server Agent is Enabled.
6- Enable CDC feature  on your table/s (if not enabled) .
7- Check the two jobs in your SQL server Agent , Check the new table under the system table
8- Insert data in the oraginal table .
9- Update Data in the original table .
10- Delete data from the orignal table .
in the finale that’s very very help to all DBA that’s my opnion
in the next blog i will explain How to Enable the CDC feature in Some column in Table .
Best Regardes
Mostafa Elmasry

 

Error 22022 SQL Server Agent not running

When you run a SQL Server Agent job in Microsoft SQL Server 2000, you may receive the following error message:
Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action.”
If you receive this error message, first make sure that the SQL Server Agent service is running. To do this, follow these steps:

1.Click Start, click Run, type Services.msc, and then click OK.
2.In the Services window, locate the SQL Server Agent service.
3.Make sure that the value of the Status column of the SQL Server Agent service is Running.
then
EXEC sp_configure ‘show advanced’, 1;
RECONFIGURE;
EXEC sp_configure ‘allow updates’, 0;
RECONFIGURE;
EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO

 

 
10 Comments

Posted by on October 14, 2011 in Errors

 

Enable Remote Connection on SQL Server 2008

“Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 28 – Server
doesn’t support requested protocol) (Microsoft SQL Server)”

 

 

 

 

 

“Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 – Error
Locating Server/Instance Specified) (Microsoft SQL Server)”

 

 

 

 

 

“Cannot connect to SQL-Server-Instance-Name
Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”

 

 

 

 

To enable remote connection on SQL Server

  1. Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  2. Enable TCP/IP protocol for SQL Server 2008 to accept remote connection.
  3. (Optional)
    Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Note: In SQL Server 2008 , there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.
Step-by-step

1- Open SQL Server Configuration Manager. Click Start -> Programs-> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.

 

 

 

 

 

 

 

 

2- On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service

 

 

 

 

 

 

 

 

 

 

3- Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.

 

 

 

 

 

 

 

 

 

 

 

 

4- On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.

 

 

 

 

 

 

 

 

 

 

 

5- Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.

 

 

 

 

 

 

 

 

 

 

 

6-On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.

 

 

 

 

 

 

 

 

 

 

 

 

7- Right-click on TCP/IP and select Enable to enable the protocol.

 

 

 

 

 

 

 

 

 

 

 

 

8- On the left window, select SQL Server Services. Select SQL Server (INSTANCE NAME) on the right window -> click Restart. The SQL Server service will be restarted.

 

 

 

 

 

 

 

 

 

 

 

 

9-Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.

10- Right-click on the SQL Server Instance and select Properties.

 

 

 

 

 

 

 

 

 

 

 

 

 

11- On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.

12 -Right-click on the SQL Server Instance and select Restart.

 
 

Configure SQL Server Agent XPs

To Enable Agent XPs

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

To disable Agent XPs

sp_configure ‘show advanced options’, 0;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 0;

GO

RECONFIGURE

GO

 
Leave a comment

Posted by on October 10, 2011 in Configuration

 

Agent XPs Component is turend off

Cannot show requested dialog. Additional information: unable to execute requested command. ‘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.

 

 

 

 

 

 

 

 

 

 

 

 

 

Actions Required to resolve this Error

 

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

 
Leave a comment

Posted by on October 10, 2011 in Errors