I'm running a T-SQL script to migrate old data to our new DB. It's taking to long to process! Right now it's more than 2 and a half hours. Is there a way to check the status of the executing script?
我正在运行一个T-SQL脚本来将旧数据迁移到我们的新数据库。这需要很长时间才能完成!现在已超过2个半小时。有没有办法检查执行脚本的状态?
4 个解决方案
#1
you could just continually run "select count(*) from table_being_populated" on the new database and compare it to the counts in the old DB and watch it grow as the script is running to get an idea of how much data is currently in the new DB.
您可以在新数据库上连续运行“select count(*)from table_being_populated”并将其与旧数据库中的计数进行比较,并观察它在脚本运行时增长,以了解新数据库当前有多少数据D B。
#2
An easy way would be to include print statements at intervals in your script.
一种简单的方法是在脚本中间隔包含打印语句。
#3
RAISERROR ('marker', 10, 1) WITH NOWAIT
This provides immediate feedback to the client: PRINT may not. Severity 10 also means it's a warning so does not make the code fall over.
这为客户提供即时反馈:PRINT可能不会。严重性10也意味着它是一个警告,因此不会使代码失效。
#4
You aren't moving the data is some sort of cursor are you? If you are moving one record at a time, this could take days or months even depending on how much data. Now if you have lots of data to move, you might want a loop but still not a one record at a time cursor, rather a loop that processes a batch of records. It is often faster to insert a million records, 1000 at a time than all million at once.
你是不是移动数据是某种光标的吗?如果您一次移动一条记录,则可能需要数天或数月,具体取决于数据量。现在,如果要移动大量数据,您可能需要一个循环但仍然不是一个时间游标的一个记录,而是一个处理一批记录的循环。插入一百万条记录通常更快,一次1000条记录,而不是一次百万条记录。
If everything is currently in one transaction, you may also have problems if you need to stop and rethink this long_running process as it will have to rollback everything. This can take a long time.
如果当前所有事务都在一个事务中,那么如果您需要停止并重新考虑这个long_running进程,您可能也会遇到问题,因为它必须回滚所有内容。这可能需要很长时间。
SInce you didn't set this up with logging or some other way to watch progress easily, I think the suggestion to monitor the count(*) on the tables being inserted to is a good one (and may be you only real choice). Just make sure you use no lock or the results may not return until the insert is done. Also check to see if there is blocking happening, often when something like this is taking too long, some other process is blocking it.
如果您没有通过日志记录或其他方式轻松查看进度,我认为监控正在插入的表上的计数(*)的建议是一个好的(并且可能是您唯一的选择)。只需确保不使用锁定,否则在插入完成之前结果可能不会返回。还要检查是否发生了阻塞,通常当这样的事情花费太长时间时,其他一些进程就会阻止它。
#1
you could just continually run "select count(*) from table_being_populated" on the new database and compare it to the counts in the old DB and watch it grow as the script is running to get an idea of how much data is currently in the new DB.
您可以在新数据库上连续运行“select count(*)from table_being_populated”并将其与旧数据库中的计数进行比较,并观察它在脚本运行时增长,以了解新数据库当前有多少数据D B。
#2
An easy way would be to include print statements at intervals in your script.
一种简单的方法是在脚本中间隔包含打印语句。
#3
RAISERROR ('marker', 10, 1) WITH NOWAIT
This provides immediate feedback to the client: PRINT may not. Severity 10 also means it's a warning so does not make the code fall over.
这为客户提供即时反馈:PRINT可能不会。严重性10也意味着它是一个警告,因此不会使代码失效。
#4
You aren't moving the data is some sort of cursor are you? If you are moving one record at a time, this could take days or months even depending on how much data. Now if you have lots of data to move, you might want a loop but still not a one record at a time cursor, rather a loop that processes a batch of records. It is often faster to insert a million records, 1000 at a time than all million at once.
你是不是移动数据是某种光标的吗?如果您一次移动一条记录,则可能需要数天或数月,具体取决于数据量。现在,如果要移动大量数据,您可能需要一个循环但仍然不是一个时间游标的一个记录,而是一个处理一批记录的循环。插入一百万条记录通常更快,一次1000条记录,而不是一次百万条记录。
If everything is currently in one transaction, you may also have problems if you need to stop and rethink this long_running process as it will have to rollback everything. This can take a long time.
如果当前所有事务都在一个事务中,那么如果您需要停止并重新考虑这个long_running进程,您可能也会遇到问题,因为它必须回滚所有内容。这可能需要很长时间。
SInce you didn't set this up with logging or some other way to watch progress easily, I think the suggestion to monitor the count(*) on the tables being inserted to is a good one (and may be you only real choice). Just make sure you use no lock or the results may not return until the insert is done. Also check to see if there is blocking happening, often when something like this is taking too long, some other process is blocking it.
如果您没有通过日志记录或其他方式轻松查看进度,我认为监控正在插入的表上的计数(*)的建议是一个好的(并且可能是您唯一的选择)。只需确保不使用锁定,否则在插入完成之前结果可能不会返回。还要检查是否发生了阻塞,通常当这样的事情花费太长时间时,其他一些进程就会阻止它。