If you are using SQL Server 2008 or higher you can query the sys.partitions catalog view. The filed [rows] shows the number of records in each partition of the database table.
This query returns the schema and table names, and total number of rows. If you have only one partition for each table, you can wipe out SUM() and GROUP BY from the query.
s.name AS schema_name,
o.name AS table_name,
SUM(p.[rows]) AS total_rows
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE o.[type] = 'U'
AND p.index_id < 2
GROUP BY s.name, o.name;
I’ve received a note that SQL Server Data Tools has some problems when working with references to another database or server in a database project. I wanted to find out what is really happens in SSDT when I import a database with dependencies. I’m using Visual Studio 2013 with Update 4.
I’ve created a database with the stored procedure that will use the following things in queries:
- linked server;
- four-part name;
The import of database to SSDT database project has finished successfully. I noticed that the linked server has not been imported. I think that’s because linked server is a server object, not a database one. But you can add a linked server to your database project manually.
When I open the stored procedure which queries the linked server, I see a warning about unresolved reference.
The same warning appears when I open a procedure with a four-part name reference. As for the last case with a synonym, SSDT does not show any warning. But in all cases we’ve got some difficulties/features such as:
- Intellisense does not work on that objects;
- Build passes with a lot of warnings SQL71502 or SQL71562 about unresolved references;
- Build passes even if I’ve made an error in the name of a referenced table/field/function, etc.;
- Errors can be found only in the deployment phase.
- we can use any form of references to another databases via linked server, four-part name notation and synonym;
- linked server is not imported to database project automatically;
- SSDT is unaware of objects through the references that will lead to a late discovering of errors.
During my session on 24 Hours of PASS 2015 Russian Edition I was asked about the Import database wizard in SQL Server Data Tools. The particular option is “Maximum files per folder”.
This option allows you to split the whole number of database objects to separate folders with a specified quantity of these objects. The dropdown list contains three values: 100, 500 and 1000. You can enter the value manually, but the minimum is 20 (SSDT auto-corrects it if you enter a quantity less then 20).
As a result you will get the following structure in you database project.
If you have installed Windows on virtual hard disk, you can copy this VHD to another machine and add it to boot menu.
This can be made with the following steps:
- just double-click on it and the disk will be automatically (works on Windows 8.1 and Windows Server 2012);
- if the first method does not work, open Command prompt and run commands:
select vdisk file=D:\win81.vhd
select volume #
You can choose any free disk letter to assign for your VHD, not only Z.
2.Add entry to boot menu.
Open Command prompt, if it was not opened before, and run commands:
3.Optionally reboot the operating system and you will see a new entry in boot menu.
Now you can select this OS and run it!
This post is about how to prepare the USB Flash to install Windows from it. It works fine with Windows 8.1 and Windows Server 2012. Maybe it could work with Windows 7 and Windows Server 2008 (I has not checked myself).
1) Mount Windows image. Just double-click on ISO image. Remember the letter of the drive that Windows assigned to this image. In my case it’s disk G:
2) Plug in USB flash and remember the letter of the drive. It’s H:
3) Open command prompt and do NOT close it until the end.
4) Run the following commands to create a NTFS partition on USB flash:
||starts Disk Partition tool
||shows a list of disks, find USB flash disk and remember its number
|select disk #
||replace # with USB flash disk number
||deletes everything on USB flask
|create partition primary
|select partition 1
||makes it active
|format fs=ntfs quick
||quick formats with NTFS file system
5) Run the following commands to create a boot sector on USB flash:
||change drive, G: is a drive letter of Windows image
||go to a folder
|bootsect /nt60 H:
||create a boot sector, H: is a drive letter of USB flash
6) Copy all files from all directories, including empty subdirectories, hidden and system files from Windows image (drive G:) to USB flash (disk H:):
||go to a drive root
|xcopy G: H: /E /F /H
7) Close command prompt
I’m glad to announce that I will hold a full-day training at SQLSaturday #398 on June, 5 2015 (Krasnodar, Russia). As a Microsoft trainer I’ve taught many courses for DBA but I have not seen a thorough education kit that can make a solid base for disaster recovery. So I’ve started to learn my self how to make it efficiently and now I want to share my knowledge with the students.
On my opinion, a good skills on disaster recovery are impossible without a good understanding of how SQL Server operates with files through the IO subsystem, what is an internal structure of SQL Server files and so on. That’s why I combine two topics into single course.
This training will be about managing database files and transaction log, how to make backup and restore – from simple cases such as full or log backup to a more complicated scenarios like piecemeal restore. We will talk about optimization both the file management and recovery process. Also we mention how to save OLAP databases in SSAS and packages in SSIS.
Each DBA meets sooner or later the situation when the database grows and it needs to be moved to another disk, the free space is running out unexpectedly, or that is even worse, the hard drive has gone away and it is necessary to restore the database immediately. You have to work very quickly and, that’s the most important, competently.
In this seminar we start from the fundamental basics of SQL Server file management, including data files and transaction log file, take a look how to optimize it.
Then we dive into the world of backups – from simple backup and restore to more complicated scenarios such as piecemeal restore. We reveal the secrets how to make backup/restore process faster.
In practice we will work with a number of “how-to” scenarios concerning file management and database recovery which DBA can meet in his job.
- Fundamental basics of SQL Server file management (data files and transaction log file);
- Internals of transaction log and optimization;
- Managing file location and size;
- Planning the recovery solution and choosing the recovery model;
- Backup and restore basics;
- Complicated recovery scenarios (file, filegroup and page backup/restore);
- Optimization of backup and restore process;
- Additional features of SQL Server Enterprise version (online restore, piecemeal restore, database snapshot);
- Dealing with system databases;
- Dealing with FileStream;
- Working with the databases of SQL Server Analysis Services and Integration Services.
There’s only one week before the online conference dedicated to SQL Server. 24 Hours of PASS will be held on March 18-19. The detailed information is available on sqlpass.org
I will present a session about SQL Server Data Tools. Especially about offline database projects and unit testing.
I’ve installed Windows 7 on VHD a couple of years ago and now I need some extra free space to install program. So I have to extend my VHD disk and the partition.
This can be done from another Windows operating system with the diskpart command.
select vdisk file=C:\win7.vhd
expand vdisk maximum=204800
select volume #
If needed we can check the statuses of disks and volumes by entering additional commands
I think it would be possible to run the Windows installation disk, open Command Prompt by pressing Shift+F10 and make all the steps listed above.
You can install Windows 7 and above on hard disk or on virtual hard disk (VHD). In the last case you need to create a VHD file on hard disk and install Windows inside that VHD file. The boot loader is placed outside the VHD automatically during the installation process.
During Windows installation you will see a prompt to select a type of installation: upgrade or custom.
Press Shift + F10. The Command Prompt will appear. Type the following commands:
create vdisk file=C:\win7.vhd maximum=102400 type=fixed
select vdisk file=C:\win7.vhd
The size of the VHD will be 100Gb.
After that select the Custom option to make a clean installation of Windows.
Important! The type of VHD must be fixed because I’ve encountered problems to install Windows from DVD or USB flash on expandable disk. But when you install Windows inside Hyper-V virtual machine, you won’t have any problem with expandable VHD.