Making the Log file small

backups shrink the logical space, not physical...
try backup log with the no_log clause, then shrink the file
Tina wrote:
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a
> transaction backup that it will make it small. That did give it 90% free
> space. So then I did a shrink (all three types) but that did not shrink i
t
> at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d
> reattach it that a new small log file will be built. But, the reattach
> would not work saying that the log file is missing. So I had to move it
> back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> TI'm using ss2005 and Management Studio...
I have this huge log file and I want it to be small. I read that if I do a
transaction backup that it will make it small. That did give it 90% free
space. So then I did a shrink (all three types) but that did not shrink it
at all. I did a database shrink and that did nothing.
Then I read that if I detach it, delete the log file (I just moved it), and
reattach it that a new small log file will be built. But, the reattach
would not work saying that the log file is missing. So I had to move it
back and now I still have this huge log file.
How can I make it small?
Thanks,
T|||backups shrink the logical space, not physical...
try backup log with the no_log clause, then shrink the file
Tina wrote:
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a
> transaction backup that it will make it small. That did give it 90% free
> space. So then I did a shrink (all three types) but that did not shrink i
t
> at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d
> reattach it that a new small log file will be built. But, the reattach
> would not work saying that the log file is missing. So I had to move it
> back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T|||After you backup your log file, you can shrink the file with DBCC
SHRINKFILE. For example:
DBCC SHRINKFILE('MyDatabase_Log', 100)
See the SQL Server Books Online for more info on DBCC SHRINKFILE.
The most common cause of out-of-control log files is failure to implement a
backup/recovery plan. If you choose to run the database in the
FULL/BULK_LOGGED recovery model, you will need to do regular transaction log
backups to keep the log size reasonable and provide the means to minimize
data loss after a failure. You can also run the database in the SIMPLE
recovery model so that you don't need to bother with log backups. However,
your only recovery recourse in the SIMPLE model is to restore from the last
full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
> a transaction backup that it will make it small. That did give it 90%
> free space. So then I did a shrink (all three types) but that did not
> shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it),
> and reattach it that a new small log file will be built. But, the
> reattach would not work saying that the log file is missing. So I had to
> move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>|||After you backup your log file, you can shrink the file with DBCC
SHRINKFILE. For example:
DBCC SHRINKFILE('MyDatabase_Log', 100)
See the SQL Server Books Online for more info on DBCC SHRINKFILE.
The most common cause of out-of-control log files is failure to implement a
backup/recovery plan. If you choose to run the database in the
FULL/BULK_LOGGED recovery model, you will need to do regular transaction log
backups to keep the log size reasonable and provide the means to minimize
data loss after a failure. You can also run the database in the SIMPLE
recovery model so that you don't need to bother with log backups. However,
your only recovery recourse in the SIMPLE model is to restore from the last
full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
> a transaction backup that it will make it small. That did give it 90%
> free space. So then I did a shrink (all three types) but that did not
> shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it),
> and reattach it that a new small log file will be built. But, the
> reattach would not work saying that the log file is missing. So I had to
> move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>|||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/
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a transaction backup
> that it will make it small. That did give it 90% free space. So then I d
id a shrink (all three
> types) but that did not shrink it at all. I did a database shrink and tha
t did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d reattach it that a new
> small log file will be built. But, the reattach would not work saying tha
t the log file is
> missing. So I had to move it back and now I still have this huge log fil
e.
> How can I make it small?
> Thanks,
> T
>|||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/
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
a transaction backup
> that it will make it small. That did give it 90% free space. So then I d
id a shrink (all three
> types) but that did not shrink it at all. I did a database shrink and tha
t did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), an
d reattach it that a new
> small log file will be built. But, the reattach would not work saying tha
t the log file is
> missing. So I had to move it back and now I still have this huge log fil
e.
> How can I make it small?
> Thanks,
> T
>sql

making an item repeat

Sorry.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"hero281" wrote:

> i have created a sql database with a vb.net front end. i have records that
> databse that have dates attached. i want each record to repeat in a given
> time frame according to a field in the datarow. is it possible.
> someone please help my ship is sinking.
> hero281i have created a sql database with a vb.net front end. i have records that
databse that have dates attached. i want each record to repeat in a given
time frame according to a field in the datarow. is it possible.
someone please help my ship is sinking.
hero281|||Sorry.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"hero281" wrote:

> i have created a sql database with a vb.net front end. i have records that
> databse that have dates attached. i want each record to repeat in a given
> time frame according to a field in the datarow. is it possible.
> someone please help my ship is sinking.
> hero281|||"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Sorry.
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "hero281" wrote:
>
here is a sample layout of the database.
column format:
id taskname duedate freq
autonumber clean floors 06/01/06 30
" " " "
i would like to duplicate this task based on the freq.|||"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Sorry.
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "hero281" wrote:
>
here is a sample layout of the database.
column format:
id taskname duedate freq
autonumber clean floors 06/01/06 30
" " " "
i would like to duplicate this task based on the freq.|||"hero281" <hero281@.discussions.microsoft.com> wrote in message
news:4A89D9EA-A1C0-4389-A53C-818FDB384648@.microsoft.com...
>
> "Alejandro Mesa" wrote:
>
that[vbcol=seagreen]
given[vbcol=seagreen]
>
> here is a sample layout of the database.
> column format:
> id taskname duedate freq
> autonumber clean floors 06/01/06 30
> " " " "
> i would like to duplicate this task based on the freq.
>
It's still not clear what you want.
Do you want something like
1 Clean Floor 06/01/06 30
1 Clean Floor 06/02/06 30
.
.
.
1 Clean Floor 06/30/06 30
?
Not really sure what you're doing then with the freq?
What if it's not the same as the number of days in the month?

>
>|||"hero281" <hero281@.discussions.microsoft.com> wrote in message
news:4A89D9EA-A1C0-4389-A53C-818FDB384648@.microsoft.com...
>
> "Alejandro Mesa" wrote:
>
that[vbcol=seagreen]
given[vbcol=seagreen]
>
> here is a sample layout of the database.
> column format:
> id taskname duedate freq
> autonumber clean floors 06/01/06 30
> " " " "
> i would like to duplicate this task based on the freq.
>
It's still not clear what you want.
Do you want something like
1 Clean Floor 06/01/06 30
1 Clean Floor 06/02/06 30
.
.
.
1 Clean Floor 06/30/06 30
?
Not really sure what you're doing then with the freq?
What if it's not the same as the number of days in the month?

>
>|||hero281,
A table of numbers will be very handy here.
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
select
[id],
taskname,
duedate,
freq
from
dbo.t1
inner join
dbo.number as n
on n.number <= t1.freq
go
AMB
"hero281" wrote:

>
> "Alejandro Mesa" wrote:
>
>
> here is a sample layout of the database.
> column format:
> id taskname duedate freq
> autonumber clean floors 06/01/06 30
> " " " "
> i would like to duplicate this task based on the freq.
>
>
>|||hero281,
A table of numbers will be very handy here.
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
select
[id],
taskname,
duedate,
freq
from
dbo.t1
inner join
dbo.number as n
on n.number <= t1.freq
go
AMB
"hero281" wrote:

>
> "Alejandro Mesa" wrote:
>
>
> here is a sample layout of the database.
> column format:
> id taskname duedate freq
> autonumber clean floors 06/01/06 30
> " " " "
> i would like to duplicate this task based on the freq.
>
>
>sql

Maintenance Plan tips

Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
>
Hi Frederik
Is there any reason why you will run differential backups? Unless the
database are really big, I'd simply just do a full backup every night.
That makes administration easier and when you have to restore a backup,
it's only a single file you need to restore.
Regards
Steen Schlter Persson
Databaseadministrator / SystemadministratorHi!
I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
And would like
some input on my backup plan.
Background
The server is a database development server where databases are added and
deleted on a weekly basis. All the newly added databases has to be backuped
without any need for backup configuration changes.
In case of a crash/mistake I would like the most recent backup to be no
older
than 24h, as well as you have to be able to go one week back in time to
restore a database.
How does the below backup plan look?
Full backups on saturdays
Differential backups sunday - friday.
All backups kept for 2 weeks
Is this a good plan or is there a better way.
pros/cons?
/Fredrik|||Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
>
Hi Frederik
Is there any reason why you will run differential backups? Unless the
database are really big, I'd simply just do a full backup every night.
That makes administration easier and when you have to restore a backup,
it's only a single file you need to restore.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
Have a look at the script I've posted here: http://www.realsqlguy.com/?p=8
I'm not a big fan of maintenance plans, I think they hide certain
critical operations from the "administrator", when that person really
should understand what's going on.
The script I've linked to is used on my production servers (slightly
edited for public consumption). The script is scheduled to run every
five minutes, and each time it runs, it does the following, for each
database on the server:
- if a database is in Simple recovery mode, and it has been 24 hours
since the last full backup, a full backup is done
- if it is has been more than 7 days since a full backup was done of a
database, a full backup is done
- if it has been more than 3 days since a full backup, and it's
currently after 9:00pm on Friday, a full backup is run. This keeps your
full backups clustered around the weekends, typically non-production
time in many environments.
- if a database is in Simple recovery mode, and the log is 75% full, the
log is truncated
- if a database is not in Simple mode, and the log is 75% full OR it has
been more than 5 minutes since the last t-log backup, a transaction log
backup is done
Sounds like this would be perfect for your environment. The script will
automatically detect new databases as they are added. You shouldn't
have to touch a thing.|||Fredrik Danielsson wrote:
Hi!
I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
And would like
some input on my backup plan.
Background
The server is a database development server where databases are added and
deleted on a weekly basis. All the newly added databases has to be backuped
without any need for backup configuration changes.
In case of a crash/mistake I would like the most recent backup to be no
older
than 24h, as well as you have to be able to go one week back in time to
restore a database.
How does the below backup plan look?
Full backups on saturdays
Differential backups sunday - friday.
All backups kept for 2 weeks
Is this a good plan or is there a better way.
pros/cons?
/Fredrik
Hi Frederik
Is there any reason why you will run differential backups? Unless the
database are really big, I'd simply just do a full backup every night. That
makes administration easier and when you have to restore a backup, it's only
a single file you need to restore.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator
Hi Steen,
The reason why I'm thinking about doing Differential backups is because
there are a number of large databases that we receive from our customers.
These databases are only used to migrate data from, they are never altered.
It would be enterly possible to do full backups every night on the
development databases. But the databases that we receive can be quite large.
(30-40GB)
The machine only has 270 GB of space for databases and backups.
If there is a simple way of excluding some databases from the backups there
would be no problem. But it's more important that new databases are included
in the backups
/Fredrik|||Fredrik D wrote:
> Fredrik Danielsson wrote:
FYI, SQL backup compress REALLY well. You might consider creating a
compressed OS volume on which to store your backup files.|||Microsoft Compression is not supported on and SQL Server files (data,
log, backup, or otherwise). See this link:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
You are better off using SQLBackup or LiteSpeed if you need to compress
your backups.
Tracy McKibben wrote:
> Fredrik D wrote:
> FYI, SQL backup compress REALLY well. You might consider creating a
> compressed OS volume on which to store your backup files.|||PSPDBA wrote:
> Microsoft Compression is not supported on and SQL Server files (data,
> log, backup, or otherwise). See this link:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[
/url]
>
Interesting, because I've been compressing my backup folders for YEARS.
You are absolutely correct that the data and log files cannot be
compressed, but the BACKUPS can be. From the article you linked to,
under the Compression heading:
"All SQL Server database log and data files should remain in an
uncompressed state."
Not a word about backups...

> You are better off using SQLBackup or LiteSpeed if you need to compress
> your backups.
Why? If you want to do real-time compression without using the OS
compression, pipe the backup through GZip. Does EXACTLY the same thing
that either of those products do, with a much lower price tag.|||Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
Have a look at the script I've posted here: http://www.realsqlguy.com/?p=8
I'm not a big fan of maintenance plans, I think they hide certain
critical operations from the "administrator", when that person really
should understand what's going on.
The script I've linked to is used on my production servers (slightly
edited for public consumption). The script is scheduled to run every
five minutes, and each time it runs, it does the following, for each
database on the server:
- if a database is in Simple recovery mode, and it has been 24 hours
since the last full backup, a full backup is done
- if it is has been more than 7 days since a full backup was done of a
database, a full backup is done
- if it has been more than 3 days since a full backup, and it's
currently after 9:00pm on Friday, a full backup is run. This keeps your
full backups clustered around the weekends, typically non-production
time in many environments.
- if a database is in Simple recovery mode, and the log is 75% full, the
log is truncated
- if a database is not in Simple mode, and the log is 75% full OR it has
been more than 5 minutes since the last t-log backup, a transaction log
backup is done
Sounds like this would be perfect for your environment. The script will
automatically detect new databases as they are added. You shouldn't
have to touch a thing.|||Fredrik D wrote:
> Fredrik Danielsson wrote:
FYI, SQL backup compress REALLY well. You might consider creating a
compressed OS volume on which to store your backup files.