Microsoft SQL Server Database and DB log File Shrinking in one shot

Microsoft SQL Server Database and DB log File Shrinking in one shot

Most of the time  our database server use to go out of memory, whenever we restore Prod copy on Dev, then we use to do shrinking of log files and Database, doing this one by one is big task. Then I thought of doing this in one shot, here I am sharing a SQL code for the same.

I have used [sysdatabases] and [master_files] tables to get required metadata, and “while loop” for repeating DBCC Shrink for all databases, we can even do this using Courser but I prefer While loop in simple cases.

----------------------------------------------------------------------------

--#1 Get all Database names, Log file names and metadata by this SQL

----------------------------------------------------------------------------

select db.name as DBName,db.[dbid],df.name as DBFileName,df.type_Desc,df.State_desc

into #DBNames from [master].[sys].[sysdatabases] db inner join

[master].[sys].[master_files] df on df.database_id = db.[dbid]

and db.name not in ('master','tempdb','model','msdb') and df.State_desc='ONLINE'

-- Sample Result

/*

DBName        dbid   DBFileName           type_Desc    State_desc

-----------------------------------------------------------

AccountsDW    5             AccountsDW_Data      ROWS         ONLINE

AccountsDW    5             AccountsDW_log       LOG          ONLINE

SalesMaster   6             SalesMaster_Data ROWS             ONLINE

SalesMaster   6             SalesMaster_Log      LOG          ONLINE

*/

----------------------------------------------------------------------------

--#2 Declare required variables

----------------------------------------------------------------------------

Declare @DBID int =0, @MaxDBID int =0,@MinDBID int =0

Declare @SQL varchar(max)='',@DBName varchar(200),@Filename varchar(100)=''

----------------------------------------------------------------------------

--#3 Take Min Max DB id for further looping logic

----------------------------------------------------------------------------

Select @MinDBID=Min([dbid]),@MaxDBID=Max([dbid]) from #DBNames

-- Setting Starting point from Minimun DBID

Set @DBID = @MinDBID

----------------------------------------------------------------------------

--#4 Take DB name, log file name for shrinking in loop.

----------------------------------------------------------------------------

---- Here i have used 'LOG' in where condition because we need Log file name in ShringFile() function .

---- and Databse name for ShringDatabase() Function.

Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @MinDBID and type_Desc = 'LOG'

----------------------------------------------------------------------------

--#5 Loop for all databases

----------------------------------------------------------------------------

while @DBID<=@MaxDBID

begin

  -- Used Dynamic SQL for all databases.

  Set @SQL ='Use '+@DBName+ ' '+Char(10)

  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)

  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

 

  --#6 Increment DBid for looping over all databases

  Set @DBID = @DBID+1

  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'

  Print (@SQL)

  Exec (@SQL)

end

----------------------------------------------------------------------------

--#7 Final Output Print, wheich will execute one by one.

/*

Use AccountsDW

DBCC SHRINKFILE(AccountsDW_log,5)

DBCC SHRINKDATABASE(AccountsDW)

Use SalesMaster

DBCC SHRINKFILE(SalesMaster_Log,5)

DBCC SHRINKDATABASE(SalesMaster)

*/

----------------------------------------------------------------------------

Sys table help

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysdatabases-transact-sql?view=sql-server-2017

Thank you,
please leave your feedback and suggestions, your new ideas could help many more developers. 

Get First Business day of Month, in MS SQL Server

Get First Business day of the Month

In this article, we will discuss how to get First day of the month and First Business day of the month (Excludes Saturday and Sunday if it comes on 1^st day of month), SQL server does not have direct function to get all this we need to write some workaround code.

First, get First day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-15', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Now, get First Business day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-5', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth) -- Gets Sunday

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth) -- Gets Saturday

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

Let’s write Function to get First Business day of the Month

Create  Function [dbo].[ufnGetFirstBusinessDay](@YourDate Datetime)

Returns Datetime as

Begin

--This part gets First date of the month.

Declare @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

--This part gets First business day of the month,

--excludes Saturday and Sunday if it comes on 1st day of month

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth)

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth)

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

End

--Select dbo.GetFirstBusinessDay('2019-6-15')

Accounting Format for numbers in SSRS report

    SSRS report does not have Accounting-formatting feature like MS Excel have. But some banking and Financial Client asks Accounting Formatting to numbers in SSRS, here is the solution for this

Image shows Differences of accounting right align currency format

To achieve this you need to Tweak Expression as shown below, give space after 0 for accounting format and after – to align no data in same format. you can change this expression as per your Need, Space is the Solution to get Accounting Format.

#,0;(#,0);'-'    

#,0 ;(#,0);'- '

Please look into below image

Thank you, Please give your reply, question,  Feedback and any additional Tweaks 

Talk to Anup: Difference between Bill Inmon and Ralph Kimball Da...

Talk to Anup: Difference between Bill Inmon and Ralph Kimball Da...: Difference between Bill Inmon and Ralph Kimball Data warehouse model Bill Inmon is first author to introduce Data warehouse design and...

Ralph Kimball Data warehouse model

Ralph Kimball Data warehouse model and Development approach

Ralph Kimball is well known author who introduced new pattern of data storage in Data Warehouse, so called Dimensional Data Architecture; Based on Fact and Dimension tables.

Kimball’s model is much more refined and simplified, where all Subject oriented data you can store in Dimensions, and measures In fact tables; whereas all dimension tables will be connected to fact tables by foreign key relation.

Ralph Kimball introduced Star and Snowflake Schema, to store data in Data Marts/ Data Warehouse.

Implementing this model is very easy and time saving. But Kimball’s model mostly suitable for small and mid-cap industries like Automobile, Education, and Retail.

Here is I have tried to Design Kimball’s DW model

Ralph Kimball’s Model follows Bottom to Top approach, that is nothing but lowest level of detailed data is stored in Data Marts. That is the reason this approach is called Bottom Up approach.

Here Subject Oriented data is stored in Dimension tables and Measures of same data will be stored in Fact tables. 

Take an Example of Students Enrollments, Students Information and Enrollment information; will be stored in Dimension tables whereas number of Students enrolled per class, per Year, percentage will be stored in Fact tables; And dimension tables will be connected to fact table by foreign key relation.

To normalize data at maximum level Kimball introduced Snowflake Schema. One dimension is further subdivided into another dimension, connected with foreign key.

Please leave your comments, suggestions  

Thank you!

Bill Inmon Data warehouse model

Bill Inmon Data warehouse model and Development approach

Bill Inmon is called Father of Data warehouse concepts, he is the first author to introduce the Data Warehouse, a computer generated data also can be stored as Data warehouse and use for further analysis and decision making.

Bill Inmon mainly concentrated to store data as a centralized repository. Where all different systems data will be stored including history and Data Access layers can access unique information with history.

Inmon's definition says that Data warehouse is Subject Oriented, Non- Volatile, Integrated and Time variant collection of Data.

 I got opportunity to work on both the design patterns; I tried to compare what exactly real-time difference between these two design patterns.

Here is I have tried to Design Inmon’s Data Warehouse model

Bill Inmon’s model mainly suits for big industry, large data cap, like Insurance, Banking, and Finance where huge data collection will be there, daily basis; Almost 1 to 10 TB data collection daily, now here is a big question, how to manage this much of big data; Multiple transactions by single user, lots of history by single users, one user holds multiple accounts, actually this is a very big challenge for companies to manage the data, coming from different systems.

Bill Inmon’s model gives robust solution for all challenges, where you can have master tables or we can call it as a repository tables.

Repository Tables or Master Tables are partially or fully normalized (depends of requirements), holding the subject oriented master data; a Unique record with history, so you can easily and exactly track latest updates and previous transactions.

Once we have all Master tables as a Repository, The Subject Oriented data Marts will be created from master data with reports specific Lookup tables. Which we can directly use for Reports, Data Visualization, data mining, Analytics, Predictive reporting.

All this is called Business Intelligent, It helps for decision making to the higher management.

Please leave your comments, suggestions  

Thank you!

Difference between Bill Inmon and Ralph Kimball Data warehouse model

Difference between Bill Inmon and Ralph Kimball Data warehouse model

Bill Inmon is first author to introduce Data warehouse design and Kimball has introduced new, relatively simpler Dimensional model design, with Star and snowflake schema.

Here are some differences between Inmon and Kimball’s model.

Bill Inmon Model	Ralph Kimball Model
Inmon Model is little complex to design, and can have lot of customization based on your own style.	Kimball Model relatively easier to develop, time saving and fixed Star or Snowflake schema designs we can use.
Supports Huge industry needs.	Perfectly matches to medium and small company needs.
Time talking and long projects.	Comparatively less time taking.
Require Expertise engineers thought project.	Once developed, little experienced engineers can work.
Design Supports Enterprise applications and wide range of data.	Design supports individual and medium cap clients business area.
After development modification and customization is easier and can be done faster.	After Development modification and customization is time taking process.
Inmon Design uses ER model in Data warehouse.	Kimball Model uses Star and Snowflake Schema.
In Inmon’s design Data Marts are physically separated from Data warehouse.	Whereas in Kimball’s model it’s not necessary to separate Data marts from Dimensional Data warehouse.
Inmon Design suggests First developing Central repository and then developing Data marts from Central repository.	Whereas Kimball’s design suggest developing first critical Data Mart’s which can supports all reporting and analytical needs.

Please leave your comments, suggestions.  

Thank you!