SQL Tidbits Jan 2012

January 30th 2012

  • Though SQL Server Express Edition does not have SQL Agent, you will see that in SQL Server Configuration Manager.

January 29th 2012

  • By default every database user when no other security permissions are granted, inherits the permissions of public role.

January 28th 2012

  • On a Database level, the database role db_owner has the highest permission in on the database, including dropping the database, hence care should be taken while assigning it should to any users.

January 27th 2012

  • A report in SQL Server Reporting Services can contain one or more sub-reports.

January 26th 2012

  • When a  maintenance plan is created whether or not it is scheduled to run at regular intervals, there will be a SQL Agent job created corresponding to that maintenance plan.

January 25th 2012

  • It is a best practice to have autogrowth property enabled to at least one data and one log file (if there multiple data and log files) for user databases.

January 24th 2012

  • Transaction log truncation will not result in the reduction in the size of the transaction log file. In order to reduce the size of the log file, it has to be shrinked.

January 23rd 2012

  • Tempdb create time can be useful to know when was the SQL Server restarted last time.

January 22nd 2012

  • Though you can change the recovery model of Master database, you cannot perform a log backup, when tried, the backup operations fails.

January 21st 2012

  • You cannot configure Database Mirroring or Log Shipping for system databases.

January 20th 2012

  • TableSample clause can be used to retrieve a random set of records from a table.

January 19th 2012

  • When installing SQL Server on a failover cluster, you have to choose both  Replication and Full-text search or none of them.

January 18th 2012

  • System table dbo.sysmaintplan_plan in msdb has a one row corresponding to each maintenance plan you have on that Instance

January 17th 2012

  • By default a database table is created in the default filegroup of that database.
January 16th 2012
  • A SQL Server Database can have only 1 default filegroup at any given point of time.
January 15th 2012
  • SSDT Database Projects has to be installed first, in order to create and deploy database projects in SQL Server 2012.
January 14th 2012
  • The maximum number of failover clustering nodes in Enterprise Edition of SQL Server 2008 (or R2) is limited by the maximum number of nodes supported by the underlying Windows OS.
January 13th 2012
  • Usage of sp_dbcmptlevel system stored procedure should be avoided. To change a compatibility level, use
ALTER DATABASE DBNAME SET COMPATIBILITY_LEVEL = XYZ
January 12th 2012
  • Only the members who belong to either syadmin or diskadmin fixed serverrole can execute the system stored procedure sp_addumpdevice
January 11th 2012
  • Quickest way to create an empty table similar to another table is by using…
SELECT TOP 0 * INTO NEW_TABLE_NAME FROM ACTUAL_TABLE_NAME
January 10th 2012
  • You cannot perform partial backups using SQL Server Management Studio or Maintenance Plans.
January 9th 2012
  • Standard Edition of SQL Server (as of SQL 2008)  supports a maximum of 2 nodes only.
January 8th 2012
  • The least compatibility level you can set to a database in SQL Server 2012 is 90 (SQL Server 2005).
January 7th 2012
  • Trace flag 7806 is required to enable Dedicated Administrator Connection (DAC) in Express Editions.
January 6th 2012
  • A SQL Agent Job can have multiple schedules.
January 5th 2012
  • Replication Monitor is the primary tool used to monitor the Replication, apart from that Replication Monitoring Objects, System Monitor and SSMS can be used to monitor Replication.
January 4th 2012
  • Lock Pages is Memory is also available for Standard Editions of SQL Server. Initially it was available only for Enterprise Edition.
January 3rd 2012
  • It is considered to be better to use pull subscription when there are large number of Subscribers.
January 2nd 2012
  • Multiple databases can be dropped in a single T-SQL Statement. (see below for an  script example)…
DROP DATABASE DB1,DB2,DB3
GO

January 1st 2012

  • Maximum Database size in a  SQL Server 2008 R2 Express Edition is 10 GB (in previous editions it was 4 GB).