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’.

GRANT SELECT ON [TBL_XXXXXX] TO databaseusername

By executing below query, update permission granted to the specific columns in a table ‘TBL_XXXXXX’ for the user ‘databaseusername’

GRANT UPDATE(columnname1) ON [TBL_XXXXXX] TO databaseusername

GRANT UPDATE(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" }
            2 { $arch = "Alpha" }
            3 { $arch = "PowerPC" }
            6 { $arch = "Itanium" }
            9 { $arch = "x64" }
        }
        $manfg = $wmiinfo[0].manufacturer
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Servername -value $servername
        $obj | Add-Member Noteproperty CPU -value $cpu
        $obj | Add-Member Noteproperty Description -value $description
        $obj | Add-Member Noteproperty Sockets -value $sockets
        $obj | Add-Member Noteproperty Cores -value $cores
        $obj | Add-Member Noteproperty Architecture -value $arch
        $obj | Add-Member Noteproperty Manufacturer -value $manfg
        $obj
    }
}

·             # The Get-WMIObject cmdlet enables you to get any WMI object. You can also use Get-WMIObject to list out the WMI classes present to aid in discovery. #

Function is called with a listing of servers as the single parameter. If parameter is not specified, the local server "." is used.

$result = GetCPUinfo server1, server2, server3

the output can then be showed into a Format-Table or similar.

$result | format-table -auto

Output should be look like this.

# 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’.

CREATE TABLE #tbl_SPACETABLE(NAME VARCHAR(100),ROWS INT,RESERVED VARCHAR(100),DATA VARCHAR(100),INDEXSIZE VARCHAR(100),UNSUSED VARCHAR(100))

Insert the stored procedure ‘SP_MSFOREACHTABLE ‘ results by using below command.

INSERT INTO #tbl_SPACETABLE EXEC SP_MSFOREACHTABLE @COMMAND1="EXEC SP_SPACEUSED '?' "

It will insert below columns in the table #tbl_SPACETABLE.

name	nvarchar(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.
rows	char(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.
reserved	varchar(18)	Total amount of reserved space for objname.
data	varchar(18)	Total amount of space used by data in objname.
index_size	varchar(18)	Total amount of space used by indexes in objname.
unused	varchar(18)	Total amount of space reserved for objname but no yet used.

SP_SPACEUSED stored procedure is used to find the space used by objects in

a database.

? is passed as argument for sp_spaceused stored procedure to retrieve space for all tables in a database.

Fetch the space used by all tables in a database by runnning below query

SELECT * FROM #tbl_SPACETABLE

If you need to fetch space for single table,then use below query.

SP_SPACEUSED 'TABLE_NAME'

Hope you will find the space of all tables easily.

---

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 as replicated in the publisher. Hence the entries were removed and replication was started again.

Replication is in synch now.

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

Output:

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 

STANDARD #1

How to find the data files and log files for those not in the proper drives as per database standards?

Data file check

select name, physical_name from sys.master_files

where type = 0 and left(physical_name,12) <> 'A:\Data\SQL\'

Log file Check

select name,database_id, physical_name from sys.master_files

where type = 1 and left(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
Text:
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.

Cause:

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

Resolution:

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 stating above error message 14421 won’t come anymore.

Select * from msdb.dbo.log_shipping_monitor_primary
select * from msdb.dbo.log_shipping_monitor_secondary

Delete from msdb.dbo.log_shipping_monitor_primary where primary_database='logshippingdatabasename'

Delete from msdb.dbo.log_shipping_monitor_secondary where secondary_database='logshippingdatabasename'

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.

="TRUNCATE TABLE ” &A1 &" "

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

Step1:

First check below ping information related to the specific server.

C:\Documents and Settings\username>ping servername

Pinging servername.xx.yyy.com [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 time=95ms TTL=122

Reply from 12.345.67.890: bytes=32 time=95ms 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 = 97ms, Average = 95ms

---------------------------------------------------------------------------------

If I ping server name, it show server name with fully qualified name. If I reverse ping with ip address, it show only server name not with fully qualified name.

We couldn't able to find fully qualified name along with server name.

 Step2:

Resolution:

Ask DNS support team to make changes in DNS entry to resolve above reverse ping issue.

After that change, we can able to connect SQL server with no issues.

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 * from sysobjects where 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 from employee group by id  having count(*)>1

Select id from employee1 s group by id  having count(*)>1

Select id from employee2 group by id  having count(*)>1

Select id from employee3 group by id  having count(*)>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;

GRANT VIEW SERVER STATE 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]

Or

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.

 

DECLARE @spid VARCHAR(4) 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) INSERT INTO #tbl_spwho2 EXEC SP_WHO2 ALTER TABLE #tbl_spwho2 ADD ProcessInfo VARCHAR(4000) DECLARE SELECT_SPID CURSOR FOR SELECT SPID FROM #tbl_spwho2 SET @spid = '' OPEN SELECT_SPID FETCH NEXT FROM SELECT_SPID INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #tbl_spid_info (EventType VARCHAR(50),Parameters VARCHAR(30),EventInfo VARCHAR(4000)) INSERT INTO #tbl_spid_info (EventType,Parameters,EventInfo) EXEC('DBCC INPUTBUFFER(' + @spid + ')') UPDATE #tbl_spwho2 SET ProcessInfo = B.EventInfo FROM #tbl_spwho2 A,#tbl_spid_info B WHERE A.Spid = @spid DROP TABLE #tbl_spid_info FETCH NEXT FROM SELECT_SPID INTO @spid END CLOSE SELECT_SPID DEALLOCATE SELECT_SPID --Input the specified database name by replacing XXX in the below query. INSERT INTO tbl_spwho2 SELECT * FROM #tbl_spwho2 (NOLOCK) WHERE DBName = 'XXXXXX' --SELECT * FROM tbl_spwho2 DROP TABLE #tbl_spwho2

4. Then schedule the job as customer need like every 5 minutes to monitor the specific database activity.

That’s’ it.

Note: if customer needs to track database activity between Mondays to Friday means, Schedule the job in weekdays.Then we will get information using below query.

SELECT * FROM tbl_spwho2

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' GO

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.