sqlIdeas

Archive files based on number of files configuration in SQL Server - SSIS

2012-02-19T09:20:00.000+05:30

Hope you read earlier posts to archive files which are older as per days and you liked them. If you have not read then you can go there with following where you have various scenario with the same,

Archive old database backup files using TSQL Script - SQL Server
Delete files from specified folder using File System Task in SQL Server - SSIS
Delete files from specified folder using Script Task in SQL Server - SSIS
Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS
Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS

Recently i was asked by my friends to delete the files bases on <n> number of files, not based on days. Such a way we can keep <n> number of files every time and rest files get archived. I will give you an example here as i have three folders and want to keep different number of files in each folders only. Says keep 3 files in BackupFolder1, keep 2 files in BackupFolder2 and keep 1 file in BackupFolder3. So how can we achieve it? Let's move on the steps,

First we will check the files existing in folders,

We are creating a ssis package here and will use tasks to perform the archive process,

Also the variables used in the whole task flow,

1.
You can see the flow of the tasks, so we will be here for task 1 which will have fetch the records from table which really need to be archived. We have inserted folder paths and number of the files which need to keep for each folders. Let's view the records in table,

Now we will create a task1 with Execute SQL Tasks and get the list of archive folders as figured below,

It fetch the folders and archive numbers for each row and assign to variable which is object datatype. For variable assignment you need set ResultSet option to 'Full result set' as above. After that you mapping variables as follow for output,

2.
We need to go through the values coming out from variable and then map it to two variables again for archive folder name and archive numbers using Foreach Loop Container,

You can see how the object variable holds the values and assigned to another variables again as per below shot,

3.
Now use Execute SQL Task again and get the list of the files which need to deleted, You need to write some SQL statement to get those files as per archive numbers. So we will look the configuration and variable values mapping and using them in the script. You need to enable xp_cmdshell from configuration.

Same as earlier steps we need to put the sql query in SQLStatement option and set 'Full result set' for ResultSet, You can see two arrows which will be input values of the script which going to be executed, You can collect the same script here,

CREATE TABLE #TempFiles
(
Seq int identity(1,1),
FileName varchar (500)
)

DECLARE @FullPath varchar(500), @sql varchar(500)

SET @FullPath = ?
SET @sql = 'dir /B /O-D '+ @FullPath

INSERT INTO #TempFiles (FileName)
EXEC xp_cmdshell @sql

SELECT @FullPath + FileName  as FullName
FROM #TempFiles
WHERE FileName is not null
AND Seq > ?

DROP TABLE #TempFiles

After completion of above get the resulted values in another variable and pass them to next step,

4.
Use Foreach Lopp Container to process delete operation for each files same steps we did earlier for Foreach Loop,

5.
Finally use Script Task to delete the file one by one as i did in below image. You need to pass the value of file name as read only,

Now use the following script to perform actual task for deletion, Use below script in your script task,

You can copy same script from here,

--//Apply below line in #region namespaces
using System.IO;

--//Apply below code inside 
public void Main()
{
string FilePath = Dts.Variables["User::FileName"].Value.ToString();
System.IO.File.Delete(FilePath);
Dts.TaskResult = (int)ScriptResults.Success;
}

After running the package files will be deleted as per archive number defined in the tables for each folders.

You can schedule this package in some frequency. Hope this help you.

Restore Full Database from multiple backup files - SQL Server

2012-02-10T19:29:00.001+05:30

As I know database restoration is a best practice on periodically to check and verify database backup copies and issues while restoration due to any stuffs. I have wrote for the database backups as how can we perform full database backup completely and split in to multiple files to reduce IO and time. As we have performed full database backups into split multiples files, Same way we will perform restoration from those complete and split multiple files.

First we will see to restore full database complete backup and tried for from multiple files.

#1. Using TSQL

Let us run the below script first and get the logical names for database data and log files

RESTORE FILELISTONLY FROM 
DISK =  'D:\DBBackups\ReportServer\ReportServer.bak'
GO

After collection and putting logical file names in below script , it will restore the database from complte backup.

RESTORE DATABASE ReportServerComplteCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy_log.ldf'

As above we can restore database from multiple files as following,

RESTORE DATABASE ReportServerSplitCopy FROM 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy_log.ldf'

#2. From Management Studio.

We can also perform restoration from SSMS by adding those multiple backups as per shot.

You can see over here after adding those files it will shown as single backup set.

Hope this help you.

DDL Trigger and DDL Auditing in SQL Server 2005

2012-02-04T18:36:00.000+05:30

For very sensitive and secure databases, it is required to track all the activities and changes done with the database objects. And it should be very secure as well, so nobody can play with database objects.
Even we should have all the history and tracking for what are database object structure changes occurred, what, when and who did it?

For the tracking all these stuffs we need to have a best process to change them and it should be done by responsible person. Bur sometime the issues may be occur with database objects changes, at that time if we have all the enough information then we can correct and revert back it as original.

Today i would like to introduce to handle all the stuffs, and it is Database DDL Triggers. DDL Triggers can be specified at database and server level, but for the database objects tracking we need it on database.

Let us see an example.

-- Creating audit database
CREATE DATABASE AuditDatabase
GO

USE AuditDatabase
GO

-- Creating table, which capture all the objects structure changes
CREATE TABLE ObjectTracking
(
TrackingId bigint identity(1,1),
TrackingDate datetime NOT NULL DEFAULT GETDATE(),
DatabaseName varchar(500),
EventType varchar(500),
ObjectName varchar(500),
ObjectType varchar(500),
LoginName varchar(500),
HostName varchar(500),
SqlCommand nvarchar(max)
)

GO

Now to audit the database objects DDL operation or structure changes we have to create DDL trigger at database level.

-- Creating triggers, which will be fire on every objects actions on the database of that instances.
CREATE TRIGGER [AuditObjects]
ON DATABASE
FOR
-- DDL_DATABASE_LEVEL_EVENTS 
-- Defining all the DDL database events on which we need track
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS
BEGIN
SET NOCOUNT ON

DECLARE @data XML
DECLARE @HostName varchar(500)
SET @data = EVENTDATA()
set @HostName = HOST_NAME()

-- Inserting tracking information in audit table
INSERT INTO ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'),
@HostName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
END
GO

Now we have created tables and DDL trigger to capture the object movement. Now we have turn to test it.

Testing :

-- Creating , Altering and Destroying some of the various objects
-- Creating table event
CREATE TABLE AuditTest (AuditId int, Auditname char(10))
GO

-- Altering table event
ALTER TABLE AuditTest
ADD  AuditDate datetime NULL
GO

-- Creating Indexes events
CREATE CLUSTERED INDEX  ix_AuditId on AuditTest(AuditId)
GO

CREATE NONCLUSTERED INDEX  ix_AuditDate on AuditTest(AuditDate)
GO

-- Dropping index events
DROP INDEX  ix_AuditDate on AuditTest
GO

-- Creating view events
CREATE VIEW AuditView
AS
SELECT * FROM AUDITTEST
GO

-- Creating procedure events
CREATE PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AUDITVIEW
END
GO

-- Altering procedure events
ALTER PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AuditTest
END
GO

-- Dropping view and table events
DROP VIEW AuditView
GO

DROP TABLE AuditTest
GO

Now audit test over, we have to review history in audit table now,

SELECT
TrackingId,
TrackingDate,
DatabaseName,
EventType,
ObjectName,
ObjectType,
LoginName,
--HostName,
'Paresh-PC' as HostName,
SqlCommand
FROM ObjectTracking
ORDER BY TrackingDate
GO

Conclusion : We have some other new features for DDL Auditing in SQL Server 2008. But before sql server 2008 we can use this method for auditing.

Are you using this or using something else? Make your comments.

Alter failed for Login sa. Cannot set a credential for principal 'sa'. - Error encountered in SQL Server

2012-01-28T07:32:00.000+05:30

Recently when i worked with SQL Server security, i encountered with one error while trying to modify 'SA' account properties. The exception details looks following,

Alter failed for Login sa. Cannot set a credential for principal 'sa'.

Also you can see the image below for the same.

The fix for the error is the option "Map to Credential" is checked in the "General" tab of the Login Properties Page as mentioned below,

Hope this help you.

Application Role in SQL Server

2012-01-20T19:29:00.000+05:30

In last post we have seen custom database roles as how can we create it and assign required access to users. We also noticed that we can add multiple members with same role. That was the security with database roles and members comes into the picture. Now here we will study of Application Role. This is the security for the application level and no such members comes into the picture.

Application Role :
As per msdn, An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.

Workaround:
We can implement application role and take into effect with following steps, I am going to here with some of examples, so like create those required objects, so we can set them with application role.

1. Create required objects

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Here we have created same required objects as we created in earlier post for database role.

2. Create an application role

USE demo
GO

-- sp_addapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_addapprole 'AppRole', 'AppPwd'
GO

3. Add permission to this application role

USE demo
GO

GRANT SELECT ON SampleTable1 to AppRole;
GRANT SELECT, INSERT,UPDATE ON SampleTable2 to AppRole;
GRANT EXEC ON SampleSP1 to AppRole;
GO

You can see we have assigned same access to application role as we did in earlier post for the custom database role. Application role is created here now, You can also create/view with expanding Roles inside security tab for particular database and inside it you can find Application Role tab.

4. Connecting database and activating application role
Application roles are enabled/activated by sp_settapprole system stored procedure and it has required password.So application can be connected to SQL Server with this application role with scope of particular session and required password to authenticate it to connect SQL Server. To authenticate application roles and activate it it should be required to use in .Net connection code or other application database connection method code. You can refer the link here to use application role in application code.

sp_settapprole system stored procedure activate the application role for the specific connection while connecting with application and the syntax is as following,

USE demo
GO

-- sp_setapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_setapprole 'AppRole', 'AppPwd'
GO

We can change the password for the application role with following,

USE demo
GO

-- sp_approlepassword [@rolename = ] 'rolename', [@password = ] 'new password'

EXEC sp_approlepassword 'AppRole', 'AppChangedPwd'
GO

Hope these all the steps are enough to implement application role. Stay tuned for more.

Custom Database Role in SQL Server

2012-01-13T19:31:00.000+05:30

Recently while working with database security, I learned database roles as how the each role used. Apart from the server level roles if we need to require to assign access/rights to particular databases level then we need to go through database level roles.

Following are the fixed database level roles as per MSDN,
db_owner : Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin : Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin : Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator : Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin : Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter : Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter : Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader : Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

You can see the image for all above fixed database roles. Now we will see how can we use the roles and bind with users. Let's create a small demo with example.

Here i am creating required objects used for demo, So let's do that.

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Now here i want assigned to the access to user as following,
1. User can see the data from SampleTable2 table.
2. User can perform select/insert/update operation on SampleTable1 table, not delete operation.
3. Can execute stored procedure SampleSP1.
4. Can not execute stored procedure SampleSP2.

Workaround 1:
If we assigned fixed database roles to User like db_datareader and db_datawriter for particular database, then user can perform all DML operation on all tables, views for the database. Even if we assigned EXECUTE permission to user then user can execute all the stored procedures.

After all these permission we need to deny permission from the user for some of the tables and stored procedures which are not required to be accessed.

Workaround 2:
Instead of doing above such stuffs, We will create a new custom database level roles and assigned requited access to user for the objects.

USE demo
GO

CREATE LOGIN [SupportUser] 
WITH PASSWORD=N'SupportUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO

CREATE ROLE [SupportRole] 
Go

GRANT SELECT ON SampleTable2 TO [SupportRole];
GRANT SELECT,INSERT,UPDATE ON SampleTable1 to [SupportRole];
GRANT EXEC ON SampleSP1 to [SupportRole]
GO

EXEC sp_addrolemember N'SupportRole', N'SupportUser'
GO

Let's connect the SQL Server instance with this newly created user and see the access rights,

USE demo
GO

PRINT 'Inserting in SampleTable1'
GO
INSERT INTO SampleTable1
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample1'
GO

PRINT 'Inserting in SampleTable2'
GO
INSERT INTO SampleTable2
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample2'
GO

PRINT 'Deleting from SampleTable1'
GO
DELETE FROM SampleTable1
GO

PRINT 'Viewing from SampleTable1'
GO
SELECT * FROM SampleTable2
GO
SELECT * FROM SampleTable1
GO


PRINT 'Executing SampleSP11'
GO
EXEC SampleSP1
GO
PRINT 'Executing SampleSP2'
GO
EXEC SampleSP2
GO

You can see the below image to see the access of running user,

Main benefit of the custom database role is role can be assigned to multiple users. You can see below script where i have assigned same role to different user. So once role created it can be assigned to multiple users.

USE demo
GO

CREATE LOGIN [DBAUser] 
WITH PASSWORD=N'DBAUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [DBAUser] FOR LOGIN [DBAUser]
GO

EXEC sp_addrolemember N'SupportRole', N'DBAUser'
GO

Hope you liked this post.

Changing Rows to Columns Using PIVOT - SQL Server

2012-01-07T19:13:00.000+05:30

During working with one logic, i got chance to work with PIVOT operation. Sometime we need do require rowdata as column in our custom logic, then we can use some temp table and then populate agreegate data in temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as column.

Before go ahead to run the script of Pivot, we will create database and table objects.

CREATE DATABASE DEMO
GO

USE DEMO
GO

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)

GO

-- Inerting some sample records

INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'

GO

Now we will check the original table data and aggregated data using Pivot.
So we will run both script for the same.

-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated data using Pivot and converted rows to column
SELECT
*
 FROM
 (
    SELECT 
 ItemCode, 
 ItemName, 
 ItemColour
    FROM TblPivot
 ) AS P
PIVOT
(
  Count(ItemName) FOR ItemColour IN (Red, Blue, Green)
) AS pv

GO

You can review here and see how the PIVOT is working. Let me share your experience with PIVOT operation.

Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS

2011-12-30T19:55:00.000+05:30

After using some of the ways to delete the files from the particular folder like Delete file using Script Task and File System Task in SSIS. We have seen such methods and used logic to get the files from the loop container and process to delete them and same thing applied for the scripts as well. we also looked for the files deletion which are older than some Retention period with Script Task also.

In earlier posts we have not used any parameters in the scripts and directly applied folder path and retention period values there. But here i would like to use parameters for the folder path and retention period and pass through the scripts and delete them as per condition.

1. Let check the files from the target folder.

2. Create parameters and set the values. Here it is going to be delete the files which are older then 3 days from the E:\ImagesBackup folder in this case.

3. Drag and drop File System Task

5. Put the parameters as ReadOnlyVariables

6. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not. Here you can see the parameters used in the script.

Please note here we need to import system.IO namespace.

7. Turn on final step and run package. Files older than specified retention period get deleted.

You can use the script below for the same as mentioned in above image,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");

foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS

2011-12-23T19:40:00.000+05:30

Earlier we have seen for the Delete file using Script Task and files deletion using File System Task in SSIS. We have directly wrote the logic to get the files and delete from specified folder in the script for the Script Task. For File System Task we have Used Foreach Loop Container and variables to hold file names which are passed from earlier stage one by one and then finally used with File System Task to delete it.

Here we have same but not to delete all the files inside passed folder but get deleted files which are older than specified days. This is nothing but minor change in script where we pass the folder path and retention period. Lets start the steps as following,

1. Let check the files from the target folder.

2. Drag and drop Script Task.

3. Open script editor from the properties.

3. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not.

Please note here we need to import system.IO namespace.

4. Turn on final step and run package. Files older than specified retention period get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = 3;
string directoryPath = @"E:\ImagesBackup";
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.png");
     foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Archive old database backup files using TSQL Script - SQL Server

2011-12-16T19:43:00.002+05:30

I experienced into one issues for database backups were failed. And this was due to space issues on disk drive. This disk drive is spacific to allocated for the database backups only.

The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all files and continue this activity so many days.If you create a maintenance plans then it have option to delete old backups files. But i have the stored procedure for the database backups. So i do not have option to delete old and unused database backup files.

Finally, I have created a script to clean those old backups. I have created one stored procedure in which you need to pass there parameters, One is Backup type as want to delete full, differential or transaction log backups. Second is From days and third one is End day.

Please make sure XP_CMDSHELL is enabled in database instance as thisis require to enable it to delete database backup files to be deleted physically.
Here id query to enable it.

USE MASTER
GO

EXEC SP_CONFIGURE 'show advanced options',1
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE
GO

I have already told you as i have created script to delete the old DATABASEPROPERTY backups, please find below SP for the same.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteDBBackupOldFiles]
@BackupType char(1) = 'D', -- 'D'-Full, 'I'-Differential, 'L'-Log backup type
@StartDayFromToday int,
@EndDayFromToday int
AS
BEGIN
SET NOCOUNT ON

DECLARE @IsFileExists int
DECLARE @DeletedFile VARCHAR(500)
DECLARE @OldFiles VARCHAR(500)

DECLARE OldFiles CURSOR FAST_FORWARD  FOR
SELECT 
bmf.physical_device_name
from msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE DEVICE_TYPE = 2 
AND TYPE = @BackupType 
AND BACKUP_START_DATE < = GETDATE() - @StartDayFromToday
AND BACKUP_START_DATE > = GETDATE() - @EndDayFromToday
-- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday 

OPEN OldFiles

FETCH NEXT FROM OldFiles INTO @OldFiles

WHILE @@FETCH_STATUS =0
BEGIN

EXEC XP_FILEEXIST @OldFiles,@IsFileExists OUTPUT
IF @IsFileExists=1
BEGIN

PRINT 'Deleted File : ' + @DeletedFile

SET @DeletedFile = 'DEL ' + @OldFiles

EXEC XP_CMDSHELL @DeletedFile

END
FETCH NEXT FROM OldFiles INTO @OldFiles
END
CLOSE OldFiles
DEALLOCATE OldFiles
END

Finally after creating a stored procedures , it is time to run and clean the old database backups. After running it will remove all the old database baclups as per specified parameters. Like we have passed 'D',3 and 10 with SP, So i will delete Full database backups whicl are older then 3 days ago and 10 days before created.

EXEC [DeleteDBBackupOldFiles] 
  @BackupType  = 'D',
  @StartDayFromToday = 3,
  @EndDayFromToday = 10

Which method you are using for old databsee backup maintenance? You can read my earlier posts for delete files using File System Task and Script Task in SSIS.

Delete files from specified folder using Script Task in SQL Server - SSIS

2011-12-14T19:47:00.002+05:30

Earlier we have seen for the files deletion using File System Task in SSIS. We have used it with Foreach Loop Container. Used a variable to hold file names which are passed from earlier stage one by one and then finally used with File System Task to delete it.

Now i am going to use Script Task to delete all files from specified folder. Here i have added script to get each files from specified folder and then delete them as you can see in the following steps. Lets start to follow them.

1. Drag and drop Script Task.

2. Open script editor from the properties.

3. Apply attached script in editor and save it.

Please note here we need to import system.IO namespace.

4. Turn on final step and run package. Files get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here

 string directoryPath = @"E:\TestFolder";
            string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.txt");
            foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                currFileInfo.Delete();
              
            }

Hope you liked this post. Stay tuned for more.

Delete files from specified folder using File System Task in SQL Server - SSIS

2011-12-10T18:58:00.002+05:30

We know all the various methods to delete the particular or all files from the specified folder using some of the methods like xp_delete_file, Ole Automation Procedures and with xp_cmdshell command line utility which we used for the old files archive or cleanup purpose.

Here i am going to share some of the screens which delete the files with SSIS using File System Task. Let me share them one by one.

1. Folder having some test files.

2. Drag and drop Foreach Loop Container and File System Task. Foreach Loop Container used to get all the files inside that folder one by one and process with File System Task. Open the Foreach Loop Container properties, goto Collection tab and select Foreach File Enumerator as specified in screen below.

3. Select the folder from where need to delete the files. Also apply extension if you need.

4. For the file assignment we need one variable and the values allocated from Foreach Loop Container process. So assign it from variable mapping as per shot taken below.

5. Drag File System Task, select Delete File operation and define Source Connection.

6. Finally run package and files will get deleted.

My recent blog posts and just learned tips - SQL Server

2011-12-02T20:33:00.002+05:30

Recently I have posted some of the articles and some just learned from the month of October. This article just summarize all the posts which recently published. You can find the links below to go in the details.

Blog Posts:

Just Learned tips:

Hope these posts help you.

Backup Statistics and History - SQL Server

2011-11-30T19:46:00.000+05:30

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at other place and using for the database restore which used for reporting purpose or used in log shipping purpose. Because database backups are most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loses.

You can read my earlier posts for Database Backup Compression, Database Backup files Verification , Automated All Databases Backups Script and Split Database Full Backup to Multiple files.

I would like share the script which help us to show the database backups status, history of their when they are done based on schedule, at where are taking and when, backup types , backups physical device and size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statistics and status information.

USE MSDB
GO

SELECT 
bs.server_name AS Server, -- Server name
bs.database_name AS DatabseName , -- Database name
CASE bs.compatibility_level 
 WHEN 80 THEN 'SQL Server 2000'
 WHEN 90 THEN 'SQL Server 2005 '
 WHEN 100 THEN 'SQL Server 2008'
 WHEN 110 THEN 'SQL Server 2011'
END AS CompatibilityLevel , -- Return backup compatibility level
recovery_model AS Recoverymodel , -- Database recovery model
CASE bs.type 
 WHEN 'D' THEN 'Full' 
 WHEN 'I' THEN 'Differential' 
 WHEN 'L' THEN 'Log' 
 WHEN 'F' THEN 'File or filegroup' 
 WHEN 'G' THEN 'Differential file' 
 WHEN 'P' THEN 'P' 
 WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
bs.backup_start_date AS BackupstartDate, -- Backup start date
bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
CASE device_type
 WHEN 2 THEN 'Disk - Temporary'
 WHEN 102  THEN 'Disk - Permanent'
 WHEN 5  THEN 'Tape - Temporary'
 WHEN 105  THEN 'Tape - Temporary'
 else 'Other Device'
END AS DeviceType, -- Device type
bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
ORDER BY bs.backup_start_date DESC

GO

The transact-SQL for backupmediafamily and backupset are accordingly are http://msdn.microsoft.com/en-us/library/ms190284.aspx and http://msdn.microsoft.com/en-us/library/ms186299.aspx

Hope you liked this post.

NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server

2011-11-25T19:33:00.002+05:30

When we created a new database it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolatino level help for the same as ther are operating same. we have some another options other than this. But i am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

For NOLOCK , we need to put this hint on table level, so it is require to put for every tables level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere tables refers in query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written at top of the query or stored procedure.

Let us look on small demo to elaborate it. First checking here database default isolation level

USE DEMO
GO
DBCC USEROPTIONS

Starting with creating database and table objects.

IF (OBJECT_ID('TrnTable','U') > 0)
DROP TABLE TrnTable

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO

Now for the demo we will run the below script with session 1

-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction

After that we will get the same rows which are updating in above session which are not committed yet in another session. It will be go on waiting to release the lock held by session 1.

We are not closing this transacrion here , and created a new session and run following scripts having NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.

-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

Do not forget to commit or rollback transaction TRAN1
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?

"Can not add a shared registered server with the same name as configuration Server" - Central Management Servers register error in SQL Server 2008

2011-11-19T19:10:00.000+05:30

You may know how can we register SQL Server instances with Central Management Servers (CMS) and also how can we perform multi server query will all of instances of registered SQL Server instances.

There you can see i have registered one shared SQL Server 2011 instance under CMS and named it Denali. Under Shared instance i have registered SQL Server 2008 and same SQL Server instance of Denali which i already registered.

Here SQL server 2008 successfully registered but SQL Server 2011 has encountered error. But how i have registered it which i am going to explain here.

This is because same SQL instance already registered as shared SQL server.

How can i register same SQL Server Denali or 2011 instance again?

I have changed the port and applied static port as following from SQL Server TCP/IP properties which we will available at SQL Server Configuration Monitor.

Then please see the screen shot below as i have register SQL Server Denali instance with port.

Now both SQL Server registered successfully.

Did you get it earlier? How did you resolved?

Database Backup Compression, Amazing feature for DBA - SQL Server 2008

2011-11-16T19:44:00.001+05:30

Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more tome to execute and very CPU, memory and IO consumptive. And important thing is backups activity should be completed with in down time or pick time when more users are not connected with databases.

What is solution?
We have alternative ways to use the some backup tool that can help to use in this matter.
But SQL Server itself provide the best feature and supported SQL Server 2008 or newer version.
That is "Backup Compression". You can read my earlier posts for Automated All Databases Backups, Database Backup files Verification and Details and Split Database Full Backup to Multiple files

Using this feature , we can take a database backups with compression option. And will really reduce time required to backup it, reduce server IO and less CPU and memory consumptive.
It is veryfull feature for the DBA.

Let us look on below example which will clear you the difference between the noncompressed and compressed backups.We will first perform non-compressed backups for the database which have 4 GB size.

Using Management Studio :

Using TSQL:

#1. Performing noncompressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_NonCompressedBackup.bak' 
WITH NAME = N'ReportServer-Full NonCompressed Database Backup',
NO_COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

#2. Performing compressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_CompressedBackup.bak' 
WITH NAME = N'ReportServer-Full Compressed Database Backup',
COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

From the result oputput , you can view the time for the backup execution, CPU usage.
Here you have screen for the both of the backups size.

You can use below query to get the backup statistics,

SELECT 
bs.database_name AS DatabaseName , -- Database name
backup_size/compressed_backup_size as CompressionRatio, 
CASE bs.type 
 WHEN 'D' THEN 'Full' 
 WHEN 'I' THEN 'Differential' 
 WHEN 'L' THEN 'Log' 
 WHEN 'F' THEN 'File or filegroup' 
 WHEN 'G' THEN 'Differential file' 
 WHEN 'P' THEN 'P' 
 WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
bs.backup_start_date AS BackupstartDate, -- Backup start date
bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
and database_name = 'ReportServer'
ORDER BY bs.backup_start_date DESC

You can set the default backup setting to Compressed as following,

By TSQL :

USE MASTER
GO

EXEC SP_CONFIGURE 'backup compression default', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

From UI :

I hope you liked this feature..

Review of some replication issues and workaround - SQL Server

2011-11-05T18:24:00.000+05:30

After posting some snaps to configure transactional replication in sql server having publication server as distributor, i want to share some of the issue which i got during working with it.

You may also faced the same issue and got resolved as well. You can comment if you faced any other replication issues and the solution for the same. I have shared some of them as Just learned on http://beyondrelational.com.

1."There is insufficient system memory to run this query" - Log reader agent error for subscriber database :

During working with replication this is one of the error which i encountered and get resolved it. This is coming with log reader agent and it stopped working. It's due to the issue memory. i have flushed the unused memory consumed by Adhoc queries and system cache.

The error looks, "Error messages:
There is insufficient system memory to run this query. (Source: MSSQL_REPL, Error number: MSSQL_REPL27453)"

To flush the memory use the below commands,
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')

You can read the same tips here.

2. "Could not find stored procedure 'sp_MSins_TableName' in replication in sql server" - Log reader agent error:

It came after reinitialize all subscription for one publication and it stopped working with error,

"Could not find stored procedure 'sp_MSins_TableName' in replication in sql server"

This is due to replication object was not created for insert and named like 'sp_MSins_TableName'. The solution for this issue, we need to create these missing replication objects using 'sp_scriptpublicationcustomprocs' which generates scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication. Workaround for the solution is,

EXEC sp_scriptpublicationcustomprocs 'PublicationName'

After running generated scripts in subscriber database, log reader agent started to work. You may read same tip here.

3."The row was not found at the Subscriber when applying the replicated command" :

It encountered it with log reader agent and it was stopped due to error as,

"Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000CAB2A00038CB6003B00000000, Command ID: 2)
Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)"

Row conflict issue was occurred due to some of the tables which we can get the details with following query to reach towards solution.

SELECT
*
FROM dbo.MSarticles m
WHERE EXISTS
(
SELECT mc.Article_id from MSrepl_commands mc
WHERE mc.xact_seqno = 0x000CAB2A00038CB6003B00000000
and mc.Article_id = m.article_id
)

You can read it in details here. I will share more replication errors if will get in future.

Multi Monitor Support of SSMS - A new feature of SQL Server Denali

2011-11-03T21:18:00.001+05:30

I have drafted all the new features and enhancements introduced by SQL Server denali CTP1 and CTP3. Multi Monitor Support is the new feature among them.

We can do with this feature:
1. Using this feature we can use multi screen of query analyzer or editor.
2. You can monitor separately it and run the output individually.
3. You can resize all the screens.
4. You can drag and drop at the place you want.
5. You can use Registered servers and object explorer and it's details with multi screens.

For more idea, You can see the below screen shows which i have captured during enjoyed with it.

1. This screen shot have multi screens of query analyzer with SSMS.

2. This screen shot captured during docking/undocking the screens.

Hope you liked this post.

"The database owner SID recorded in the master database differs from the database owner SID recorded in database" - SQL CLR DLL Register error in SQL Server

2011-10-27T05:08:00.001+05:30

Recently while working with SQL CLR functionality and created DLL for the SQL CLR.
But while registering this DLL in the database i got one surprised error.

Lt's show you the script so you have more idea. We have a script to register DLL as following,

SP_CONFIGURE 'clr enabled',1
GO
RECONFIGURE
GO

USE SQLCLRDb 
GO

CREATE ASSEMBLY [SQLCLR_ASSEMBLY] 
FROM 'C:\SQLCLR_ASSEMBLY.dll' WITH permission_set = UNSAFE 

GO

And the error is coming like

The database owner SID recorded in the master database differs from the database owner SID recorded in database.
You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement.

The solution for this issue is which we have the script below. This script will change db owner of the running database and make it trust worthy on.

USE SQLCLRDb 
GO

ALTER DATABASE SQLCLRDb SET TRUSTWORTHY ON
go

EXEC SP_CHANGEDBOWNER 'UserName'
GO

After running above query, i come out from the issue and registered SQL CLR DLL successfully.

I think you also suffered same or different issues with SQL CLR. Please comment your issues and the solution for the same.

Database Backup files Verification and Details - SQL Server

2011-10-22T09:31:00.000+05:30

As a best practice, DBA need to verify each database backups are properly done or not, also make sure the backups are OK then is readable and can be restored. Because so many databases are scheduled as FULL and differential backups weekly/daily and transaction log backups on every hours or whatever as per requirement performing to avoing data loss.

You can read my earlier articles for script to automated all types of database backup and split database backup to multiple files.

How can we verify the backup files?
SQL Server provide VERIFYONLY clause and we can use it with Restore command. Please see the below details for the same as how it works.

#1. VERIFYONLY
Verify databse backup integrity and checking backups are currupted or not.
Per SQL Book online, Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable.
However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical. For more information, see the Remarks.

If the backup is valid, the SQL Server Database Engine returns a success message.

Let us run the query to verify FULL, Differential and Transactional log backups and will see the output come out from it.

RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

How can we get the backup files details?
Using HEADERONLY with Restore command we have details for database backup files.
Let us run the query to verify FULL, Differential and Transactional log backups and will see the output come out from it.

#2. HEADERONLY
Per SQL Book online, Returns a result set containing all the backup header information for all backup sets on a particular backup device.

RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

Here another command FILELISTONLY which will the logical file and physical files details of backup files.

#3. FILELISTONLY
Per SQL Book online, Returns a result set containing a list of the database and log files contained in the backup set.

RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

What you are performing activity with backups?

Grouping Sets vs Native method of Group By - Performance review in SQL Server 2008

2011-10-16T08:56:00.000+05:30

Previously i have posted for the overview and usage of the Grouping Sets as how can we get the aggregate data of different group sets with GROPING SETS vs Native method of group by.

If you have not read my earlier post for the same then please read it before go ahead with this demonstration. In this demo i am going to show the performance of Grouping Sets and native method of group by.

Let us start the demo here.

-- Creatind database and table 
CREATE DATABASE GroupingDB

GO

USE GroupingDB

GO

IF (OBJECT_ID('GroupingTable') > 0)
DROP TABLE GroupingTable

CREATE TABLE GroupingTable
  (
     MainCategoryName VARCHAR(100)
     ,SubCategoryId   VARCHAR(100)
     ,VALUE           BIGINT
  )

GO

Now we are going to insert so many records in table for the presentation of demo.

-- Insert some demo records for diffrent group sets
INSERT INTO GroupingTable
SELECT 'Main-1','Sub-1',100
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-1','Sub-2',200
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-2','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-2',400
GO 500

As i said to check the performance of both of the script using them and check the execution plan.

-- Get aggregate data using native method of group by of different sets
SELECT NULL, NULL,
  SUM(VALUE) as Total
FROM   GroupingTable
Union all
SELECT  MainCategoryName
,NULL
,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY MainCategoryName 
Union all
SELECT  NULL,
  SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY SubCategoryId 
union all
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
  MainCategoryName
  ,SubCategoryId

-- Get aggregate data using Grouping Sets of different sets
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

You can see the data rows of above both scripts are same and given same result set.
Now look for execution plan.

This performance review is totally based on the data and depends on the your business requirement.
Before implementing this new feature please check the execution and decide you view.

Multi server Query with Central Management Servers - SQL Server 2008

2011-10-02T18:01:00.000+05:30

Before SQL Server 2008 , when we need gather all information and details related to server or database level, we must run the script individually by connecting each SQL Server instances.

But SQL Server 2008 came up and easy our work for that. It has introduced a new feature - Central Management Servers (CMS). With Central Management Servers we can configure and register SQL Server instances with shared SQL Server instance. Then we run the query against all the SQL instance and get details for all instances.

Let's you demonstrate the same in details here.

1. How to open Central Management Servers?

Go to View --> Registered Servers
or
press Ctrl + Alt + G.

2. How can register SQL Server instances in CMS?

Expand Database engine from Registered Servers.

Right click on CMS and click on Register SQL Server Management.

The screen will appear like below,

In above i have registered SQL Server Denali instance which will be shared SQL Server instance.

Now I am creating new SQL Server Group under CMS and then register SQL Server 2008 and SQL Server 2011 by right click on group and then go on the link of registration and then go on same way as i did for SQL Server Denali instance.

3. How can we perform multi server query against all SQL Server ?

Go on right click on Shared SQL Server instance under CMS and click on New Query.

Let's do here same and execute the query and see what will be the result?

This feature very help us to run the script against all the registered SQL Server instances. Hope you liked this post.

How to invoke Job thorugh SQL script - SQL Server

2011-09-29T19:15:00.000+05:30

We are mostly create the SQL job and schedule it to run automated. But sometime as per requirement we need to invoke and run on demand basis, not on schedules basis.

I have did the same thing and the following is the small demo ready for you.

Let's create one stored procedure and schedule in job.

USE [Master]
GO

CREATE PROCEDURE JobInvokeProc
AS
BEGIN
SET NOCOUNT ON

WAITFOR DELAY '00:00:15';

END
GO

Now we will create the job for the same and execute this stored procedure in job.

The created script for the job is as following,

USE [msdb]
GO

/****** Object:  Job [Start_JobInvokeProc]    Script Date: 05/05/2011 07:51:53 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/05/2011 07:51:53 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Start_JobInvokeProc', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'[Uncategorized (Local)]', 
  @owner_login_name=N'paresh-PC\paresh', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step_JobInvokeProc]    Script Date: 05/05/2011 07:51:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_JobInvokeProc', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'exec JobInvokeProc', 
  @database_name=N'master', 
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Now i have one more we i get from MSDB database and customize it which is used to get the SQL job status information as running or not.

This is the script to get the Job status.

USE [master]
GO

CREATE PROCEDURE GetJobStatus     
  @job_Name           varchar(max),     
  @status INT OUTPUT    
AS      
BEGIN      
  DECLARE @can_see_all_running_jobs INT ,    
  @job_type           VARCHAR(12)     ,  -- LOCAL or MULTI-SERVER      
  @owner_login_name   sysname         ,      
  @subsystem          NVARCHAR(40)    ,      
  @category_id        INT             ,      
  @enabled            TINYINT         ,      
  @execution_status   INT             ,  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions      
  @date_comparator    CHAR(1)         ,  -- >, < or =      
  @date_created       DATETIME        ,      
  @date_last_modified DATETIME        ,      
  @description        NVARCHAR(512)   ,  -- We do a LIKE on this so it can include wildcards      
  @schedule_id        INT                 
     
  set @job_type           = NULL  -- LOCAL or MULTI-SERVER      
  set  @owner_login_name  = NULL      
  set @subsystem          = NULL      
  set @category_id        = NULL      
  set @enabled            = NULL      
  set @execution_status   = NULL  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions      
  set @date_comparator    = NULL  -- >, < or =      
  set @date_created       = NULL      
  set @date_last_modified = NULL      
  set @description        = NULL  -- We do a LIKE on this so it can include wildcards      
  set @schedule_id        = NULL     
    
  DECLARE @job_owner   sysname      
      
  declare @job_id UNIQUEIDENTIFIER     
  set @job_id = (select job_id from msdb.dbo.sysjobs where name = @job_Name)    
    
  SET NOCOUNT ON      
      
  -- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.      
  -- This proc should only ever be called by sp_help_job, so we don't verify the      
  -- parameters (sp_help_job has already done this).      
      
  -- Step 1: Create intermediate work tables      
  DECLARE @job_execution_state TABLE (job_id                  UNIQUEIDENTIFIER NOT NULL,      
                                     date_started            INT              NOT NULL,      
                                     time_started            INT              NOT NULL,      
                                     execution_job_status    INT              NOT NULL,      
                                     execution_step_id       INT              NULL,      
                                     execution_step_name     sysname          COLLATE database_default NULL,      
                                     execution_retry_attempt INT              NOT NULL,      
                                     next_run_date           INT              NOT NULL,      
                                     next_run_time           INT              NOT NULL,      
                                     next_run_schedule_id    INT              NOT NULL)      
  DECLARE @filtered_jobs TABLE (job_id                   UNIQUEIDENTIFIER NOT NULL,      
                               date_created             DATETIME         NOT NULL,      
                               date_last_modified       DATETIME         NOT NULL,      
                               current_execution_status INT              NULL,      
                               current_execution_step   sysname          COLLATE database_default NULL,      
                               current_retry_attempt    INT              NULL,      
                               last_run_date            INT              NOT NULL,      
                               last_run_time            INT              NOT NULL,      
                               last_run_outcome         INT              NOT NULL,      
                               next_run_date            INT              NULL,      
                               next_run_time            INT              NULL,      
                               next_run_schedule_id     INT           NULL,      
                               type                     INT              NOT NULL)      
  DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,      
                            last_run_date         INT              NOT NULL,      
                            last_run_time         INT              NOT NULL,      
                            next_run_date         INT              NOT NULL,      
                            next_run_time         INT              NOT NULL,      
                            next_run_schedule_id  INT              NOT NULL,      
                            requested_to_run      INT              NOT NULL, -- BOOL      
                            request_source        INT              NOT NULL,      
                            request_source_id     sysname          COLLATE database_default NULL,      
                            running               INT              NOT NULL, -- BOOL      
                            current_step          INT              NOT NULL,      
                            current_retry_attempt INT              NOT NULL,      
                            job_state             INT              NOT NULL)      
      
  -- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)      
  SELECT @can_see_all_running_jobs = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)      
  IF (@can_see_all_running_jobs = 0)      
  BEGIN      
    SELECT @can_see_all_running_jobs = ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0)      
  END      
  SELECT @job_owner = SUSER_SNAME()      
      
  IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater      
    INSERT INTO @xp_results      
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id      
  ELSE      
    INSERT INTO @xp_results      
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner      
      
  INSERT INTO @job_execution_state      
  SELECT xpr.job_id,      
         xpr.last_run_date,      
         xpr.last_run_time,      
         xpr.job_state,      
         sjs.step_id,      
         sjs.step_name,      
         xpr.current_retry_attempt,      
         xpr.next_run_date,      
         xpr.next_run_time,      
         xpr.next_run_schedule_id      
  FROM @xp_results                          xpr      
       LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),      
       msdb.dbo.sysjobs_view                sjv      
  WHERE (sjv.job_id = xpr.job_id)      
      
  -- Step 3: Filter on everything but dates and job_type      
  IF ((@subsystem        IS NULL) AND      
      (@owner_login_name IS NULL) AND      
      (@enabled          IS NULL) AND      
      (@category_id      IS NULL) AND      
      (@execution_status IS NULL) AND      
      (@description      IS NULL) AND      
      (@job_id           IS NULL))      
  BEGIN      
    -- Optimize for the frequently used case...      
    INSERT INTO @filtered_jobs      
    SELECT sjv.job_id,      
           sjv.date_created,      
           sjv.date_modified,      
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)      
           CASE ISNULL(jes.execution_step_id, 0)      
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'      
           END,      
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)      
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)      
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)      
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0   -- type placeholder             (we'll fix it up in step 3.4)      
    FROM msdb.dbo.sysjobs_view                sjv      
         LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)      
    WHERE ((@schedule_id IS NULL)      
      OR   (EXISTS(SELECT *       
                 FROM msdb.dbo.sysjobschedules as js      
                 WHERE (sjv.job_id = js.job_id)      
                   AND (js.schedule_id = @schedule_id))))      
  END      
  ELSE      
  BEGIN      
    INSERT INTO @filtered_jobs      
    SELECT DISTINCT      
           sjv.job_id,      
           sjv.date_created,      
           sjv.date_modified,      
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)      
           CASE ISNULL(jes.execution_step_id, 0)      
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'      
           END,      
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)      
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)      
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)      
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0   -- type placeholder             (we'll fix it up in step 3.4)      
    FROM msdb.dbo.sysjobs_view                sjv      
         LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)      
         LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)      
    WHERE ((@subsystem        IS NULL) OR (sjs.subsystem            = @subsystem))      
      AND ((@owner_login_name IS NULL)       
          OR (sjv.owner_sid            = msdb.dbo.SQLAGENT_SUSER_SID(@owner_login_name)))--force case insensitive comparation for NT users      
      AND ((@enabled          IS NULL) OR (sjv.enabled              = @enabled))      
      AND ((@category_id      IS NULL) OR (sjv.category_id          = @category_id))      
      AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))      
                                       OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))      
      AND ((@description      IS NULL) OR (sjv.description       LIKE @description))      
      AND ((@job_id           IS NULL) OR (sjv.job_id               = @job_id))      
      AND ((@schedule_id IS NULL)      
        OR (EXISTS(SELECT *       
                 FROM msdb.dbo.sysjobschedules as js      
                 WHERE (sjv.job_id = js.job_id)      
                   AND (js.schedule_id = @schedule_id))))      
  END      
      
    
    
  -- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'      
  UPDATE @filtered_jobs      
  SET current_execution_status = NULL      
  WHERE (current_execution_status = 4)      
    AND (job_id IN (SELECT job_id      
                    FROM msdb.dbo.sysjobservers      
                    WHERE (server_id <> 0)))      
      
  -- Step 3.2: Check that if the user asked to see idle jobs that we still have some.      
  --           If we don't have any then the query should return no rows.      
  IF (@execution_status = 4) AND      
     (NOT EXISTS (SELECT *      
                  FROM @filtered_jobs      
                  WHERE (current_execution_status = 4)))      
  BEGIN      
    DELETE FROM @filtered_jobs      
  END      
      
  -- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for      
  --           multi-server jobs there are multiple last run details in sysjobservers, so      
  --           we simply choose the most recent].      
  IF (EXISTS (SELECT *      
              FROM msdb.dbo.systargetservers))      
  BEGIN      
    UPDATE @filtered_jobs      
    SET last_run_date = sjs.last_run_date,      
        last_run_time = sjs.last_run_time,      
        last_run_outcome = sjs.last_run_outcome      
    FROM @filtered_jobs fj,      
         msdb.dbo.sysjobservers sjs      
    WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =      
           (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)      
            FROM msdb.dbo.sysjobservers      
            WHERE (job_id = sjs.job_id))      
      AND (fj.job_id = sjs.job_id)      
  END      
  ELSE      
  BEGIN      
    UPDATE @filtered_jobs      
    SET last_run_date = sjs.last_run_date,      
        last_run_time = sjs.last_run_time,      
        last_run_outcome = sjs.last_run_outcome      
    FROM @filtered_jobs         fj,      
         msdb.dbo.sysjobservers sjs      
    WHERE (fj.job_id = sjs.job_id)      
  END      
      
  -- Step 3.4 : Set the type of the job to local (1) or multi-server (2)      
  --            NOTE: If the job has no jobservers then it wil have a type of 0 meaning      
  --                  unknown.  This is marginally inconsistent with the behaviour of      
  --                  defaulting the category of a new job to [Uncategorized (Local)], but      
  --                  prevents incompletely defined jobs from erroneously showing up as valid      
  --                  local jobs.      
  UPDATE @filtered_jobs      
  SET type = 1 -- LOCAL      
  FROM @filtered_jobs         fj,      
       msdb.dbo.sysjobservers sjs      
  WHERE (fj.job_id = sjs.job_id)      
    AND (server_id = 0)      
  UPDATE @filtered_jobs      
  SET type = 2 -- MULTI-SERVER      
  FROM @filtered_jobs         fj,      
       msdb.dbo.sysjobservers sjs      
  WHERE (fj.job_id = sjs.job_id)      
    AND (server_id <> 0)      
      
  -- Step 4: Filter on job_type      
  IF (@job_type IS NOT NULL)      
  BEGIN      
    IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'LOCAL')      
      DELETE FROM @filtered_jobs      
      WHERE (type <> 1) -- IE. Delete all the non-local jobs      
    IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'MULTI-SERVER')      
      DELETE FROM @filtered_jobs      
      WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs      
  END      
      
  -- Step 5: Filter on dates      
  IF (@date_comparator IS NOT NULL)      
  BEGIN      
    IF (@date_created IS NOT NULL)      
    BEGIN      
      IF (@date_comparator = '=')      
        DELETE FROM @filtered_jobs WHERE (date_created <> @date_created)      
      IF (@date_comparator = '>')      
        DELETE FROM @filtered_jobs WHERE (date_created <= @date_created)      
      IF (@date_comparator = '<')      
        DELETE FROM @filtered_jobs WHERE (date_created >= @date_created)      
    END      
    IF (@date_last_modified IS NOT NULL)      
    BEGIN      
      IF (@date_comparator = '=')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified <> @date_last_modified)      
      IF (@date_comparator = '>')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified <= @date_last_modified)      
      IF (@date_comparator = '<')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified >= @date_last_modified)      
    END      
  END      
      
  -- Return the result set (NOTE: No filtering occurs here)      
  SELECT @status = ISNULL(fj.current_execution_status, 0)           -- This column will be NULL if the job is non-local      
            
  FROM @filtered_jobs fj      
       LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (fj.job_id = sjv.job_id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so1 ON (sjv.notify_email_operator_id = so1.id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so2 ON (sjv.notify_netsend_operator_id = so2.id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so3 ON (sjv.notify_page_operator_id = so3.id)      
       LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sjv.category_id = sc.category_id)      
  ORDER BY sjv.job_id      
      
return @status    
END      
  
GO

Now will run the script to invoke the SQL job through analyzer and will see the SQL job status.

Let's run the below code and see the output

USE [master]
GO

-- Get the job status before invoke the job 
DECLARE @status INT  
DECLARE @JobName VARCHAR(500)  

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 
------------------------------------------
-- Run this query to invoke the job
EXEC msdb.dbo.Sp_start_job @JobName  
------------------------------------------
-- Get the job status after invoke the job
WAITFOR DELAY '00:00:3';

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus  
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 
GO

When you run the script again while executing the job, It will raise the error while invoke it again.

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Start_JobInvokeProc (from User paresh-PC\paresh) refused because the job is already running from a request by User paresh-PC\paresh.

So better option is we need to check the job status first then we can invoke.

The revised script is,

USE [master]
GO

/* Get the job status before invoke the job */
DECLARE @status INT  
DECLARE @JobName VARCHAR(500)  

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 

-- Added condition here to check the job is already running or not.
IF (@status <> 1)
BEGIN
/* Run this query to invoke the job*/
EXEC msdb.dbo.Sp_start_job @JobName  
END
GO

How to reset SQL Server all default settings in SQL Server Denali CTP3?

2011-09-26T19:47:00.000+05:30

Problem:

Before a few days ago i have installed SQL Server Denali CTP3. Then i have querying in query analyzer and found, minimizing result is not working with CTRL+R is not working. Before CTP3 i have already Denali CTP1 installed in my machine and it was working perfectly, So it stopping to work in SQL Server CTP3.The default SQL Server settings was changed during this installation.

One of the solution:

I did not find any option to resolve it. Then i found one solution to reset these setting which are changed during Denali CTP3 installation. Below are some snaps which i performed to recover it back.

Step 1.

Step 2.

Step 3.

Step 4.

Step 5.

After completion of all above steps, i recovered my SQL Server default setting and CTRL+R working perfect now with CTP3.

You may also faced this issue. I have this solution, You have which one?