Database Name problem

I have a database server and I have other web server. I want to connect to Microsoft SQL server I connected. My database name has " " space in it line
"My Database" I use php to connect to MSSQL but php dosent see or understand space in Database name... I cant change database name because other sites also uses this database. How can solve this prblem pls help me.Put the database name in brackets. [My Database]

database name issue

Any known problem or in future a problem in using database name with a space
in it.
Example "North Wind" or "MY DB"If you use a space in the name you must enclose all references to it in
square braces... ie [my db]... I prefer NOT to name dbs this way.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:O$kMq1zlDHA.1672@.TK2MSFTNGP09.phx.gbl...
> Any known problem or in future a problem in using database name with a
space
> in it.
> Example "North Wind" or "MY DB"
>|||By the way... If you include spaces, you may run into software that does NOT
use square braces, which means the software would not work against your db.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:O$kMq1zlDHA.1672@.TK2MSFTNGP09.phx.gbl...
> Any known problem or in future a problem in using database name with a
space
> in it.
> Example "North Wind" or "MY DB"
>sql

Database MSDB in suspect state.

Hi Friends,
MSDB database for my SQL 2000 Enterprise is in SUSPECT state. I have enough free space on disk. I don't see anything particular in Windows event viewer.
Please help.
Regards,
Jamil
what happens if you run a sp_resetstatus 'msdb'?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:3954BE12-CAD0-41C1-A437-EEC82D4E09E1@.microsoft.com...
> Hi Friends,
> MSDB database for my SQL 2000 Enterprise is in SUSPECT state. I have
enough free space on disk. I don't see anything particular in Windows event
viewer.
> Please help.
> Regards,
> Jamil
>
|||When I run resetstatus I get a warning that I need to recover this database first.
"Hilary Cotter" wrote:

> what happens if you run a sp_resetstatus 'msdb'?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
> news:3954BE12-CAD0-41C1-A437-EEC82D4E09E1@.microsoft.com...
> enough free space on disk. I don't see anything particular in Windows event
> viewer.
>
>
|||Jamil,
I use this checklist to troubleshoot such issues:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH,
Paul Ibison
sql

database move

I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts?
Robert
Hi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:

> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts?
> Robert
|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
|||Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:

database move

I recently moved a database and its transaction log file to another drive du
e
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set o
n
the mdf and ldf files.
John
"Robert Gandrud" wrote:

> I recently moved a database and its transaction log file to another drive
due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get
an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set
on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
>|||Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service accoun
t
> that starts mssqlserver didn't have specific rights to the new location.
I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit t
he
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
>sql

database move

I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
Kelly
Yes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>
|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:
> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
> "KellyLeia@.gmail.com" wrote:
>> Is there anyway to make the maintenance plan delete the backup files
>> BEFORE it backs up. My backups are failing due to lack of disk space,
>> because it essentially creates two copies of backups before deleting.
>> I know i can just write a script to delete them before hand, but it
>> would be alot neater to use the maintenace plan because the maintenance
>> plan is more dynamic that the script. Suggestions?
>> Kelly
>>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> > Yes. If you create a backup plan using the Database Maintenance Wizard,
> > you
> > can set the amount of time you want to keep the backup files. In SQL 2005
> > it
> > translates in a Maintenance Cleanup Task.
> >
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>
> > "KellyLeia@.gmail.com" wrote:
> >
> >> Is there anyway to make the maintenance plan delete the backup files
> >> BEFORE it backs up. My backups are failing due to lack of disk space,
> >> because it essentially creates two copies of backups before deleting.
> >> I know i can just write a script to delete them before hand, but it
> >> would be alot neater to use the maintenace plan because the maintenance
> >> plan is more dynamic that the script. Suggestions?
> >>
> >> Kelly
> >>
> >>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
< EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> < EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in messa
ge
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maintenance

I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
Message posted via http://www.sqlmonster.com
both.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon
|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:

>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?
|||I have applied "autoshrink" to many of my databases specifically=
to try and keep the size of the logs down, and it seems to help.=
I had much trouble with logs growinbg larger than the database,=
but this has improved with autoshrink and nightly log backups.

> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)
|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.

> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)

Database Maintenance

I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
--
Message posted via http://www.sqlmonster.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically= to try and keep the size of the logs down, and it seems to help.= I had much trouble with logs growinbg larger than the database,= but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)

Database Maintenance

I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
Message posted via http://www.droptable.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
droptable.com" <forum@.droptable.com> wrote:

>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically=
to try and keep the size of the logs down, and it seems to help.=
I had much trouble with logs growinbg larger than the database,=
but this has improved with autoshrink and nightly log backups.

> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.droptable.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2m
sftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and
keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg large
r than the database, but
this has improved with autoshrink and nightly log backups.

> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.droptable.com
User submitted from AEWNET (http://www.aewnet.com/)

Database log file

hi all

my database log file has been full and the H.D partition not have any space

what's the procedures to remove or empty the log file

thanks & regards

Are you running SQL Server 2000 or SQL Server 2005? What recovery model is your database using (Full or Simple)?

If you are using the Full Recovery model, you have to periodically backup and truncate the log. Otherwise, it will fill up. Most likely, you have a long-running transaction that is causing this.

If you are running SQL 2005, you can run this query to find out why the transaction log cannot be backed up and truncated.

-- Find the problem with the transaction log

SELECT name, database_id, log_reuse_wait_desc

FROM sys.databases

|||

I guess the question to be asked is, do you need to recover your database to a point in time or to the last full backup.

1. Point in time

a. Your recovery model needs to be set to 'Full' - which it is already because it's filling up.

b. You need to backup you log file routinely, the frequency is determined by how much data you are prepared to lose in the event of a failure i.e. if you only want a maximum 15 minutes of data lost then you need to backup your log file every 15 minutes. By backing up your log file the inactive portion of the log file will be truncated hence it will free up space within the log file for new transactions (note however this will not decrease the log file size on the disk) and reduce growth in the log file.

2. Recover to the last full database backup

a. Set your recovery model to 'simple', your log file will not fill up anymore and sql won't fall over because of disk space issues relating to the log file..

b. Backup you database each day (or as per your backup procedures/policies)

The first step is to reduce the size of your log file to make it more manageable:

1. Backup your log file to disk (or just truncate it if you're not interested in retaining the back file)

To Disk:

BACKUP LOG { database_name } TO DISK='your backup location'

Truncate:

BACKUP LOG { database_name } WITH TRUNCATE_ONLY

Both methods will remove the inactive portion of the log file. Once this has completed you need to reduce the size of the file on the disk, to do this have a look at the DBCC SHRINKFILE command in BOL, there are a few options that may be of interest.