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.
- A SQL Server Database can have only 1 default filegroup at any given point of time.
- SSDT Database Projects has to be installed first, in order to create and deploy database projects in SQL Server 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.
- Usage of sp_dbcmptlevel system stored procedure should be avoided. To change a compatibility level, use
ALTER DATABASE DBNAME SET COMPATIBILITY_LEVEL = XYZ
- Only the members who belong to either syadmin or diskadmin fixed serverrole can execute the system stored procedure sp_addumpdevice
- 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
- You cannot perform partial backups using SQL Server Management Studio or Maintenance Plans.
- Standard Edition of SQL Server (as of SQL 2008) supports a maximum of 2 nodes only.
- The least compatibility level you can set to a database in SQL Server 2012 is 90 (SQL Server 2005).
- Trace flag 7806 is required to enable Dedicated Administrator Connection (DAC) in Express Editions.
- A SQL Agent Job can have multiple schedules.
- 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.
- Lock Pages is Memory is also available for Standard Editions of SQL Server. Initially it was available only for Enterprise Edition.
- It is considered to be better to use pull subscription when there are large number of Subscribers.
- 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).