Sidd's SQL Solution

After several pure T-SQL posts, today I thought to write about a post which discuss more about the MS SQL Server instance level properties and try to shed more light on it. In this post I'll talk about the most commonly used property i.e. memory settings. Although many of us have seen this option and many are very much familiar but let us revisit it to check whether we are using it or understand it the way it should be. Though on memory page we see five option but in this post we will talking more about only three, which are "AWE", "maximum server memory" and  "minimum server memory" . First up is " AWE " which is short for " A ddress W indowing E xtensions". It is special API built when the 64 Bit architecture was introduced and applications were still in phase where they were need to be fully ported on it. As most of us know that 32 Bit application can use only of 2-3GB of physical memory (depending on the OS option used...

In continuation to my previous blog, which was for database space usage , today I'll be writing about another most seeked similar information, which is table space usage details within a given database. SQL Server by default has provided a very good stored procedure named "sp_spaceused". This SP provides details like number of rows currently in a table along with space usage. The only problem with it is that it shows value for only one object at a time. In today's script we will see how by adding just few more statements we can use the same SP to collect information for all the tables. --=========================================================== create table #temp ( table_name varchar ( 500 ), no_of_rows varchar ( max ), reserved varchar ( max ), data_pages varchar ( max ), index_size varchar ( max ), unused_pages varchar ( max )) insert into #temp exec sp_msforeachtable 'exec sp_spaceused''?''' select ...

Hi and a warm welcome to all. In today's post I'll be writing about a very common and one of the most asked information i.e. the space used by databases. In almost every organization that I've worked with, there was someone who seeked this information for one reason or another. So, you know if you have even more than just 10 databases in your MS SQL Instance then collating such information by "right-click-properties" method is a time consuming task. So, below are two scripts which help you to get all the required details very quickly and precisely. First script is a more simpler version which will be helpful in-case you need total space (only) consumed by databases or database files. --=========================================================== create table #t1 ( db_name varchar ( 255 ), file_id tinyint , type_desc char ( 10 ), logical_name varchar ( 255 ), physical_name varchar ( 1000 ), state_desc char ( 10 ), size int ) insert i...