A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search which was taking lot of memory. However, doing this I realised that examining DBCC MemoryStatus output is tedious and time taking and what if it can be parsed into a table. It will be very easy to find memory culprit then. So, here is a powershell script to parse DBCC MemoryStatus. The script is very si...
Read More
This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sql instances. The new version is available here DB-Migrate v1 . The Move-Database function can now be used to move database to remote instances. There are 4 new parameters - IsCopy It’s a Boolean variable when true will copy database files to new location. The default value is false. - Datafile...
This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures-a-primer http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/777/the-tsql-classes-stored-procedures-parameter-sniffing http://www.sqlservergeeks.com/blogs/Ahmad...
Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If you are not aware of Sql Server Reporting Services,it is a tool used for reporting and comes bundled with Standard, Developer and Enterprise editions of Sql Server. Creating a report in Sql Server Reporting Services (SSRS) is simple and easy,besides the tool is very user friendly. Lets create a sma...
Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion. So in the figure below, CustomerID is an identity column: Now, let say I have a requirement to insert a CustomerID with value as ‘10’, just below of this value. How can I perform that? There are two ways: Either you turn off the Identity property by going into design, and opting for col...
Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions into a single powershell module named DB-Migrate. The module combines the below tasks; to get detailed information about these tasks please go through the respective blog links. 1. Move logins from source to destination instance. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/768/sql-server-u...
Hi SQL Geeks, Here are the blog posts by Sarabpreet Singh for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks Here is a summary of all his posts with links: Resolving – Disk Missing Issue for TempDB with a Twist http://www.sqlservergeeks.com/blogs/sarab/sql-server-bi/781/resolving-%E2%80%93-disk-missing-issue-for-tempdb-with-a-twist If you like our blogs do l...
Hi SQL Geeks, Here are the blog posts by Prince Rastogi for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks Here is a summary of all his posts with links: SQL Server: Handling Hierarchical data inside the database Part3: Index on HierarchyID http://www.sqlservergeeks.com/blogs/princerastogi/personal/788/sql-server-handling-hierarchical-data-inside-the-database-part...
Hi SQL Geeks, Here are the blog posts by Manohar Punna for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks Here is a summary of all his posts with links: Upgrade fails - "The Instance ID 'MSSQLSERVER' is already in use by SQL Server instance 'MSSQLSERVER.INACTIVE'." http://www.sqlservergeeks.com/blogs/manu0417/personal/785/upgrade-fails-the-instance-id-mssqlserver-...
Hi SQL Geeks, Here are the blog posts by Amit Karkhanis for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks Here is a summary of all his posts with links: SQL Server: SSAS - Understanding and configuring Processing Settings http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/789/sql-server-ssas-understanding-and-configuring-processing-settings If you like our b...
Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks Here is a summary of all his posts with links: The TSQL Classes Stored Procedures: Parameter Sniffing Resolution http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/787/the-tsql-classes-stored-procedures-parameter-sniffing-resolution SQL Server: Using Powers...
Hi Friends, I observed a strange issue while creating a login in SQL Server 2012 SP1. While creating a new login… After entering some required info… When I click on user mapping and select a database… The db_owner role gets selected automatically. This is not how it is supposed to be. Infact, when I click OK the login gets created but actually the role is not applied for the user. When you open the login again and verify from the same page… … the roles is actua...
This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures-a-primer http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/777/the-tsql-classes-stored-procedures-parameter-sniffing http://www.sqlservergeeks.com/blogs/AhmadO...
Processing is an operation to keep an Analysis Services database (ASDB) up-to-date with the current data from an OLTP system or a data warehouse based on how an ASDB is designed. It is the responsibility of an administrator to manage the processing tasks on a regular basis. It may be a single step or a series of steps to populate the Analysis Services objects with data based on the type of objects and the various processing options. Processing – Behind the scenes Processing is an important...
Hi Friends, Today we will see how we can implement Index on HierarchyID Column, and what benefit we can achieve by using these indexes. In My previous blogs on HierarchyID we saw how can we use HierarchyID Column as well as how can we search ancestors and descendant values for HierarchyID column. You can go through on previous blogs by using below links: http://sqlservergeeks.com/blogs/princerastogi/personal/782/sql-server-handling-hierarchical-data-inside-the-database-part1 http://sqlservergeek...
This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures-a-primer http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/777/the-tsql-classes-stored-procedures-parameter-sniffing In last class, I talked about parameter sn...
Hi Friends, Today we will see how we can search ancestors and descendants in a HierarchyID column. In the previous blog you saw that how can we handle hierarchical data with HierarchyID data type. There we saw creation of table using HierarchyID data type as well as inserting the hierarchical data inside that column. You can go for previous blog with below link: http://sqlservergeeks.com/blogs/princerastogi/personal/782/sql-server-handling-hierarchical-data-inside-the-database-part1 Now run that...
This is one of the very frequent errors we receive when we try to upgrade to 2008 or 2008 R2 or to that matter even to 2005. The upgrade may fail for any reason initially and later when you fix that issue and retry to upgrade it keeps on crashing by showing the below error message. The Instance ID 'MSSQLSERVER' is already in use by SQL Server instance 'MSSQLSERVER.INACTIVE'. To continue, specify a unique Instance ID. In the registry you will see the below key. HKEY_LOCAL_MACHINE\SOFTWARE\Microso...
Migrating jobs from one server to other can be easily done with powershell. Let’s look into the script. At first, include reference to the relevant SMO assemblies and then create the source and destination sql instance objects. [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIMana...
As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scripting to find the free space available in each data or log files for an SQL Database. Here is a simple query which will return free space available in all data and log files of all the databases in an instance of your SQL Server. By providing the paramenters facilitated in the script you can get fre...
Hi Friends, Sometimes we need to handle hierarchical data in databases like trees where data elements are having relationship like parent and child. Before SQL Server 2008 it was complicated to deal with such kind of data. Inside SQL Server 2008 Microsoft introduces a new data type HierarchyID to handle such type of data and reduce the complexity. By using this data type you can also use various methods provided by SQL Server to deal with such data. Here we will see how can, we use it: USE Maste...
A not so common but major issue could be missing Disk\Drive issue for any SQL Server, If there were user DB files on that disk chances are you’ll lose some data if the same Disk is not allocated again to you, Moreover you have to bear downtime. (Though there are some exceptions to it which we’ll discuss in another blog post.) But in case your TempDB was there on missing Drive\location – it will not even allow your SQL Server to start & gives the below error: CREATE FILE enc...
Here's a simple powershell script to shrink log files across database. Shrinking T-log isn't a good practise, however at times we might need to release space to OS in development/UAT servers. The script comes in handy then. The script shouldn't be executed on production. [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')| out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null [System.Reflection.Assembly]::LoadWithPartia...
One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell script comes in handy in this case. Let’s start by creating a server and database object. $inst="AHMAD-PC" # Connect to the specified instance $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst # get database collection $dbs = $s.Databases | where-object {$_.IsSystemObject -eq...
This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures-a-primer In the last class I talked about plan caching and how it speeds up the stored procedure execution time. In this class I’ll look talk about parameter sniffing problem; which can be referred to ...