Category Archives: Database Administration

How to debug SSIS package

You or your team or someone has written an SSIS package which is running flawlessly over few days/months/years. One fine day SSIS package is failing continuously and you need to troubleshoot and fix the issue. Error message is not showing much information about why the package has been failed and until what step the packageContinue reading “How to debug SSIS package”

New SQL Server 2016 Online Alter column

Over the few months i have been playing with SQL Server 2016 new features. Microsoft SQL Server 2016 introduced new alter column with ONLINE option. Let’s explore this. Create a table and insert data    Find what page has been allocated for the above operation. I have used un documented function fn_PhysLocCracker to get the informationContinue reading “New SQL Server 2016 Online Alter column”

SQL 2014 Service Pack 2 is now Available !!!

Microsoft released SQL Server 2014 SP2 is available for download. These are the improvements introduced in SQL Server 2014 SP2 Automatic Soft NUMA partitioning Dynamic Memory Object Scaling MAXDOP hint for DBCC CHECK* commands Enable >8TB for Buffer Pool SOS_RWLock spinlock Improvement Spatial Native Implementation Database Cloning Tempdb supportability Database Instant File Initialization Logging New DMFContinue reading “SQL 2014 Service Pack 2 is now Available !!!”

How to move Tables/objects between Filegroups

In this post, I will be explaining how to move tables/objects between different FileGroups. Create a sample database Create Two file groups. In this example i have created two  filegroups MoveFG_DATA_1 and MoveFG_DATA_2 Create data files in different filegroups. In this example, i have created four data files, two for each filegroup Now, create a tableContinue reading “How to move Tables/objects between Filegroups”

New SQL Server 2016 Live Query Statistics

SQL Server 2016 introduce a new feature Live query statistics which allows us to view the  real-time insights into query without needing to wait for the query to complete. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc.Continue reading “New SQL Server 2016 Live Query Statistics”

New SQL Server 2016 SCOPED CONFIGURATION

Microsoft SQL Server 2016 introduced new way of configuring some of the instance level setting to database level which gives you the ability to easily make several database level configuration changes such as Setting MAXDOP for an individual database Set the query optimizer cardinality estimation model independent of the database to compatibility level. Enable orContinue reading “New SQL Server 2016 SCOPED CONFIGURATION”

“If you could give a DBA just one piece of advice, what would it be?”

“If you could give a DBA just one piece of advice, what would it be?” For those of you hunger to learn and explore your skills you may read the book DBA Jump start  This book contains advice from most renowned successful Database Professionals. Cheers Hope you like the post! Ramasankar Molleti LinkedIn: LinkedIn Profile Twitter: Twitter

Export and Import the database objects using BACPAC file

In this post i will be explaining about how to export the database objects to backpac and import from bacpac file. Connect to SQL Server , Right click on the database and Go to Tasks –> Export Data Tier Application   2. Introduction screen will be appeared as shown below, click next to continue 3. Export setting screenContinue reading “Export and Import the database objects using BACPAC file”

Different ways to Monitor Deadlocks

Every database environment, deadlocks are common problems and it is important for DBA’s to monitor deadlocks and make sure to prevent them from reoccurring. There are multiple ways to monitor deadlocks Using Trace Flags  We can enable two trace flags 1204 and 1222 to capture deadlock information in error log. Run the below code, this willContinue reading “Different ways to Monitor Deadlocks”