Datafiles placement in filegroup

I'm working with a production database connecting remotely. All the datafile
s, index files and log is placed on the same disk but in different filegroup
names based on separate physical files. But I expect if I place the index f
iles or/add log files sep
arate from the datafiles I mean in different disk it will improve the perfor
mance.
If my expectation is correct then can I change the place of the index and lo
g files in separate location while the database is on-line yes the database
is implemented with log-shipping too.
Please evaluate my query and do give a proper suggestion.
Thanks in advance
Sunilsurely it will improve the performance if the log files are placed seperate
from the data files (different disks).
for the indexes u can delete the existing index and recreate it specifying
the new location.
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4690C174-4988-4737-82D9-7C0ED08FDF5D@.microsoft.com...

quote:

---

> I'm working with a production database connecting remotely. All the

---

datafiles, index files and log is placed on the same disk but in different
filegroup names based on separate physical files. But I expect if I place
the index files or/add log files separate from the datafiles I mean in
different disk it will improve the performance.

quote:

---

> If my expectation is correct then can I change the place of the index and

---

log files in separate location while the database is on-line yes the
database is implemented with log-shipping too.

quote:

---

>
> Please evaluate my query and do give a proper suggestion.
>
> Thanks in advance
> Sunil
>

---

Datafiles placement in filegroup

I'm working with a production database connecting remotely. All the datafiles, index files and log is placed on the same disk but in different filegroup names based on separate physical files. But I expect if I place the index files or/add log files separate from the datafiles I mean in different disk it will improve the performance.
If my expectation is correct then can I change the place of the index and log files in separate location while the database is on-line yes the database is implemented with log-shipping too.
Please evaluate my query and do give a proper suggestion.
Thanks in advance
Sunilsurely it will improve the performance if the log files are placed seperate
from the data files (different disks).
for the indexes u can delete the existing index and recreate it specifying
the new location.
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:4690C174-4988-4737-82D9-7C0ED08FDF5D@.microsoft.com...
> I'm working with a production database connecting remotely. All the
datafiles, index files and log is placed on the same disk but in different
filegroup names based on separate physical files. But I expect if I place
the index files or/add log files separate from the datafiles I mean in
different disk it will improve the performance.
> If my expectation is correct then can I change the place of the index and
log files in separate location while the database is on-line yes the
database is implemented with log-shipping too.
>
> Please evaluate my query and do give a proper suggestion.
>
> Thanks in advance
> Sunil
>|||You are mostly correct.
Creating new files doesn't automatically improve
performace, it also depends upon the raid configuration of
the disk its going on.
For instance putting a log file on a RAID 0 disk will make
it faster than putting it on a raid 5 disk.
Although its different for every type of application a
normal implementation with cost constraints its to put the
log files on a raid 0 or raid 0+1 with the data files on
raid 5.
J
>--Original Message--
>I'm working with a production database connecting
remotely. All the datafiles, index files and log is placed
on the same disk but in different filegroup names based on
separate physical files. But I expect if I place the index
files or/add log files separate from the datafiles I
mean in different disk it will improve the performance.
>If my expectation is correct then can I change the place
of the index and log files in separate location while the
database is on-line yes the database is implemented with
log-shipping too.
>
>Please evaluate my query and do give a proper suggestion.
>
>Thanks in advance
>Sunil
>.
>

Datafile Full

Hi NG,
We have a datafile on drive d: and the log file on drive e: and now the
problem is drive d: is running out of space. We are planning to buy a new
hardisk drive f: what should I do next after we install the new hardisk?
Should I create a new datafile and include that in the primary filegroup and
set the new datafile as default. Is that all I need to do?
Thank you in advance.
its 1 option, yes.
just disable the file growth option for the first file (the one on the d
you can also create a new file group and move some tables from the primary
file group to the new one.
"JackCool" <jackcool@.yahoo.com> wrote in message
news:upMbY3aKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> We have a datafile on drive d: and the log file on drive e: and now the
> problem is drive d: is running out of space. We are planning to buy a new
> hardisk drive f: what should I do next after we install the new hardisk?
> Should I create a new datafile and include that in the primary filegroup
> and set the new datafile as default. Is that all I need to do?
> Thank you in advance.
>

Datafile Full

Hi NG,
We have a datafile on drive d: and the log file on drive e: and now the
problem is drive d: is running out of space. We are planning to buy a new
hardisk drive f: what should I do next after we install the new hardisk?
Should I create a new datafile and include that in the primary filegroup and
set the new datafile as default. Is that all I need to do?
Thank you in advance.its 1 option, yes.
just disable the file growth option for the first file (the one on the d
you can also create a new file group and move some tables from the primary
file group to the new one.
"JackCool" <jackcool@.yahoo.com> wrote in message
news:upMbY3aKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> We have a datafile on drive d: and the log file on drive e: and now the
> problem is drive d: is running out of space. We are planning to buy a new
> hardisk drive f: what should I do next after we install the new hardisk?
> Should I create a new datafile and include that in the primary filegroup
> and set the new datafile as default. Is that all I need to do?
> Thank you in advance.
>

DataFile and Log File

I want to know the datafile and log file for the given input parameter. I am
basically creating function and the input parameter would be database name
and that will return me DataFile and the LogFile.
Can anybody tell me which query I have to used in the function. I have to
used to get the DataFile and LogFile
Have a look at the sys.database_files catalog view in BOL.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>I want to know the datafile and log file for the given input parameter. I
>am basically creating function and the input parameter would be database
>name and that will return me DataFile and the LogFile.
> Can anybody tell me which query I have to used in the function. I have to
> used to get the DataFile and LogFile
>
|||In SQL Server 2000?
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGIt1IRiHHA.392@.TK2MSFTNGP06.phx.gbl...
> Have a look at the sys.database_files catalog view in BOL.
> --
> Andrew J. Kelly SQL MVP
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>
|||On Sat, 28 Apr 2007 10:42:15 -0400, Roger wrote:

>In SQL Server 2000?
Hi Roger,
A more polite way to write this would have been; "Oh, I'm sorry I forgot
to include in my original post that I'm using SQL Server 2000. Is there
an alternative method for that version?"
The answer to that question would have been to check the sysfiles system
table in Books Online.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Data-driven subscription - Invalid parameter?

Hi,

I've set up a data-driven subscription on a report but it's not sending and the error log says it's because:

Default value or value provided for the report parameter 'CSRFaxID' is not a valid value.

For the particular dataset record causing the error I'm passing the value Null to the report parameter. The report parameter CSRFaxID is set up with "Allow Null" and with a default value of Null. I can run the report in my browser with CSRFaxID set to null, but the scheduled subscription is not able to produce the report with the same parameter value. Does anyone have any idea what the problem is and how I can fix it?

Regards,

Greg McNamara

Hi,

NULL values can cause plenty of headaches and unpredictable behavior in sub-systems.

Try not to use NULL. Set the default to 'UNK' or 0 instead and make sure that no null values are left over.

If needed disallow nulls or replace nulls by something like 'UNK'

Philippe

|||

Hi Philippe,

Thanks for your help. I actually tried something similar to this and it worked ok. The column is type uniqueidentifier so I just passed a guid with all zeroes. It seems a bit strange to have to use this workaround, there seems to be an inconsistency between data-driven subscription datasets and defined report parameters where NULL is concerned.

Regards,

Greg

Data-driven subscription - Invalid parameter?

Hi,

I've set up a data-driven subscription on a report but it's not sending and the error log says it's because:

Default value or value provided for the report parameter 'CSRFaxID' is not a valid value.

For the particular dataset record causing the error I'm passing the value Null to the report parameter. The report parameter CSRFaxID is set up with "Allow Null" and with a default value of Null. I can run the report in my browser with CSRFaxID set to null, but the scheduled subscription is not able to produce the report with the same parameter value. Does anyone have any idea what the problem is and how I can fix it?

Regards,

Greg McNamara

Hi,

NULL values can cause plenty of headaches and unpredictable behavior in sub-systems.

Try not to use NULL. Set the default to 'UNK' or 0 instead and make sure that no null values are left over.

If needed disallow nulls or replace nulls by something like 'UNK'

Philippe

|||

Hi Philippe,

Thanks for your help. I actually tried something similar to this and it worked ok. The column is type uniqueidentifier so I just passed a guid with all zeroes. It seems a bit strange to have to use this workaround, there seems to be an inconsistency between data-driven subscription datasets and defined report parameters where NULL is concerned.

Regards,

Greg

databse restore

Hello,
I have situation where I will need to restore from.
Monday -- Full backup + Transaction Log ever hour
Tuesday -- Deferential Backup +Transaction Log ever hour
Wednesday --Deferential Backup Transaction Log ever hour at 7 PM database
suspect and I will need to restore from my backup
Do I go and restore Full backup + deferential backup of Tuesday +
deferential Backup of Wednesday + Transaction log that was taken after that
deferential backup on Wednesday ?
Thanks,Hello,
I would recommend to find out first why database became suspect and fix that
problem. To restore use Monday full backup, the last differential backup
(Wednesday) and all the transaction log backups since the last differential
backup.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ron A" wrote:
> Hello,
>
> I have situation where I will need to restore from.
>
> Monday -- Full backup + Transaction Log ever hour
>
> Tuesday -- Deferential Backup +Transaction Log ever hour
>
> Wednesday --Deferential Backup Transaction Log ever hour at 7 PM database
> suspect and I will need to restore from my backup
>
> Do I go and restore Full backup + deferential backup of Tuesday +
> deferential Backup of Wednesday + Transaction log that was taken after that
> deferential backup on Wednesday ?
>
> Thanks,
>
>|||Ron
How about restoring FULL backup + LOG backup at 7PM ( I assume you took
backup log file just after a database became corrupted)
"Ron A" <omranu@.Gmail.com> wrote in message
news:er$LhgoHIHA.2100@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> I have situation where I will need to restore from.
>
> Monday -- Full backup + Transaction Log ever hour
>
> Tuesday -- Deferential Backup +Transaction Log ever hour
>
> Wednesday --Deferential Backup Transaction Log ever hour at 7 PM
> database suspect and I will need to restore from my backup
>
> Do I go and restore Full backup + deferential backup of Tuesday +
> deferential Backup of Wednesday + Transaction log that was taken after
> that deferential backup on Wednesday ?
>
> Thanks,
>
>

Databse in Use

I am attempting to schedule a Transaction log update to our reports server.
the transaction log completes fine as long as no one is in the DB. How can
I restart SQL or drop the users before I run the log restore so I do not get
the Database in use error?How about executing ALTER DATABASE and set it in single user with the
ROLLBACK IMMEDIATE option?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> I am attempting to schedule a Transaction log update to our reports
server.
> the transaction log completes fine as long as no one is in the DB. How
can
> I restart SQL or drop the users before I run the log restore so I do not
get
> the Database in use error?
>|||Thanks for the quick response. I will give that a shot.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:exy65V8pDHA.372@.TK2MSFTNGP11.phx.gbl...
> How about executing ALTER DATABASE and set it in single user with the
> ROLLBACK IMMEDIATE option?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
> news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > I am attempting to schedule a Transaction log update to our reports
> server.
> > the transaction log completes fine as long as no one is in the DB. How
> can
> > I restart SQL or drop the users before I run the log restore so I do not
> get
> > the Database in use error?
> >
> >
>

Databases Starting Repeatedly

I have four databases that show up in the database log as
starting over and over again. While this activity is
going on the web based application is unable to connect.
I have checked all available log files for errors but have
not received the first one as of yet.
I need to call on the experts in the field. Have any of
you ever seen this before? If so, what did you do to
correct the problem?
This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
with 2GB of ram and two 2.4 GHZ processors.
I would appreciate anyone's help as this is a show stopper
for me. Thanks in advance.
A portion of log follows.
**********************************************
2003-09-29 09:53:38.42 server Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
Microsoft Corporation.
2003-09-29 09:53:38.42 server All rights reserved.
2003-09-29 09:53:38.42 server Server Process ID is 996.
2003-09-29 09:53:38.42 server Logging SQL Server
messages in file 'e:\Program Files\Microsoft SQL
Server\MSSQL\log\ERRORLOG'.
2003-09-29 09:53:38.42 server SQL Server is starting at
priority class 'normal'(4 CPUs detected).
2003-09-29 09:53:38.67 server SQL Server configured for
thread mode processing.
2003-09-29 09:53:38.67 server Using dynamic lock
allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2003-09-29 09:53:38.79 server Attempting to initialize
Distributed Transaction Coordinator.
2003-09-29 09:53:40.29 spid3 Starting up
database 'master'.
2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
2003-09-29 09:53:40.74 spid3 Skipping startup of clean
database id 5
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 6
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 7
2003-09-29 09:53:40.78 spid5 Starting up
database 'msdb'.
2003-09-29 09:53:40.78 spid6 Starting up
database 'hsmqa'.
2003-09-29 09:53:40.78 spid7 Starting up
database 'WebTrend'.
2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
version '8.0.760'.
2003-09-29 09:53:40.78 spid8 Starting up
database 'model'.
2003-09-29 09:53:40.78 spid9 Starting up
database 'WebData'.
2003-09-29 09:53:40.78 spid12 Starting up
database 'WebCache'.
2003-09-29 09:53:40.81 spid7 Analysis of
database 'WebTrend' (10) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.85 spid12 Analysis of
database 'WebCache' (12) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.92 server SQL server listening on
TCP, Shared Memory, Named Pipes.
2003-09-29 09:53:40.92 server SQL Server is ready for
client connections
2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
2003-09-29 09:53:41.10 spid8 Starting up
database 'tempdb'.
2003-09-29 09:53:41.21 spid3 Recovery complete.
2003-09-29 09:53:41.21 spid3 SQL global counter
collection task is created.
2003-09-29 09:53:42.45 spid3 Launched startup
procedure 'hbi_unlock_objects'
2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
version '2000.80.194' to execute extended stored
procedure 'xp_qv'.
2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
version '2000.80.760' to execute extended stored
procedure 'sp_MSgetversion'.
2003-09-29 09:54:30.64 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:54:30.75 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:54:30.84 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:44.43 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:44.53 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:44.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:45.98 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.07 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.17 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.26 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.36 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.45 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.57 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.67 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.76 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.86 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.95 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.04 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.14 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.23 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.32 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.42 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.53 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.76 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.86 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.95 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.21 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.31 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.40 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.93 spid53 Starting up
database 'hsmprod'.
2003-09-29 10:00:01.78 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:01.87 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:01.98 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.07 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:02.40 spid87 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.51 spid87 Starting up
database 'hsmtrain'.Hi Thomas,
Such issues tend to be complex and require extensive research. I'd like to
set the right expectations and let you know that it may take a while for us
to help you narrow down the problem.
Please check to see if the "Auto close" is checked in the Option tab in the
property dialog of the database. If so, make sure this option is unchecked
and try to connect to SQL Server again. Does this resolve the problem?
If not, I would like you to provide me with more information regarding this
issue so that I can narrow down it.
Please make sure that the MSSQLServer service, for the SQL Server you are
trying to connect to, is started and running before doing any further
troubleshooting.
1. What do you mean by "the web based application is unable to connect"?
How do you connect SQL Server? Please describe it in detail. Do any error
messages occur?
2. Ping the server using the server name and IP address. See if the request
completes successfully or times out.
3. Can you connect to SQL Server using Query Analyzer? If not, does any
error message occur?
4. What is the MDAC version on the client machine? For more information
regarding how to check the MDAC version, please refer to the following
article:
301202 HOW TO: Check for MDAC Version
http://support.microsoft.com/?id=301202
5. Please provide the complete error log, you can send it at
v-yshao@.microsoft.com
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Most probably the autoclose database option turned on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Thomas Rhodes" <thomas.rhodes@.nghs.com> wrote in message
news:1c7601c386a0$fb9485d0$a001280a@.phx.gbl...
> I have four databases that show up in the database log as
> starting over and over again. While this activity is
> going on the web based application is unable to connect.
> I have checked all available log files for errors but have
> not received the first one as of yet.
> I need to call on the experts in the field. Have any of
> you ever seen this before? If so, what did you do to
> correct the problem?
> This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
> with 2GB of ram and two 2.4 GHZ processors.
> I would appreciate anyone's help as this is a show stopper
> for me. Thanks in advance.
> A portion of log follows.
> **********************************************
> 2003-09-29 09:53:38.42 server Microsoft SQL Server
> 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
> 2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
> Microsoft Corporation.
> 2003-09-29 09:53:38.42 server All rights reserved.
> 2003-09-29 09:53:38.42 server Server Process ID is 996.
> 2003-09-29 09:53:38.42 server Logging SQL Server
> messages in file 'e:\Program Files\Microsoft SQL
> Server\MSSQL\log\ERRORLOG'.
> 2003-09-29 09:53:38.42 server SQL Server is starting at
> priority class 'normal'(4 CPUs detected).
> 2003-09-29 09:53:38.67 server SQL Server configured for
> thread mode processing.
> 2003-09-29 09:53:38.67 server Using dynamic lock
> allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
> 2003-09-29 09:53:38.79 server Attempting to initialize
> Distributed Transaction Coordinator.
> 2003-09-29 09:53:40.29 spid3 Starting up
> database 'master'.
> 2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
> 2003-09-29 09:53:40.74 spid3 Skipping startup of clean
> database id 5
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 6
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 7
> 2003-09-29 09:53:40.78 spid5 Starting up
> database 'msdb'.
> 2003-09-29 09:53:40.78 spid6 Starting up
> database 'hsmqa'.
> 2003-09-29 09:53:40.78 spid7 Starting up
> database 'WebTrend'.
> 2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
> version '8.0.760'.
> 2003-09-29 09:53:40.78 spid8 Starting up
> database 'model'.
> 2003-09-29 09:53:40.78 spid9 Starting up
> database 'WebData'.
> 2003-09-29 09:53:40.78 spid12 Starting up
> database 'WebCache'.
> 2003-09-29 09:53:40.81 spid7 Analysis of
> database 'WebTrend' (10) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.85 spid12 Analysis of
> database 'WebCache' (12) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.92 server SQL server listening on
> TCP, Shared Memory, Named Pipes.
> 2003-09-29 09:53:40.92 server SQL Server is ready for
> client connections
> 2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
> 2003-09-29 09:53:41.10 spid8 Starting up
> database 'tempdb'.
> 2003-09-29 09:53:41.21 spid3 Recovery complete.
> 2003-09-29 09:53:41.21 spid3 SQL global counter
> collection task is created.
> 2003-09-29 09:53:42.45 spid3 Launched startup
> procedure 'hbi_unlock_objects'
> 2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
> version '2000.80.194' to execute extended stored
> procedure 'xp_qv'.
> 2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
> version '2000.80.760' to execute extended stored
> procedure 'sp_MSgetversion'.
> 2003-09-29 09:54:30.64 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:54:30.75 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:54:30.84 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:44.43 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:44.53 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:44.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:45.98 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.07 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.17 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.26 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.36 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.45 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.57 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.67 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.76 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.86 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.95 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.04 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.14 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.23 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.32 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.42 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.53 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.76 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.86 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.95 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.21 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.31 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.40 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.93 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 10:00:01.78 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:01.87 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:01.98 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.07 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:02.40 spid87 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.51 spid87 Starting up
> database 'hsmtrain'.|||Michael:
First, Thank you for your reply. I appreciate the
professional level and degree of your response.
Second, yes the Auto Close was set. I have since removed
the check mark. I have several other databases running on
this server in support of the web based application I
mentioned. These other databases started normally and
remained so after I had restarted the server. If I had
given more thought to situation I could have compared the
properties between the problem databases and those that
functioned as expected. The problem ceased yesterday
morning around 10:00am even with the auto close still
set. I suspect the web application connection succeeded
and maintain its connection thereafter.
Just quickly, should others have interest, I'll respond to
your questions.
1. The web application could not connect from the web
application server. This is a separate server from the
database. I am not exactly sure how the vendor connects
to the database in the code behind the pages. No errors
reported back to Internet Explorer except that the page
could not be found. Basically the weblogic web server
could not start without the connection to the database.
2. I did ping all the servers associated with this
application. I receive successful results on all counts.
3. Yes I was able to connect to the database server using
Query Analyzer and was able to query tables and data from
the affected databases.
4. I have the latest MDAC installation that comes with
SQL SP3a.
Again, I thank you for your help.
Thomas
>--Original Message--
>Hi Thomas,
>Such issues tend to be complex and require extensive
research. I'd like to
>set the right expectations and let you know that it may
take a while for us
>to help you narrow down the problem.
>Please check to see if the "Auto close" is checked in the
Option tab in the
>property dialog of the database. If so, make sure this
option is unchecked
>and try to connect to SQL Server again. Does this resolve
the problem?
>If not, I would like you to provide me with more
information regarding this
>issue so that I can narrow down it.
>Please make sure that the MSSQLServer service, for the
SQL Server you are
>trying to connect to, is started and running before doing
any further
>troubleshooting.
>1. What do you mean by "the web based application is
unable to connect"?
>How do you connect SQL Server? Please describe it in
detail. Do any error
>messages occur?
>2. Ping the server using the server name and IP address.
See if the request
>completes successfully or times out.
>3. Can you connect to SQL Server using Query Analyzer? If
not, does any
>error message occur?
>4. What is the MDAC version on the client machine? For
more information
>regarding how to check the MDAC version, please refer to
the following
>article:
>301202 HOW TO: Check for MDAC Version
>http://support.microsoft.com/?id=301202
>5. Please provide the complete error log, you can send it
at
>v-yshao@.microsoft.com
>I am standing by for your response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Thomas,
Thank you for taking time to write in your experience so that it may help
other customers who may encounter similar issues. As per your previous
post, I am going consider this issue as Resolved. However, should you have
any further questions or concerns, please feel free to send your post here.
I will continue to work with you.
Thanks for using MSDN newsgroup again.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

DatabaseMail process shuts down by itself

I have a job set up to run sp_send_dbmail. Then I notice there were no emails sent out. I check the log I found "DatabaseMail Process is shutting down".
In the log, there was "DatabaseMail Process Started" also.
I don't know what is calling this processes.
If in the log, Stop was the last activity, all I can make this process start is to restart the database.

Does anyone know the trick?

Experiencing the same problem as you, I managed to start the process which was automatically shut down by sending a new test mail. After the process was started I tried running the job which shoul trigger the mail again. No luck this time either, but in the log I now found that no default mail profile was set.

I couldn't find a good was to set a default profile in the Management Studio IDE other than removing the old account and profile and then create it all over again, this time setting the new profile as the default profile. After that It worked.

Hope it works out.

|||

If you right click on Database Mail you can select the mail log. Are there any errors in there? Check the SQL Agent error log and the SQL Server Log.

You need to set up an initial DBMail profile (for the Server) and the email server it is pointing to. Also try to run the Database Mail Configuration Wizard again or open up Management, and right-click Database Mail, then follow the prompts.

Also try to restart SQLAgent in this case.

|||

Database Mail process shuts itself down after a certain period of inactivity. The default value is 10 minutes, which can be changed via Database Mail Configuration Wizard (can be found in Management Studio Object Explorer under Management | Database Mail) in "Configure System Parameters" dialog.

There are some other steps you can take to troubleshoot the issue, such as making sure your profile is set correctly. Here is a good starting point:

http://msdn2.microsoft.com/en-us/library/ms175887.aspx

DatabaseMail process shuts down by itself

I have a job set up to run sp_send_dbmail. Then I notice there were no emails sent out. I check the log I found "DatabaseMail Process is shutting down".
In the log, there was "DatabaseMail Process Started" also.
I don't know what is calling this processes.
If in the log, Stop was the last activity, all I can make this process start is to restart the database.

Does anyone know the trick?

Experiencing the same problem as you, I managed to start the process which was automatically shut down by sending a new test mail. After the process was started I tried running the job which shoul trigger the mail again. No luck this time either, but in the log I now found that no default mail profile was set.

I couldn't find a good was to set a default profile in the Management Studio IDE other than removing the old account and profile and then create it all over again, this time setting the new profile as the default profile. After that It worked.

Hope it works out.

|||

If you right click on Database Mail you can select the mail log. Are there any errors in there? Check the SQL Agent error log and the SQL Server Log.

You need to set up an initial DBMail profile (for the Server) and the email server it is pointing to. Also try to run the Database Mail Configuration Wizard again or open up Management, and right-click Database Mail, then follow the prompts.

Also try to restart SQLAgent in this case.

|||

Database Mail process shuts itself down after a certain period of inactivity. The default value is 10 minutes, which can be changed via Database Mail Configuration Wizard (can be found in Management Studio Object Explorer under Management | Database Mail) in "Configure System Parameters" dialog.

There are some other steps you can take to troubleshoot the issue, such as making sure your profile is set correctly. Here is a good starting point:

http://msdn2.microsoft.com/en-us/library/ms175887.aspx

DatabaseMail process shuts down by itself

I have a job set up to run sp_send_dbmail. Then I notice there were no emails sent out. I check the log I found "DatabaseMail Process is shutting down".
In the log, there was "DatabaseMail Process Started" also.
I don't know what is calling this processes.
If in the log, Stop was the last activity, all I can make this process start is to restart the database.

Does anyone know the trick?

Experiencing the same problem as you, I managed to start the process which was automatically shut down by sending a new test mail. After the process was started I tried running the job which shoul trigger the mail again. No luck this time either, but in the log I now found that no default mail profile was set.

I couldn't find a good was to set a default profile in the Management Studio IDE other than removing the old account and profile and then create it all over again, this time setting the new profile as the default profile. After that It worked.

Hope it works out.

|||

If you right click on Database Mail you can select the mail log. Are there any errors in there? Check the SQL Agent error log and the SQL Server Log.

You need to set up an initial DBMail profile (for the Server) and the email server it is pointing to. Also try to run the Database Mail Configuration Wizard again or open up Management, and right-click Database Mail, then follow the prompts.

Also try to restart SQLAgent in this case.

|||

Database Mail process shuts itself down after a certain period of inactivity. The default value is 10 minutes, which can be changed via Database Mail Configuration Wizard (can be found in Management Studio Object Explorer under Management | Database Mail) in "Configure System Parameters" dialog.

There are some other steps you can take to troubleshoot the issue, such as making sure your profile is set correctly. Here is a good starting point:

http://msdn2.microsoft.com/en-us/library/ms175887.aspx

Database/Log placement

I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...

quote:

---

> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave

---

Database/Log placement

I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave

Database wont backup or anything...

Hi

I have a database on my SQL Server which since Saturday will not backup. It is part of a maintenance plan which should do backup, log and optimisations. None of these will work on this database. Running a backup manually or using the maintenance plan jobs once started the job just sits there saying 'Executing job step 1'.

We had a problem with the server on Friday to do with tape backups and the server was rebooted. There was no issue with SQL Server its self. All other databases are OK and backup normally even those in the same maintenance plan as this dodgy database.

The system which uses this database works fine with no problems. EDIT: I should add that the first attempt at backing up where I realised there was a problem I stopped the backup from running and it said it had been going for 102 Hours! Even though it went for that amount of time it made no progress. The database only normally takes about 30 secs to a minute to backup.

What can I do to fix this?

Neil.are you able to backup the database through the QA or EM?|||Hi

It does not work in EM but I will give QA a quick try.

Neil.|||Have tried QA and that isn't working either. I set stats to 10 so it should give me some progress but I don't get anything :(|||Don't use a Maint Plan?

Write the backup yourself, or steal one

You should be able to find something here

http://weblogs.sqlteam.com/tarad/category/95.aspx|||how much space is on the destination drive and how big is your database?|||Run sp_who2 in Query Analyzer. Look for a session that is labeled "Backup database". Kill that SPID. It is possible that you have a failed backup that is blocking all others.|||Database is 50Mb and directory has about 25gig spare. Have just tried sp_who2 and there i only one thing in the list relating to my database and its me. There are no Backup Database commands running.|||Something I did get yesterday but forgot to post was when running the backup in QA it responded after several minutes saysing:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Is this trying to tell me my D drive is offline or something? If so it lies because that's where everything else is backing up to OK.

Neil.|||Have u changed the location of ur backups lately....try changing the folder location there might be some permissions issues on that particular drive......|||Mystery almost solved...

Have changed the backup directory to use the L drive instead and I can backup the database sucessfully. If I try a backup on the usual D drive area it fails or never starts.

This is what I think happened:

The server I am using is a cluster between two servers so and at the weekend our techies had a problem with the tape software on one of the servers say ServerX. The cluster was currently running on ServerX and when they had to reboot it the cluster failed over to ServerY. So now the cluster is on ServerY and for some reason I can't backup this database to ServerY D drive.

Does that sound about right? Anyone know why I woul have issues with one database not saving to the D drive?|||Seems ok now we backed up to L drive ok. Now tried D drive again and that works. Weired...

database with two data files

I only need short description or even link about database with two data
files and/or two log files. How does it work,...?
thanks
Hi
In BOL, look up the keyword "filegroups, described". Gives a perfect
description and examples.
Regards
Mike
"Lugos" wrote:

> I only need short description or even link about database with two data
> files and/or two log files. How does it work,...?
> thanks
>
|||Thanks. That's what I am looking for. Shame is i did not find it myself.
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> In BOL, look up the keyword "filegroups, described". Gives a perfect
> description and examples.
> Regards
> Mike
> "Lugos" wrote:
>

database with two data files

I only need short description or even link about database with two data
files and/or two log files. How does it work,...?
thanksHi
In BOL, look up the keyword "filegroups, described". Gives a perfect
description and examples.
Regards
Mike
"Lugos" wrote:
> I only need short description or even link about database with two data
> files and/or two log files. How does it work,...?
> thanks
>|||Thanks. That's what I am looking for. Shame is i did not find it myself.
Mike Epprecht (SQL MVP) wrote:
> Hi
> In BOL, look up the keyword "filegroups, described". Gives a perfect
> description and examples.
> Regards
> Mike
> "Lugos" wrote:
>
>>I only need short description or even link about database with two data
>>files and/or two log files. How does it work,...?
>>thanks

database with two data files

I only need short description or even link about database with two data
files and/or two log files. How does it work,...?
thanksHi
In BOL, look up the keyword "filegroups, described". Gives a perfect
description and examples.
Regards
Mike
"Lugos" wrote:

> I only need short description or even link about database with two data
> files and/or two log files. How does it work,...?
> thanks
>|||Thanks. That's what I am looking for. Shame is i did not find it myself.
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> In BOL, look up the keyword "filegroups, described". Gives a perfect
> description and examples.
> Regards
> Mike
> "Lugos" wrote:
>

database updation log

how could I see which user run what query on the datbase
(sql 2000) at what time. pl help
regards, shahidYou need to have a trace running at that point-in-time, to see what the
users were running at that time. Do you have one?
Or you need to have some sort of logging in your application or stored
procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"shahid" <anonymous@.discussions.microsoft.com> wrote in message
news:2b13201c467fb$a8013480$a401280a@.phx
.gbl...
how could I see which user run what query on the datbase
(sql 2000) at what time. pl help
regards, shahid|||Hi,
SQL Server will not log any Select statements. Only way to look into that is
activate Profiler. You can save the profiler output to a Table for analysis.
FYI, You can see the information only from the point you activate the
profier.
For doing this you could use the GUI interface Profiler or look into the
books online for the procedure "sp_trace_create".
Thanks
Hari
MCDBA
"shahid" <anonymous@.discussions.microsoft.com> wrote in message
news:2b13201c467fb$a8013480$a401280a@.phx
.gbl...
> how could I see which user run what query on the datbase
> (sql 2000) at what time. pl help
> regards, shahid