While trying to update a table definition (change a field type from NVARCHAR(5) to NVARCHAR(8)) we caused a lock on the database. There were a large number of rows (>11,000,000) so we decided to kill the process and roll back the update.
During the rollback the status of the process was showing as 'sleeping' with a Wait Type of IO_COMPLETION or PAGEIOLATCH_EX.
How can I give the KILL/ROLLBACK process a higher priority and prevent it going to sleep?
We use an sql agent job running on our SQL 2000 server to import data from our business system every ten minutes. The job runs a number of steps and usually takes about 5 to 6 minutes to run. This data is used for various packing processes.
This will usually run with no problems but on occasion we have problems where a step will get 'stuck' which means we do not get new data from the business system.
I would like to display the status of the job on a screen in our office so we can see if there is a problem. Is there a query I can perform on the database to return the status of a job, if it is running, which step it's on, how long it's been running and when it is next scheduled to run?
We have two MS SQL servers (one 2000 and one 2005) that run a number of jobs at various time during the day and night. These jobs have a number of steps that will pull data from an Informix database (our business system) and update tables for use by various reporting and production systems.
We had a failure of these imports over the weekend which resulted in many reporting systems giving wrong data.
Due to security restrictions we are not able to enable sending emails from the servers so how can we monitor these job effectively without having to constantly check the Job status in Enterprise manager or MS SQL Studio? Is it possible to run a query on the servers and find the status of various jobs?
Currently we backup our MSSQL 2000 and 2005 databases using software to copy files to tape nightly. Database sizes are 14-16Gb and 500Mb.
As SQL server can backup a database to a file would we be better scheduling SQL server to create a backup file and then backing up these files to tape? Also if we use this method is it possible to somehow create a log of transactions that have been completed since the last backup so we can recreate the database and apply the additional transactions?