SQL TIPS:
Script to check when was SQL Server restarted
There several ways you can check when was SQL server restarted last time. Below are some sql queries to quickly find out the server restart date time. You can run any one of below, all will give same result.
1) sp_readerrorlog 0,1,'Copyright (c)'
2) SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
3) SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;
4) select start_time from sys.traces where is_default = 1
5) SELECT crdate FROM sysdatabases WHERE name='tempdb'
6) SELECT create_date FROM sys.databases WHERE name = 'tempdb'
|
Script to find number of physical CPU installed on the server
SQL Script to check number of CPU installed on the server.
Physical CPU (Core) = CPU Count / Hyper threaded Ratio
SELECT cpu_count,
hyperthread_ratio,
(cpu_count/hyperthread_ratio) as [Physical CPUs]
FROM sys.dm_os_sys_info
|
Sample SQL Script demonstrating moving row from one database table to another database table
Use Database1;
INSERT INTO Databas2.dbo.TableName SELECT * FROM Database1.dbo.TableName;
|
System Databases in SQL Server 2008
1. Master Database: master database records all the system-level information for an instance of SQL Server.
2. Msdb Database: msdb is used by the SQL Server Agent for scheduling alerts and jobs, database mail, Service Broker, and other services. If you aren’t actively working with things like jobs, alerts, log shipping, etc you can pretty safely ignore msdb… sort of.
One important item is that msdb holds backup history. Using the msdb tables (you can start by taking a look at msdb.dbo.backupset), it’s possible to determine when each database and filegroup was last backed up.
3. Model Database: model database is used as the template for all databases created on the instance of SQL Server. If you change the model database, such as database size, collation, recovery model, and other database options, thereafter if any databases are created then modified model database changes will be applied.
DB Files: mssqlsystemresource.mdf, mssqlsystemresource.ldf
It is best to think of the resource database as if it were another system DLL. The resource database is designed to make it easy for quick database upgrades. If new system objects are being put in place, it is only necessary to swap out the resource database MDF file
Typically, the only way to view the contents of the resource database is using the OBJECT_DEFINITION system function.
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
5. Tempdb: It holds all temporary tables and stored procedures.
Sql Server usese isr to resolve large or nested queries,
This is also being used while sorting data before displaying the results to the user, online indexing, BCP.
Temp database store's temporary tables (#temptable or ##temptale), table variables, ,row versioning, create or rebuild indexes sorted in TempDB, cursors, work tables etc. Each time the SQL Server instance is restarted all objects in tempdb are destroyed, so permanent objects cannot be created in this database.
By default tempdb starts at 8MB in size and will continue to grow by ten percent until the drive is full. If the tempdb is sized too small, system resources will be used growing the tempdb file. This overhead can be avoided by increasing the default size of tempdb.
6. Distribution Database: (Importance with merging and replication in Sql Server)
Primary data source to support SQL Server replication, The Distributor is a server that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication. To set up replication, you must configure a Distributor. Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.
Actullay there are many ways to configure replication, which can be a further topic of discussion.
7. ReportServer Database.
This is the Primary database for Reporting Services to store the Meta data and object definitions
A Reporting Services application uses two SQL Server relational databases for internal storage. By default, the databases have names as ReportServer and ReportServerTempdb. ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.