SQL Start /wait Command?

May 16, 2008

So, I'm fairly new to SQL, and I'm working with a SQL2k5 Database with pre-made packages and what-not. This database was setup before I started this job, and now I'm trying to improve part of the processing in SQL, and so far so good, but I can't figure a couple things out.

The main problem is when I start a SQL command to launch a DTS package from a .sql file, how can I make it wait for the package to complete or fail before moving onto the next part of the .sql script? Hope it's a simple question, I've just taught myself enough SQL to get by in a couple of weeks.

Thanks!

View 6 Replies


ADVERTISEMENT

How Do You Start A Query Running, But Don't Wait For It To Finish

Apr 6, 2001

Here's my dilema, I want to run a stored procedure that starts another stored procedure running, but does not wait for the stored procedure to complete execution.

The stored procedure should execute immediately, and leave the other procedure to complete running in the background. Is there any way to do this?

View 3 Replies View Related

Open Transaction With Sleeping Status And Wait Type As AWAITING COMMAND

Sep 4, 2015

During our DR drill we found that the same code which used to run perfectly fine on our Primary Data Centre is running very slow on Disaster Recovery DB Server and there are Lot's of open transaction with sleeping status and waittype as 'AWAITING COMMAND'.CPU, Memory and disk utilization are good. The ping reply between the app server and the DB server is well within the limit's even blocking is not their, also nothing is reported in the error logs. We are using SQL server 2014 STD 64 BIT on Windows Server 2012.

View 6 Replies View Related

How To Resolve An Indefinite Wait State On Update Command In SQL2005 (64bit 2003 Server)?

Jun 16, 2006

I am in the process of moving a SQL2000 database to a SQL2005 database.

Porting from: SQL200, Windows Server 2000(SP4) (32 bit dual processor)
to:SQL2005, Windows Server 2003(SP1) (x64 bit dual processor)


After porting the database from SQL2000 to SQL2005 (no changes) running the same update statement from Management Studio on the 2003 Server and and Query analiser on the 2000 Server.

SQL2000 completes the command in 2 minutes SQL2005 is still running after 60 minutes.

SQL2000 is the live/production system with users connected, the SQL2005 is in a test environment with no other processors running.

When the problem first showed up the SQL2005 activity monitor displayed CXPACKET wait type on 2 processes with the same pid number. I now no longer have any wait type being displayed but my wait time is increasing rapidly. No block is reported.

I assume that I have an CXPACKET lock problem.

Am I correct that I have a CXPACKET problem and if so what is the resolution?

The update statement is as follows...

update BI_LENDING_TRANSACTIONS
set [Balance Movement Month] = M.[Balance Movement Month]
from BI_LENDING_TRANSACTIONS as T,
BI_BALANCE_MOVEMENT_DATES as M,
BI_COMPANIES as C
where (T.[Transaction Date] >=
(SELECT DATEADD(d, - 70, minDate) from (select min([Transaction Date]) minDate
from p_BI_LENDING_TRANSACTIONS) t1)
OR
T.[Transaction Date] >= C.[MostRecentSnapShotDate] or
T.[Value Date] = T.[Balance Movement Month] ) and
T.[Value Date] <= C.[MostRecentSnapShotDate] and
T.[Value Date] >= T.[Transaction Date] and
T.[Company_Code] = M.[Company_Code] and
T.[Value Date] > M.[SnapShotFromDate] and
T.[Value Date] <= M.[SnapShotToDate] and
C.[Company_Code] = M.[Company_Code]



View 1 Replies View Related

Start Job From The Command Prompt?

Feb 23, 2006

I have a Microsoft SQL server that is setup, and inside it is a job. Inside the job is a bunch of lines of transact sql statements.

I would like to be able to start the job from the command prompt. Moreover, I do not want to have to create new files or modify anything that is existing. I just want to be able to have it set up so that a new user who installs the server and all of the job on their machine fresh, can just punch in the command to run the job.

I know I can copy the transact sql statements from the job, and run them through the command prompt, but that would be a poor way to do it, because if the statements in the job were ever modified, with this task being automated, the command prompt statements would be out of date.

So I just need a way to start the existing job from the command prompt. Is this possible? Maybe with OSQL?

View 4 Replies View Related

SQL COMMAND START SQL AGENT

Dec 14, 2007

HELLO TO ALL OF YOU


IS THERE ANY SQL COMMAND IN ORDER TO START SQL AGENT ?

THANKS IN ADVANCE

View 3 Replies View Related

Start A Job Task From The Command Line & Check To See If It&#39;s Running

Apr 22, 2000

I need to start a job task from the command line. Does anyone know of the correct command(s) to do this?

I also need to check to see if a specific job task is currently executing from the command line. Does anyone know of the correct command(s) to do this?

Thanks for any assistance.

Jim

View 4 Replies View Related

SAC Command Line Utility Doesn't Start SQLBrowser Service

Feb 16, 2007

I've exported surface area configuration settings from a server and tried importing them onto another server. The remote connection settings are imported but the SQLBrowser service remains Disabled. Am I doing something wrong?

The command line command I ran (in the C:Program FilesMicrosoft SQL Server90Shared directory) is "sac.exe in sacconfig.xml".

The following is my config file:

<?xml version="1.0" encoding="utf-8"?>
<CommandArguments xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Components>
<anyType xsi:type="Component">
<Type>SqlServer</Type>
<InternalType>DatabaseEngine</InternalType>
<Instances>
<anyType xsi:type="Instance">
<Name>SQLEXPRESS</Name>
<Service>
<Name>MSSQL$SQLEXPRESS</Name>
<StartMode>Auto</StartMode>
<ServiceState>Running</ServiceState>
</Service>
<Features>
<anyType xsi:type="Pair">
<Key>AdhocRemoteQueries</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>CLRIntegration</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>DedicatedAdministrator</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>DatabaseMail</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>HTTPAccess</Key>
<Value xsi:type="ArrayOfAnyType" />
</anyType>
<anyType xsi:type="Pair">
<Key>OLEAutomation</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>ServiceBroker</Key>
<Value xsi:type="ArrayOfAnyType" />
</anyType>
<anyType xsi:type="Pair">
<Key>SQLMail</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>WebAssistant</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>XP_cmdshell</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
</Features>
<Protocol>
<anyType xsi:type="Pair">
<Key>NamedPipes</Key>
<Value xsi:type="xsd:boolean">true</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>TCPIP</Key>
<Value xsi:type="xsd:boolean">true</Value>
</anyType>
</Protocol>
</anyType>
</Instances>
</anyType>
<anyType xsi:type="Component">
<Type>SqlBrowser</Type>
<InternalType>SqlBrowserService</InternalType>
<Service>
<Name>SQLBrowser</Name>
<StartMode>Auto</StartMode>
<ServiceState>Running</ServiceState>
</Service>
</anyType>
</Components>
<Version>9.0.242.0</Version>
</CommandArguments>

View 1 Replies View Related

SQL 2005 Won't Start After Moving Model Database With Alter DB Command

Sep 11, 2007

I used the code below to move the Model database and Model log file to new SAN drive locations.
After running the commands I stopped the SQL server and moved the physical files to the correct location.
Now I am unable to start SQL server, I get error 17204 Could not open file:Access is denied

I think the problem maybe that for FILENAME new_path I put "J:SQL ServerMSSQL.1MSSQLData"
but did not include model.mdf in the file path.

How do I correct the path now that SQL service won't start?
I have included the log file below.



ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )


2007-09-11 13:19:31.82 Server Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-09-11 13:19:31.82 Server (c) 2005 Microsoft Corporation.
2007-09-11 13:19:31.82 Server All rights reserved.
2007-09-11 13:19:31.82 Server Server process ID is 3264.
2007-09-11 13:19:31.82 Server Authentication mode is MIXED.
2007-09-11 13:19:31.82 Server Logging SQL Server messages in file 'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2007-09-11 13:19:31.82 Server This instance of SQL Server last reported using a process ID of 2648 at 9/11/2007 1:10:02 PM (local) 9/11/2007 8:10:02 PM (UTC). This is an informational message only; no user action is required.
2007-09-11 13:19:31.82 Server Registry startup parameters:
2007-09-11 13:19:31.82 Server -d D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2007-09-11 13:19:31.82 Server -e D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2007-09-11 13:19:31.82 Server -l D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2007-09-11 13:19:31.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-09-11 13:19:31.84 Server Detected 4 CPUs. This is an informational message; no user action is required.
2007-09-11 13:19:31.96 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-09-11 13:19:31.98 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Database mirroring has been enabled on this instance of SQL Server.
2007-09-11 13:19:32.00 spid5s Starting up database 'master'.
2007-09-11 13:19:32.09 spid5s SQL Trace ID 1 was started by login "sa".
2007-09-11 13:19:32.10 spid5s Starting up database 'mssqlsystemresource'.
2007-09-11 13:19:32.10 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid5s Server name is 'SS02'. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid9s Starting up database 'model'.
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'J:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file J:SQL ServerMSSQL.1MSSQLData for file number 1. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "J:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'I:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file I:SQL ServerMSSQL.1MSSQLData for file number 2. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "I:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s File activation failure. The physical file name "I:SQL ServerMSSQL.1MSSQLData" may be incorrect.
2007-09-11 13:19:32.26 spid9s Error: 945, Severity: 14, State: 2.
2007-09-11 13:19:32.26 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2007-09-11 13:19:32.26 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2007-09-11 13:19:32.26 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

View 4 Replies View Related

PAGEIOLATCH_SH Wait

Aug 1, 2000

I am running into problems while running a large procedure, and i think it may have something to do with a PAGEIOLATCH_SH wait problem.
My server, whose sole purpose is to run this one procedure, is doing plenty of disk i/o, and the CPU’s bouncing around, so I assume it’s working. But when I look at its process info, it seems to be sleeping a lot of the time on PAGEIOLATCH_SH. No other users are in the DB, so I'm quite confused. I don't find much info on this anywhere, so any insight would be very appreciated.

View 1 Replies View Related

Event Wait

Jan 15, 2008

I'm new to SQL Sever 2005 and I'm trying to do what Informatica (Power Center - ETL) is trying does.

I have created a work flow and it is scheduled to run at every night 1:00 AM .The process is to load a flat file (CRV.data) into the database from a shared location.The flat file is transfered from a 3rd party and once the file transfer is complete it will create a indicator file (0 byte eg: CRV.DONE file) which indicates the CRV.data transfer is complete.

In my workflow I will be waiting for the CRV.DONE indicator file and once it is avaiable I will start loading the CRV.data and once the load is completed I will delete CRV.DONE file and be ready for the next day load.

Please let me know if there is any way in SQL Server 2005 to achieve it.Thanks

View 3 Replies View Related

Wait For A Response?

Jun 11, 2007

Hi. We are migrating a mainframe datacom database to SQL Server. One of our client-server applications already uses SQL Server. This application uses a middleware product to query and update the datacom database being migrated. We are considering using Service Broker to replace the middleware.

In many cases the client does not need a response provided the message is queued and will eventually get delivered. However, in some cases the client would like to wait for the message to be processed before proceeding. Is there an easy way to both submit and optionally wait for a response - with data - in a single stored procedure? If client does not want to continue to wait, is there a way to use a procedure to check for the returned message later?

We have not used Service Broker before and are doing for a "sanity" check before proceeding. We do not want to tightly couple the two databases at this time.

View 4 Replies View Related

Wait Problem...

Dec 11, 2007

I have installed performance dashboard on 2 different servers. The first server have User Session CPU Time 71% and Wait Time =28%, The other server have Cpu Time of 20% and Wait Time of 79%. Have I understand that stands in SQL Server Waits And Queues that I have some typ of wait problem in my second server?


Then I tries to run this
Select

'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)),
'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats


First Server
%signal waits %resource waits
--------------------------------------- ----------------------------
0.07 99.93
Second Server
%signal waits %resource waits
--------------------------------------- ----------------------------
0.12 99.88

Messy in my head€¦ Help please

My second server have

CLR with 50% in the historial wait. Rest in Sleep

My first server have
99% in sleep wait category.




View 6 Replies View Related

Sysprocesses Says: Wait Forever

Sep 30, 2004

I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.

Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

What should I do?

View 6 Replies View Related

Wait Type: NETWORKIO

Jan 14, 2005

I have an ASP.NET web application that hangs on a single database UPDATE command for 5+ minutes. I can see this occur in SQL Profiler. This is a one row UPDATE statement on a small table (~600 rows). There are no JOINs or sub queries. There are no other users using the system. During this 5+ minutes, I can see the job in Enterprise Manager with a wait type of NETWORKIO. Since both IIS And SQL Server are running on the same system, the network shouldn't be an issue. Any ideas?

View 5 Replies View Related

NETWORKIO - Wait Type

Sep 29, 2006

Hello, my first post??

View 4 Replies View Related

Using BACKUP From OSQL And Wait For It's End

Aug 31, 2006

I'm writing a small vbscript to backup a db and some related files, so I used a WSShell calling OSQL to run a Sql BACKUP command, then after it's finished I XCOPY the resulting file plus some other related files. But the problem is that OSQL ends it's execution as soon as the BACKUP command is sent to SqlServer, not when the backup itself ends.
Anyone knows how to synchronize the two? How to wait, inside OSLQ, for the end of the BACKUP execution?
TIA
Luigi

View 4 Replies View Related

Causes For Latch Wait Time

Jan 6, 2008

I setup a SQL Agent to send me an email when the Average Latch Wait Time is greater than 300ms. Now I receive an email every 15 seconds stating that the current ALWT is 3916ms. That value never changes with the emails. However, the perfmon shows nothing at all (shows zero).

I also have a Buffer cache hit ratio of 2848.00.

These numbers are when there is NOBODY on the DB at all It is just sitting there. When I reboot the server, as soon as SQL starts it starts to send the emails again.

Server: Intel Xeon Quad Core 2.66
RAM: 4GB (with /3GB in the boot.ini)
RAID 1: OS
RAID 1: Data (DB and logs)
CPU Utilization: 0-1%
RAM Utilization: 527MB
OS: Server 2003 R2 With SP2
SQL: 2005 Standard with SP2

How can I determine if the ALWT is really 3916?

I executed 'Select * from sysprocesses where SPID>50 and waittime>0'

Which showed;

BROKER_RECEIVE_WAITFOR
waittime=110640ms
waittype=0x0075
status=suspended
program_name=DatabaseMail90 - Id<3780>

Any assistance is greatly appreciated.


Thank You

Magnum

View 4 Replies View Related

Sql Server Wait Interface?

Jul 20, 2005

Oracle has a 'wait interface' for monitoring bottlenecks, etc... does SQLServer have utilities like this?

View 3 Replies View Related

Check Condition And Wait

Feb 1, 2007

Hi,

I have a data-flow-task that imports data to sqlserver.

Now I want to check, if a special column of an imported record is null.

If yes, I have to wait 10 minutes and jump to the data-flow-task again. (Cjeck and wait).

How can I do this with the integration services?

Thanks

Gerd

View 1 Replies View Related

SSIS Job To Wait And Run After AS400 Job

Mar 7, 2008



We have a few SSIS jobs that we are currently manually kicking off after we are sure that certain AS400 jobs have run. We want to completely automate this process, so that we don't have to babysit. What is the most efficient way to do this? In the past (on SQL Server 7 no less) I've seen the 400 job setting a flag to 'Y' in a 400 file, FTPing it down to a flat file, and then the SQL job running every five minutes checking the flag. When it was 'Y', the SQL job would run. We do not have the option of using FTP here. Any suggestions would be appreciated! After the job runs, we'd like it to kick off a report as well

View 2 Replies View Related

Need To Exec TSQL And Then Wait 60 Sec And Run Again

Dec 14, 2007

I am tring to fiqure out how i can run a TSQL and the have it start again 60 sec after it compleats, without me have to push the button. I just need it to loop over and over until my data is deleted. I have to do it this way so my site will still allow customers to login and I need the break so they can. Any help would be great.

View 3 Replies View Related

Wait For Incoming CSV Files

Jul 5, 2007

are there any tasks in SSIS that watches a folder and waits for incoming files?

View 9 Replies View Related

Async_network_io Wait During Replication

Jun 8, 2006

I have re-initialize some subscription. After generated snapshot successfully, the synchronization view showing 'The process is running and is waiting for a response from the server.' and I found the replication process is under 'async_network_io wait'. The job has run for more than 8 hrs. Any idea to improve it? Thanks in advaise.

View 3 Replies View Related

How To Wait For File Before Continuing

Apr 11, 2007

Good morning,

I will start off with the default warning message: I am a beginner. That said, I have an SSIS process that calls an external executable to transform a data file through a homegrown C program. (This will eventually be converted, but for the moment needs to remain.) The end of the run creates a *.done file. How do I use the SSIS tasks to pause/wait, checking periodically, for the existence of this file before continuing with processing tasks? I apologize if this is easy, but I am stumped.

Thanks in advance,
Roger

Info:
SQL 2005
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

View 3 Replies View Related

IS Same? (RS Start In Window Severvices V RS Start In Configration)

Mar 30, 2007



1) RS Start in Window Severvices
2) RS Start in Configration

1) and 2) Is Same?

I think that It looks like same Operation.

View 1 Replies View Related

Current Acitvity - Wait Types

Feb 9, 2001

When I run some of the quiries, in current activity, wait type would say:
PAGEIOLATCH_SH what does it means ? Is there a source I can see all
types of wait types ?

Help is much appreciated.
Thanks,

View 1 Replies View Related

Average Lock Wait Time

Oct 3, 2014

I keep getting an alert from a Third-Party Tool stating my server is reporting failed components with the following message

Components Reporting Problems:
Average Lock Wait Time(ms)(Critical)

When I look at sp_who2 or sp_lock I don't see anything blocking processes or anything else out of the ordinary.

View 2 Replies View Related

Chronic CXPACKET Wait Issue

Jul 24, 2007

CXPACKET wait type current makes up 63% of all the wait types which is causing latency. I need to identify the specific workloads responsible for waits so I can optimise or MAXDOP them. I already know how to retreived the top IO, CPU, Memory consuming queries but how do I identify the statements and order them by wait time?

Can someone point me in the direction of a command, DMV or will the top CPU list be adequate?

Thanks

View 3 Replies View Related

Why Does UNION ALL Wait For A Row From Each Input Buffer?

Dec 13, 2006

Hello,

It seems strange to me that once the UNION ALL component waits for at least one row from each input buffer before it puts anything into the output buffer (that's the behaviour that I have observed anyway).



Why does it do that?



Thanks

-Jamie



[SSIS Team Followup]

View 4 Replies View Related

DB Engine :: Histogram Of Wait Events

Oct 5, 2015

I would like to know what a particular session is waiting on (cpu, io, memory etc).

For that I try to figure out how to use extended events. I would prefer to avoid the use files written to db-server, why I tried "histogram target".

Well my attempt does not produce usefull output,

-- check current event sessions

SELECT a.name,
CASE WHEN b.name IS NOT NULL
THEN 'Started'
ELSE 'Stopped'
END AS current_state

[Code] ....

The intention was to get a pareto chart, to see the top wait events in the session. I would also like to see amount of cpu used. Based on this tuning effort could be prioritized.

View 6 Replies View Related

Moving Data From One Filegroup To Another.....but Wait, There Is More!!

May 13, 2008

OK, I know this is out there all over and yes I did a search for this topic; however, I am confused about tables with an image data type and with moving text file group to another filegroup.

Here is what I have:

I have a table storing imaged documents and has become very large. I want to move the table to another filegroup. The table is created like this:

USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/13/2008 14:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID])
REFERENCES [dbo].[TOPICS] ([TopicID])
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS2]

On a test DB, I moved the clustered and nonclustered indexes to a secondary filegroup no problem, but it still shows to be stored in the primary filegroup. I read an article about having to create a new table in the secondary in order to move the images and text file group. Has anyone come across this?

Do I need to drop the clustered index and FK to move to a secondary filegroup?

Or

Do I create a new table into the secondary filegroup and then add the Clustered index and constraints?

View 4 Replies View Related

DB Engine :: Analyzing Wait Stats

Aug 4, 2015

For one of our SQL server 2005 Ent edition 64 bit SP4 which has transnational replication set up and used for heavy reporting, i was trying to counter out the performance of slow running queries which basically runs and get suspended and most often are seeing waiting:So i tried to analyse the wait stats and come up with below stats where ASYNC_NETWORK_IO dominated for a collection of two weeks data.

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved