USE msdb; GO CREATE USER 'NewUser' FOR LOGIN 'ExistingLogin'; GO EXEC sp_addrolemember 'SQLAgentUserRole', 'NewUser'; GO
Many different things can contribute to the cause of a deadlock in SQL Server. Below are some suggestions on how to eliminate, or at least mitigate, deadlocking problems in your applications. This list is only a starting point and should not be considered a complete list of the options you have to prevent or reduce deadlocking. You may want to share this list with your developers, along with the information you identified during your trace.
• Ensure the database design is properly normalized.
• Have the application access database objects in the same order every time.
• Keep transactions as short as possible.
• During transactions, don’t allow any user input.
• Avoid cursors.
• Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
• Consider using the NOLOCK hint to prevent locking.
• Use as low a level of isolation as possible for user connections.
Yes, it’s possible… using SQLBackupAndFTP!
- creates MS SQL Server database backups.
- Zips, Encrypts and sends backups to a folder, FTP, Dropbox, Google Drive and Amazon S3.
- Runs on a flexible Schedule
- Sends e-mail confirmations
- Makes full, differential or transaction log backups, runs custom scripts, backups file folders, allows to view the results on the web and more. Available in free and paid versions.
SELECT o.name AS table_name, c.name AS column_name, ( 'alter table [' + o.name + '] ' + 'alter column [' + c.name +'] ' + case when c.system_type_id = TYPE_ID('text') then 'varchar(max)' else 'nvarchar(max)' end + ' ' + case when c.is_nullable = 1 then 'null' else 'not null' end + ';' + char(10) + 'UPDATE [' + o.name + '] set [' + c.name + '] = [' + c.name + '];' + char(10) +char(13) ) cmdsql INTO #tmp FROM sys.columns c left join sys.objects o on (c.object_id = o.object_id) where o.type = 'U' and o.is_ms_shipped = 0 and c.system_type_id = TYPE_ID('ntext') or c.system_type_id = TYPE_ID('text') while exists(select null from #tmp) begin set nocount on declare @sql nvarchar(max) select top 1 @sql = cmdsql from #tmp set nocount off exec sp_executesql @sql set nocount on delete from #tmp where cmdsql = @sql end drop table #tmp