We are very excited to be launching a new appliance to help customers consolidate thousands of databases, and at the same time enable IT to respond to business needs much faster through an elastic database private cloud infrastructure. The new HP Enterprise Database Consolidation Appliance , Optimized for SQL Server, was designed, tested and engineered jointly by Microsoft and HP and is available today. Background We have talked to many customers that face the challenge of managing the continuou...
Read More
Hi Folks, As you would have seen, I have discussed about DBCC previously. But this blog-post is purely on TRACEON. DBCC TRACEON is being used to enable the specified trace flags. Well, you can see the syntax for the TRACEON: DBCC TRACEON (trace# [,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ] And for the TRACEOFF: DBCC TRACEOFF (trace# [,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ] Here, ‘trace#’ is for the number of trace flag to turn on or off, ‘n’ is the placeholder that indicates mu...
We've wrapped up getting SQL Server Parallel Data Warehouse on Dell hardware and proud to announce the Dell Parallel Data Warehouse Appliance that includes Dell PowerEdge R610 Servers and Dell PowerVault MD36xxF series high density storage. The Dell Parallel Data Warehouse Appliance is a rack solution with a single control rack and one to four data racks. It's racked and installed at the factory floor before being delivered to the data center. Roll it in and it will work. It offers built-in redu...
Many times we use “Execute Package Task” to execute a child package from a parent/wrapper package and we use “Parent Package Variables” as means of configuration for child package. Do you know that we have to keep “ExecuteOutOfProcess” to “False” in order to use parent variables in child package? If we set “ExecuteOutOfProcess” to “True” than parent variables are not visible in child package and configured child’s vari...
Hi Friends, I am not lazy today, just physically conservative :) –so a small post and only scripts ! :) -- FUN with row constructors use tempdb go create table amit1 (name varchar(20), age int); go create table amit2 (name varchar(20), age int); go -- Insert multiple records INSERT INTO amit1 VALUES('Amitabh', 5), ('Abhishek', 6); go SELECT * FROM amit1 go --Now try this: does this work? INSERT INTO amit2 VALUES ((SELECT Name FROM amit1), (SELECT Age FROM amit1)) go --what about this? INSE...
This is documented in Microsoft KB article 958004. When you restore a database from 2000 version of SQL Server to ver 2005 (specific builds being SQL Server 2005 build 2191 or later builds of SQL Server 2005 Service Pack 1, SQL Server 2005 Service Pack 2 or later builds), you find inconsistent file growth settings. Read more on this here: http://support.microsoft.com/kb/958004 @SQLServerGeeks
Hi folks, You might have seen my blog posts which was all about DBCC CHECKDB and how to repair the database using DBCC CHECKDB. But I have tried to focus this blog on the various DBCC Commands: DBCC CHECKALLOC (‘database’): A subset of DBCC CHECKDB that checks the allocation of all pages in the database. The report is very detailed, i.e., listing the extent count (64 KB or 8 data pages) and data-page usage of every table and index in the database. DBCC CHECKFILEGROUP (‘filegrou...
The SQL Server Team held Fireside Chats at the end of the 24 Hours of PASS on September 7-8, 2011 to give attendees the opportunity to ask questions directly to members of the SQL Server engineering team. The engineers answered most of the questions during the chat, however there were a few questions that we promised to follow up on at a later date. That date has come! Please find the answers to all the outstanding questions listed below. We encourage you to listen to the audio recordings (below...
Hi Friends, In one of my recent trainings, when I was demonstrating the internals of STATISTICS, a participant asked if we can find out the last update date of the statistics. Yes, we can do that using STATS_DATE. Here is a small example. Create a table and insert some test data. USE tempdb GO CREATE TABLE Contact( FirstName nvarchar(100), LastName nvarchar(100), Phone nvarchar(30), Title nvarchar(20) ) GO -- Populate the table with a few rows. INSERT INTO Contact VALUES(N'Amit',N'Bansal',N'9172...
In my previous post , I have discussed about various options available in SSIS to load multiple flat files. One of the mentioned options is using “MultiFlatFile”. A multi flat file connection allows us to access multiple files in parallel i.e. we can read data from multiple files in parallel and it produces data as UNION ALL of all files as if we are reading data from a single file. To use multiple flat files in a connection, we need to create a connection of “MultiFlatFile&rdq...
Many times we require to import multiple files in SSIS. There are various ways to achieve this and the best option depends upon on case to case. The available options are: For Each Loop and a variable to create dynamic connection In Single dataflow, use separate Connection for different files and use “Union All” or separate Data flow for each file Use “MultipleFlatFile” connection For Each Loop and use ExecuteSQL to have OPENROWSET to read data from flat files. Option 1 a...
Hi Friends, I like writing about my trainings – there is so much to learn in every training assignment. I get to meet new minds, new personalities, get to know their SQL stories, their issues, remedies, tips, tricks, techniques and bunch of little things ! I mentioned in my last blog that this has been a seasons of multiple batches for the same client. Last time it was 2 batches for the Bank, one in Gurgaon & another in Chennai. And this time I repeat the same batch in Bangalore after ...
Hello Folks, You might have seen my last blog post, which was a brief introduction about DBCC CHECKDB. If you want to refer it again, then please check the link: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/315/sql-server-%E2%80%93-dbcc-checkdb Well I have concentrated this blog-post mainly on how to repair the database with the help of DBCC CHECKDB. I have made some keynotes about it: If there’s an error and DBCC CHECKDB can fix it up, then DBCC CHECKDB indicates th...
Hello Friends, Keep your system up to date with the most recent cumulative update package for SQL Server 2008 SP3. SQL Server 2008 SP3 Cumulative Update 1 contains fixes released in SQL Server 2008 SP2 CU 5 & 6. For customers upgrading to Service Pack 3 from SQL Server 2008 SP3 CU5 (Build 10.00.4316.00) or CU6 (Build 10.00.4321.0) should consider applying SQL Server 2008 SP3 CU 1 to ensure fixes resident on the system are available post upgrade to Service Pack 1. CU#1 KB Article: http://supp...
Many of you know that SQL Server 2008 R2 can be installed side-by-side with SQL Server 2008. SQL Server 2008 R2 is a minor release. Since both the versions share the same major version, R2 installation will automatically upgrade the shared components between the two versions. In case you are planning to install R2 along with 2008, you should check out this link: http://msdn.microsoft.com/en-us/library/ee210714.aspx
While apply Service Pack 2 update to a SQL Server 2008 instance, you might come across the following: This basically means that if fdhost.exe is stopped, than a reboot will not be required. Fdhost.exe is the Full Text Search Service and can be stopped from the Services Control Panel or from SQL Server configuration Manager.
Hi Friends, We know that READ UNCOMMITTED Isolation level allows dirty read. That is, neither does it ask for locks nor does it honor locks held by other transactions. This is true in case of reading and writing data. But the story is different if another transaction has issued a DDL statement. Let us see. In a query window, execute the following code: -- connection 1 USE ADVENTUREWORKS2008; BEGIN TRANSACTION; ALTER TABLE Person.Person ADD Address2 nvarchar(100); --ROLLBACK TRANSACTION; GO In an...
Hello Folks, You might have heard about DBCC. If not, I am going to give you some heads up: DBCC Commands stands for Database Console Commands. They are being used for: Checking database integrity Performing Maintenance Operations on databases, tables, indexes, and filegroups Collecting and displaying information during troubleshooting issues The main function of DBCC CHECKDB is to check the logical and physical integrity of all the objects in the specified database by performing the following o...
Want to try out Denali (SQL Server 2012) without installing it? A HyperV image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010 is available at http://www.microsoft.com/download/en/details.aspx?id=27740 The following software is configured on the virtual machine: SQL Server "Denali" CTP3 SharePoint 2010 Office 2010 Enjoy ! @SQLServerGeeks http://www.FaceBook.com/SQLServerGeeks
Hello Friends, Kicking off PASS Summit 2011, Microsoft Corporate Vice President Ted Kummert addressed an audience of more than 5,000 technology professionals to discuss how Microsoft Corp. is evolving its data platform to respond to customer needs. Kummert described how SQL Server 2012, formerly code-named “Denali,” addresses the growing challenges of data and device proliferation by enabling customers to rapidly unlock and extend business insights, both in traditional datacenters an...
Find 4 th , 5 th and 6 th ranked entity Let me first confess. This blog was never a planned effort. It just happened. How it happened…that’s equally interesting. Most of the time you might find questions to determine “Nth highest/lowest salary” of an employee. I was thinking on the similar lines but to determine the 4 th , 5 th and 6 th highest/lowest ranked employee on the basis of their salary. The first idea that followed was to create a CTE (Common Table Expression) ...
Hi Friends, This is an old writing of mine from my old blog – just wanted to re post this here. Management by exception – don’t be too sure that things are running fine. Probably, that’s the time you need to worry when everything is running smoothly, especially your Database Mirroring session . When your DB Mirroring session is established and running smoothly, you can use the Mirroring Monitor to monitor the session. You can see many performance metrics including the sen...
Hello Folks, Do you know how to import CSV file into SQL Server? Or, How to load comma delimited file into SQL Server? It’s with the help of Bulk Insert. Well I have noted down some keynotes about this: Bulk-Insert can be used within any T-SQL script or stored procedures to import the data into SQL Server. It is being used to import the data into a database table or view in a user-specified format. The parameters of the command specify the table receiving the data, the location of the sour...
Hi Friends, In one of my recent training, one participant had an understanding that Log Sequence Numbers (LSNs) are only generated by user transactions / user activity. Well, that’s not true. LSNs will be generated by Checkpoint process also. Whenever Checkpoint is issued manually or automatically by SQL Server, Checkpoint begin LSN and CheckPoint end LSN are written to the Log file. Here is a small example: Create a test database. CREATE DATABASE TributeDB; GO Create a test table. USE Tri...
Hello Folks, You might have heard about it or maybe not. Don’t worry at all because I will be going to give you some of the heads up: Logon triggers were introduced earlier with the arrival of SQL Server 2005 SP2. It is somewhat similar to the DDL trigger which I had discussed it earlier in my blogs. Here’s the link for it: http://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/306/sql-server-%E2%80%93-similarities-and-differences-between-dml-ddl-triggers Some main point...