Tweet
SQL Server logs, changing level of logging
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
How to truncate SQL Server Transaction Log
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
FROM sys.master_files
WHERE DB_NAME(database_id) like ‘%AdventureWorks%’
and name like ‘%Log%’
order by SizeMB desc
SQL Server Permissions of Fixed Database Roles
Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server.
SQL Server Error Messages
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…
SQL server SQLDIAG Utility
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…
SQL Server | How to create a Read Only User/Role
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…
TSQL | Delete All Objects from Database
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.
What patches are applied to my SQL Server
Use
- select @@VERSION” OR
- SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) ” Or
- Simply look at the value in the Management Studio Explorer
How to use SP_CONFIGURE in SQL Server
sp_configure is a tool to display and change SQL Server settings. Please be aware that changing these settings can dramatically affect your instance.
SQL Server Maintenance Scripts : YourSqlDba & SQL Server Maintenance Solution
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.
How to create a new named instance under SQL 2005/2008
We can only configure one default instance under SQL server installation. If we need additional instance it should be a named instance.
How to locate SQL server clustered database & it’s properties
1. How to locate if I am connected to a SQL server cluster?
SELECT SERVERPROPERTY(‘IsClustered’)
SQLIO SAN/Local Disk Subsystem Benchmark Tool
- 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.
Database Engine Tuning Advisor : how to Import SQL profiler traces at DTA
Let’s create a TRACE for Performance Analysis
USE [Test]
GO
/****** Object: Table [dbo].[Person] Script Date: 07/30/2009 15:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Troubleshooting Bottlenecks Using Dynamic Memory Views : Part III
- Windows System Monitor – PART I
- DBCC commands – PART II
- Dynamic Memory Views - PART III
- SQL Server Profiler – - PART IV
Troubleshooting Bottlenecks : Using SQL Server Profiler & Windows System Monitor Together
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/
Please initiate a Trace on SQL server before going ahead. Within a SQL server Profiler window click on the top button for “Performancve Monitor” to enable logging.
Troubleshooting Bottlenecks Using SQL Server Profiler : PART IV
- Windows System Monitor – PART I
- DBCC commands – PART II
- Dynamic Memory Views - PART III
- SQL Server Profiler – - PART IV
SQL Server Profiler – - PART IV
Troubleshooting Bottlenecks Using Windows System Monitor : Part I
- Windows System Monitor – PART I
- DBCC commands – PART II
- Dynamic Memory Views - PART III
- SQL Server Profiler – - PART IV
Windows System Monitor
Backups and Recovery Options on SQL server
Backup Types
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…
Using a Maintenance Plan to Backup SQL Databases
Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode.
SQL Server Transaction Log Shipping Replication
Prerequisite :
- 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
