How to downgrade a SQL Server from 2008 R2 to SQL Server 2005?

Here’s the situation. You have done an in-place upgrade of SQL Server from 2005 to SQL Server 2008 R2 and you do not have any backups for 2005 version. (This was one of the interview questions I faced)   You can achieve this by Right click the database, go to ‘Generate Scripts’, Advanced, ‘Script to…


Find the percentage of fragmentation in a table for a database (What’s the permission level needed?)

I can find out the percentage of fragmentation for a particular table using the below query: USE AdventureWorksGOSELECT object_name(IPS.object_id) AS [TableName], SI.name AS [IndexName], IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pagesFROM sys.dm_db_index_physical_stats(db_id(N’master’), NULL, NULL, NULL , ‘DETAILED’) IPS JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id JOIN sys.indexes SI WITH (nolock) ON IPS.object_id…


DBCC CHECKDB – Good to know!

If you’re a DBA, then you’ll need the below content at least once in your career. Your company needs to run DBCC CHECKDB on all system and user databases to keep it safe from corruption and take necessary actions when in trouble. Sometimes 825 errors get converted to 824 eventually. And as a DBA you…

1

SQL Server Agent jobs collecting useful information, how to save them?

Hi, I was working on an OLTP environment where tempdb started growing crazy! Before even I got a hold of the server, someone restarted the SQL Service and had a smile on his face (thinking he solved the problem), well, tempdb hadn’t learnt it’s lesson from this guy 😀 I got hold of queries from…

1

What should my backup strategies be for system databases?

If you’re a DBA, you’ll say, it’s simple; in the maintenance plan, just select ‘all databases’ or if you rely on tape backups (file level), then there’s no question.   Why would I write a blog about it? Everything goes fine until we have the system databases working, the moment we don’t have backups for…

1

What did I learn?

This blog is slightly non-technical rather it’s a bit about myself. I worked in Microsoft IGTSC for 4 years. Spent sleepless nights (because I was working :p) and spent good days during days while I was working for US after-hours business on SQL Azure technologies. In this period, I gained a lot of technical skills….


Check connectivity to Azure SQL Database using Azure PowerShell

This comes in handy to see if the customer is able to connect to the database server or not. In other words, if you want to check if the database connection can be reached to the server or not can be found by performing a small test. If (New-Object System.Net.Sockets.TCPClient -ArgumentList ‘xxxxxxxxxxx.database.windows.net’,1433) { Write-Host ‘YES’…

1

Slow disk IO issues for SQL Server

There are 100’s of blogs on the topic that I am writing about today. It always helps me to look at the data flow chart so sharing what I feel is helpful.   This is KKB from SQL Server world!


Permissions for Windows administrator to perform SQL Server setup

You’re starting a SQL Server setup | you’ve faced permission related errors during SQL Server setup | you’ve also gone through the article however finding it difficult to proceed, then read below 🙂 Open Command prompt, on Windows 7 or above choose to “Run as Administrator” type: cd %temp% Type following command line exactly as…


How to find thumbprint of an Azure certificate for Windows Powershell

It’s going to be a simple blog post. If you’re new to Azure PowerShell and you’re looking to find out the thumbprint of an Azure certificate, then you can read below. If you go through https://azure.microsoft.com/en-in/documentation/articles/powershell-install-configure/, you’ll learn how to -Get-AzurePublishSettingsFile- at ‘Use the certificate method’.  Once you login using your subscription email id and download…

2