Its all about numbers – Some capacity specifications within SQL Server !


Recently I was doing a pretty large SQL 2012 deployment, and was doing some research on capacity specifications.

There were lot of numbers which I found really interesting and some were really new to me. I thought it would be worth sharing them.

Maximum Database Size  – 524,272 terabytes (Both 32bit and 64bit)

Databases Per Instance – 32,767 (Both 32bit and 64bit)

File Groups Per Database – 32,767 (Both 32bit and 64bit)

Files Per Database – 32,767 (Both 32bit and 64bit)

File Size Data  – 16 TB (Both 32bit and 64bit)

File Size Log  – 2 TB (Both 32bit and 64bit)

Instances Failover Cluster  - 25 instances on a failover cluster when using a shared cluster disk as the stored option (Both 32bit and 64bit)

Nonclustered indexes per table  – 999 (Both 32bit and 64bit)

Parameters per stored procedure  – 2100 (Both 32bit and 64bit)

Parameters per user-defined function – – 2100 (Both 32bit and 64bit)

Partitions per partitioned table or index  – 15,000 (Both 32bit and 64bit)

XML indexes – 249  (Both 32bit and 64bit)

Columns per SELECT statement – 4,096 (Both 32bit and 64bit)

Columns per INSERT statement - 4,096 (Both 32bit and 64bit)

There are lot many numbers which might surprise you and the complete list is available here.

mscorlib.tlb could not be loaded , SSMS Just wont agree to run !


Today I opened SSMS in one of my SQL 2005 box,and to my surprise I got this error right away

“The file C:\WINDOWS\Microsoft.NET\Framework\%version%\mscorlib.tlb could not be loaded.”

I was running SQL 2005 SP3 (Yes,you heard it right !) and the only .NET framework which was installed on the box was .NET 2.0.

I went ahead to check the folder C:\Windows\Microsoft.Net\Framework and to my surprise I found that the folder framework is missing !

How did that happen ?  Before I get into identifying what caused this to happen,I had to fix the problem with SSMS.SSMS should be opened directly from the server ( I agree that its not a good practice,however the situation demanded it)

I decided to try my luck with repairing .NET framework as this was one of the options I had.I went ahead and did the repair of .NET framework 2.0 from Add/Remove options.

The repair just went smooth and I could see the missing files/ folders getting created again while the setup was running.

SSMS just launched perfect and the issue was fixed.

Conclusion


You can also do this repair in case you face similar issue.I will be focusing on why this corruption happened during the coming days and will update the same post in case I get some hints.

Thanks for reading and keep repairing !

This 64-bit application couldn’t load because your PC doesn’t have a 64-bit processor – Really !


From last night I have been doing lot of planning to setup a virtual environment on my laptop.It was kind a little complex one as I will be dealing with 3 VM’s on the laptop.One has to be setup as a domain controller and a windows cluster has to be setup and install SQL 2012 on all the VMs and do some real test on Availability groups.

Yes,some real testing on Availability Groups !

After intense planning I decided to install the very first VM using Virtual Box (https://www.virtualbox.org/).I sized the VM pretty well giving 1GB memory,25GB HDD space and of course CPU.

I mounted the ISO of Windows Server 2008 R2 SP1 and was ready to start the install process and suddenly an error came up

I was pretty sure that the laptop which I was using was having a 64bit architecture and it was a Intel core i7 processor.

What else could go wrong ?

There is one area you need to check first if you run into the similar issue,ie check if Virtualization Technology has been enabled in your BIOS.

Wow,cool isnt ?

Yes,this has to be checked out and Microsoft virtual PC has a pretty decent article which will help you to find where its available in BIOS for different laptop models.

http://www.microsoft.com/windows/virtual-pc/support/configure-bios.aspx

For my laptop this was indeed disabled by default.I enabled the same and was back to track.

I hope this quick post will help someone,some day.

Thanks for reading,and keep building virtual machines to test SQL 2012 !

max_files column for sys.traces – When can it have NULL value


Today there was an interesting question on max_files column value which is available under sys.traces view object.The question was like this  -

max_files in sys.traces is 0 and that means zero, not NULL – correct?

Before we get into the details,I would like to mention that the max_files column means the maximum number of rollover files.When we create a trace we have the option to enable file roll over,which simply means that if the first trace file size is set as 5MB,then after reaching that limit sql profiler will create another file to continue capturing the trace.This will continue until and unless the trace is stopped.

Now,can ever this max_file value be NULL ? Yes,after research I found that this value will be NULL if the is_rollover flag is set as 0(Ie,we dont choose enable file rollover when creating the trace)

 

select max_files,is_rollover,id AS Trace_ID from sys.traces
WHERE id <>1 --Filer DefaultTrace

 

Another interesting fact is related to default trace.The default trace max_file value is 5 and max_size is 20 and this cannot be changed.

Thanks for reading.

SQL Beautifier


Today I came across some tools which does smart way of formatting your SQL code.Who does’nt like a code which is well formatted and easy to read.

I remember my college days when I used to write multiple lines of code for my final semester project and manually format the code for printing and ease of understanding purpose.

I would have scored better grades if I had thought about this tool long back!!! There are many tools out there,however the one which I liked well is  -

http://www.dpriver.com/pp/sqlformat.htm?ref=wangz.sqlformat.htm

You can get beautiful and well formatted codes like

BEGIN TRAN

DECLARE @endDate DATETIME

SET @endDate = Dateadd(hh, 1, Getdate()) – 1 hours from now

WHILE Getdate() < @endDate
BEGIN
INSERT INTO [Test]
VALUES      (1)

WAITFOR delay ’00:00:02′;
END

Hope you all liked this tool and happy coding!

<Update Added 12/10/2012

Today I found another tool which is pretty impressive,its http://sqlformat.appspot.com/

<Update>

Could not obtain exclusive lock on database MODEL


Today I was doing some test cases around model database,and was querying some of the tables.I just missed to close that session and was creating a new database.

Straight away I was hit by this error

We all know that a new database inherit the model structure,however I was not aware that an exclusive lock is required for the model.

I was pretty sure that consistency is the major factor which is behind this design behavior and it was confirmed after my discussion with #sqlhelp experts.

I was able to find a connect ID too for this behavior,and Microsoft has confirmed the exact reason for this -

http://connect.microsoft.com/SQLServer/feedback/details/631177/creating-database-fails-with-error-could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later

The ID was closed with ‘By Design’ reason.

Thanks for reading.

Compare different SQL Server edition features in a smart and better way!


One thing I have noticed in forums is that people are curious about features available with different flavors of SQL Server.

Most of the times questions comes like I installed SQL Server 2008 Express and find Database Mail missing,Do I need to re-install SQL Server again?

The answer is SQL Server Express doesn’t support Database Mail.

Its not really easy to remember what feature comes in with which edition,however there is a very smart and easy comparison page provided my Microsoft.

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx 

You have the option to select multiple versions and compare the features here.Hope this post will help you answer some of the feature related questions.

Thanks for reading.