SQL Tidbits Apr 2010

April 30th

  • Estimated Execution Plan and Actual Execution Plan might be might differ, they are not supposed to be similar.

April 29th

  • Activity Monitor in SQL Server 2008 Management Studio is available on right clicking on the SQL Server Instance name in the object explorer.

April 28th

  • Activity Monitor from a SQL Server 2008 Management Studio does not work on SQL Server 2000 Instance.

April 27th

  • There can be multiple secondary databases and secondary servers for a single Primary Database in Log Shipping.

April 26th

  • In a Log Shipping configuration, the primary database is the actual database on which the Transaction Log backups occur.

April 25th

  • A new SQL Server error log is created each time SQL Server is started.

April 24th

  • The number of SQL Server error logs for a SQL Server can be set to a value between 6 and 99. By default it is set to 6 log files.

April 23rd

  • sp_spaceused  stored procedure helps to retrieve the size of a table.

April 22nd

  • Date and time data types in SQL Server (Smalldatetime and Datetime) includes both a date value along with time, if the time value is not included while inserting, updating the time value is stored as Midnight 12:00:00 AM.

April 21st

  • The only way the new system database, Resource database can be restored is manually, i.e, doing a file copy and paste and replace the existing file.

April 20th

  • Surface are configuration tool is not available in SQL Server 2008, it has been included as a facet in the newly introduced Policy based Management.

April 19th

  • Surface Area Configuration is a tool introduced in SQL Server 2005, which helps in enabling / disabling additional features in SQL Server before continuing to use / utilize the features.

April 18th

  • One of the advantages in Windows Authentication Mode is the is authenticated by windows need not be authenticating to SQL Server separately / additionally.

April 17th

  • Among the SQL Server fixed server roles, sysadmin role has higher privileges, that is a user with sysadmin role permission has the most or max permissions on the SQL Server.

April 16th

  • It is a SQL Server best practices to have the Data files and Log files separate physical drives to ensure recoverability of the databases.

April 15th

  • Primarily Log shipping can be broken into three main tasks, transaction log backup,Copying the backup to destination server and restoring the backup to secondary database.

April 14th

  • Implementing SSIS packages, not always means either a source or destination (in case of data transfer) is a SQL Server. Both source and destination can be non SQL Server such as a flat file source and Oracle destination or a Excel source and Access destination and so on..

April 13th

  • In SQL Server 2005, Activity Monitor can be found under Management node of the Object Explorer in Management Studio.

April 12th

  • In SQL Server 2008, Activity Monitor can be opened by right clicking on the Instance and from the pop up menu choose Activity Monitor.

April 11th

  • In SQL Server 2000, Logins cannot be disabled where as in SQL Server 2005, they can be disabled and enabled when needed.

April 10th

  • Sysadmin fixed server role is the role with maximum set of permissions and members of that role have all rights on a SQL Server Instance.

April 9th

  • SELECT COUNT(*) FROM TableName gives the row count of a table.

April 8th

  • In Database Mirroring, the principal database will continue to work in case of the mirrored database not available.

April 7th

  • Every time SQL Server is restarted, a new file named ERRORLOG is created and SQL Server messages are logged in to that file.

April 6th

  • When a database snapshot is created on a database, the source database cannot be deleted unless the database snapshot is dropped or deleted.

April 5th

  • When a database is in Read-Only, mode only select statements can be executed against it.

April 4th

  • When connected from SQL Server Management Studio, DTS packages of a SQL Server 2000 instance are displayed/shown under Management node -> Legacy -> Data Transformation Services.

April 3rd

  • Job activity Monitor under SQL Server Agent Node enables to view the status of the Jobs in SQL Server.

April 2nd

  • Schedules in SQL Agent can be enabled and disabled.

April 1st

  • Spatial datatypes, geography and geometry are introduced in SQL Server 2008.

Leave a Reply