Home » SQL Server (Page 57)
Category Archives: SQL Server
How to Recycle SQL Server error logs – 5 Types of logs
1) Recycle SQL Error log exec msdb..sp_cycle_errorlog 2) Recycle SQL Agent Error log exec msdb..sp_cycle_agent_errorlog 3) Recycle(rollover) SQL Server Default trace EXEC sp_configure ‘default trace’, 0 RECONFIGURE EXEC sp_configure ‘default trace’, 1 RECONFIGURE 4) Recycle SQLFT#.Log a. Before 2008 – restart the indexer b. After 2008 – no way to cycle to full text log … Continue reading
SQL Server 2005 Onwards – Mirrored Backup || Cool Feature
SQL Server 2005 onwards includes the mirroring of backup media sets to provide redundancy of your critical database backups. Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. These malfunctions are very serious because backups are the last line of defense against data loss. SQL Server 2005 Standard Edition supports … Continue reading
How to find current/particular transaction level?
There is 2 ways of finding current / particular transaction level :- 1) SELECT CASE transaction_isolation_level WHEN 0 THEN ‘Unspecified’ WHEN 1 THEN ‘ReadUncomitted’ WHEN 2 THEN ‘Readcomitted’ WHEN 3 THEN ‘Repeatable’ WHEN 4 THEN ‘Serializable’ WHEN 5 THEN ‘Snapshot’ END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions where session_id = @@SPID 2) DECLARE @UserOptions TABLE(SetOption varchar(100), Value … Continue reading
Script to get details of permissions on Database objects
Script to get details of permissions on Database objects Script to get details of permissions on Database objects SELECT [UserName] = ulogin.[name], [UserType] = CASE princ.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ WHEN ‘G’ THEN ‘Windows Group’ END, [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] … Continue reading
Procedure xp_cmdshell expects parameter ‘command_string’ of type ‘varchar’.
You can’t use varchar(max) with xp_cmdshell. Try a finite number (0 to 8000). varchar(max) is having capacity of 2GB & works as TEXT data type in background.