To view SQL Server error log from SQL Server Enterprise Manager
- Expand a server group, and then expand a server.
- Expand Management, and then expand SQL Server Logs.
- Click the SQL Server Log to view it. Error log information appears in the details pane
Step 1. Find Size of Database Log files
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
WHERE DB_NAME(database_id) like ‘%AdventureWorks%’
and name like ‘%Log%’
order by SizeMB desc
Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server.
SQL Server comes with pre-defined error messages that can be raised when a particular condition occurs. Built-in error messages are stored in the sysmessages table of the master database. Depending on the severity level, error messages can be returned to the offending connection or could be recorded in the SQL Server error log. The severity level determines the type and seriousness of the problem that caused the error. The following table summarizes various error severity levels: Read more…
SQLDIAG.exe executable can be found in the SQL Server installation directory under the BINN folder. SQLDIAG records the SQL Server configuration, contents of the error logs (current, as well as archived logs), and Windows configuration including registry keys. If SQL Server is running when SQLDIAG is executed, it will record the output of the following routines: Read more…
I have number of SQL server databases and users . I am in a need to create read only access to users who wants to look at data. Read more…
Often I have to copy number of SQL server database structures from prod system for testing. It’s difficult to get rid of all objects within a database manually. Following script will do just about that.
- select @@VERSION” OR
- SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) ” Or
- Simply look at the value in the Management Studio Explorer
sp_configure is a tool to display and change SQL Server settings. Please be aware that changing these settings can dramatically affect your instance.
If you are looking for a good TSQL packaged scripts which will do usual database maintenance activities then you are on right page. I have detailed 2 such projects which will help you to setup maintenance job in less than an hour.
We can only configure one default instance under SQL server installation. If we need additional instance it should be a named instance.
1. How to locate if I am connected to a SQL server cluster?
- Download http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en and install SQLIO
- It will be installed under C:\Program Files\SQLIO. Please add this directory in your FIle Path
- SQLIO will need a test file to run against your drives for performance.
Let’s create a TRACE for Performance Analysis
/****** Object: Table [dbo].[Person] Script Date: 07/30/2009 15:33:32 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[Person](
[ID] [int] NOT NULL,
[First] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[last] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[County] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Enable IO and Explain Plan settings using “set options”
Right click on “server name” and select “properties”
Here is a situation – users are complaining about a slow SQL server response.
We want to quantify if that is result of a hard hitting SQL query or this is to do with some application/background jobs running on the server.
Let’s put to practise our skills from http://www.oracledbasupport.co.uk/troubleshooting-bottlenecks-using-sql-server-profiler-part-iv/ & http://www.oracledbasupport.co.uk/troubleshooting-bottlenecks-using-windows-system-monitor-part-i/
There are 5 backup types. All these backups can be carried out online. databases. Offline backups require SQLSERVER be stopped. Offline backups are useful for the master, msdb and model databases
1. Full : This takes a complete copy of the database and logs.
2. Differential : This takes a copy of all changed pages since the last full backup (for database and logs.)
3. Transaction Log : This takes a copy of the transactions logs and clears its contents on completion.
4. File or File group : This copies only the specified file or group.
5. File differential : This takes a copy of all changed pages in file since the last file backup. In situations when the backup is too big for a backup window. Read more…
Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode.
- 2 separate SQL database instances at 2 different physical boxes – T-B0399254ED744 & TVLX4686.
- A share accessible to Primary and Secondary Server for log shipping
Here I am going to log replicate AdventureWorks database from T-B0399254ED744 to TVLX4686