Sql-server – Does MSSQL Server need more space than the size of the data itself for importing? How much more

In the comments you indicate that your SQL Service is running as the Network Service account.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms684272(v=vs.85).aspx

The NetworkService account is a predefined local account used by the service control manager. This account is not recognized by the security subsystem, so you cannot specify its name in a call to the LookupAccountName function. It has minimum privileges on the local computer and acts as the computer on the network.

This is the default account for installations under Vista and Windows Server 2008. During installation this account was granted Full Control file system permissions to the SQL Server data directory as seen in the File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups section of the following article.

http://msdn.microsoft.com/en-us/library/ms143504.aspx#Reviewing_ACLs

Since you have relocated some of your database file the SQL Server service account needs permissions to the new locations. In the comments @Marian provided instructions for granting Network Service the required permissions.

Right click the drive -> Properties -> Security -> Edit -> add the Network Service user -> give him full control. By default he hasn't. Or better, just create a specific folder and give him Full Control permissions there. And move the data files in that folder.

The advice to create a new folder and move the files there is excellent as well.

Any additional services that are running as the Network Service account will aslo have permissions to this folder. Ideally the SQL Services should each have their own separate account.

http://support.microsoft.com/kb/2160720

When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts

No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

• binary or string data would be truncated => error message enhancments

And now it has been delivered in the following versions:

• SQL Server 2019
• SQL Server 2017 CU12
• SQL Server 2016 SP2 CU6

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

Msg 2628, Level 16, State 1, Line 11
String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.