Kill Inactive Session using Cursor

 I had a requirement to select all the inactive session with more than 24 hours and kill the process where status is "Sleeping".

Below is the small Cursor which will select all inactive session where status is "sleeping" and kill the process based on the spid.



DECLARE @SPID VARCHAR(20)
DECLARE @STATUS VARCHAR(20)
DECLARE @QUERY VARCHAR(20)
DECLARE CUR CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE db_name(dbid)='Ashish' and STATUS='sleeping' and login_time<GETDATE()-1
OPEN CUR
FETCH NEXT FROM CUR INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY= 'KILL ' + @SPID
print (@QUERY)
EXEC (@QUERY)
FETCH NEXT FROM CUR INTO @SPID
END
CLOSE CUR
DEALLOCATE CUR

Service Account Permission Issue in Active Directory

Recently I faced an issue with Service account after enabling the service broker for one of the database.

Scenario: As per Client requirement I enabled the service broker for one of the database. After that there was huge Logs generating in SQL error Logs.

Error: The activated proc '[IdentityServerPolicy].[SqlQueryNotificationStoredProcedure-cdad7b08-c759-4d81-b4ef-c583cb6d45ca]' running on queue 'IdentityServerPolicy.SqlQueryNotificationService-cdad7b08-c759-4d81-b4ef-c583cb6d45ca' output the following:  'Could not obtain information about Windows NT group/user XXX\ABC, error code 0x5.'

I tried to run below command to get the details of service account.

Xp_Logininfo 'domain\account name'

But i got below error

Solution: The service account needs "Read permissions"in the active directory.

Open Active directory. Search for the login-> Right Click->Go to the properties->Select Security->Select "Authenticated Users"-> In bottom select "Read Permission" and click on the 'Allow' check box->
Apply->.Ok

After giving the permission I ran the same command and now i got the details about the service account.

Database Backup using Cursor

Some times it requires to take a backup of all the databases or more than one database which exist on your SQLinstance. There is a small cursor which will take a backup of all the database at a time to the specified location.
In the below Cursor you have to provide the path as where do you want to keep the backup files and the database name as per your requirement.
In the below example I am taking backup of all the user database to 'D:\Backup\' location.


DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
 DEALLOCATE db_cursor

Change owner of the Maintenance plan

Sometimes when we create a maintenance plan in SQL server, the owner of the maintenance plan will remain same as the user who creates the plan. If the user's account is deleted from the active directory, in that case the job associated with the maintenance plan will fail with below error:

Message
The job failed.  Unable to determine if the owner 
(domain/xxx_user) of job Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'domain/xxx_user', error code 0x5. [SQLSTATE 42000] (Error 15404)). 

To avoid such situation, change the owner of the maintenance plan to 'sa' by executing below update command when you creates the maintenance plan.

--SQL 2008 Server

UPDATE msdb.dbo.sysssispackages
SET [ownersid]=0x01
WHERE [name]='YourMaintPlanName'


--SQL 2005 Server

UPDATE [msdb].[dbo].[sysdtspackages90]
SET [ownersid] = 0x01 --sa user
WHERE [name] = 'YOUR_MAINT_PLAN_OR_PACKAGE'

--change the owner of a SQL Server 2000 Maintenance Plan

UPDATE [msdb].[dbo].[sysdbmaintplans]
SET [owner] = 'sa'
WHERE [plan_name] = 'YOUR_MAINT_PLAN'

--change the owner of a SQL Server 2000 DTS package
--you need to update the owner column as well

UPDATE [msdb].[dbo].[sysdtspackages]
SET [owner] = 'sa',
[owner_sid] = 0x01 --sa user
WHERE [name] = 'YOUR_DTS_PACKAGE'

cannot execute xp_cmdshell. Access is denied while copying data over the network

Today i faced a problem, i was trying to copy some of the files from one server to another server using xp_cmdshell, while executing it was giving error that "Access is denied while copying data over the network".

Here is the T-SQL that i was trying to execute.

Exec xp_cmdshell 'copy D:\SQLBACKUP\Test.bak \\TEST-10\SQLBACKUP'

Steps need to check
1. Check  xp_cmdshell is enabled or not  in server configuration

2. Check if required user has a permission on destination folder.

3. Third and important point is that SQL Server (MSSQLSERVER)  should run under some domain account(service account). It should not run under Local System.

If SQL service is running under Local system, xp_cmdshell will not be able to copy any files to the network location. xp_cmdshell can only copy to the local drive.