REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***
sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>
|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
sql

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.developersdex.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> sham,
>> Since BACKUP DATABASE only supports 64 backup devices, you should not
>> need more than 64 to restore. If you paste in your RESTORE command here,
>> maybe it will be apparent what is wrong.
>> RLF
>> "sham kulkarni" <sham252664@.gmail.com> wrote in message
>> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>
>

Refresh data from production to development

Hi all, I've been assigned a task of refreshing data from the
production env to development env.

what i got is a backup file of a db in the prod env, i now need to
make that into the development env.

I can restore it to the dev env no problem, but the warnings i got are
that the tables owner in prod and dev env need to be different, that
is, owner is A in prod env and owner is B in dev env.

So I need to:
1) restore the db in dev env
2) change table owner from A to B
3) change related triggers
4) change related views

Can anyone suggest me an approach that is most efficient?

Thanks a lot.Hiya Bosco,

I'd create a script that dynamically builds a "sp_changeobjectowner"
statement for all objects that user A owns.. It'll simlify the steps
to this

1. Restore db in dev
2. Create and run permissions script

Syntax is as follows:

EXEC sp_changeobjectowner '<table_name>', 'B'|||Bosco

Here are some aricles that should help you.

http://www.support.microsoft.com/?id=314546 Moving DB's between Servers

http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach

http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore

http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers

http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore

http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users

http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission Errors After Restoring Dump

http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers

Hope this helps

John

Redundancy

Is there any untility, program, or script that I can use to backup a SQL
database and will look for the main SQL server? If for anyreason that server
is not seen on the network a backup server will pick up from the last back up
and carry on where the first server left off. It seems logical there would
be.
For starters, check out Fail-over Clustering in the SQL Server Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> Is there any untility, program, or script that I can use to backup a SQL
> database and will look for the main SQL server? If for anyreason that
> server
> is not seen on the network a backup server will pick up from the last back
> up
> and carry on where the first server left off. It seems logical there
> would
> be.
|||Hi
For the various highg availablility options check out:
http://www.microsoft.com/sql/techinf...ailability.asp
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> Is there any untility, program, or script that I can use to backup a SQL
> database and will look for the main SQL server? If for anyreason that
> server
> is not seen on the network a backup server will pick up from the last back
> up
> and carry on where the first server left off. It seems logical there
> would
> be.
|||Dan,
Would this only work on either NT or 2k Server or could this be done on any
MS OS? Exactly what I am trying to solve is this: I am working with a Point
of Sale software that uses either MSDE or SQL Server for the larger sites to
control the databases. My problem is what happens if the main server goes
down? Clustering could be an option. Any other ideas?
"Dan Guzman" wrote:

> For starters, check out Fail-over Clustering in the SQL Server Books Online.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
>
>
|||> Would this only work on either NT or 2k Server or could this be done on
> any
> MS OS?
You need SQL Server Enterprise edition as well as one of the server OS
editions below. From the Books Online Fail-over Clustering overview:
<Excerpt href-"adminsql.chm::/ad_clustering_7t9v.htm">
Before installing failover clustering, you must install Microsoft Windows
NT 4.0, Enterprise Edition, Microsoft Windows 2000 Advanced Server or
Windows 2000 Datacenter Server, and the Microsoft Cluster Service (MSCS).
</Excerpt>
Additionally, you need to have supported hardware, including shared disks.
Clustering may be an option for you larger POS implementations but not for
the MSDE ones.
There are some new high-availability features in the upcoming SQL 2005
version that don't require specialized clustering hardware. See the
high-availability demo at
http://www.microsoft.com/sql/2005/pr...mos/hademo.asp
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...[vbcol=seagreen]
> Dan,
> Would this only work on either NT or 2k Server or could this be done on
> any
> MS OS? Exactly what I am trying to solve is this: I am working with a
> Point
> of Sale software that uses either MSDE or SQL Server for the larger sites
> to
> control the databases. My problem is what happens if the main server goes
> down? Clustering could be an option. Any other ideas?
> "Dan Guzman" wrote:
|||Paul,
SQL Server Enterprise Edition is going to cost you a fair bit. One of the
best third party products I have seen is XOSoft's WANYSyncHA.
see: www.xosoft.com
The WANYSyncHA product allows automatic failover to another server, with
continously replicating changes at the byte level.
It also includes Data Rewind which allows you to restore the database to a
particular snapshot should both databases become corrupt.
Regards,
Andrew
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> Dan,
> Would this only work on either NT or 2k Server or could this be done on
any
> MS OS? Exactly what I am trying to solve is this: I am working with a
Point
> of Sale software that uses either MSDE or SQL Server for the larger sites
to[vbcol=seagreen]
> control the databases. My problem is what happens if the main server goes
> down? Clustering could be an option. Any other ideas?
> "Dan Guzman" wrote:
Online.[vbcol=seagreen]
SQL[vbcol=seagreen]
back[vbcol=seagreen]
|||Correction, product is WANSynchHA, not WANYSyncHA..
Andrew
"Andrew" <me@.anonymous.com> wrote in message
news:41bf7b50$1_1@.news.iprimus.com.au...[vbcol=seagreen]
> Paul,
> SQL Server Enterprise Edition is going to cost you a fair bit. One of the
> best third party products I have seen is XOSoft's WANYSyncHA.
> see: www.xosoft.com
> The WANYSyncHA product allows automatic failover to another server, with
> continously replicating changes at the byte level.
> It also includes Data Rewind which allows you to restore the database to a
> particular snapshot should both databases become corrupt.
> Regards,
> Andrew
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> any
> Point
sites[vbcol=seagreen]
> to
goes[vbcol=seagreen]
> Online.
> SQL
that[vbcol=seagreen]
last[vbcol=seagreen]
> back
there
>

Redundancy

Is there any untility, program, or script that I can use to backup a SQL
database and will look for the main SQL server? If for anyreason that server
is not seen on the network a backup server will pick up from the last back up
and carry on where the first server left off. It seems logical there would
be.For starters, check out Fail-over Clustering in the SQL Server Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> Is there any untility, program, or script that I can use to backup a SQL
> database and will look for the main SQL server? If for anyreason that
> server
> is not seen on the network a backup server will pick up from the last back
> up
> and carry on where the first server left off. It seems logical there
> would
> be.|||Hi
For the various highg availablility options check out:
http://www.microsoft.com/sql/techinfo/administration/2000/availability.asp
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> Is there any untility, program, or script that I can use to backup a SQL
> database and will look for the main SQL server? If for anyreason that
> server
> is not seen on the network a backup server will pick up from the last back
> up
> and carry on where the first server left off. It seems logical there
> would
> be.|||Dan,
Would this only work on either NT or 2k Server or could this be done on any
MS OS? Exactly what I am trying to solve is this: I am working with a Point
of Sale software that uses either MSDE or SQL Server for the larger sites to
control the databases. My problem is what happens if the main server goes
down? Clustering could be an option. Any other ideas?
"Dan Guzman" wrote:
> For starters, check out Fail-over Clustering in the SQL Server Books Online.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> > Is there any untility, program, or script that I can use to backup a SQL
> > database and will look for the main SQL server? If for anyreason that
> > server
> > is not seen on the network a backup server will pick up from the last back
> > up
> > and carry on where the first server left off. It seems logical there
> > would
> > be.
>
>|||> Would this only work on either NT or 2k Server or could this be done on
> any
> MS OS?
You need SQL Server Enterprise edition as well as one of the server OS
editions below. From the Books Online Fail-over Clustering overview:
<Excerpt href-"adminsql.chm::/ad_clustering_7t9v.htm">
Before installing failover clustering, you must install Microsoft Windows
NT® 4.0, Enterprise Edition, Microsoft Windows® 2000 Advanced Server or
Windows 2000 Datacenter Server, and the Microsoft Cluster Service (MSCS).
</Excerpt>
Additionally, you need to have supported hardware, including shared disks.
Clustering may be an option for you larger POS implementations but not for
the MSDE ones.
There are some new high-availability features in the upcoming SQL 2005
version that don't require specialized clustering hardware. See the
high-availability demo at
http://www.microsoft.com/sql/2005/productinfo/demos/hademo.asp
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> Dan,
> Would this only work on either NT or 2k Server or could this be done on
> any
> MS OS? Exactly what I am trying to solve is this: I am working with a
> Point
> of Sale software that uses either MSDE or SQL Server for the larger sites
> to
> control the databases. My problem is what happens if the main server goes
> down? Clustering could be an option. Any other ideas?
> "Dan Guzman" wrote:
>> For starters, check out Fail-over Clustering in the SQL Server Books
>> Online.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
>> > Is there any untility, program, or script that I can use to backup a
>> > SQL
>> > database and will look for the main SQL server? If for anyreason that
>> > server
>> > is not seen on the network a backup server will pick up from the last
>> > back
>> > up
>> > and carry on where the first server left off. It seems logical there
>> > would
>> > be.
>>|||Paul,
SQL Server Enterprise Edition is going to cost you a fair bit. One of the
best third party products I have seen is XOSoft's WANYSyncHA.
see: www.xosoft.com
The WANYSyncHA product allows automatic failover to another server, with
continously replicating changes at the byte level.
It also includes Data Rewind which allows you to restore the database to a
particular snapshot should both databases become corrupt.
Regards,
Andrew
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> Dan,
> Would this only work on either NT or 2k Server or could this be done on
any
> MS OS? Exactly what I am trying to solve is this: I am working with a
Point
> of Sale software that uses either MSDE or SQL Server for the larger sites
to
> control the databases. My problem is what happens if the main server goes
> down? Clustering could be an option. Any other ideas?
> "Dan Guzman" wrote:
> > For starters, check out Fail-over Clustering in the SQL Server Books
Online.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> > > Is there any untility, program, or script that I can use to backup a
SQL
> > > database and will look for the main SQL server? If for anyreason that
> > > server
> > > is not seen on the network a backup server will pick up from the last
back
> > > up
> > > and carry on where the first server left off. It seems logical there
> > > would
> > > be.
> >
> >
> >|||Correction, product is WANSynchHA, not WANYSyncHA..
Andrew
"Andrew" <me@.anonymous.com> wrote in message
news:41bf7b50$1_1@.news.iprimus.com.au...
> Paul,
> SQL Server Enterprise Edition is going to cost you a fair bit. One of the
> best third party products I have seen is XOSoft's WANYSyncHA.
> see: www.xosoft.com
> The WANYSyncHA product allows automatic failover to another server, with
> continously replicating changes at the byte level.
> It also includes Data Rewind which allows you to restore the database to a
> particular snapshot should both databases become corrupt.
> Regards,
> Andrew
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> > Dan,
> >
> > Would this only work on either NT or 2k Server or could this be done on
> any
> > MS OS? Exactly what I am trying to solve is this: I am working with a
> Point
> > of Sale software that uses either MSDE or SQL Server for the larger
sites
> to
> > control the databases. My problem is what happens if the main server
goes
> > down? Clustering could be an option. Any other ideas?
> >
> > "Dan Guzman" wrote:
> >
> > > For starters, check out Fail-over Clustering in the SQL Server Books
> Online.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> > > > Is there any untility, program, or script that I can use to backup a
> SQL
> > > > database and will look for the main SQL server? If for anyreason
that
> > > > server
> > > > is not seen on the network a backup server will pick up from the
last
> back
> > > > up
> > > > and carry on where the first server left off. It seems logical
there
> > > > would
> > > > be.
> > >
> > >
> > >
>

Reducing the size of the Transaction Log.

We have a SQL Server 2000 SP3 database with recovery model set to full. I
set up 3 automatic backup jobs as follows:
Each day,Mon thru Fri at 9:00 am
1 step - full backup of database to file A with overwrite
Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
1 step - transaction log backup of database to file A with append
Each day,Mon thru Fri at 5:05 pm
1st step - transaction log backup of database to file A with append
2nd step - execute BACKUP LOG WITH NO_LOG
3rd step - execute DBCC SHRINK (db_file_name,1)
First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want, even
though I back up the Log.
When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
However, I get an error in the Event Viever(Application Log) stating
"Database Log truncated".
My question is:
Should I worry about this error ?
Is any data loss likely will this arrangement ?
After all, I truncate the Transaction Log on purpose, right after backing it
back in the previous step.
Regards,
SezginYou should not use the no_log command if you require point in time recovery,
and if you do not require point in time recovery, set your recovery mode to
simple. I would advise not to be shrinking your log or data file like you
are as if the db or log has to autogrow it costs you at the point it needs
to allocate more space and that is typically when your db is busy.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> We have a SQL Server 2000 SP3 database with recovery model set to full. I
> set up 3 automatic backup jobs as follows:
> Each day,Mon thru Fri at 9:00 am
> 1 step - full backup of database to file A with overwrite
> Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> 1 step - transaction log backup of database to file A with append
> Each day,Mon thru Fri at 5:05 pm
> 1st step - transaction log backup of database to file A with append
> 2nd step - execute BACKUP LOG WITH NO_LOG
> 3rd step - execute DBCC SHRINK (db_file_name,1)
> First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
> NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
even
> though I back up the Log.
>
> When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> However, I get an error in the Event Viever(Application Log) stating
> "Database Log truncated".
> My question is:
> Should I worry about this error ?
> Is any data loss likely will this arrangement ?
> After all, I truncate the Transaction Log on purpose, right after backing
it
> back in the previous step.
> Regards,
> Sezgin
>
>|||Thanks for your reply Ray,
The database has a high number of transactions every day. I have to make
sure any possible data loss is minimized. That is why I used full recovery
model. During tests, in about 5 weeks time, the Transaction Log grew up to
nearly 7 GB (the data file currently is just over 1 GB). I tried my best to
shrink the Transaction Log without using the "Backup Log with no_log"
command, but to no success. Could you offer any advice or alternative
solution.
Best regards,
Sezgin Rafet
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> You should not use the no_log command if you require point in time
recovery,
> and if you do not require point in time recovery, set your recovery mode
to
> simple. I would advise not to be shrinking your log or data file like you
> are as if the db or log has to autogrow it costs you at the point it needs
> to allocate more space and that is typically when your db is busy.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > We have a SQL Server 2000 SP3 database with recovery model set to full.
I
> > set up 3 automatic backup jobs as follows:
> >
> > Each day,Mon thru Fri at 9:00 am
> > 1 step - full backup of database to file A with overwrite
> >
> > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > 1 step - transaction log backup of database to file A with append
> >
> > Each day,Mon thru Fri at 5:05 pm
> > 1st step - transaction log backup of database to file A with append
> > 2nd step - execute BACKUP LOG WITH NO_LOG
> > 3rd step - execute DBCC SHRINK (db_file_name,1)
> >
> > First I tried to shrink the Log file without issuing the "BACKUP LOG
WITH
> > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> even
> > though I back up the Log.
> >
> >
> > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > However, I get an error in the Event Viever(Application Log) stating
> > "Database Log truncated".
> >
> > My question is:
> > Should I worry about this error ?
> > Is any data loss likely will this arrangement ?
> >
> > After all, I truncate the Transaction Log on purpose, right after
backing
> it
> > back in the previous step.
> >
> > Regards,
> >
> > Sezgin
> >
> >
> >
> >
>|||THe log should eventually shrink, and you should NOT be using backup log
with no_log...
I noticed however that you said you were using dbcc shrink...
The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
be part of the problem?
--
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
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply Ray,
>
> The database has a high number of transactions every day. I have to make
> sure any possible data loss is minimized. That is why I used full recovery
> model. During tests, in about 5 weeks time, the Transaction Log grew up to
> nearly 7 GB (the data file currently is just over 1 GB). I tried my best
to
> shrink the Transaction Log without using the "Backup Log with no_log"
> command, but to no success. Could you offer any advice or alternative
> solution.
>
> Best regards,
>
> Sezgin Rafet
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > You should not use the no_log command if you require point in time
> recovery,
> > and if you do not require point in time recovery, set your recovery mode
> to
> > simple. I would advise not to be shrinking your log or data file like
you
> > are as if the db or log has to autogrow it costs you at the point it
needs
> > to allocate more space and that is typically when your db is busy.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > We have a SQL Server 2000 SP3 database with recovery model set to
full.
> I
> > > set up 3 automatic backup jobs as follows:
> > >
> > > Each day,Mon thru Fri at 9:00 am
> > > 1 step - full backup of database to file A with overwrite
> > >
> > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > 1 step - transaction log backup of database to file A with append
> > >
> > > Each day,Mon thru Fri at 5:05 pm
> > > 1st step - transaction log backup of database to file A with append
> > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > >
> > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> WITH
> > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> > even
> > > though I back up the Log.
> > >
> > >
> > > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > > However, I get an error in the Event Viever(Application Log) stating
> > > "Database Log truncated".
> > >
> > > My question is:
> > > Should I worry about this error ?
> > > Is any data loss likely will this arrangement ?
> > >
> > > After all, I truncate the Transaction Log on purpose, right after
> backing
> > it
> > > back in the previous step.
> > >
> > > Regards,
> > >
> > > Sezgin
> > >
> > >
> > >
> > >
> >
> >
>|||Thanks Wayne,
I used DBCC SHRINKFILE(db_log_file_name,1) , just have mistyped it in the
post.
Sezgin Rafet
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23G0pjgIyDHA.2440@.TK2MSFTNGP12.phx.gbl...
> THe log should eventually shrink, and you should NOT be using backup log
> with no_log...
> I noticed however that you said you were using dbcc shrink...
> The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
> be part of the problem?
> --
> 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
>
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks for your reply Ray,
> >
> >
> >
> > The database has a high number of transactions every day. I have to make
> > sure any possible data loss is minimized. That is why I used full
recovery
> > model. During tests, in about 5 weeks time, the Transaction Log grew up
to
> > nearly 7 GB (the data file currently is just over 1 GB). I tried my best
> to
> > shrink the Transaction Log without using the "Backup Log with no_log"
> > command, but to no success. Could you offer any advice or alternative
> > solution.
> >
> >
> >
> > Best regards,
> >
> >
> >
> > Sezgin Rafet
> >
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > > You should not use the no_log command if you require point in time
> > recovery,
> > > and if you do not require point in time recovery, set your recovery
mode
> > to
> > > simple. I would advise not to be shrinking your log or data file like
> you
> > > are as if the db or log has to autogrow it costs you at the point it
> needs
> > > to allocate more space and that is typically when your db is busy.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > > We have a SQL Server 2000 SP3 database with recovery model set to
> full.
> > I
> > > > set up 3 automatic backup jobs as follows:
> > > >
> > > > Each day,Mon thru Fri at 9:00 am
> > > > 1 step - full backup of database to file A with overwrite
> > > >
> > > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > > 1 step - transaction log backup of database to file A with append
> > > >
> > > > Each day,Mon thru Fri at 5:05 pm
> > > > 1st step - transaction log backup of database to file A with append
> > > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > > >
> > > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> > WITH
> > > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I
want,
> > > even
> > > > though I back up the Log.
> > > >
> > > >
> > > > When I use BACKUP LOG WITH NO_LOG everything seems to be working
OK.
> > > > However, I get an error in the Event Viever(Application Log) stating
> > > > "Database Log truncated".
> > > >
> > > > My question is:
> > > > Should I worry about this error ?
> > > > Is any data loss likely will this arrangement ?
> > > >
> > > > After all, I truncate the Transaction Log on purpose, right after
> > backing
> > > it
> > > > back in the previous step.
> > > >
> > > > Regards,
> > > >
> > > > Sezgin
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
David
DBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>
|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing LDF files

Hi,
If I understand it correctly, you only need an LDF file to restore to a point in time after the last full backup? If this is so, then Could the LDF file not be reduced in size on perfoming a full backup?
Most of the time it's not an issue as there is enough space on the HDD, but is it possible to reduce the ldf file size periodically (manually would be fine). Is changing the recovery mode from FULL to SIMPLE and then back to FULL an option?
If so, is anyone able to tell me how, exactly, I can do this? ... I've sifted through the documentaion to no avail ... :eek:
Many thanks
RobNo, you cannot just truncate the log when you take the full backup, because you do not know whether the error forcing you to restore the database will occur prior to or after the last full backup. You may leave the office at 4pm, at 5 pm some tables disappear. Backups are scheduled to run at midnight. If the logs are truncated when you arrive at the office the next morning, you cannot restore to point in time anymore.

Thus, the ONLY way you should free space in the logfiles is backing them up with the backup log statement, which frees up place internally in the log files.|||do not change recovery mode FULL to SIMPLE. it will break log chain.|||If you are just starting off with SQL Server administration, I would recommend using the maintenance plan wizard (if you are using SQL 2005, be sure to download and install SP2, which has significant improvements to maintenance plans in general).

General guidelines:

SQL 7.0/2000:
1. Create a maintenance plan that includes only the system databases (master, model, msdb). Do a full backup daily.

2. Create another maintenance plan that includes only the user databases. Do a full backup daily and a transaction log backup as often as you are comfortable (and as often as needed to keep the logs at a stable/reasonable size).

3. For both plans:
a. Back up over the network (if your network is stable), or straight to disk (separate from data/log files).
b. Retain full backups on disk for 2-3 days (more if you can afford it).
c. Retain log backups for as long as necessary to get back to your last good full backup.
c. Make sure there's another process that writes the db backups to tape.

SQL 2005
1. Create one plan for full backups for all databases

2. Create another plan for log backups for all user databases.

3. Follow other guidelines as above.

There are lots of tweaks and other things that you can do to improve backup performance, this is just a guide to get you started.

Be sure to also:
1. Practice recovery:
a. Practice full database restore
b. Practive database restore to point in time
c. Practice recovery from tape
d. Practice recovery from a dead server (ie, full rebuild/fresh install)

2. Validate your tape backups

3. Document your backup strategy and document your recovery plan.

Regards,

hmscott

Reducing Backup Size

Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.
Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
"C TO" wrote:

> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.

Reducing Backup Size

Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to be
very cost productive. But Lightspeed will proably be as close to an industry
standard that you will get.
--
"C TO" wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.

Reducing Backup Size

Hello World,
Are there any obvious reasons not to shrink a database right before backup
with a recovery model of "Simple" and re-allocate the free space right after
backup? This is a ETL intensive database.
Backup strategy: daily differential and weekly full.
Thanks.Hi,
Backup file will only utilize the used portion of data. So why do you want
to shrink the free portion and re allocate
after the backup?
Thanks
Hari
SQL Server MVP
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:3A646608-80FC-4277-8403-D73E624A9C75@.microsoft.com...
> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right
> after
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.|||C TO wrote:
> Hello World,
> Are there any obvious reasons not to shrink a database right before
> backup with a recovery model of "Simple" and re-allocate the free
> space right after backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.
SQL Server does not backup empty space; only the data. To save backup
space and reduce backup and recovery time, consider using a 3rd party
backup solution for SQL Server. I work for Quest and we sell LiteSpeed.
There are other 3rd part backup solutions that do this as well.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I agree, go the third party route on this one. Download a free eval of
lightspeed from Imceda. You will be amazed by the time and space savings.
For the budget minded, I have found Red-Gate (www.red-gate.com I think) to b
e
very cost productive. But Lightspeed will proably be as close to an industr
y
standard that you will get.
--
"C TO" wrote:

> Hello World,
> Are there any obvious reasons not to shrink a database right before backup
> with a recovery model of "Simple" and re-allocate the free space right aft
er
> backup? This is a ETL intensive database.
> Backup strategy: daily differential and weekly full.
> Thanks.

Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K

Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
Sam
You can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.

> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>

Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K

Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>

Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K

Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.

> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>