Description
There seems some kind of buffer limitation when using the sqlcmd tool to run query.
Problem:
When I use sqlcmd to run restore database sql query, it will hang if I keep the STATS=5 keyword in the query (see manual_testing_setup_script.sql below). Also, when use the sqlcmd to run query files with bunch of update query statement, I can only run 4 update statements at a time and it will hang at the fifth update statement.
Reproduce:
Below is the log when try to reproduce the issue. In the Dockerfile, besides copies the sql query file and download the sqlcmd to the container, I will start the server and run a simple restore database query but when it does that, it will hang and stop at 30 percent processed.
For this test, I used Microsoft northwind.bak file.
=> [internal] load .dockerignore 0.0s
=> => transferring context: 2B 0.0s
=> [internal] load metadata for mcr.microsoft.com/azure-sql-edge:latest 0.0s
=> CACHED [ 1/15] FROM mcr.microsoft.com/azure-sql-edge:latest 0.0s
=> [internal] load build context 0.0s
=> => transferring context: 758B 0.0s
=> [2/8] COPY --chown=mssql:root manual_testing_setup_script.sql /backup/manual_testing_setup_script.sql 0.0s
=> [3/8] COPY --chown=mssql:root northwind.bak /backup/northwind.bak 0.0s
=> [4/8] COPY --chown=mssql:root restore_db_test.sh /backup/restore_db_test.sh 0.0s
=> [5/8] RUN chmod a+rwx /backup/restore_db_test.sh 0.2s
=> [6/8] RUN chmod g+rwx /backup/restore_db_test.sh 0.2s
=> [7/8] RUN if [ ! -d /opt/mssql-tools/bin ] ; then echo '\"/opt/mssql-tools/bin\"" directory not found, download the sqlcmd tool...' >> /tmp/arm64.log && mkdir -p /o 3.0s
=> [8/8] RUN /backup/restore_db_test.sh 276.4s
=> => # 5 percent processed.
=> => # 11 percent processed.
=> => # 15 percent processed.
=> => # 20 percent processed.
=> => # 26 percent processed.
=> => # 30 percent processed.
this is my sql query in the manual_testing_setup_script.sql file
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2048;
GO
RECONFIGURE;
GO
RESTORE DATABASE [northwind]
FROM
DISK = N'/backup/northwind.bak'
WITH
FILE = 1,
MOVE N'northwind' TO N'/var/opt/mssql/data/northwind.mdf',
MOVE N'northwind_log' TO N'/var/opt/mssql/data/northwind.LDF',
NOUNLOAD,
STATS = 5
GO
If I removed the STATS = 5 in my query, the build will succeed.
Below is the content in my restore_db_test.sh file
export ACCEPT_EULA=Y
export SA_PASSWORD='password1@'
export MSSQL_SA_PASSWORD='password1@'
echo "Starting up sql server..."
/opt/mssql/bin/sqlservr > /backup/startup.log &
echo " Waiting a few seconds for sql server to start..."
sleep 30
echo "Run sql server setup ....."
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i /backup/manual_testing_setup_script.sql
The same issue also happened when I try to run multiple DB update statements. I will need to break them down into smaller batches, then it can run successfully without hanging.
Finally, the same issue doesn't happen when I use the Azure Data Studio sqlcmd tool to run the query or to restore the database.
Thank you for your time and help. Please let me know if you need more information.
Original question: #36 (reply in thread)