Showing posts from 2011

How to grant update permission to specific columns in a table in sql?

Do you know how to grant select permissions on TBL_XXXXXX table in XXX instance in XXXX server. And also grant update permissions on the same table at column level for the columns columnname1 and columnname2.?
By executing below query, select permission granted to the table ‘TBL_XXXXXX’ for the user ‘databaseusername’.
By executing below query, update permission granted to the specific columns in a table ‘TBL_XXXXXX’ for the user ‘databaseusername’
GRANTUPDATE(columnname1)ON [TBL_XXXXXX] TO databaseusername
GRANTUPDATE(columnname2)ON [TBL_XXXXXX] TO databaseusername
That’s it.

How to resync the existing log shipping setup:

1. Take full backup of Primary database in the primary server.
2. Copy to secondary server database backup location drive.
3. Disable all jobs (Backup job in primary server, Copy and Restore jobs in secondary server)
To change secondary datbase into online.
Restore database secondarydatabasename With recovery
4. Restore the secondary server database using primary server database backup file with standby /readonly or with norecovery mode.
5. Enable        ->Backup job first        ->Copy job Next        ->Restore job last.
6. View successive schedules for successful job history.
That’s it
Hope you will re configure the existing non-sync log shipping setup into sync mode.

DTS Packages Error log details Findings.

How to view dtspackage error log in a table?
Select * from msdb..sysdtssteplog order by endtime desc Select * from msdb..sysdtstasklog order by endtime desc
Select * from msdb..sysdtspackagelog order by logdate desc
How to find dtspackage owner name ?
Select * from msdb..sysdtspackages where name ='dtspackagename'

My MCTS certificate.

I have done my MCTS certification .

If anybody need assistance in doing certifications,Please contact me .

Steps to find the Cpu cores and Cpu Sockets in SQL 2000, SQL2005 and Sql2008 through Powershell in SQL2008.

In SQL Server Management Studio 2008 (executed on the server where you want the information) right click anywhere in the object explorer tree under that server and select Start PowerShell.  Then paste the command I showed below.  Alternatively you can just run Powershell.exe on the server, and paste that command and get the desired results
Let’s write function named GetCpuinfo.

function GetCPUinfo {
    param ([array]$servernames = ".")
    foreach ($servername in $servernames) {
        [array]$wmiinfo = Get-WmiObject Win32_Processor -computer $servername
        $cpu = ($wmiinfo[0].name) -replace ' +', ' '
        $description = $wmiinfo[0].description
        $cores = ( $wmiinfo | Select SocketDesignation | Measure-Object ).count
        $sockets = ( $wmiinfo | Select SocketDesignation -unique | Measure-Object ).count
        Switch ($wmiinfo[0].architecture) {
            0 { $arch = "x86" }
            1 { $arch = "MIPS" }

# To find the number of CPU Total Cores (Processor count) in the SQL through query analyzer in Sql2000, Sql2005 and Sql2008.

# To find the number of CPU Total Cores (Processor count) in the SQL through query analyzer in Sql2000, Sql2005 and Sql2008.
Run the XP_MSVER in the query analyzer.

Determining the Space Used by all tables in a Database?

Determining the Space Used by all tables in a Database
Lets create one temporary table to store the output of stored procedure ‘SP_MSFOREACHTABLE’.
Insert the stored procedure ‘SP_MSFOREACHTABLE ‘ results by using below command.
It will insert below columns in the table #tbl_SPACETABLE.
namenvarchar(128)Name of the object for which space usage information was requested.
The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.rowschar(11)Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.r…

Replication Error (Source: MSSQLServer, Error number: 2627).

Error in the Replication monitor:
Command attempted: {CALL sp_MSins_dboXXXXX (2365938, '"Set PROCESSED=(2,-20) ON CREATEs WHERE SerID already exists in [subscriberdatabase]" completed:', {ts '2011-09-18 08:07:56.537'}, {ts '2011-09-18 08:07:56.537'}, NULL, NULL)} (Transaction sequence number: 0x00023911000035F3000300000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint 'PK_tablename-table'. Cannot insert duplicate key in object ‘Tablename-table''. (Source: MSSQLServer, Error number: 2627) Get help: http://help/2627
Resolutions Steps:
Go to subscriber database
Run below query.
1. Select * from [dbo].[Tablename-table] where rowid>=2365938
Then delete the records by executing below query.
delete * from [dbo].[Tablename-table] where rowid>=2365938
2. Finally restart the distribution job.
Replication was failing due to a "Primary Key violation error". We found certain entries in the subscriber, which were not marked …

Backup job failure issue.

Backup job failed with below error:
Msg 3202, Level 16, State 2, Line 1
Write on "H:\Backups\XXXX\XXX_FULL_20110926.BAK" failed: 33(The process cannot access the file because another process has locked a portion of the file.)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Resolution: As per below image, check the Disk properties for ‘Compress drive to save disk space’ is checked or not.
If yes, please uncheck and execute the backup query, it will execute successfully. Simultaneous running of both process (backup and drive compress process) cause the backup failure.

Large Log Ratio compared with data file size:

Large Log Ratio compared with data file size:

When log ratio is more than 2 times of data file size, how can we resolve to less size of log file than the data file as per database standard?
Run below query steps:
dbcc opentran
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator
backup log dbname with truncate_only
(If you run this above query, you are recommended to take full back up next after above query, if not it will break LSN sequence) otherwise you will get below error message during transaction log backup job operation.
System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)
if above query not working means, follow the below query
use master Go
alter database dbname
set recovery simple

alter database dbname set recovery full

use dbname dbcc shrinkfile(dbname_log,sizetoshrink)

That’s it.
Hope you will resolve large log ratio issue
How to find the data files and log files for those not in the proper drives as per database standards?
Data file check
selectname, physical_name fromsys.master_files wheretype= 0 andleft(physical_name,12)<>'A:\Data\SQL\'
Log file Check

selectname,database_id, physical_name fromsys.master_files wheretype= 1 andleft(physical_name,12)<>'B:\Logs\SQL\'
If any records found for the above query,if so seems to be some of the datafiles and log files not following database standard.
We need to change mdf and ldf to the appropriate path.

How to stop the log shipping alert which sending the error message 14421?

How to stop the log shipping alert which sending the below error message?

Error Message:

Over the previous interval, there were 5 occurrences of the following event.

Logfile: Application
Event Type: Error
Event ID: 14421
Computer: XXXXXXXX
Source: MSSQL$SQL2008
The log shipping secondary database Servername \SQL2008.Databasename has restore threshold of 45 minutes and is out of sync.
No restore was performed for 9426 minutes. Restored latency is 8 minutes. Check agent log and logshipping monitor information.


Database which involved in source as well in destination server for log shipping setup is not available. Because database dropped by somebody.


The database name entry is found in the below logshipping tables that are referencing the alert jobs LSAlert_ XXXXXXXX \SQL2008 and LSAlert_YYYYYYY\SQL2008 in both primary and secondary server.

Action Taken:
By removing the record entry in the below log shipping tables corresponding to the log shipping database, Alerts …
How to do any activity like select, delete or truncate for all tables in a databases?
Use databasename
Go sp_tables

Filter the above query output by TABLE_TYPE AS TABLE.
Then copy all table_name column values from the results in SSMS and paste it in the excel sheet columns.
Then join table_name with below statements in another excel column.
Lets take how to truncate all tables in a database.


Employeesdetails TRUNCATE TABLE  Employeesdetails

Any other method to do the above activity.

How to check existing job is scheduled or not in sql server?

How to check existing job is scheduled or not in sql server?
By running below stored procedure in sql server management studio,we can able to find job schedule details.
Exec msdb..sp_help_jobschedule @job_name='DBA Maintenance plan job’
If it shows row means, job scheduled else not.

Steps for connection related issues in sql server

Please make use of below steps for connection related issues in sql server
First check below ping information related to the specific server.
C:\Documents and Settings\username>ping servername
Pinging [12.345.67.890] with 32 bytes of data:
Reply from 12.345.67.890: bytes=32 time=96ms TTL=122 Reply from 12.345.67.890: bytes=32 time=95ms TTL=122 Reply from 12.345.67.890: bytes=32 time=95ms TTL=122 Reply from 12.345.67.890: bytes=32 time=96ms TTL=122
Ping statistics for 12.345.67.890:     Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds:     Minimum = 95ms, Maximum = 96ms, Average = 95ms -------------------------------------------------------------------------------- C:\Documents and Settings\ username >ping -a 12.345.67.890
Pinging servername [12.345.67.890] with 32 bytes of data:
Reply from 12.345.67.890: bytes=32 time=97ms TTL=122 Reply from 12.345.67.890: bytes=32 time=95ms TTL=122 Reply from 12.345.67.890: bytes=32 …

Duplicate records finding in all tables in a database.

Duplicate records finding in all tables in a database.

--To find all userdefined tables in the database.
Use databasename GO select*fromsysobjectswhere xtype='u'
--then copy the tables in excelsheet column and calculate using below formula in --another excel column
="select id from "&A1&" group by id where having count(*)>1 "
--Id is the primary key column name,A1 is the table cell name. Finally execute the all tables select query collections from the excel sheet to the database.
Select id fromemployeegroupby id  havingcount(*)>1 Select id fromemployee1 s groupby id  havingcount(*)>1 Select id fromemployee2groupby id  havingcount(*)>1 Select id fromemployee3groupby id  havingcount(*)>1
--if any records come,it seems to be presence of duplicate records…
Is any other method to find?

Give Access to check Activity monitor and to run SQL Profiler for non sysadmin login.

Scenario: Need to get permission on the server that will enable to see the activity monitor and to work with the SQL Server Profiler on sql server.
Those permission are needed for testing the work done on the Test DB -------------------------------------------------------------------------------------------- How to give access to check Activity monitor?
USE master; GRANTVIEWSERVERSTATE TO [windowslogin]
How to give access to run Sql Profiler on the server to one windows login i.e. non sysadmin?
USE master; GRANT ALTER TRACE TO [windowslogin]
Security tab --> Logins --> right click name to modify select properties --> Securables tab --> Click add --> Select the server you want to add the permissions to. The permissions should appear in the effective permissions list
You need to check the Grant column for the required permissions.

How to grant permission to view database objects definition?

Scenario: Customer asked DBA to grant permission to view recently created stored procedure script in SQL. How will we do?
Solution: For example the stored procedures sp1, sp2 and sp3 are created recently. User asking view access only for stored procedure objects .Not executes permission.
How will you do?
By using this query, we can do.
USE databasename GO GRANT VIEW DEFINITION ON sp1 TO sqlloginname; GRANT VIEW DEFINITION ON sp1 TO sqlloginname; GRANT VIEW DEFINITION ON sp3 TO sqlloginname;

View definition role allows user to view the corresponding objects
Any other methods?

Monitoring Database Activity by Job

How can we find the process happening to a particular database in SQL Server for a specific period of time?
1. Go to SQL SERVER AGENT ->JOBàNew JobàJob name as ‘Monitoring database activity’
2. Give owner as ‘sa’ or those who have permission to run job.

3. Then go to steps->Enter step name as ‘Monitoring database name’àSelect Type as ‘Transact SQL Script’ àSelect database as ‘Master’àCopy the commands available in the below box into Command square box in the job step.

CREATE TABLE #tbl_spwho2 (SPID VARCHAR(4),Status VARCHAR(30),Login VARCHAR(50),HostName VARCHAR(50),BlkBy VARCHAR(4),DBName VARCHAR(30),Command VARCHAR(100),CPUTime VARCHAR(15),DiskIO VARCHAR(15),LastBatch VARCHAR(50),ProgramName VARCHAR(100),SPID1 VARCHAR(4),RequestID INT)


ALTER TABLE #tbl_spwho2 ADD ProcessInfo VARCHAR(4000)


SET @spid = ''


Performance Tuning by creating index.

Lets take one stored procedure having 3 delete statements took 10 mins to complete execution.Customer asking to reduce the execution time.
Table size:2.5GB

As a DBA,What will we do?

1.First check for presence of index in the condidtional columns in the delete statements.

2.If no means,please create non-clustered index in the conditional coulmns in the tables available in the delete queries.

3.Then execute the stored procedure.

4.Now it will took 50seconds to finish execution.

5.Thats it.

Let us know for anyother ways?

How to resolve Error 22051?

Jobs failed with below error message.
Error Message: Executed as user: XXXXXX-svc. Mail queued. [SQLSTATE 01000] (Message 0)  File attachment or query results size exceeds allowable value of 1000000 bytes. [SQLSTATE 42000] (Error 22051).  The step failed.

Resolution Steps:
Go to Database Mail under Management in Sql Server Management Studio -->Select Configuration Task as View or Change system parameters -->Give Maximum File Size (Bytes) as to 1.5 million
That’s it
Now mail sending commands in the job will execute successfully.

How to change monitor server for existing log shipping setup database in sql server?

Proceed below steps to change monitor server into new one in existing log shipping setup for a database.
First log into publisher database server and Right Click Publisher Database->Click Properties- Select Transaction Log Shipping>Uncheck the Checkbox for ‘Enable this as a primary database in a log shipping configuration.
Secondly Check the Checkbox for ‘Enable this as a primary database in a log shipping configuration
Now we can able to edit the Monitor server instance name settings.
Here we will change monitor server into a new monitor server name. 
Click OK.
That’s it.
Check log shipping working fine or not?

How to backup database into less size using LiteSpeed query?

Use below script to back up the database ‘kumartest’.

exec master.dbo.xp_backup_database

@database = N'kumartest',

@compressionlevel = 1,

@filename = N'J:\SQLBackup\MSSQL\kumartest\kumartest_Full_201003041713.bak',

@init = 0,

@with = N'SKIP',

@with = N'STATS = 10'

INITàspecifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

SKIPàdisables the backup set expiration and name checking usually performed by the BACKUP statement to prevent overwrites of backup sets

STATS [= percentage] àDisplays a message each time another percentage completes, and is used to measure progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

Hope we will get compressed backups.