0

Does MSSQL Server need more space than the size of the data itself for importing? How much more? How to tell?

Recently tried copying data into MSSQL server (2012)

Microsoft SQL Server Management Studio          11.0.3156.0
Microsoft Analysis Services Client Tools        11.0.3130.0
Microsoft Data Access Components (MDAC)         6.2.9200.16384
Microsoft MSXML                                 3.0 5.0 6.0 
Microsoft Internet Explorer                     9.10.9200.22387
Microsoft .NET Framework                        4.0.30319.18449
Operating System                                6.2.9200

using BCP and was seeing errors like

Error = [Microsoft][ODBC Driver17 for SQL Server][SQL Server]The transaction log for database 'EpicShd' is full due to 'CHECKPOINT'.

and

Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The transaction log for database 'EpicShd' is full due to 'ACTIVE_TRANSACTION'.

When looking at the DB itself, it threw...

Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For context, the total size of the data (at least the TSVs that BCP was copying in) was ~10G, while the total storage space on the DB server was 28G, so I would think there would not be a problem. I am bringing in multiple tables each being 500MB-1GB and doing it in set of 1-2 at a time.

Does copying data into MSSQL server take more space than the data itself? How much (temporary storage)? How can I tell (not much experience with DBA)?

0

The error messages are indicating that the log file is full. SQL Server has two files (at least) for every database; one is the data file and one is the log file. On your particular server, it is possible that the log file is on a disk that doesn't have enough space for the log file while the data file may be on a disk where it has plenty of space. If the database is in Simple recovery mode, simply execute a full backup and the log file will clear. However, you may need to find out where the log file is located and make sure the disk has enough space to house the log file while performing the BCP. You can do this by right-clicking on the database and going to Properties. Then click on Files on the left side. This will show you the physical path for the log file (.ldf).

2
  • Looking at the log file from your instructions I can see that it was 1MB (we simply restored a day old snapshot of the DB server) and is on the same disk as the data file for the target DB. Are you saying that this log file plus the 10G of data attempting to be copied in was going past the 20G of remaining free space on the server? How could this be happening? Note that most of the bcp copy-in operations are preceeded by a truncation of the target table for each. – lampShadesDrifter Feb 6 '20 at 22:01
  • You need to monitor the transaction log file size as it's importing the data. Normally I allow for 3x space in the data and log files when loading data. – Randolph West Feb 9 '20 at 21:00
0

First, check what is your recovery mode, having log errors like the ones you pasted are often related to recovery mode Full. Check it using following query:

select name, recovery_model_desc from sys.databases

If it is anything but simple, every bit of data is saved multiple times, first in data file and in the log file. If you have staging tables, all inserts to these tales are preserved in logs too, even if you delete the data from the table itself.
It is needed for the possible recovery to point in time.
In this scenario you need to switch recovery mode to Simple and then db will purge logs automatically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.