T-SQL Tip: Getting the File Locations for all DBs in a SQL Server Instance

by

—— Dynamically builds  T-SQL statements for retrieving the file — locations for all the databases in the SQL Server Instance—— SQL Server versions  supported: SQL Server 2005 or later—SELECT ‘use ‘+ [name]+’; select ”’+[name]+”’ as DBName,cast ([name] as varchar(45)) as LogicalFileName,cast (filename as varchar(100)) as FileName from sysfiles;’ as SQLStatement FROM master.sys.databases Details: Just execute the statements generated … Read more…

Internal Query Processor Error: The query processor could not produce a query plan

by

OK folks, it did happen at some point, to get this error message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.   Reproducing the Internal Query Processor Error Message I was trying to execute the following query: SELECT *  FROM dbo.tbl1 WHERE  tbl2ID=(SELECT … Read more…

Handling Disk Space Issues During Heavy Index Rebuild Operations

by

There are times where you need to massively rebuild indexes on some really large databases, after indicated by the relevant analysis of course. However, rebuilding indexes, requires also the adequate amount of free disk space that will be used during the rebuild operation (mainly for the sorting process). Usually the required space is the size of … Read more…

Argument data type ntext is invalid for argument 1 of left function

by

When you try to run a string function directly on a text or ntext column, in this example the “LEFT” function, you will get the following error: Argument data type ntext is invalid for argument 1 of left function. Similarly, if you try to run the REPLACE function against a text or ntext column, you will … Read more…

There was an unexpected failure during the setup wizard

by

In this article, we will be discussing how to resolve the following SQL Server error: “There was an unexpected failure during the setup wizard”.   When you might get this error OK folks, this is a little bit “tricky” issue. The scenario is the following: You are trying to install SQL Server 2005, then you … Read more…

[Webcast] SQL Server 2012: The Database Engine

by

– Celebrating 250.000+ Visits! – SQL Server 2012 shipped with a plethora of useful new features. In this webcast we will see the majority of the Database Engine features. To this end, we are going to see enhancements that fall into the following SQL Server areas: High Availability Manageability Enhancements Programmability Performance & Scalability Security … Read more…

[Celebrating 250.000 Visits!] Q&A Session on SQL Server

by

– Celebrating 250.000+ Visits! – As promised a few days ago, the celebrations for the 250.000+ visits on my blog continue with the Q&A Session on SQL Server! You can submit your SQL Server-related questions using this form. I will be accepting questions until October 24, 2012. After the submission is over, I will categorize all … Read more…

[Celebrating 250.000 Visits!] SQL Server 2012 – The Database Engine [TRAILER]

by

– Celebrating 250.000+ Visits! – Within the context of celebrating the 250.000+ visits on my blog, on Wednesday, October 17th, 2012, I will make available my brand new webcast on SQL Server 2012 focusing on its Database Engine features. In order to spread the news about the webcast this time I thought of doing something … Read more…

Celebrating 250.000 Visits!

by

Today, my blog has reached the 250.000 visits. That’s 1/4 of a million! 🙂 I started this blog four years ago with a simple purpose: Share knowledge with the Community. Be a part of the Community. Four years later I must say that I have received more than what I gave. The interaction with you, … Read more…

T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances

by

If you are looking for a T-SQL tip, on how to create a simple linked server between SQL Server instances, then the below tip might come in handy.   T-SQL Example for Creating the Linked Server The below T-SQL code does just that: — –Note: The Entire Operation Takes Place on the Local Instance — … Read more…

Getting the Disk Usage Statistics for all Tables in a Database

by

I know, there are standard reports and other GUI tools for doing this task for SQL Server 2005 or later but what about when you need to extract such information from a SQL Server 2000 instance or when you just want to write some code? 🙂   Get the disk usage statistics using the undocumented … Read more…

Snippets Generator v1.0 – Stable Version Now Available!

by

[Update 2018] Please visit Snippet’s Generator page for the latest release of the tool and much more. We are very happy to announce that the first stable version of Snippets Generator is now available! Do you need to create T-SQL snippets for your SQL Server 2012 instance? Snippets Generator is the tool for the job! And of … Read more…

How To Get Basic SQL Server Instance Info (T-SQL Script)

by

With the below T-SQL script, you can get basic SQL Server instance info such as: Full instance name SQL Server version Edition Collation Number of databases Product level (i.e. SP-level) …and more   The T-SQL Script The script uses the built-in SQL Server function SERVERPROPERTY. SELECT SERVERPROPERTY(‘ServerName’) AS FullInstanceName, REPLACE(SUBSTRING(@@version,0,CHARINDEX(‘-‘,@@version)),’Microsoft ‘,”) as FullSQLVersion, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion, SERVERPROPERTY(‘ProductLevel’) … Read more…

T-SQL Tip: Retrieving Database File Sizes

by

The following script generates T-SQL statements that when ran, they return file size information for all the user databases on a SQL Server instance. –Script that generates T-SQL providing size information for all database files on a SQL Server InstanceSELECT ‘SELECT ”’+[name]+”’ as DBName,cast(f.name as varchar(25)) as DBFileName,f.Filename as PhysicalFileName,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB … Read more…

New Post Series: T-SQL Tips

by

Hello friends! I hope you are all having a great summer time! I am having a great summer time too, however I felt the need for some summer-blogging and so here I am, writing another article! 🙂 I will start this article with a simple question: How many times we all search the Web for … Read more…