Showing posts with label huge. Show all posts
Showing posts with label huge. Show all posts

Wednesday, March 28, 2012

HELP! Transaction Log is huge

Hi all,
I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
Transaction log backup right after is 126MB. I tried shrinking and
everything else I could think of. I even deleted the DB and restored it from
a full DB backup. The ldf still comes up with 11GB. I have another
installation of the same database on another server where the ldf shows a
normal size (40MB).
Can anybody tell me what is going on? During the restore I moved the data
files to another disk that has lots of space but there is something wrong
here and I would like to get to the bottom of it.
Thanks for your help,
ClausHi, what is your recovery set too. If it is set to "Full" you need to do a
translog backup, and truncate after.
If you set it to simple, it will checkpoint and keep the log resonable.
Try backup log (database) with truncate_only
Then shrink the log file.
"cjobes" wrote:
> Hi all,
> I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
> the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
> Transaction log backup right after is 126MB. I tried shrinking and
> everything else I could think of. I even deleted the DB and restored it from
> a full DB backup. The ldf still comes up with 11GB. I have another
> installation of the same database on another server where the ldf shows a
> normal size (40MB).
> Can anybody tell me what is going on? During the restore I moved the data
> files to another disk that has lots of space but there is something wrong
> here and I would like to get to the bottom of it.
> Thanks for your help,
> Claus
>
>|||I have it set to full because we do a daily backup and then hourly
transaction log backups. The one instance works as expected but the other
one has this 11GB ldf and I don't seem to be able to get it down.
Now after reading your advice I switched the db to simple, run a backup and
then shrank the log file. Now it's down to 0.5MB. My question is, shouldn't
it do that automatically when the full db backup runs with the option to
remove inactive entries from the transaction log?
Thanks for your help,
Claus
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||Hi,
As soon as you run the trasnaction log backup the inactive entries of
transaction log will be cleared. But the physcal file will not get shrink
automatically until or unless you enable to database option "AUTO_SHRINK".
Turning on this option in a production server is not recommended.
Because auto shrinking will reduce the performance.
To avoid this after the transaction log backup you could do a DBCC
SHRINKFILE (refer books online) on the LDF file frequently
(Daily or weekly once.. based on the LDF growth) to reduce the LDF file
size.
DBCC SQLPERF(LOGSPACE)
will give you the exact file size and usage
Thanks
Hari
MCDBA
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> I have it set to full because we do a daily backup and then hourly
> transaction log backups. The one instance works as expected but the other
> one has this 11GB ldf and I don't seem to be able to get it down.
> Now after reading your advice I switched the db to simple, run a backup
and
> then shrank the log file. Now it's down to 0.5MB. My question is,
shouldn't
> it do that automatically when the full db backup runs with the option to
> remove inactive entries from the transaction log?
> Thanks for your help,
> Claus
>
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do
a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored
it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf
shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>|||Thanks for the helpful advice. I will keep an eye on this.
Claus
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eWRTv$ZhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> Hi,
> As soon as you run the trasnaction log backup the inactive entries of
> transaction log will be cleared. But the physcal file will not get shrink
> automatically until or unless you enable to database option "AUTO_SHRINK".
> Turning on this option in a production server is not recommended.
> Because auto shrinking will reduce the performance.
> To avoid this after the transaction log backup you could do a DBCC
> SHRINKFILE (refer books online) on the LDF file frequently
> (Daily or weekly once.. based on the LDF growth) to reduce the LDF file
> size.
> DBCC SQLPERF(LOGSPACE)
> will give you the exact file size and usage
> Thanks
> Hari
> MCDBA
>
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I have it set to full because we do a daily backup and then hourly
> > transaction log backups. The one instance works as expected but the
other
> > one has this 11GB ldf and I don't seem to be able to get it down.
> >
> > Now after reading your advice I switched the db to simple, run a backup
> and
> > then shrank the log file. Now it's down to 0.5MB. My question is,
> shouldn't
> > it do that automatically when the full db backup runs with the option to
> > remove inactive entries from the transaction log?
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do
> a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
> and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
> it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
> shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
>|||I have the same problem with a client as you do. I did the backup and
shrink but the log file is still at 23gigs!
Where do I find the option to 'truncate' in the SQL Server Enterprise
Manager?
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||You have to do it in the analyzer
backup log "database" with truncate_only
You can also add it as a task in a job.
"Dean Penderghast" wrote:
> I have the same problem with a client as you do. I did the backup and
> shrink but the log file is still at 23gigs!
> Where do I find the option to 'truncate' in the SQL Server Enterprise
> Manager?
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>
>|||I did that and the log file is still 23 gigs in size. I've done a full
transaction backup and tried to resize the file and still can't get it to
shrink. Am I missing something simple here?
Dean Penderghast
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:DF200740-7079-472C-8D0B-563ECC3FBBFD@.microsoft.com...
> You have to do it in the analyzer
> backup log "database" with truncate_only
> You can also add it as a task in a job.
> "Dean Penderghast" wrote:
> > I have the same problem with a client as you do. I did the backup and
> > shrink but the log file is still at 23gigs!
> >
> > Where do I find the option to 'truncate' in the SQL Server Enterprise
> > Manager?
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
> >sql

HELP! Transaction log huge, & I'm out of space!

I have several .ldf files for one of my databases and one
of them is taking up 23 gigs. I really dont need to save
the transaction logs so I would like to just delete them
and start with a smaller log file. We created another
log file on c: now just so we can keep things running but
that is going to fill up very soon as well.
Is there any ways I can just delete the all the
transaction logs and just create one that is very small?
When I try to delete them I get an error message that
says I cant delete the file because it is not empty.
When I take the database offline to delete the file I
cant do that either because I cant access the database.
Any suggestions?
Two things:
A) Put your databases into Simple Recovery mode. They're probably in
Full Recovery mode now, which is why your logs are growing so quickly.
B) To truncate the logs, run:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then use sp_helpfile to get the file ID of the log file(s), and for
each, run:
DBCC SHRINKFILE(<FileID>, EMPTYFILE)
This should drop any space available back to the operating system.
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?
|||Hi,
Detach the database. Then delete the .ldf (rename it to be on the safe
side). Then reattach the database. It will create a new .ldf. Once this
is achieved, you can permanently delete that .ldf.
If you never need the .ldf, change the recovery model to simple.
HTH,
Bernie Yaeger
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?
|||You can't attach a database with multiple log files using
sp_attach_single_file_db. The method Adam suggested is the right approach
to remove unneeded log files.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.n et...
> Hi,
> Detach the database. Then delete the .ldf (rename it to be on the safe
> side). Then reattach the database. It will create a new .ldf. Once this
> is achieved, you can permanently delete that .ldf.
> If you never need the .ldf, change the recovery model to simple.
> HTH,
> Bernie Yaeger
> "Groundid" <groundid@.comcast.net> wrote in message
> news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
>
|||Hi Dan,
If you either delete or rename (at the DOS level) the log file, you will not
be attaching a database with multiple log files.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7raWMjjEHA.3608@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You can't attach a database with multiple log files using
> sp_attach_single_file_db. The method Adam suggested is the right approach
> to remove unneeded log files.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.n et...
this
>
|||"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
> If you either delete or rename (at the DOS level) the log file, you will
not
> be attaching a database with multiple log files.
Bernie,
To understand what Dan is talking about (and to get a glimpse into how I
once destroyed a weeks' worth of work and decided to never again manually
delete the log file):
Create a database with two log files. Detach it. Delete one (or both)
of the log files. Now try to re-attach it.
|||Hi groundid,
Dont delete, just shrink the file. Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Idetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
4. Check the current file size by execution
DBCC SQLPERF(LOGSPACE)
Have a look into the below article on shrink file,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Note:
Please perform a full database backup after performing the above steps, as
well as schedule a transaction log backup frequently based on ur
requirement.
Thanks
Hari
MCDBA
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?
|||Bernie, try the script below.
CREATE DATABASE TwoLogFiles ON
(NAME='TwoLogFiles',
FILENAME='C:\TwoLogFiles.mdf')
LOG ON
(NAME='TwoLogFiles_Log1',
FILENAME='C:\TwoLogFiles_Log1.ldf'),
(NAME='TwoLogFiles_Log2',
FILENAME='C:\TwoLogFiles_Log2.ldf')
EXEC sp_detach_db 'TwoLogFiles'
EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
EXEC sp_attach_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf',
'C:\TwoLogFiles_Log1.ldf'
EXEC sp_attach_single_file_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
> Hi Dan,
> If you either delete or rename (at the DOS level) the log file, you will
> not
> be attaching a database with multiple log files.
> Bernie
>
|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> Bernie, try the script below.
> CREATE DATABASE TwoLogFiles ON
> (NAME='TwoLogFiles',
> FILENAME='C:\TwoLogFiles.mdf')
> LOG ON
> (NAME='TwoLogFiles_Log1',
> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> (NAME='TwoLogFiles_Log2',
> FILENAME='C:\TwoLogFiles_Log2.ldf')
> EXEC sp_detach_db 'TwoLogFiles'
> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> EXEC sp_attach_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf',
> 'C:\TwoLogFiles_Log1.ldf'
> EXEC sp_attach_single_file_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
>
|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23k44GyjjEHA.1136@.tk2msftngp13.phx.gbl...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.n et...
> not
> Bernie,
> To understand what Dan is talking about (and to get a glimpse into how
I
> once destroyed a weeks' worth of work and decided to never again manually
> delete the log file):
> Create a database with two log files. Detach it. Delete one (or
both)
> of the log files. Now try to re-attach it.
>

HELP! Transaction log huge, & I'm out of space!

I have several .ldf files for one of my databases and one
of them is taking up 23 gigs. I really dont need to save
the transaction logs so I would like to just delete them
and start with a smaller log file. We created another
log file on c: now just so we can keep things running but
that is going to fill up very soon as well.
Is there any ways I can just delete the all the
transaction logs and just create one that is very small?
When I try to delete them I get an error message that
says I cant delete the file because it is not empty.
When I take the database offline to delete the file I
cant do that either because I cant access the database.
Any suggestions?Two things:
A) Put your databases into Simple Recovery mode. They're probably in
Full Recovery mode now, which is why your logs are growing so quickly.
B) To truncate the logs, run:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then use sp_helpfile to get the file ID of the log file(s), and for
each, run:
DBCC SHRINKFILE(<FileID>, EMPTYFILE)
This should drop any space available back to the operating system.
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Hi,
Detach the database. Then delete the .ldf (rename it to be on the safe
side). Then reattach the database. It will create a new .ldf. Once this
is achieved, you can permanently delete that .ldf.
If you never need the .ldf, change the recovery model to simple.
HTH,
Bernie Yaeger
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||You can't attach a database with multiple log files using
sp_attach_single_file_db. The method Adam suggested is the right approach
to remove unneeded log files.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> Hi,
> Detach the database. Then delete the .ldf (rename it to be on the safe
> side). Then reattach the database. It will create a new .ldf. Once this
> is achieved, you can permanently delete that .ldf.
> If you never need the .ldf, change the recovery model to simple.
> HTH,
> Bernie Yaeger
> "Groundid" <groundid@.comcast.net> wrote in message
> news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
>|||Hi Dan,
If you either delete or rename (at the DOS level) the log file, you will not
be attaching a database with multiple log files.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7raWMjjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> You can't attach a database with multiple log files using
> sp_attach_single_file_db. The method Adam suggested is the right approach
> to remove unneeded log files.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
this[vbcol=seagreen]
>|||"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> If you either delete or rename (at the DOS level) the log file, you will
not
> be attaching a database with multiple log files.
Bernie,
To understand what Dan is talking about (and to get a glimpse into how I
once destroyed a weeks' worth of work and decided to never again manually
delete the log file):
Create a database with two log files. Detach it. Delete one (or both)
of the log files. Now try to re-attach it.|||Hi groundid,
Dont delete, just shrink the file. Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Idetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
4. Check the current file size by execution
DBCC SQLPERF(LOGSPACE)
Have a look into the below article on shrink file,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Note:
Please perform a full database backup after performing the above steps, as
well as schedule a transaction log backup frequently based on ur
requirement.
Thanks
Hari
MCDBA
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Bernie, try the script below.
CREATE DATABASE TwoLogFiles ON
(NAME='TwoLogFiles',
FILENAME='C:\TwoLogFiles.mdf')
LOG ON
(NAME='TwoLogFiles_Log1',
FILENAME='C:\TwoLogFiles_Log1.ldf'),
(NAME='TwoLogFiles_Log2',
FILENAME='C:\TwoLogFiles_Log2.ldf')
EXEC sp_detach_db 'TwoLogFiles'
EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
EXEC sp_attach_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf',
'C:\TwoLogFiles_Log1.ldf'
EXEC sp_attach_single_file_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> Hi Dan,
> If you either delete or rename (at the DOS level) the log file, you will
> not
> be attaching a database with multiple log files.
> Bernie
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> Bernie, try the script below.
> CREATE DATABASE TwoLogFiles ON
> (NAME='TwoLogFiles',
> FILENAME='C:\TwoLogFiles.mdf')
> LOG ON
> (NAME='TwoLogFiles_Log1',
> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> (NAME='TwoLogFiles_Log2',
> FILENAME='C:\TwoLogFiles_Log2.ldf')
> EXEC sp_detach_db 'TwoLogFiles'
> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> EXEC sp_attach_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf',
> 'C:\TwoLogFiles_Log1.ldf'
> EXEC sp_attach_single_file_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23k44GyjjEHA.1136@.tk2msftngp13.phx.gbl...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> not
> Bernie,
> To understand what Dan is talking about (and to get a glimpse into how
I
> once destroyed a weeks' worth of work and decided to never again manually
> delete the log file):
> Create a database with two log files. Detach it. Delete one (or
both)
> of the log files. Now try to re-attach it.
>sql

HELP! Transaction log huge, & I'm out of space!

I have several .ldf files for one of my databases and one
of them is taking up 23 gigs. I really dont need to save
the transaction logs so I would like to just delete them
and start with a smaller log file. We created another
log file on c: now just so we can keep things running but
that is going to fill up very soon as well.
Is there any ways I can just delete the all the
transaction logs and just create one that is very small?
When I try to delete them I get an error message that
says I cant delete the file because it is not empty.
When I take the database offline to delete the file I
cant do that either because I cant access the database.
Any suggestions?Two things:
A) Put your databases into Simple Recovery mode. They're probably in
Full Recovery mode now, which is why your logs are growing so quickly.
B) To truncate the logs, run:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then use sp_helpfile to get the file ID of the log file(s), and for
each, run:
DBCC SHRINKFILE(<FileID>, EMPTYFILE)
This should drop any space available back to the operating system.
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Hi,
Detach the database. Then delete the .ldf (rename it to be on the safe
side). Then reattach the database. It will create a new .ldf. Once this
is achieved, you can permanently delete that .ldf.
If you never need the .ldf, change the recovery model to simple.
HTH,
Bernie Yaeger
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||You can't attach a database with multiple log files using
sp_attach_single_file_db. The method Adam suggested is the right approach
to remove unneeded log files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> Hi,
> Detach the database. Then delete the .ldf (rename it to be on the safe
> side). Then reattach the database. It will create a new .ldf. Once this
> is achieved, you can permanently delete that .ldf.
> If you never need the .ldf, change the recovery model to simple.
> HTH,
> Bernie Yaeger
> "Groundid" <groundid@.comcast.net> wrote in message
> news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
>> I have several .ldf files for one of my databases and one
>> of them is taking up 23 gigs. I really dont need to save
>> the transaction logs so I would like to just delete them
>> and start with a smaller log file. We created another
>> log file on c: now just so we can keep things running but
>> that is going to fill up very soon as well.
>> Is there any ways I can just delete the all the
>> transaction logs and just create one that is very small?
>> When I try to delete them I get an error message that
>> says I cant delete the file because it is not empty.
>> When I take the database offline to delete the file I
>> cant do that either because I cant access the database.
>> Any suggestions?
>|||Hi Dan,
If you either delete or rename (at the DOS level) the log file, you will not
be attaching a database with multiple log files.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7raWMjjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> You can't attach a database with multiple log files using
> sp_attach_single_file_db. The method Adam suggested is the right approach
> to remove unneeded log files.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:hsuYc.11778$Es2.6336186@.news4.srv.hcvlny.cv.net...
> > Hi,
> >
> > Detach the database. Then delete the .ldf (rename it to be on the safe
> > side). Then reattach the database. It will create a new .ldf. Once
this
> > is achieved, you can permanently delete that .ldf.
> >
> > If you never need the .ldf, change the recovery model to simple.
> >
> > HTH,
> >
> > Bernie Yaeger
> >
> > "Groundid" <groundid@.comcast.net> wrote in message
> > news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> >> I have several .ldf files for one of my databases and one
> >> of them is taking up 23 gigs. I really dont need to save
> >> the transaction logs so I would like to just delete them
> >> and start with a smaller log file. We created another
> >> log file on c: now just so we can keep things running but
> >> that is going to fill up very soon as well.
> >>
> >> Is there any ways I can just delete the all the
> >> transaction logs and just create one that is very small?
> >> When I try to delete them I get an error message that
> >> says I cant delete the file because it is not empty.
> >> When I take the database offline to delete the file I
> >> cant do that either because I cant access the database.
> >> Any suggestions?
> >
> >
>|||"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> If you either delete or rename (at the DOS level) the log file, you will
not
> be attaching a database with multiple log files.
Bernie,
To understand what Dan is talking about (and to get a glimpse into how I
once destroyed a weeks' worth of work and decided to never again manually
delete the log file):
Create a database with two log files. Detach it. Delete one (or both)
of the log files. Now try to re-attach it.|||Hi groundid,
Dont delete, just shrink the file. Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Idetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
4. Check the current file size by execution
DBCC SQLPERF(LOGSPACE)
Have a look into the below article on shrink file,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Note:
Please perform a full database backup after performing the above steps, as
well as schedule a transaction log backup frequently based on ur
requirement.
Thanks
Hari
MCDBA
"Groundid" <groundid@.comcast.net> wrote in message
news:24e601c48dee$ca240e70$a301280a@.phx.gbl...
> I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs. I really dont need to save
> the transaction logs so I would like to just delete them
> and start with a smaller log file. We created another
> log file on c: now just so we can keep things running but
> that is going to fill up very soon as well.
> Is there any ways I can just delete the all the
> transaction logs and just create one that is very small?
> When I try to delete them I get an error message that
> says I cant delete the file because it is not empty.
> When I take the database offline to delete the file I
> cant do that either because I cant access the database.
> Any suggestions?|||Bernie, try the script below.
CREATE DATABASE TwoLogFiles ON
(NAME='TwoLogFiles',
FILENAME='C:\TwoLogFiles.mdf')
LOG ON
(NAME='TwoLogFiles_Log1',
FILENAME='C:\TwoLogFiles_Log1.ldf'),
(NAME='TwoLogFiles_Log2',
FILENAME='C:\TwoLogFiles_Log2.ldf')
EXEC sp_detach_db 'TwoLogFiles'
EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
EXEC sp_attach_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf',
'C:\TwoLogFiles_Log1.ldf'
EXEC sp_attach_single_file_db
'TwoLogFiles',
'C:\TwoLogFiles.mdf'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> Hi Dan,
> If you either delete or rename (at the DOS level) the log file, you will
> not
> be attaching a database with multiple log files.
> Bernie
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> Bernie, try the script below.
> CREATE DATABASE TwoLogFiles ON
> (NAME='TwoLogFiles',
> FILENAME='C:\TwoLogFiles.mdf')
> LOG ON
> (NAME='TwoLogFiles_Log1',
> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> (NAME='TwoLogFiles_Log2',
> FILENAME='C:\TwoLogFiles_Log2.ldf')
> EXEC sp_detach_db 'TwoLogFiles'
> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> EXEC sp_attach_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf',
> 'C:\TwoLogFiles_Log1.ldf'
> EXEC sp_attach_single_file_db
> 'TwoLogFiles',
> 'C:\TwoLogFiles.mdf'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> > Hi Dan,
> >
> > If you either delete or rename (at the DOS level) the log file, you will
> > not
> > be attaching a database with multiple log files.
> >
> > Bernie
> >
>|||Hi Dan, Adam,
Why are you guys talking about 2 transaction logs? This guy has one.
Here's a process I've used countless times and haven't experienced any
problem:
1.. right click the database in em;
2.. select all tasks;
3.. select detach database;
4.. proceed to detach;
5.. delete the .ldf at the DOS level;
6.. right click the 'databases' line beneath the server in em; all tasks;
attach the .mdf (browse for it to the appropriate sub);
7.. it will automatically create its own blank transaction log;
I am using the simple recovery model, but I don't know if this makes any
difference.
Now tell me why this doesn't work in your opinion and explain to me why it
always works for me (SQL Server 2000).
Bernie
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23k44GyjjEHA.1136@.tk2msftngp13.phx.gbl...
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> >
> > If you either delete or rename (at the DOS level) the log file, you will
> not
> > be attaching a database with multiple log files.
> Bernie,
> To understand what Dan is talking about (and to get a glimpse into how
I
> once destroyed a weeks' worth of work and decided to never again manually
> delete the log file):
> Create a database with two log files. Detach it. Delete one (or
both)
> of the log files. Now try to re-attach it.
>|||Bernie,
> Why are you guys talking about 2 transaction logs? This guy has one.
Here's a quote from the OP:
"I have several .ldf files for one of my databases and one
of them is taking up 23 gigs."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/|||> Why are you guys talking about 2 transaction logs? This guy has one.
As Tibor mentioned, the OP has multiple logs.
The procedure you suggest out to work with a single log file but is not the
preferred method. Even with a single log, the best approach to reduce log
file size is DBCC SHRINKFILE. This doesn't require the database to be
detached.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:MiFYc.18971$Es2.8127856@.news4.srv.hcvlny.cv.net...
> Hi Dan, Adam,
> Why are you guys talking about 2 transaction logs? This guy has one.
> Here's a process I've used countless times and haven't experienced any
> problem:
> 1.. right click the database in em;
> 2.. select all tasks;
> 3.. select detach database;
> 4.. proceed to detach;
> 5.. delete the .ldf at the DOS level;
> 6.. right click the 'databases' line beneath the server in em; all tasks;
> attach the .mdf (browse for it to the appropriate sub);
> 7.. it will automatically create its own blank transaction log;
> I am using the simple recovery model, but I don't know if this makes any
> difference.
> Now tell me why this doesn't work in your opinion and explain to me why it
> always works for me (SQL Server 2000).
> Bernie
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
>> Bernie, try the script below.
>> CREATE DATABASE TwoLogFiles ON
>> (NAME='TwoLogFiles',
>> FILENAME='C:\TwoLogFiles.mdf')
>> LOG ON
>> (NAME='TwoLogFiles_Log1',
>> FILENAME='C:\TwoLogFiles_Log1.ldf'),
>> (NAME='TwoLogFiles_Log2',
>> FILENAME='C:\TwoLogFiles_Log2.ldf')
>> EXEC sp_detach_db 'TwoLogFiles'
>> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
>> EXEC sp_attach_db
>> 'TwoLogFiles',
>> 'C:\TwoLogFiles.mdf',
>> 'C:\TwoLogFiles_Log1.ldf'
>> EXEC sp_attach_single_file_db
>> 'TwoLogFiles',
>> 'C:\TwoLogFiles.mdf'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
>> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
>> > Hi Dan,
>> >
>> > If you either delete or rename (at the DOS level) the log file, you
>> > will
>> > not
>> > be attaching a database with multiple log files.
>> >
>> > Bernie
>> >
>>
>|||Hi Tibor,
You're correct - I misread his question. Tx
Bernie
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23l2Sv9ojEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Bernie,
> > Why are you guys talking about 2 transaction logs? This guy has one.
> Here's a quote from the OP:
> "I have several .ldf files for one of my databases and one
> of them is taking up 23 gigs."
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||Hi Dan,
You're correct - I misread the initial question. Sorry.
Bernie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ewXt$lpjEHA.2788@.tk2msftngp13.phx.gbl...
> > Why are you guys talking about 2 transaction logs? This guy has one.
> As Tibor mentioned, the OP has multiple logs.
> The procedure you suggest out to work with a single log file but is not
the
> preferred method. Even with a single log, the best approach to reduce log
> file size is DBCC SHRINKFILE. This doesn't require the database to be
> detached.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:MiFYc.18971$Es2.8127856@.news4.srv.hcvlny.cv.net...
> > Hi Dan, Adam,
> >
> > Why are you guys talking about 2 transaction logs? This guy has one.
> > Here's a process I've used countless times and haven't experienced any
> > problem:
> > 1.. right click the database in em;
> > 2.. select all tasks;
> > 3.. select detach database;
> > 4.. proceed to detach;
> > 5.. delete the .ldf at the DOS level;
> > 6.. right click the 'databases' line beneath the server in em; all
tasks;
> > attach the .mdf (browse for it to the appropriate sub);
> > 7.. it will automatically create its own blank transaction log;
> > I am using the simple recovery model, but I don't know if this makes any
> > difference.
> >
> > Now tell me why this doesn't work in your opinion and explain to me why
it
> > always works for me (SQL Server 2000).
> >
> > Bernie
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:uvhweXkjEHA.536@.TK2MSFTNGP11.phx.gbl...
> >> Bernie, try the script below.
> >>
> >> CREATE DATABASE TwoLogFiles ON
> >> (NAME='TwoLogFiles',
> >> FILENAME='C:\TwoLogFiles.mdf')
> >> LOG ON
> >> (NAME='TwoLogFiles_Log1',
> >> FILENAME='C:\TwoLogFiles_Log1.ldf'),
> >> (NAME='TwoLogFiles_Log2',
> >> FILENAME='C:\TwoLogFiles_Log2.ldf')
> >>
> >> EXEC sp_detach_db 'TwoLogFiles'
> >>
> >> EXEC master..xp_cmdshell 'DEL C:\TwoLogFiles_Log2.ldf'
> >>
> >> EXEC sp_attach_db
> >> 'TwoLogFiles',
> >> 'C:\TwoLogFiles.mdf',
> >> 'C:\TwoLogFiles_Log1.ldf'
> >>
> >> EXEC sp_attach_single_file_db
> >> 'TwoLogFiles',
> >> 'C:\TwoLogFiles.mdf'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> >> news:fmwYc.12253$Es2.6666988@.news4.srv.hcvlny.cv.net...
> >> > Hi Dan,
> >> >
> >> > If you either delete or rename (at the DOS level) the log file, you
> >> > will
> >> > not
> >> > be attaching a database with multiple log files.
> >> >
> >> > Bernie
> >> >
> >>
> >>
> >
> >
>

Friday, March 23, 2012

help! question about hardware request for SQL Server and Analysis Services

I built a database with a huge table which has 18 billion lines for 6(or 7) columns.

And in the Analysis Services the table is used to create a dimention and a measure.

One of the other two dimentions is made form a 4000 line table,and the other is

made from a 3 million line table(time dimension table).

The specification of my Server is Windows Server 2003 with Xeon Intel Cpu 5160 @.3.00GHz,

2.99GHz,4.00G RAM.

The problem comes out when i start the processing of Analysis Services.

the "time out" error comes out after 1 hour, however, the performance i need

is completing the Processing for one cube within 10 munites.

I want to know the hardware request to reach my needs and how to speed the

processing of Analysis Services.

Thank you

So, to be clear, you have an 18 billion row fact table, you have built a cube on top of it and you want a full process to complete in 10 minutes?

Chris

|||

Dear Mr

yes! I wanna a full process within 10 minutes.

Can it be done?

thanks in advance

tomigisi

|||

The short answer is no, not if you're using MOLAP storage for your measure group. The best throughput I've ever seen for cube processing with MOLAP storage is around 200000 rows per second, and that was a maximum rather than something that could be consistently achieved throughout the whole process; you should be happy with 60-70000 rows per second if you've got a top-end server and a properly-tuned relational datasource.

I would experiment with using ROLAP or HOLAP storage, or with pre-aggregating your fact table in some way to reduce the overall number of rows.

Sorry,

Chris

|||

We have a monster server: 8 - 3.2GHZ processors, a disk frame that reads/writes 400 MB/sec, 32 GB of ram, etc and we couldn't even process 18 Billion rows that fast. The slowdown is reading rows from the DB, and most likely not the server (unless you have slow disks).

sql

Monday, February 27, 2012

Help!

Our sql server started to this...
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.
We added new, quite huge sql-based site to our iis farm and after that we
get some times these error messages. Pages wich report these erros are fully
working. Why do get these errors? In last day we have recieved about ten of
these erors. Eroros are coming from all kins of asp. and .net pages at the
same time. After error occusr everything works fine again.
Wha can we do to prevent this happening? What causes these type of error
messages?
Server and connection strings are configured well, this is not error of miss
spelled program or connectionstrings. Theese erros are caused somewhere
else.
t
Hi
DSN, Did you configure an alias in Client Network Utility?
"TLehtinen" <timo@.raa.to> wrote in message
news:44155ecc$0$7489$39db0f71@.news.song.fi...
> Our sql server started to this...
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied.
> We added new, quite huge sql-based site to our iis farm and after that we
> get some times these error messages. Pages wich report these erros are
> fully working. Why do get these errors? In last day we have recieved about
> ten of these erors. Eroros are coming from all kins of asp. and .net pages
> at the same time. After error occusr everything works fine again.
> Wha can we do to prevent this happening? What causes these type of error
> messages?
>
> Server and connection strings are configured well, this is not error of
> miss spelled program or connectionstrings. Theese erros are caused
> somewhere else.
>
> t
>
|||Hello!
alias?
Everything has worked fine before we added this quite huge new web page
thing... Now pages applications that have worked before are reporting theese
error. not all the time only about ten error / day (and we do have now quite
a bit of transactions).
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi
> DSN, Did you configure an alias in Client Network Utility?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>
|||The error is pretty clear. It could not find the server. Did you connect to
SQL Server on this (new one) web page?
"TLehtinen" <timo@.raa.to> wrote in message
news:441564a0$0$7479$39db0f71@.news.song.fi...
> Hello!
> alias?
> Everything has worked fine before we added this quite huge new web page
> thing... Now pages applications that have worked before are reporting
> theese error. not all the time only about ten error / day (and we do have
> now quite a bit of transactions).
>
> T
>
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestiss:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>
|||aargh!!
Yes I know it seems like pretty clear to you and to me. But theese erros are
not systmaticly hapepening. These erros are occuring becouse of something
else. But what is that something else?
Normally every page are workin fine then I see from our eroor log that in
same minute (in same ten seconds) many applications are reporting this
error. And after that many hours without errors...
Does SQL server report theese erros if there is too much load on that time?
Or what might cause theese erros...
t
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl. ..
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>
|||Could my prblem be related to this?
http://support.microsoft.com/default...b;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl. ..
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>

Help!

Our sql server started to this...
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.
We added new, quite huge sql-based site to our iis farm and after that we
get some times these error messages. Pages wich report these erros are fully
working. Why do get these errors? In last day we have recieved about ten of
these erors. Eroros are coming from all kins of asp. and .net pages at the
same time. After error occusr everything works fine again.
Wha can we do to prevent this happening? What causes these type of error
messages?
Server and connection strings are configured well, this is not error of miss
spelled program or connectionstrings. Theese erros are caused somewhere
else.
tHi
DSN, Did you configure an alias in Client Network Utility?
"TLehtinen" <timo@.raa.to> wrote in message
news:44155ecc$0$7489$39db0f71@.news.song.fi...
> Our sql server started to this...
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r access
> denied.
> We added new, quite huge sql-based site to our iis farm and after that we
> get some times these error messages. Pages wich report these erros are
> fully working. Why do get these errors? In last day we have recieved about
> ten of these erors. Eroros are coming from all kins of asp. and .net pages
> at the same time. After error occusr everything works fine again.
> Wha can we do to prevent this happening? What causes these type of error
> messages?
>
> Server and connection strings are configured well, this is not error of
> miss spelled program or connectionstrings. Theese erros are caused
> somewhere else.
>
> t
>|||Hello!
alias?
Everything has worked fine before we added this quite huge new web page
thing... Now pages applications that have worked before are reporting theese
error. not all the time only about ten error / day (and we do have now quite
a bit of transactions).
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi
> DSN, Did you configure an alias in Client Network Utility?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>|||The error is pretty clear. It could not find the server. Did you connect to
SQL Server on this (new one) web page?
"TLehtinen" <timo@.raa.to> wrote in message
news:441564a0$0$7479$39db0f71@.news.song.fi...
> Hello!
> alias?
> Everything has worked fine before we added this quite huge new web page
> thing... Now pages applications that have worked before are reporting
> theese error. not all the time only about ten error / day (and we do have
> now quite a bit of transactions).
>
> T
>
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestiss:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>|||aargh!!
Yes I know it seems like pretty clear to you and to me. But theese erros are
not systmaticly hapepening. These erros are occuring becouse of something
else. But what is that something else?
Normally every page are workin fine then I see from our eroor log that in
same minute (in same ten seconds) many applications are reporting this
error. And after that many hours without errors...
Does SQL server report theese erros if there is too much load on that time?
Or what might cause theese erros...
t
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>|||Could my prblem be related to this?
http://support.microsoft.com/defaul...kb;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>

Help!

Our sql server started to this...
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.
We added new, quite huge sql-based site to our iis farm and after that we
get some times these error messages. Pages wich report these erros are fully
working. Why do get these errors? In last day we have recieved about ten of
these erors. Eroros are coming from all kins of asp. and .net pages at the
same time. After error occusr everything works fine again.
Wha can we do to prevent this happening? What causes these type of error
messages?
Server and connection strings are configured well, this is not error of miss
spelled program or connectionstrings. Theese erros are caused somewhere
else.
tHi
DSN, Did you configure an alias in Client Network Utility?
"TLehtinen" <timo@.raa.to> wrote in message
news:44155ecc$0$7489$39db0f71@.news.song.fi...
> Our sql server started to this...
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied.
> We added new, quite huge sql-based site to our iis farm and after that we
> get some times these error messages. Pages wich report these erros are
> fully working. Why do get these errors? In last day we have recieved about
> ten of these erors. Eroros are coming from all kins of asp. and .net pages
> at the same time. After error occusr everything works fine again.
> Wha can we do to prevent this happening? What causes these type of error
> messages?
>
> Server and connection strings are configured well, this is not error of
> miss spelled program or connectionstrings. Theese erros are caused
> somewhere else.
>
> t
>|||Hello!
alias?
Everything has worked fine before we added this quite huge new web page
thing... Now pages applications that have worked before are reporting theese
error. not all the time only about ten error / day (and we do have now quite
a bit of transactions).
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi
> DSN, Did you configure an alias in Client Network Utility?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> Our sql server started to this...
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied.
>> We added new, quite huge sql-based site to our iis farm and after that we
>> get some times these error messages. Pages wich report these erros are
>> fully working. Why do get these errors? In last day we have recieved
>> about ten of these erors. Eroros are coming from all kins of asp. and
>> .net pages at the same time. After error occusr everything works fine
>> again.
>> Wha can we do to prevent this happening? What causes these type of error
>> messages?
>>
>> Server and connection strings are configured well, this is not error of
>> miss spelled program or connectionstrings. Theese erros are caused
>> somewhere else.
>>
>> t
>|||The error is pretty clear. It could not find the server. Did you connect to
SQL Server on this (new one) web page?
"TLehtinen" <timo@.raa.to> wrote in message
news:441564a0$0$7479$39db0f71@.news.song.fi...
> Hello!
> alias?
> Everything has worked fine before we added this quite huge new web page
> thing... Now pages applications that have worked before are reporting
> theese error. not all the time only about ten error / day (and we do have
> now quite a bit of transactions).
>
> T
>
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> DSN, Did you configure an alias in Client Network Utility?
>>
>> "TLehtinen" <timo@.raa.to> wrote in message
>> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> Our sql server started to this...
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied.
>> We added new, quite huge sql-based site to our iis farm and after that
>> we get some times these error messages. Pages wich report these erros
>> are fully working. Why do get these errors? In last day we have recieved
>> about ten of these erors. Eroros are coming from all kins of asp. and
>> .net pages at the same time. After error occusr everything works fine
>> again.
>> Wha can we do to prevent this happening? What causes these type of error
>> messages?
>>
>> Server and connection strings are configured well, this is not error of
>> miss spelled program or connectionstrings. Theese erros are caused
>> somewhere else.
>>
>> t
>>
>|||aargh!!
Yes I know it seems like pretty clear to you and to me. But theese erros are
not systmaticly hapepening. These erros are occuring becouse of something
else. But what is that something else?
Normally every page are workin fine then I see from our eroor log that in
same minute (in same ten seconds) many applications are reporting this
error. And after that many hours without errors...
Does SQL server report theese erros if there is too much load on that time?
Or what might cause theese erros...
t
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>> Hello!
>> alias?
>> Everything has worked fine before we added this quite huge new web page
>> thing... Now pages applications that have worked before are reporting
>> theese error. not all the time only about ten error / day (and we do have
>> now quite a bit of transactions).
>>
>> T
>>
>> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> DSN, Did you configure an alias in Client Network Utility?
>>
>> "TLehtinen" <timo@.raa.to> wrote in message
>> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> Our sql server started to this...
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied.
>> We added new, quite huge sql-based site to our iis farm and after that
>> we get some times these error messages. Pages wich report these erros
>> are fully working. Why do get these errors? In last day we have
>> recieved about ten of these erors. Eroros are coming from all kins of
>> asp. and .net pages at the same time. After error occusr everything
>> works fine again.
>> Wha can we do to prevent this happening? What causes these type of
>> error messages?
>>
>> Server and connection strings are configured well, this is not error of
>> miss spelled program or connectionstrings. Theese erros are caused
>> somewhere else.
>>
>> t
>>
>>
>|||Could my prblem be related to this?
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>> Hello!
>> alias?
>> Everything has worked fine before we added this quite huge new web page
>> thing... Now pages applications that have worked before are reporting
>> theese error. not all the time only about ten error / day (and we do have
>> now quite a bit of transactions).
>>
>> T
>>
>> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> DSN, Did you configure an alias in Client Network Utility?
>>
>> "TLehtinen" <timo@.raa.to> wrote in message
>> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> Our sql server started to this...
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied.
>> We added new, quite huge sql-based site to our iis farm and after that
>> we get some times these error messages. Pages wich report these erros
>> are fully working. Why do get these errors? In last day we have
>> recieved about ten of these erors. Eroros are coming from all kins of
>> asp. and .net pages at the same time. After error occusr everything
>> works fine again.
>> Wha can we do to prevent this happening? What causes these type of
>> error messages?
>>
>> Server and connection strings are configured well, this is not error of
>> miss spelled program or connectionstrings. Theese erros are caused
>> somewhere else.
>>
>> t
>>
>>
>