Database growth and Replication

We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.

> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Database growth and Replication

We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegro ups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.

> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||That was it. Missed the autoshrink option.
Thanks
|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegrou ps.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Database growth and Replication

We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

database files locked out by SQL?

I have a SQL2005 SP2 server hosting several databases for different
applications. Recently i stood up a virtual server with Office
Communications Sever 2007 using my existing physical SQL 2005.
Everytime a reboot is required on the SQL server, the databases for OCS get
locked out. It took me a while to figure out what was going on, at first i
thought they were getting corrupted. After using SysInternals Process
explorer, I was able to determine that it is SQL itself that is locking the
database files and logs out, but apparently it isn't communicating that with
itself because when I try to bring them online, i get an error stating that
the files are in use or the disk is full.
Has anyone seen this before? I'm not an SQL expert by any means, so please
bear with me.
Cheers
Joe> Everytime a reboot is required on the SQL server, the databases for OCS get
> locked out.
I don't understand exactly what is meant here. The database files are indeed
locked by the SQL Server process, and that is expected. The files are not
supposed to be accessed by anything other than SQL Server. What error
messages are getting? What are you trying to accomplish that is prevented by
the files being locked?
Linchi
"Josef Hanning" wrote:
> I have a SQL2005 SP2 server hosting several databases for different
> applications. Recently i stood up a virtual server with Office
> Communications Sever 2007 using my existing physical SQL 2005.
> Everytime a reboot is required on the SQL server, the databases for OCS get
> locked out. It took me a while to figure out what was going on, at first i
> thought they were getting corrupted. After using SysInternals Process
> explorer, I was able to determine that it is SQL itself that is locking the
> database files and logs out, but apparently it isn't communicating that with
> itself because when I try to bring them online, i get an error stating that
> the files are in use or the disk is full.
> Has anyone seen this before? I'm not an SQL expert by any means, so please
> bear with me.
> Cheers
> Joe|||Sorry, guess I should have been more clear with the issue. After the reboot,
the databases will not come online properly. OCS gets application connection
errors, and you can't actually expand the database folder within SQL manager.
If I try and take the databases offline and then online again, I get a file
in use access error. I've managed to get the databases online by stopping
all SQL services and restarting but I shouldn't have to do this after a
reboot.
"Linchi Shea" wrote:
> > Everytime a reboot is required on the SQL server, the databases for OCS get
> > locked out.
> I don't understand exactly what is meant here. The database files are indeed
> locked by the SQL Server process, and that is expected. The files are not
> supposed to be accessed by anything other than SQL Server. What error
> messages are getting? What are you trying to accomplish that is prevented by
> the files being locked?
> Linchi
> "Josef Hanning" wrote:
> > I have a SQL2005 SP2 server hosting several databases for different
> > applications. Recently i stood up a virtual server with Office
> > Communications Sever 2007 using my existing physical SQL 2005.
> >
> > Everytime a reboot is required on the SQL server, the databases for OCS get
> > locked out. It took me a while to figure out what was going on, at first i
> > thought they were getting corrupted. After using SysInternals Process
> > explorer, I was able to determine that it is SQL itself that is locking the
> > database files and logs out, but apparently it isn't communicating that with
> > itself because when I try to bring them online, i get an error stating that
> > the files are in use or the disk is full.
> >
> > Has anyone seen this before? I'm not an SQL expert by any means, so please
> > bear with me.
> >
> > Cheers
> >
> > Joe|||"Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
news:BEC23679-2F5F-448E-BBC4-5ECA8C8E6C70@.microsoft.com...
> Sorry, guess I should have been more clear with the issue. After the
> reboot,
> the databases will not come online properly. OCS gets application
> connection
> errors, and you can't actually expand the database folder within SQL
> manager.
> If I try and take the databases offline and then online again, I get a
> file
> in use access error. I've managed to get the databases online by stopping
> all SQL services and restarting but I shouldn't have to do this after a
> reboot.
Sounds to me like something is competing for the files - first one wins and
locks the other(s) out. Do you have multiple instances of sql server
installed - each of which is configured to use the same user database(s)?|||Scott,
Thanks for the reply. There looks to be 3 instances of SQL running on the
box, the default, the RTC instance created by the OCS installation, and an
OCS instance installed using SQL setup by a co-worker. The OCS instance has
never been used to my knowledge and I have not done any database
configuration to any of the other instances myself.
From your question I gather that I need to somehow configure my RTC
databases for OCS to be used by only one of these instances of SQL?
thanks!
-Joe
"Scott Morris" wrote:
> "Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
> news:BEC23679-2F5F-448E-BBC4-5ECA8C8E6C70@.microsoft.com...
> > Sorry, guess I should have been more clear with the issue. After the
> > reboot,
> > the databases will not come online properly. OCS gets application
> > connection
> > errors, and you can't actually expand the database folder within SQL
> > manager.
> > If I try and take the databases offline and then online again, I get a
> > file
> > in use access error. I've managed to get the databases online by stopping
> > all SQL services and restarting but I shouldn't have to do this after a
> > reboot.
> Sounds to me like something is competing for the files - first one wins and
> locks the other(s) out. Do you have multiple instances of sql server
> installed - each of which is configured to use the same user database(s)?
>
>|||"Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
news:84729A32-1E6D-4CED-9343-A1FE3EC35E02@.microsoft.com...
> Scott,
> Thanks for the reply. There looks to be 3 instances of SQL running on the
> box, the default, the RTC instance created by the OCS installation, and an
> OCS instance installed using SQL setup by a co-worker. The OCS instance
> has
> never been used to my knowledge and I have not done any database
> configuration to any of the other instances myself.
> From your question I gather that I need to somehow configure my RTC
> databases for OCS to be used by only one of these instances of SQL?
What you need to do I cannot answer. I can only say that the set of
phsyical files that comprise a database should only be used within a single
instance. Multiple instances cannot "share" a database. It sounds like
you may need to get additional expertise to help with this situation.

Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Database files encryption

Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.See if this helps:
http://www.sqlservercentral.com/col...menting_efs.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:352501c51f3d$555d2140$a401280a@.phx.gbl...
Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.|||Thanks..........

>--Original Message--
>See if this helps:
>http://www.sqlservercentral.com/col...bkelley/impleme
nting_efs.asp
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:352501c51f3d$555d2140$a401280a@.phx.gbl...
>Can anyone direct me to some info about Encrypting SQL
>Server database files (Including system databases) using
>EFS ?. Facts, pros & cons e.t.c.
>Thanks.
>
>.
>sql

Database files encryption

Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.
See if this helps:
http://www.sqlservercentral.com/colu...enting_efs.asp
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:352501c51f3d$555d2140$a401280a@.phx.gbl...
Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.
|||Thanks..........

>--Original Message--
>See if this helps:
>http://www.sqlservercentral.com/colu...kelley/impleme
nting_efs.asp
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:352501c51f3d$555d2140$a401280a@.phx.gbl...
>Can anyone direct me to some info about Encrypting SQL
>Server database files (Including system databases) using
>EFS ?. Facts, pros & cons e.t.c.
>Thanks.
>
>.
>

Database files encryption

Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.See if this helps:
http://www.sqlservercentral.com/columnists/bkelley/implementing_efs.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:352501c51f3d$555d2140$a401280a@.phx.gbl...
Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.|||Thanks..........
>--Original Message--
>See if this helps:
>http://www.sqlservercentral.com/columnists/bkelley/impleme
nting_efs.asp
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:352501c51f3d$555d2140$a401280a@.phx.gbl...
>Can anyone direct me to some info about Encrypting SQL
>Server database files (Including system databases) using
>EFS ?. Facts, pros & cons e.t.c.
>Thanks.
>
>.
>

Database files compatible with Win2K & Win2003

SQL Server 2000/SP3 runs on Win2K.
I've failover clustering server run on Win2003. Are the system database
files compatible on both Win2K & Win003 ? That I can switch the databases on
Win2k to Win2003.
JT
Hi
SQL Server 2000 data files and backups, on 32 or 64 bit, are compatible on
any platform. The base OS does not matter. OS does not play a role here.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny" wrote:

> SQL Server 2000/SP3 runs on Win2K.
> I've failover clustering server run on Win2003. Are the system database
> files compatible on both Win2K & Win003 ? That I can switch the databases on
> Win2k to Win2003.
> JT

Database files compatible with Win2K & Win2003

SQL Server 2000/SP3 runs on Win2K.
I've failover clustering server run on Win2003. Are the system database
files compatible on both Win2K & Win003 ? That I can switch the databases on
Win2k to Win2003.
JTHi
SQL Server 2000 data files and backups, on 32 or 64 bit, are compatible on
any platform. The base OS does not matter. OS does not play a role here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny" wrote:
> SQL Server 2000/SP3 runs on Win2K.
> I've failover clustering server run on Win2003. Are the system database
> files compatible on both Win2K & Win003 ? That I can switch the databases on
> Win2k to Win2003.
> JT

Database files compatible with Win2K & Win2003

SQL Server 2000/SP3 runs on Win2K.
I've failover clustering server run on Win2003. Are the system database
files compatible on both Win2K & Win003 ? That I can switch the databases on
Win2k to Win2003.
JTHi
SQL Server 2000 data files and backups, on 32 or 64 bit, are compatible on
any platform. The base OS does not matter. OS does not play a role here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny" wrote:

> SQL Server 2000/SP3 runs on Win2K.
> I've failover clustering server run on Win2003. Are the system database
> files compatible on both Win2K & Win003 ? That I can switch the databases
on
> Win2k to Win2003.
> JTsql

Database files are "Hidden" after becoming Suspect

We had created some databases on the C Drive. Today the C drive went bad and
all the databases that
were on the C Drive became suspect.
I tried to copy the files onto another drive and I did to the D drive.
However, when I tried to attach the
datafile the data file was not showing up. I checked the properties and the
file's "Read Only" attribute was checked.
I unchecked it. However, the "Hidden" attribute has been greyed out and
looks like this is the reason why
the files are not showing up.
Would someone know how to address this problem ?
Thanks,
rgn
Hi
This is an OS issue, but here goes:
Run
ATTRIB
This returns all the files that are hidden in the current directory.
If some of your files are listed, run
ATTRIB -H <filename.ext>
This will remove the Hidden attribute on the file.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:OUyfFwoEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> We had created some databases on the C Drive. Today the C drive went bad
and
> all the databases that
> were on the C Drive became suspect.
> I tried to copy the files onto another drive and I did to the D drive.
> However, when I tried to attach the
> datafile the data file was not showing up. I checked the properties and
the
> file's "Read Only" attribute was checked.
> I unchecked it. However, the "Hidden" attribute has been greyed out and
> looks like this is the reason why
> the files are not showing up.
> Would someone know how to address this problem ?
> Thanks,
> rgn
>

Database files are "Hidden" after becoming Suspect

We had created some databases on the C Drive. Today the C drive went bad and
all the databases that
were on the C Drive became suspect.
I tried to copy the files onto another drive and I did to the D drive.
However, when I tried to attach the
datafile the data file was not showing up. I checked the properties and the
file's "Read Only" attribute was checked.
I unchecked it. However, the "Hidden" attribute has been greyed out and
looks like this is the reason why
the files are not showing up.
Would someone know how to address this problem ?
Thanks,
rgnHi
This is an OS issue, but here goes:
Run
ATTRIB
This returns all the files that are hidden in the current directory.
If some of your files are listed, run
ATTRIB -H <filename.ext>
This will remove the Hidden attribute on the file.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:OUyfFwoEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> We had created some databases on the C Drive. Today the C drive went bad
and
> all the databases that
> were on the C Drive became suspect.
> I tried to copy the files onto another drive and I did to the D drive.
> However, when I tried to attach the
> datafile the data file was not showing up. I checked the properties and
the
> file's "Read Only" attribute was checked.
> I unchecked it. However, the "Hidden" attribute has been greyed out and
> looks like this is the reason why
> the files are not showing up.
> Would someone know how to address this problem ?
> Thanks,
> rgn
>

Database files are "Hidden" after becoming Suspect

We had created some databases on the C Drive. Today the C drive went bad and
all the databases that
were on the C Drive became suspect.
I tried to copy the files onto another drive and I did to the D drive.
However, when I tried to attach the
datafile the data file was not showing up. I checked the properties and the
file's "Read Only" attribute was checked.
I unchecked it. However, the "Hidden" attribute has been greyed out and
looks like this is the reason why
the files are not showing up.
Would someone know how to address this problem ?
Thanks,
rgnHi
This is an OS issue, but here goes:
Run
ATTRIB
This returns all the files that are hidden in the current directory.
If some of your files are listed, run
ATTRIB -H <filename.ext>
This will remove the Hidden attribute on the file.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:OUyfFwoEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> We had created some databases on the C Drive. Today the C drive went bad
and
> all the databases that
> were on the C Drive became suspect.
> I tried to copy the files onto another drive and I did to the D drive.
> However, when I tried to attach the
> datafile the data file was not showing up. I checked the properties and
the
> file's "Read Only" attribute was checked.
> I unchecked it. However, the "Hidden" attribute has been greyed out and
> looks like this is the reason why
> the files are not showing up.
> Would someone know how to address this problem ?
> Thanks,
> rgn
>

Database files (VS 2005) - usage?

I'm just getting to grips with the new SQL Database file concept in VS 2005
and have a couple of questions in the hope that someone can clarify my
understanding.
I understand that I can now add both the <dbname>.mdf and <dbname>.ldf files
traditionally associated with a SQL Server into my application folder, and
that these are attached to SQL Express at runtime. I see this an ideal
replacement for an Access database on single user desktop applications,
leveraging the power of a SQL Server whilst offering the advantages of a
file-based db like Access (x-copy backups for example).
However, for a small multi-user system (say 5 users), am I right in thinking
that the database is now shared and therefore the database files need to be
available to all users on a network share? It seems obvious, but then does
each user attach these shared files to their local SQL Express, or is there
one application / SQL Express nominated as the 'server' with the remaining
applications running in a pure 'client' mode? And if the latter, how is the
connection string managed?
Am I barking up the wrong tree with this?
CheersAndrew Kidd wrote:
> However, for a small multi-user system (say 5 users), am I right in thinki
ng
> that the database is now shared and therefore the database files need to b
e
> available to all users on a network share?
No. The database files just need to be visible to the server. In fact
it's probably a good idea to make sure that user's can't see the
network share where the database resides.

> It seems obvious, but then does
> each user attach these shared files to their local SQL Express, or is ther
e
> one application / SQL Express nominated as the 'server' with the remaining
> applications running in a pure 'client' mode? And if the latter, how is th
e
> connection string managed?
>
One server. Multiple clients. The clients don't need Express they just
need SQL Server connectivity: Native Client or MDAC.
David Portas
SQL Server MVP
--|||Thanks David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133528080.622239.309940@.o13g2000cwo.googlegroups.com...
> Andrew Kidd wrote:
> No. The database files just need to be visible to the server. In fact
> it's probably a good idea to make sure that user's can't see the
> network share where the database resides.
>
> One server. Multiple clients. The clients don't need Express they just
> need SQL Server connectivity: Native Client or MDAC.
> --
> David Portas
> SQL Server MVP
> --
>|||When would a department, with say only 5 users and < 2GB of data, want to
move from using SQL Server Express to Workgroup Edition?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133528080.622239.309940@.o13g2000cwo.googlegroups.com...
> Andrew Kidd wrote:
> No. The database files just need to be visible to the server. In fact
> it's probably a good idea to make sure that user's can't see the
> network share where the database resides.
>
> One server. Multiple clients. The clients don't need Express they just
> need SQL Server connectivity: Native Client or MDAC.
> --
> David Portas
> SQL Server MVP
> --
>|||JT wrote:
> When would a department, with say only 5 users and < 2GB of data, want to
> move from using SQL Server Express to Workgroup Edition?
>
When they need the scalability or functionality of one of the other
editions:
http://www.microsoft.com/sql/prodin...e-features.mspx
David Portas
SQL Server MVP
--

Database Files (file name)

I've detached a database and then reactached it specifing a new name for
the database within SQL Server 2000. But, if i right click and select
the database properties and then select the "Data Files" tab the file
name is still the same as the original database. How do i go about
changing that? Thanks in advance.
Vincent
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Hi Vincent
Changing the database name only changes one value, in the sysdatabases table
in the master database. It doesn't change any info in the database itself,
including the logical names associated with the database files which are
stored in the sysfiles table in the database itself.
To change the logical file names, you can use ALTER DATABASE... MODIFY FILE,
and specify a value for NEWNAME.
Please see the complete ALTER DATABASE syntax in the Books Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vincent Cristiano" <vincec@.mail.com> wrote in message
news:eTK%23YXruEHA.1984@.TK2MSFTNGP14.phx.gbl...
> I've detached a database and then reactached it specifing a new name for
> the database within SQL Server 2000. But, if i right click and select
> the database properties and then select the "Data Files" tab the file
> name is still the same as the original database. How do i go about
> changing that? Thanks in advance.
> Vincent
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql

Database Files

Hi,
I Inserted a new file in my database, and when I try to exclude it from my database sql says that the file is not empty ! The question is : Is there a way to clean up the file so can be deleted ! What shoud i Do !
Thank's
DBCC Shrinkfile (<yourdbname>, EMPTYFILE)
Look it up in BOL for a complete description.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Doubt" <anonymous@.discussions.microsoft.com> wrote in message
news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> Hi,
> I Inserted a new file in my database, and when I try to exclude it from my
database sql says that the file is not empty ! The question is : Is there a
way to clean up the file so can be deleted ! What shoud i Do !
> Thank's
|||Hi-
You can use :-
USE MyDB
GO
DBCC SHRINKFILE (MyDataFile, 7)
GO
This example shrinks the size of a file named MyDataFile in the MyDB user
database to 7 MB.
Thanks
-Surajit
surajits@.nospam.yahoo.com
"Doubt" <anonymous@.discussions.microsoft.com> wrote in message
news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> Hi,
> I Inserted a new file in my database, and when I try to exclude it from my
database sql says that the file is not empty ! The question is : Is there a
way to clean up the file so can be deleted ! What shoud i Do !
> Thank's
|||Argh!...
I should have known better.
The real code snippit shoudl be:
Use <MyDatabaseName>
go
DBCC Shrinkfile (<MyFileName>, EMPTYFILE)
You can then use the ALTER DATABASE command to remove the file.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ezg5X%23LTEHA.3548@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> DBCC Shrinkfile (<yourdbname>, EMPTYFILE)
> Look it up in BOL for a complete description.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Doubt" <anonymous@.discussions.microsoft.com> wrote in message
> news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
my
> database sql says that the file is not empty ! The question is : Is there
a
> way to clean up the file so can be deleted ! What shoud i Do !
>

Database Files

Hi
I Inserted a new file in my database, and when I try to exclude it from my database sql says that the file is not empty ! The question is : Is there a way to clean up the file so can be deleted ! What shoud i Do
Thank'sDBCC Shrinkfile (<yourdbname>, EMPTYFILE)
Look it up in BOL for a complete description.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Doubt" <anonymous@.discussions.microsoft.com> wrote in message
news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> Hi,
> I Inserted a new file in my database, and when I try to exclude it from my
database sql says that the file is not empty ! The question is : Is there a
way to clean up the file so can be deleted ! What shoud i Do !
> Thank's|||Hi-
You can use :-
USE MyDB
GO
DBCC SHRINKFILE (MyDataFile, 7)
GO
This example shrinks the size of a file named MyDataFile in the MyDB user
database to 7 MB.
Thanks
-Surajit
surajits@.nospam.yahoo.com
"Doubt" <anonymous@.discussions.microsoft.com> wrote in message
news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> Hi,
> I Inserted a new file in my database, and when I try to exclude it from my
database sql says that the file is not empty ! The question is : Is there a
way to clean up the file so can be deleted ! What shoud i Do !
> Thank's|||Argh!...
I should have known better.
The real code snippit shoudl be:
Use <MyDatabaseName>
go
DBCC Shrinkfile (<MyFileName>, EMPTYFILE)
You can then use the ALTER DATABASE command to remove the file.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ezg5X%23LTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> DBCC Shrinkfile (<yourdbname>, EMPTYFILE)
> Look it up in BOL for a complete description.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Doubt" <anonymous@.discussions.microsoft.com> wrote in message
> news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> > Hi,
> >
> > I Inserted a new file in my database, and when I try to exclude it from
my
> database sql says that the file is not empty ! The question is : Is there
a
> way to clean up the file so can be deleted ! What shoud i Do !
> >
> > Thank's
>

Database Files

Hi,
I Inserted a new file in my database, and when I try to exclude it from my d
atabase sql says that the file is not empty ! The question is : Is there a w
ay to clean up the file so can be deleted ! What shoud i Do !
Thank'sDBCC Shrinkfile (<yourdbname>, EMPTYFILE)
Look it up in BOL for a complete description.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Doubt" <anonymous@.discussions.microsoft.com> wrote in message
news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> Hi,
> I Inserted a new file in my database, and when I try to exclude it from my
database sql says that the file is not empty ! The question is : Is there a
way to clean up the file so can be deleted ! What shoud i Do !
> Thank's|||Hi-
You can use :-
USE MyDB
GO
DBCC SHRINKFILE (MyDataFile, 7)
GO
This example shrinks the size of a file named MyDataFile in the MyDB user
database to 7 MB.
Thanks
-Surajit
surajits@.nospam.yahoo.com
"Doubt" <anonymous@.discussions.microsoft.com> wrote in message
news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
> Hi,
> I Inserted a new file in my database, and when I try to exclude it from my
database sql says that the file is not empty ! The question is : Is there a
way to clean up the file so can be deleted ! What shoud i Do !
> Thank's|||Argh!...
I should have known better.
The real code snippit shoudl be:
Use <MyDatabaseName>
go
DBCC Shrinkfile (<MyFileName>, EMPTYFILE)
You can then use the ALTER DATABASE command to remove the file.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ezg5X%23LTEHA.3548@.TK2MSFTNGP09.phx.gbl...
> DBCC Shrinkfile (<yourdbname>, EMPTYFILE)
> Look it up in BOL for a complete description.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Doubt" <anonymous@.discussions.microsoft.com> wrote in message
> news:4803DDD7-1B38-4D71-9016-CED5BFD66A65@.microsoft.com...
my[vbcol=seagreen]
> database sql says that the file is not empty ! The question is : Is there
a
> way to clean up the file so can be deleted ! What shoud i Do !
>