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.
>
Showing posts with label gigs. Show all posts
Showing posts with label gigs. Show all posts
Wednesday, March 28, 2012
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
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
> >> >
> >>
> >>
> >
> >
>
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! production memory issues??
We just started having some issues with our productin
server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
of memory and after checking the process, we are currently
only running at 1.9 gig.
The applications connecting to the database seem to be ok,
but we are unable to run any jobs via sql agent. We've
tried stopping/starting the agent and flushing the cache..
Were now going to try and reboot the server.
Here's the error messages were getting, any help would be
greatly appreciated. At this point, we can't even monitor
with Profiler because it says it dosen't have enough
memory to run. I just want to figure out the cause and
prevent it from happening again..
Please help
WARNING: Failed to reserve contiguous memory of Size= 131072.
Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
Available=111911
Global Memory Objects: Resource=1981 Locks=168
SQLCache=77 Replication=2
LockBytes=2 ServerGlobal=45
Xact=200
Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
OS Committed=1617
OS In Use=1611
Query Plan=5575 Optimizer=0
General=2391
Utilities=24 Connection=54120
Procedure Cache: TotalProcs=925 TotalPages=5550
InUsePages=3582
Buffer Counts: Commited=208688 Target=208688 Hashed=143148
InternalReservation=645
ExternalReservation=0 Min Free=508
Buffer Distribution: Stolen=55984 Free=4006
Procedures=5550
Inram=0 Dirty=5718 Kept=0
I/O=1, Latched=133, Other=137296Seems like you've configured SQL Server to use a *lot* of memory, more than
available. Can you post the output from:
sp_configure
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"dba4theday@.yahoo.com" <anonymous@.discussions.microsoft.com> wrote in
message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> We just started having some issues with our productin
> server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
> of memory and after checking the process, we are currently
> only running at 1.9 gig.
> The applications connecting to the database seem to be ok,
> but we are unable to run any jobs via sql agent. We've
> tried stopping/starting the agent and flushing the cache..
> Were now going to try and reboot the server.
> Here's the error messages were getting, any help would be
> greatly appreciated. At this point, we can't even monitor
> with Profiler because it says it dosen't have enough
> memory to run. I just want to figure out the cause and
> prevent it from happening again..
> Please help
> WARNING: Failed to reserve contiguous memory of Size=> 131072.
> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> Available=111911
> Global Memory Objects: Resource=1981 Locks=168
> SQLCache=77 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=200
> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> OS Committed=1617
> OS In Use=1611
> Query Plan=5575 Optimizer=0
> General=2391
> Utilities=24 Connection=54120
> Procedure Cache: TotalProcs=925 TotalPages=5550
> InUsePages=3582
> Buffer Counts: Commited=208688 Target=208688 Hashed=143148
> InternalReservation=645
> ExternalReservation=0 Min Free=508
> Buffer Distribution: Stolen=55984 Free=4006
> Procedures=5550
> Inram=0 Dirty=5718 Kept=0
> I/O=1, Latched=133, Other=137296|||name minimum
maximum config_value run_value
-- -- --
-- --
affinity mask -2147483648
2147483647 0 0
allow updates 0
1 0 0
awe enabled 0
1 0 0
c2 audit mode 0
1 0 0
cost threshold for parallelism 0
32767 5 5
Cross DB Ownership Chaining 0
1 1 1
cursor threshold -1
2147483647 -1 -1
default full-text language 0
2147483647 1033 1033
default language 0
9999 0 0
fill factor (%) 0
100 0 0
index create memory (KB) 704
2147483647 0 0
lightweight pooling 0
1 0 0
locks 5000
2147483647 0 0
max degree of parallelism 0
32 0 0
max server memory (MB) 4
2147483647 2147483647 2147483647
max text repl size (B) 0
2147483647 65536 65536
max worker threads 32
32767 255 255
media retention 0
365 0 0
min memory per query (KB) 512
2147483647 1024 1024
min server memory (MB) 0
2147483647 0 0
nested triggers 0
1 1 1
network packet size (B) 512
65536 4096 4096
open objects 0
2147483647 0 0
priority boost 0
1 0 0
query governor cost limit 0
2147483647 0 0
query wait (s) -1
2147483647 -1 -1
recovery interval (min) 0
32767 0 0
remote access 0
1 1 1
remote login timeout (s) 0
2147483647 20 20
remote proc trans 0
1 0 0
remote query timeout (s) 0
2147483647 600 600
scan for startup procs 0
1 1 1
set working set size 0
1 0 0
show advanced options 0
1 1 1
two digit year cutoff 1753
9999 2049 2049
user connections 0
32767 0 0
user options 0
32767 0 0
>--Original Message--
>Seems like you've configured SQL Server to use a *lot* of
memory, more than
>available. Can you post the output from:
>sp_configure
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"dba4theday@.yahoo.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> We just started having some issues with our productin
>> server. We are runnning SQL2k sp3 on Win2k. We have 3
gigs
>> of memory and after checking the process, we are
currently
>> only running at 1.9 gig.
>> The applications connecting to the database seem to be
ok,
>> but we are unable to run any jobs via sql agent. We've
>> tried stopping/starting the agent and flushing the
cache..
>> Were now going to try and reboot the server.
>> Here's the error messages were getting, any help would
be
>> greatly appreciated. At this point, we can't even
monitor
>> with Profiler because it says it dosen't have enough
>> memory to run. I just want to figure out the cause and
>> prevent it from happening again..
>> Please help
>> WARNING: Failed to reserve contiguous memory of Size=>> 131072.
>> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
>> Available=111911
>> Global Memory Objects: Resource=1981 Locks=168
>> SQLCache=77 Replication=2
>> LockBytes=2 ServerGlobal=45
>> Xact=200
>> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
>> OS Committed=1617
>> OS In Use=1611
>> Query Plan=5575 Optimizer=0
>> General=2391
>> Utilities=24 Connection=54120
>> Procedure Cache: TotalProcs=925 TotalPages=5550
>> InUsePages=3582
>> Buffer Counts: Commited=208688 Target=208688
Hashed=143148
>> InternalReservation=645
>> ExternalReservation=0 Min Free=508
>> Buffer Distribution: Stolen=55984 Free=4006
>> Procedures=5550
>> Inram=0 Dirty=5718 Kept=0
>> I/O=1, Latched=133, Other=137296
>
>.
>|||Nothing strange there. Are you sure there isn't anything else that is using
all the memory? Anything in the agent log?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> name minimum
> maximum config_value run_value
> -- -- --
> -- --
> affinity mask -2147483648
> 2147483647 0 0
> allow updates 0
> 1 0 0
> awe enabled 0
> 1 0 0
> c2 audit mode 0
> 1 0 0
> cost threshold for parallelism 0
> 32767 5 5
> Cross DB Ownership Chaining 0
> 1 1 1
> cursor threshold -1
> 2147483647 -1 -1
> default full-text language 0
> 2147483647 1033 1033
> default language 0
> 9999 0 0
> fill factor (%) 0
> 100 0 0
> index create memory (KB) 704
> 2147483647 0 0
> lightweight pooling 0
> 1 0 0
> locks 5000
> 2147483647 0 0
> max degree of parallelism 0
> 32 0 0
> max server memory (MB) 4
> 2147483647 2147483647 2147483647
> max text repl size (B) 0
> 2147483647 65536 65536
> max worker threads 32
> 32767 255 255
> media retention 0
> 365 0 0
> min memory per query (KB) 512
> 2147483647 1024 1024
> min server memory (MB) 0
> 2147483647 0 0
> nested triggers 0
> 1 1 1
> network packet size (B) 512
> 65536 4096 4096
> open objects 0
> 2147483647 0 0
> priority boost 0
> 1 0 0
> query governor cost limit 0
> 2147483647 0 0
> query wait (s) -1
> 2147483647 -1 -1
> recovery interval (min) 0
> 32767 0 0
> remote access 0
> 1 1 1
> remote login timeout (s) 0
> 2147483647 20 20
> remote proc trans 0
> 1 0 0
> remote query timeout (s) 0
> 2147483647 600 600
> scan for startup procs 0
> 1 1 1
> set working set size 0
> 1 0 0
> show advanced options 0
> 1 1 1
> two digit year cutoff 1753
> 9999 2049 2049
> user connections 0
> 32767 0 0
> user options 0
> 32767 0 0
>
> >--Original Message--
> >Seems like you've configured SQL Server to use a *lot* of
> memory, more than
> >available. Can you post the output from:
> >sp_configure
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"dba4theday@.yahoo.com"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> We just started having some issues with our productin
> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> gigs
> >> of memory and after checking the process, we are
> currently
> >> only running at 1.9 gig.
> >>
> >> The applications connecting to the database seem to be
> ok,
> >> but we are unable to run any jobs via sql agent. We've
> >> tried stopping/starting the agent and flushing the
> cache..
> >> Were now going to try and reboot the server.
> >> Here's the error messages were getting, any help would
> be
> >> greatly appreciated. At this point, we can't even
> monitor
> >> with Profiler because it says it dosen't have enough
> >> memory to run. I just want to figure out the cause and
> >> prevent it from happening again..
> >>
> >> Please help
> >>
> >> WARNING: Failed to reserve contiguous memory of Size=> >> 131072.
> >> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> >> Available=111911
> >> Global Memory Objects: Resource=1981 Locks=168
> >> SQLCache=77 Replication=2
> >> LockBytes=2 ServerGlobal=45
> >> Xact=200
> >> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> >> OS Committed=1617
> >> OS In Use=1611
> >> Query Plan=5575 Optimizer=0
> >> General=2391
> >> Utilities=24 Connection=54120
> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> InUsePages=3582
> >> Buffer Counts: Commited=208688 Target=208688
> Hashed=143148
> >> InternalReservation=645
> >> ExternalReservation=0 Min Free=508
> >> Buffer Distribution: Stolen=55984 Free=4006
> >> Procedures=5550
> >> Inram=0 Dirty=5718 Kept=0
> >> I/O=1, Latched=133, Other=137296
> >
> >
> >.
> >|||Well after further investigation we found that there were
100's of spid dated back to November coming in via SQL DMO.
Those connections were still left open and we believe they
have been the cause as the job calling the sp_oa proc's
started the same time we started seeing the error messages.
It states that there was an issue with those type of
proc's but it was fixed in SP1, were now on SP3...
The server has been running fine since we rebooted and if
we see this issue again, we are going to try and kill the
spid prior to the reboot to see if that fixes the problem.
Do you have any other idea's?
>--Original Message--
>Nothing strange there. Are you sure there isn't anything
else that is using
>all the memory? Anything in the agent log?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
>> name minimum
>> maximum config_value run_value
>> -- -- --
--
>> -- --
>> affinity mask -2147483648
>> 2147483647 0 0
>> allow updates 0
>> 1 0 0
>> awe enabled 0
>> 1 0 0
>> c2 audit mode 0
>> 1 0 0
>> cost threshold for parallelism 0
>> 32767 5 5
>> Cross DB Ownership Chaining 0
>> 1 1 1
>> cursor threshold -1
>> 2147483647 -1 -1
>> default full-text language 0
>> 2147483647 1033 1033
>> default language 0
>> 9999 0 0
>> fill factor (%) 0
>> 100 0 0
>> index create memory (KB) 704
>> 2147483647 0 0
>> lightweight pooling 0
>> 1 0 0
>> locks 5000
>> 2147483647 0 0
>> max degree of parallelism 0
>> 32 0 0
>> max server memory (MB) 4
>> 2147483647 2147483647 2147483647
>> max text repl size (B) 0
>> 2147483647 65536 65536
>> max worker threads 32
>> 32767 255 255
>> media retention 0
>> 365 0 0
>> min memory per query (KB) 512
>> 2147483647 1024 1024
>> min server memory (MB) 0
>> 2147483647 0 0
>> nested triggers 0
>> 1 1 1
>> network packet size (B) 512
>> 65536 4096 4096
>> open objects 0
>> 2147483647 0 0
>> priority boost 0
>> 1 0 0
>> query governor cost limit 0
>> 2147483647 0 0
>> query wait (s) -1
>> 2147483647 -1 -1
>> recovery interval (min) 0
>> 32767 0 0
>> remote access 0
>> 1 1 1
>> remote login timeout (s) 0
>> 2147483647 20 20
>> remote proc trans 0
>> 1 0 0
>> remote query timeout (s) 0
>> 2147483647 600 600
>> scan for startup procs 0
>> 1 1 1
>> set working set size 0
>> 1 0 0
>> show advanced options 0
>> 1 1 1
>> two digit year cutoff 1753
>> 9999 2049 2049
>> user connections 0
>> 32767 0 0
>> user options 0
>> 32767 0 0
>>
>> >--Original Message--
>> >Seems like you've configured SQL Server to use a *lot*
of
>> memory, more than
>> >available. Can you post the output from:
>> >sp_configure
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"dba4theday@.yahoo.com"
>> <anonymous@.discussions.microsoft.com> wrote in
>> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> >> We just started having some issues with our productin
>> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
>> gigs
>> >> of memory and after checking the process, we are
>> currently
>> >> only running at 1.9 gig.
>> >>
>> >> The applications connecting to the database seem to
be
>> ok,
>> >> but we are unable to run any jobs via sql agent.
We've
>> >> tried stopping/starting the agent and flushing the
>> cache..
>> >> Were now going to try and reboot the server.
>> >> Here's the error messages were getting, any help
would
>> be
>> >> greatly appreciated. At this point, we can't even
>> monitor
>> >> with Profiler because it says it dosen't have enough
>> >> memory to run. I just want to figure out the cause
and
>> >> prevent it from happening again..
>> >>
>> >> Please help
>> >>
>> >> WARNING: Failed to reserve contiguous memory of
Size=>> >> 131072.
>> >> Query Memory Manager: Grants=0 Waiting=0
Maximum=111911
>> >> Available=111911
>> >> Global Memory Objects: Resource=1981 Locks=168
>> >> SQLCache=77 Replication=2
>> >> LockBytes=2 ServerGlobal=45
>> >> Xact=200
>> >> Dynamic Memory Manager: Stolen=61534 OS
Reserved=1656
>> >> OS Committed=1617
>> >> OS In Use=1611
>> >> Query Plan=5575 Optimizer=0
>> >> General=2391
>> >> Utilities=24 Connection=54120
>> >> Procedure Cache: TotalProcs=925 TotalPages=5550
>> >> InUsePages=3582
>> >> Buffer Counts: Commited=208688 Target=208688
>> Hashed=143148
>> >> InternalReservation=645
>> >> ExternalReservation=0 Min Free=508
>> >> Buffer Distribution: Stolen=55984 Free=4006
>> >> Procedures=5550
>> >> Inram=0 Dirty=5718 Kept=0
>> >> I/O=1, Latched=133, Other=137296
>> >
>> >
>> >.
>> >
>
>.
>|||I think there's a good chance that you've found the problem, I can very well
imagine a memory leak related to sp_OA etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:0ba601c3bf6a$62e77380$a001280a@.phx.gbl...
> Well after further investigation we found that there were
> 100's of spid dated back to November coming in via SQL DMO.
> Those connections were still left open and we believe they
> have been the cause as the job calling the sp_oa proc's
> started the same time we started seeing the error messages.
> It states that there was an issue with those type of
> proc's but it was fixed in SP1, were now on SP3...
> The server has been running fine since we rebooted and if
> we see this issue again, we are going to try and kill the
> spid prior to the reboot to see if that fixes the problem.
> Do you have any other idea's?
> >--Original Message--
> >Nothing strange there. Are you sure there isn't anything
> else that is using
> >all the memory? Anything in the agent log?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> >> name minimum
> >> maximum config_value run_value
> >> -- -- --
> --
> >> -- --
> >> affinity mask -2147483648
> >> 2147483647 0 0
> >> allow updates 0
> >> 1 0 0
> >> awe enabled 0
> >> 1 0 0
> >> c2 audit mode 0
> >> 1 0 0
> >> cost threshold for parallelism 0
> >> 32767 5 5
> >> Cross DB Ownership Chaining 0
> >> 1 1 1
> >> cursor threshold -1
> >> 2147483647 -1 -1
> >> default full-text language 0
> >> 2147483647 1033 1033
> >> default language 0
> >> 9999 0 0
> >> fill factor (%) 0
> >> 100 0 0
> >> index create memory (KB) 704
> >> 2147483647 0 0
> >> lightweight pooling 0
> >> 1 0 0
> >> locks 5000
> >> 2147483647 0 0
> >> max degree of parallelism 0
> >> 32 0 0
> >> max server memory (MB) 4
> >> 2147483647 2147483647 2147483647
> >> max text repl size (B) 0
> >> 2147483647 65536 65536
> >> max worker threads 32
> >> 32767 255 255
> >> media retention 0
> >> 365 0 0
> >> min memory per query (KB) 512
> >> 2147483647 1024 1024
> >> min server memory (MB) 0
> >> 2147483647 0 0
> >> nested triggers 0
> >> 1 1 1
> >> network packet size (B) 512
> >> 65536 4096 4096
> >> open objects 0
> >> 2147483647 0 0
> >> priority boost 0
> >> 1 0 0
> >> query governor cost limit 0
> >> 2147483647 0 0
> >> query wait (s) -1
> >> 2147483647 -1 -1
> >> recovery interval (min) 0
> >> 32767 0 0
> >> remote access 0
> >> 1 1 1
> >> remote login timeout (s) 0
> >> 2147483647 20 20
> >> remote proc trans 0
> >> 1 0 0
> >> remote query timeout (s) 0
> >> 2147483647 600 600
> >> scan for startup procs 0
> >> 1 1 1
> >> set working set size 0
> >> 1 0 0
> >> show advanced options 0
> >> 1 1 1
> >> two digit year cutoff 1753
> >> 9999 2049 2049
> >> user connections 0
> >> 32767 0 0
> >> user options 0
> >> 32767 0 0
> >>
> >>
> >> >--Original Message--
> >> >Seems like you've configured SQL Server to use a *lot*
> of
> >> memory, more than
> >> >available. Can you post the output from:
> >> >sp_configure
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"dba4theday@.yahoo.com"
> >> <anonymous@.discussions.microsoft.com> wrote in
> >> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> >> We just started having some issues with our productin
> >> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> >> gigs
> >> >> of memory and after checking the process, we are
> >> currently
> >> >> only running at 1.9 gig.
> >> >>
> >> >> The applications connecting to the database seem to
> be
> >> ok,
> >> >> but we are unable to run any jobs via sql agent.
> We've
> >> >> tried stopping/starting the agent and flushing the
> >> cache..
> >> >> Were now going to try and reboot the server.
> >> >> Here's the error messages were getting, any help
> would
> >> be
> >> >> greatly appreciated. At this point, we can't even
> >> monitor
> >> >> with Profiler because it says it dosen't have enough
> >> >> memory to run. I just want to figure out the cause
> and
> >> >> prevent it from happening again..
> >> >>
> >> >> Please help
> >> >>
> >> >> WARNING: Failed to reserve contiguous memory of
> Size=> >> >> 131072.
> >> >> Query Memory Manager: Grants=0 Waiting=0
> Maximum=111911
> >> >> Available=111911
> >> >> Global Memory Objects: Resource=1981 Locks=168
> >> >> SQLCache=77 Replication=2
> >> >> LockBytes=2 ServerGlobal=45
> >> >> Xact=200
> >> >> Dynamic Memory Manager: Stolen=61534 OS
> Reserved=1656
> >> >> OS Committed=1617
> >> >> OS In Use=1611
> >> >> Query Plan=5575 Optimizer=0
> >> >> General=2391
> >> >> Utilities=24 Connection=54120
> >> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> >> InUsePages=3582
> >> >> Buffer Counts: Commited=208688 Target=208688
> >> Hashed=143148
> >> >> InternalReservation=645
> >> >> ExternalReservation=0 Min Free=508
> >> >> Buffer Distribution: Stolen=55984 Free=4006
> >> >> Procedures=5550
> >> >> Inram=0 Dirty=5718 Kept=0
> >> >> I/O=1, Latched=133, Other=137296
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
of memory and after checking the process, we are currently
only running at 1.9 gig.
The applications connecting to the database seem to be ok,
but we are unable to run any jobs via sql agent. We've
tried stopping/starting the agent and flushing the cache..
Were now going to try and reboot the server.
Here's the error messages were getting, any help would be
greatly appreciated. At this point, we can't even monitor
with Profiler because it says it dosen't have enough
memory to run. I just want to figure out the cause and
prevent it from happening again..
Please help
WARNING: Failed to reserve contiguous memory of Size= 131072.
Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
Available=111911
Global Memory Objects: Resource=1981 Locks=168
SQLCache=77 Replication=2
LockBytes=2 ServerGlobal=45
Xact=200
Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
OS Committed=1617
OS In Use=1611
Query Plan=5575 Optimizer=0
General=2391
Utilities=24 Connection=54120
Procedure Cache: TotalProcs=925 TotalPages=5550
InUsePages=3582
Buffer Counts: Commited=208688 Target=208688 Hashed=143148
InternalReservation=645
ExternalReservation=0 Min Free=508
Buffer Distribution: Stolen=55984 Free=4006
Procedures=5550
Inram=0 Dirty=5718 Kept=0
I/O=1, Latched=133, Other=137296Seems like you've configured SQL Server to use a *lot* of memory, more than
available. Can you post the output from:
sp_configure
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"dba4theday@.yahoo.com" <anonymous@.discussions.microsoft.com> wrote in
message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> We just started having some issues with our productin
> server. We are runnning SQL2k sp3 on Win2k. We have 3 gigs
> of memory and after checking the process, we are currently
> only running at 1.9 gig.
> The applications connecting to the database seem to be ok,
> but we are unable to run any jobs via sql agent. We've
> tried stopping/starting the agent and flushing the cache..
> Were now going to try and reboot the server.
> Here's the error messages were getting, any help would be
> greatly appreciated. At this point, we can't even monitor
> with Profiler because it says it dosen't have enough
> memory to run. I just want to figure out the cause and
> prevent it from happening again..
> Please help
> WARNING: Failed to reserve contiguous memory of Size=> 131072.
> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> Available=111911
> Global Memory Objects: Resource=1981 Locks=168
> SQLCache=77 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=200
> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> OS Committed=1617
> OS In Use=1611
> Query Plan=5575 Optimizer=0
> General=2391
> Utilities=24 Connection=54120
> Procedure Cache: TotalProcs=925 TotalPages=5550
> InUsePages=3582
> Buffer Counts: Commited=208688 Target=208688 Hashed=143148
> InternalReservation=645
> ExternalReservation=0 Min Free=508
> Buffer Distribution: Stolen=55984 Free=4006
> Procedures=5550
> Inram=0 Dirty=5718 Kept=0
> I/O=1, Latched=133, Other=137296|||name minimum
maximum config_value run_value
-- -- --
-- --
affinity mask -2147483648
2147483647 0 0
allow updates 0
1 0 0
awe enabled 0
1 0 0
c2 audit mode 0
1 0 0
cost threshold for parallelism 0
32767 5 5
Cross DB Ownership Chaining 0
1 1 1
cursor threshold -1
2147483647 -1 -1
default full-text language 0
2147483647 1033 1033
default language 0
9999 0 0
fill factor (%) 0
100 0 0
index create memory (KB) 704
2147483647 0 0
lightweight pooling 0
1 0 0
locks 5000
2147483647 0 0
max degree of parallelism 0
32 0 0
max server memory (MB) 4
2147483647 2147483647 2147483647
max text repl size (B) 0
2147483647 65536 65536
max worker threads 32
32767 255 255
media retention 0
365 0 0
min memory per query (KB) 512
2147483647 1024 1024
min server memory (MB) 0
2147483647 0 0
nested triggers 0
1 1 1
network packet size (B) 512
65536 4096 4096
open objects 0
2147483647 0 0
priority boost 0
1 0 0
query governor cost limit 0
2147483647 0 0
query wait (s) -1
2147483647 -1 -1
recovery interval (min) 0
32767 0 0
remote access 0
1 1 1
remote login timeout (s) 0
2147483647 20 20
remote proc trans 0
1 0 0
remote query timeout (s) 0
2147483647 600 600
scan for startup procs 0
1 1 1
set working set size 0
1 0 0
show advanced options 0
1 1 1
two digit year cutoff 1753
9999 2049 2049
user connections 0
32767 0 0
user options 0
32767 0 0
>--Original Message--
>Seems like you've configured SQL Server to use a *lot* of
memory, more than
>available. Can you post the output from:
>sp_configure
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"dba4theday@.yahoo.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> We just started having some issues with our productin
>> server. We are runnning SQL2k sp3 on Win2k. We have 3
gigs
>> of memory and after checking the process, we are
currently
>> only running at 1.9 gig.
>> The applications connecting to the database seem to be
ok,
>> but we are unable to run any jobs via sql agent. We've
>> tried stopping/starting the agent and flushing the
cache..
>> Were now going to try and reboot the server.
>> Here's the error messages were getting, any help would
be
>> greatly appreciated. At this point, we can't even
monitor
>> with Profiler because it says it dosen't have enough
>> memory to run. I just want to figure out the cause and
>> prevent it from happening again..
>> Please help
>> WARNING: Failed to reserve contiguous memory of Size=>> 131072.
>> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
>> Available=111911
>> Global Memory Objects: Resource=1981 Locks=168
>> SQLCache=77 Replication=2
>> LockBytes=2 ServerGlobal=45
>> Xact=200
>> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
>> OS Committed=1617
>> OS In Use=1611
>> Query Plan=5575 Optimizer=0
>> General=2391
>> Utilities=24 Connection=54120
>> Procedure Cache: TotalProcs=925 TotalPages=5550
>> InUsePages=3582
>> Buffer Counts: Commited=208688 Target=208688
Hashed=143148
>> InternalReservation=645
>> ExternalReservation=0 Min Free=508
>> Buffer Distribution: Stolen=55984 Free=4006
>> Procedures=5550
>> Inram=0 Dirty=5718 Kept=0
>> I/O=1, Latched=133, Other=137296
>
>.
>|||Nothing strange there. Are you sure there isn't anything else that is using
all the memory? Anything in the agent log?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> name minimum
> maximum config_value run_value
> -- -- --
> -- --
> affinity mask -2147483648
> 2147483647 0 0
> allow updates 0
> 1 0 0
> awe enabled 0
> 1 0 0
> c2 audit mode 0
> 1 0 0
> cost threshold for parallelism 0
> 32767 5 5
> Cross DB Ownership Chaining 0
> 1 1 1
> cursor threshold -1
> 2147483647 -1 -1
> default full-text language 0
> 2147483647 1033 1033
> default language 0
> 9999 0 0
> fill factor (%) 0
> 100 0 0
> index create memory (KB) 704
> 2147483647 0 0
> lightweight pooling 0
> 1 0 0
> locks 5000
> 2147483647 0 0
> max degree of parallelism 0
> 32 0 0
> max server memory (MB) 4
> 2147483647 2147483647 2147483647
> max text repl size (B) 0
> 2147483647 65536 65536
> max worker threads 32
> 32767 255 255
> media retention 0
> 365 0 0
> min memory per query (KB) 512
> 2147483647 1024 1024
> min server memory (MB) 0
> 2147483647 0 0
> nested triggers 0
> 1 1 1
> network packet size (B) 512
> 65536 4096 4096
> open objects 0
> 2147483647 0 0
> priority boost 0
> 1 0 0
> query governor cost limit 0
> 2147483647 0 0
> query wait (s) -1
> 2147483647 -1 -1
> recovery interval (min) 0
> 32767 0 0
> remote access 0
> 1 1 1
> remote login timeout (s) 0
> 2147483647 20 20
> remote proc trans 0
> 1 0 0
> remote query timeout (s) 0
> 2147483647 600 600
> scan for startup procs 0
> 1 1 1
> set working set size 0
> 1 0 0
> show advanced options 0
> 1 1 1
> two digit year cutoff 1753
> 9999 2049 2049
> user connections 0
> 32767 0 0
> user options 0
> 32767 0 0
>
> >--Original Message--
> >Seems like you've configured SQL Server to use a *lot* of
> memory, more than
> >available. Can you post the output from:
> >sp_configure
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"dba4theday@.yahoo.com"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> We just started having some issues with our productin
> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> gigs
> >> of memory and after checking the process, we are
> currently
> >> only running at 1.9 gig.
> >>
> >> The applications connecting to the database seem to be
> ok,
> >> but we are unable to run any jobs via sql agent. We've
> >> tried stopping/starting the agent and flushing the
> cache..
> >> Were now going to try and reboot the server.
> >> Here's the error messages were getting, any help would
> be
> >> greatly appreciated. At this point, we can't even
> monitor
> >> with Profiler because it says it dosen't have enough
> >> memory to run. I just want to figure out the cause and
> >> prevent it from happening again..
> >>
> >> Please help
> >>
> >> WARNING: Failed to reserve contiguous memory of Size=> >> 131072.
> >> Query Memory Manager: Grants=0 Waiting=0 Maximum=111911
> >> Available=111911
> >> Global Memory Objects: Resource=1981 Locks=168
> >> SQLCache=77 Replication=2
> >> LockBytes=2 ServerGlobal=45
> >> Xact=200
> >> Dynamic Memory Manager: Stolen=61534 OS Reserved=1656
> >> OS Committed=1617
> >> OS In Use=1611
> >> Query Plan=5575 Optimizer=0
> >> General=2391
> >> Utilities=24 Connection=54120
> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> InUsePages=3582
> >> Buffer Counts: Commited=208688 Target=208688
> Hashed=143148
> >> InternalReservation=645
> >> ExternalReservation=0 Min Free=508
> >> Buffer Distribution: Stolen=55984 Free=4006
> >> Procedures=5550
> >> Inram=0 Dirty=5718 Kept=0
> >> I/O=1, Latched=133, Other=137296
> >
> >
> >.
> >|||Well after further investigation we found that there were
100's of spid dated back to November coming in via SQL DMO.
Those connections were still left open and we believe they
have been the cause as the job calling the sp_oa proc's
started the same time we started seeing the error messages.
It states that there was an issue with those type of
proc's but it was fixed in SP1, were now on SP3...
The server has been running fine since we rebooted and if
we see this issue again, we are going to try and kill the
spid prior to the reboot to see if that fixes the problem.
Do you have any other idea's?
>--Original Message--
>Nothing strange there. Are you sure there isn't anything
else that is using
>all the memory? Anything in the agent log?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
>> name minimum
>> maximum config_value run_value
>> -- -- --
--
>> -- --
>> affinity mask -2147483648
>> 2147483647 0 0
>> allow updates 0
>> 1 0 0
>> awe enabled 0
>> 1 0 0
>> c2 audit mode 0
>> 1 0 0
>> cost threshold for parallelism 0
>> 32767 5 5
>> Cross DB Ownership Chaining 0
>> 1 1 1
>> cursor threshold -1
>> 2147483647 -1 -1
>> default full-text language 0
>> 2147483647 1033 1033
>> default language 0
>> 9999 0 0
>> fill factor (%) 0
>> 100 0 0
>> index create memory (KB) 704
>> 2147483647 0 0
>> lightweight pooling 0
>> 1 0 0
>> locks 5000
>> 2147483647 0 0
>> max degree of parallelism 0
>> 32 0 0
>> max server memory (MB) 4
>> 2147483647 2147483647 2147483647
>> max text repl size (B) 0
>> 2147483647 65536 65536
>> max worker threads 32
>> 32767 255 255
>> media retention 0
>> 365 0 0
>> min memory per query (KB) 512
>> 2147483647 1024 1024
>> min server memory (MB) 0
>> 2147483647 0 0
>> nested triggers 0
>> 1 1 1
>> network packet size (B) 512
>> 65536 4096 4096
>> open objects 0
>> 2147483647 0 0
>> priority boost 0
>> 1 0 0
>> query governor cost limit 0
>> 2147483647 0 0
>> query wait (s) -1
>> 2147483647 -1 -1
>> recovery interval (min) 0
>> 32767 0 0
>> remote access 0
>> 1 1 1
>> remote login timeout (s) 0
>> 2147483647 20 20
>> remote proc trans 0
>> 1 0 0
>> remote query timeout (s) 0
>> 2147483647 600 600
>> scan for startup procs 0
>> 1 1 1
>> set working set size 0
>> 1 0 0
>> show advanced options 0
>> 1 1 1
>> two digit year cutoff 1753
>> 9999 2049 2049
>> user connections 0
>> 32767 0 0
>> user options 0
>> 32767 0 0
>>
>> >--Original Message--
>> >Seems like you've configured SQL Server to use a *lot*
of
>> memory, more than
>> >available. Can you post the output from:
>> >sp_configure
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"dba4theday@.yahoo.com"
>> <anonymous@.discussions.microsoft.com> wrote in
>> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
>> >> We just started having some issues with our productin
>> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
>> gigs
>> >> of memory and after checking the process, we are
>> currently
>> >> only running at 1.9 gig.
>> >>
>> >> The applications connecting to the database seem to
be
>> ok,
>> >> but we are unable to run any jobs via sql agent.
We've
>> >> tried stopping/starting the agent and flushing the
>> cache..
>> >> Were now going to try and reboot the server.
>> >> Here's the error messages were getting, any help
would
>> be
>> >> greatly appreciated. At this point, we can't even
>> monitor
>> >> with Profiler because it says it dosen't have enough
>> >> memory to run. I just want to figure out the cause
and
>> >> prevent it from happening again..
>> >>
>> >> Please help
>> >>
>> >> WARNING: Failed to reserve contiguous memory of
Size=>> >> 131072.
>> >> Query Memory Manager: Grants=0 Waiting=0
Maximum=111911
>> >> Available=111911
>> >> Global Memory Objects: Resource=1981 Locks=168
>> >> SQLCache=77 Replication=2
>> >> LockBytes=2 ServerGlobal=45
>> >> Xact=200
>> >> Dynamic Memory Manager: Stolen=61534 OS
Reserved=1656
>> >> OS Committed=1617
>> >> OS In Use=1611
>> >> Query Plan=5575 Optimizer=0
>> >> General=2391
>> >> Utilities=24 Connection=54120
>> >> Procedure Cache: TotalProcs=925 TotalPages=5550
>> >> InUsePages=3582
>> >> Buffer Counts: Commited=208688 Target=208688
>> Hashed=143148
>> >> InternalReservation=645
>> >> ExternalReservation=0 Min Free=508
>> >> Buffer Distribution: Stolen=55984 Free=4006
>> >> Procedures=5550
>> >> Inram=0 Dirty=5718 Kept=0
>> >> I/O=1, Latched=133, Other=137296
>> >
>> >
>> >.
>> >
>
>.
>|||I think there's a good chance that you've found the problem, I can very well
imagine a memory leak related to sp_OA etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:0ba601c3bf6a$62e77380$a001280a@.phx.gbl...
> Well after further investigation we found that there were
> 100's of spid dated back to November coming in via SQL DMO.
> Those connections were still left open and we believe they
> have been the cause as the job calling the sp_oa proc's
> started the same time we started seeing the error messages.
> It states that there was an issue with those type of
> proc's but it was fixed in SP1, were now on SP3...
> The server has been running fine since we rebooted and if
> we see this issue again, we are going to try and kill the
> spid prior to the reboot to see if that fixes the problem.
> Do you have any other idea's?
> >--Original Message--
> >Nothing strange there. Are you sure there isn't anything
> else that is using
> >all the memory? Anything in the agent log?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:032201c3bf5a$504b7470$a301280a@.phx.gbl...
> >> name minimum
> >> maximum config_value run_value
> >> -- -- --
> --
> >> -- --
> >> affinity mask -2147483648
> >> 2147483647 0 0
> >> allow updates 0
> >> 1 0 0
> >> awe enabled 0
> >> 1 0 0
> >> c2 audit mode 0
> >> 1 0 0
> >> cost threshold for parallelism 0
> >> 32767 5 5
> >> Cross DB Ownership Chaining 0
> >> 1 1 1
> >> cursor threshold -1
> >> 2147483647 -1 -1
> >> default full-text language 0
> >> 2147483647 1033 1033
> >> default language 0
> >> 9999 0 0
> >> fill factor (%) 0
> >> 100 0 0
> >> index create memory (KB) 704
> >> 2147483647 0 0
> >> lightweight pooling 0
> >> 1 0 0
> >> locks 5000
> >> 2147483647 0 0
> >> max degree of parallelism 0
> >> 32 0 0
> >> max server memory (MB) 4
> >> 2147483647 2147483647 2147483647
> >> max text repl size (B) 0
> >> 2147483647 65536 65536
> >> max worker threads 32
> >> 32767 255 255
> >> media retention 0
> >> 365 0 0
> >> min memory per query (KB) 512
> >> 2147483647 1024 1024
> >> min server memory (MB) 0
> >> 2147483647 0 0
> >> nested triggers 0
> >> 1 1 1
> >> network packet size (B) 512
> >> 65536 4096 4096
> >> open objects 0
> >> 2147483647 0 0
> >> priority boost 0
> >> 1 0 0
> >> query governor cost limit 0
> >> 2147483647 0 0
> >> query wait (s) -1
> >> 2147483647 -1 -1
> >> recovery interval (min) 0
> >> 32767 0 0
> >> remote access 0
> >> 1 1 1
> >> remote login timeout (s) 0
> >> 2147483647 20 20
> >> remote proc trans 0
> >> 1 0 0
> >> remote query timeout (s) 0
> >> 2147483647 600 600
> >> scan for startup procs 0
> >> 1 1 1
> >> set working set size 0
> >> 1 0 0
> >> show advanced options 0
> >> 1 1 1
> >> two digit year cutoff 1753
> >> 9999 2049 2049
> >> user connections 0
> >> 32767 0 0
> >> user options 0
> >> 32767 0 0
> >>
> >>
> >> >--Original Message--
> >> >Seems like you've configured SQL Server to use a *lot*
> of
> >> memory, more than
> >> >available. Can you post the output from:
> >> >sp_configure
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"dba4theday@.yahoo.com"
> >> <anonymous@.discussions.microsoft.com> wrote in
> >> >message news:020401c3bf4b$9c221e80$a301280a@.phx.gbl...
> >> >> We just started having some issues with our productin
> >> >> server. We are runnning SQL2k sp3 on Win2k. We have 3
> >> gigs
> >> >> of memory and after checking the process, we are
> >> currently
> >> >> only running at 1.9 gig.
> >> >>
> >> >> The applications connecting to the database seem to
> be
> >> ok,
> >> >> but we are unable to run any jobs via sql agent.
> We've
> >> >> tried stopping/starting the agent and flushing the
> >> cache..
> >> >> Were now going to try and reboot the server.
> >> >> Here's the error messages were getting, any help
> would
> >> be
> >> >> greatly appreciated. At this point, we can't even
> >> monitor
> >> >> with Profiler because it says it dosen't have enough
> >> >> memory to run. I just want to figure out the cause
> and
> >> >> prevent it from happening again..
> >> >>
> >> >> Please help
> >> >>
> >> >> WARNING: Failed to reserve contiguous memory of
> Size=> >> >> 131072.
> >> >> Query Memory Manager: Grants=0 Waiting=0
> Maximum=111911
> >> >> Available=111911
> >> >> Global Memory Objects: Resource=1981 Locks=168
> >> >> SQLCache=77 Replication=2
> >> >> LockBytes=2 ServerGlobal=45
> >> >> Xact=200
> >> >> Dynamic Memory Manager: Stolen=61534 OS
> Reserved=1656
> >> >> OS Committed=1617
> >> >> OS In Use=1611
> >> >> Query Plan=5575 Optimizer=0
> >> >> General=2391
> >> >> Utilities=24 Connection=54120
> >> >> Procedure Cache: TotalProcs=925 TotalPages=5550
> >> >> InUsePages=3582
> >> >> Buffer Counts: Commited=208688 Target=208688
> >> Hashed=143148
> >> >> InternalReservation=645
> >> >> ExternalReservation=0 Min Free=508
> >> >> Buffer Distribution: Stolen=55984 Free=4006
> >> >> Procedures=5550
> >> >> Inram=0 Dirty=5718 Kept=0
> >> >> I/O=1, Latched=133, Other=137296
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Subscribe to:
Posts (Atom)