Blog

Search results for: SQL Server

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database

20-Apr-2015

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database

As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows:

26-Aug-2014

SQL SERVER: Clone SSIS Package

Recently, I was assigned a job to create many DTSX packages. When I review the packages, I found those packages (Information flow) are moreover same. There is just a difference of Source file connection and destination SQL table which is different in each of the package. It might take couple of hours to create each of the package, but I wanted to get it done in few minutes. To achieve the same, I have looked up the DTSX code (XML) and updated as follows to achieve get it done efficiently and save some time.
 

26-Jun-2014

SQL Yoga : Certificate Expired, Mirroring Stopped

Database Administrators might face this issue while certificate gets expired and database mirroring (Non domain database mirroring) gets disconnected as two servers, Primary and Secondary cannot communicate with each other. The error message can be found in Log as follows:


Message
Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx]
In this post, We are going to review step by step process to resolve this issue by providing renew parameters with certificate configurations.
 

19-Apr-2014

SQL SERVER: SSIS – Merge Join Transformation

Now, let’s have a look at functionality of Merge Join Transformation task in SSIS.
Benefit of using Merge join is, input datasets can be combination of any two datasets from (Excel file, XML file, OLEDB table, Flat file).Output can be result of INNER, LEFT Outer, or FULL Outer Join on both the datasets.
Merge Join Transformation has two inputs and one output. It does not support an error output.
 

SQL SERVER: SSIS - Look Up Transformation Task

13-Mar-2014

SQL SERVER: SSIS - Look Up Transformation Task

oday, I am going to give basic example of Lookup Transformation Task in SSIS.

Lookup transformation performs lookup operation by joining data in input columns with reference table dataset columns.Lookup can be used to access addition information from the reference dataset based on the matching criteria Reference dataset can be OLEDB table, Excel file or cache file, or SQL query result.

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

03-May-2013

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server.

SQLYoga – XML Result sets with SQL Server

14-May-2013

SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database.

SQL SERVER: Backup the database and upload it on FTP and on Network

12-Sep-2011

SQL SERVER: Backup the database and upload it on FTP and on Network

Recently one of my friend called me and asked me that his MS SQL SERVER Database server is crashed and the problem is, he has the database backups on the same server. So now as the server is crashed, and he cannot have any backup to restore the database.

SQL SERVER Cross tab Query with Pivot (Dynamic Pivot)

12-Dec-2012

SQL SERVER Cross tab Query with Pivot (Dynamic Pivot)

As I mentioned earlier, PIVOT, in my earlier post, where we have reviewed the PIVOT with static columns. In practical world, we may need to have dynamic columns that needs to be displayed as below: