16 | June | 2015 | SQL with Manoj

SQL Tip – How to get size of all Tables in a Database?

June 16, 2015 Leave a comment

You can use SQL Server system Stored Procedure sp_spaceused to get the details of a table like rows and size. Just pass the table name to this SP as a parameter:

USE [AdventureWorks2014]
GO

EXEC sp_spaceused N'Person.Person'
GO

name		rows	reserved	data		index_size	unused
Person.Person	19972   85840 KB	30504 KB	53192 KB	2144 KB

But if you want to get details of more than one or all tables of a Database then you can iterate this SP for all tables. To iterate for all tables there is an undocumented function in SQL Server i.e. sp_msForEachTable, and you can provide your sp_spaceused query as a parameter to this SP:

CREATE TABLE #TableSize (
	 name		NVARCHAR(128)
	,rows		CHAR(20)
	,reserved	VARCHAR(18)
	,data		VARCHAR(18)
	,index_size VARCHAR(18)
	,unused		VARCHAR(18)
)

sp_msForEachTable 'INSERT INTO #TableSize (name, rows, reserved, data, index_size, unused) EXEC sp_spaceused [?]'

SELECT * FROM #TableSize 

DROP TABLE #TableSize
GO

There is one more way to get the details by using system (sys) views, query below:

SELECT 
     t.name AS TableName 
    ,s.name AS SchemaName 
    ,p.rows AS RowCounts 
    ,SizeInKB = SUM(a.total_pages) * 8
    ,UsedSpaceInKB = SUM(a.used_pages) * 8
    ,UnUsedSpaceInKB = (SUM(a.total_pages) - SUM(a.used_pages)) * 8
    ,SizeInMB = (SUM(a.total_pages) * 8)/1024 
    ,SizeInGB = (SUM(a.total_pages) * 8)/(1024*1024) 
FROM sys.tables t 

INNER JOIN sys.indexes i 
ON t.object_id = i.object_id 

INNER JOIN sys.partitions p 
ON i.object_id = p.object_id and i.index_id = p.index_id 

INNER JOIN sys.allocation_units a 
ON p.partition_id = a.container_id 

INNER JOIN sys.schemas s 
ON t.schema_id = s.schema_id 

WHERE t.is_ms_shipped = 0 
AND i.object_id > 255 
--AND t.name IN ('tbl1', 'tbl2', 'tbl3', 'tbl4') 
--AND t.name LIKE 'pattern%'  
GROUP BY t.name, s.name, p.rows 
ORDER BY SizeInMB DESC

To get details of selective tables just apply the Table names above in the IN() clause or LIKE operator.

Categories: SQL Tips

SQL Error – The operation cannot be performed on a database with database snapshots or active DBCC replicas

June 16, 2015 Leave a comment

Today while restoring a Database and Dropping it I came across following errors:

Error #1. The first error was while I was trying to RESTORE an existing database from a backup file:

Msg 5094, Level 16, State 2, Line 1
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Error #2. The second error came while I tried to DROP that existing database, to ignore the previous error:

Cannot drop the database while the database snapshot “dbStaging_ss_20160607” refers to it.
Drop that database first. (Microsoft SQL Server, Error: 3709)

By both the above errors its obvious that a Database Snapshot is associated with the Database that I’m going to Restore or Drop. So first try to identify and DROP that Snapshot. You can do this by checking in SSMS under Object Explorer –> Databases –> Database Snapshot, or running the following query:

USE [master]
GO

SELECT * 
FROM sys.databases
WHERE source_database_id IS NOT NULL
GO

The above query will list all the Snapshots created for existing Databases. Just check the Snapshot that is associated with the Database you were trying to Restore.

Before dropping the respective Snapshot, script out the DDL to create the Database Snapshot back, by Right Clicking on the Database Snapshot –> Script database as –> CREATE To –> Clipboard. The script will look like below DDL Script:

USE [master]
GO

CREATE DATABASE [dbStaging_ss_20160607] 
ON ( 
	NAME = N'dbStagingDB', 
	FILENAME = N'E:\dbStaging\dbStaging_Primary.mdf' 
) 
AS SNAPSHOT OF [dbStagingDB]
GO

After you are done by scripting the Database Snapshot DDL, just DROP the Database Snapshot, just like you drop any other Database:

USE [master]
GO

DROP DATABASE [dbStaging_ss_20160607]
GO

After you are done with this, try Restoring back the Database that you were trying initially.

SQL Database Recovery tool to repair corrupt MDF file

June 16, 2015 7 comments

Database is a must have requirement of every type of Business systems, weather it is for customers, accounting or product. In order to create these databases the most common database system is Microsoft’s SQL Server. Due to its robustness and dynamic system facilities, it is a highly popular solution to manage a company’s business system.

A business database itself includes valuable data as it is a result of months hard work. What if this database becomes inaccessible due to multiple causes, and lead to a situation of data loss. Therefore, it is important to know about a reliable solution that can restore even the slightest part of database. Specialized SQL recovery tools are designed to perform SQL recoveries, therefore, they are highly in demands. Stellar Phoenix SQL Database Repair is one of a SQL database repair tool that gives the assurance of data recovery from damaged or corrupt SQL databases.

This review is about Stellar Phoenix SQL Database Repair product and its heights and short comings!

Disclaimer: This is not a paid review, and reflects my own experience while working with the product.

–> Testing Initiation

Stellar Phoenix SQL Database Repair is a popular product that claims to recover SQL database from all disaster scenarios. To check the higher promises of the product I have decided to check its accuracy and capabilities on one of my corrupt SQL server database, which is actually very large and highly important for me.

–> Installation Requirement Guide

Minimum system requirements for this recovery software are very normal as it needs A Pentium class processor, 1 GB of RAM, and 50 MB free disk space to be installed. This recovery tool is compatible with all the versions of SQL server, from MS SQL Server 7.0 to 2014, and all the versions of Windows operating systems, from Windows XP to 8 along with Windows Server 2003, 2008 and 2012.

When I have decided to test this product, I don’t need to setup a new system as all my system configurations fulfills all the requirements. One special quality I have noticed in this recovery application is; it automatically reconnects to the MS SQL server, in case of any disruption during the repair process to run the recovery smoothly.

–> Steps to select the database

Within few clicks you will be able to install the software on your machine. After successful installation, the main interface will show multiple menus and buttons to access various features of the software. Additionally, these various options can also be accessed through Quick Access Toolbar that is available at the top of the user interface, and can be customized accordingly.

In order to choose the database, you need to click on Select Database button and choose a MDF file that needs to be repaired and click ‘Repair’ to initiate the repair.

In case the location of your database file is unknown, Stellar Phoenix SQL Database Repair also avails you Find Database option that can find MDF file that you want to repair from selected drive and folder. After successful search, you will be able to see the list MS SQL Database files found in the selected drive or folder on the main window.

–> Initiate SQL database repair

Now, select the file from available list that needs to be repaired and select ‘Repair’ button to initiate repair. After successful completion, the left pane of windows displays list of repaired items along with Table, View, Synonyms, Defaults, Data Types, etc. in a tree format. The upper right pane displayed the content of selected items and the bottom pane displayed message log. You need to click on the item to preview the content of the listed items.

This software has a special feature that it counts all the records after successful repair. Although, this is an optional feature, but still worth for some users. To count my repaired records it took a good time, as the database contained a large number of items, it could be less for you if your database count is less.

–> Save Repaired items

To save the repaired files, just select the Save option available in the File menu. You could also finish this saving task by selecting ‘Save’ button from Quick Access Toolbar. The software asks you to Enter Server / Instance Name and choose a desired destination to save the repaired MDF file.

Now, select the Connect button to save the repaired MS SQL Database file. Make sure your SQL server is running during the repair process.

–> Additional Features to make the recovery task easier

I had a good experience with the software as it is easy to use and can quickly connect with the server. Here are some special features that will make your process much easier.

–> Find Specific Item from the list

After successful scan Stellar Phoenix SQL Database Repair allows you to find particular item(s) in tree view. You could use search bar available on the preview window along with two options and will give accuracy to your search.

–> Selective Recovery
This SQL database recovery tool can perform selective recovery of your database objects. You could choose to select specific objects that need to be recovered from the list and save them in your desired location.

–> Overall performance

Being developed by a reputed company (Stellar Data Recovery), Stellar Phoenix SQL Database Repair is genuinely a good software and after testing its complete modules I can say that it recovers all the objects of database along with multiple components, like Indexes, Views, Rules, User Defined Functions, Tables, Triggers, Stored Procedures, etc. It repaired my MDF and NDF files along with XML indexes and data types.

–> Sum up:

After using this product I can surely recommend this tool to SQL professionals who are looking for a tool that can help them in their disaster time. Although, the software is quite slow and will make you wait, but it will be worth when it displays the complete database which was inaccessible.

Categories: DBA Stuff, Reviews Tags: Database Corruption, Database Recovery