Changing the OPEN OBJECTS variable

SQL 2000 error in event log... 2 events as follows:
1. OPEN OBJECTS parameter may be set to low
2. Run sp_configure to change parameter.
How can I see what it is set to now? How can I change this value?
TIAThis is documented in Books on line... - search for 'Setting Configuration
Options'
It is fairly unusual ( in my experience) to need to change this in sql
2000...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||(1) exec sp_configure
(2) exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
-- Keith
"_M_" <here@.gone.com> wrote in message =news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> > 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> > How can I see what it is set to now? How can I change this value?
> > TIA
> >|||Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||It prints it event if you run it, as long as you "know" you ran it you can
ignore the extra messages.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:uH1No6YbDHA.2672@.tk2msftngp13.phx.gbl...
Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>

Changing the Logical/Physical name of a DB, or log

The system I'm working with has many databases, almost all of which have a
logical name of "DBname_Data" and "DBname_Log" which have physical names if
"DBname_Data.MDF" and "DBname_Log.LDF".
All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
What do I have to do to change this, so it is consistant with all the others?
Thanks,
Jay
Just thought to add that I know how to do it with a backup and a restore with
move. Hoping for simpler.
"JayKon" wrote:

> The system I'm working with has many databases, almost all of which have a
> logical name of "DBname_Data" and "DBname_Log" which have physical names if
> "DBname_Data.MDF" and "DBname_Log.LDF".
> All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> What do I have to do to change this, so it is consistant with all the others?
> Thanks,
> Jay
|||try alter database with the modify file portion:
e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
new_path/os_file_name ' )
BOL does a good job of explaining this so just look under 'alter database'
Robert Towne
"JayKon" wrote:
[vbcol=seagreen]
> Just thought to add that I know how to do it with a backup and a restore with
> move. Hoping for simpler.
> "JayKon" wrote:
|||Yes, it does.
Thank you.
"sql411@.nospam.com" wrote:
[vbcol=seagreen]
> try alter database with the modify file portion:
> e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
> new_path/os_file_name ' )
> BOL does a good job of explaining this so just look under 'alter database'
> Robert Towne
>
>
> "JayKon" wrote:

Changing the Logical/Physical name of a DB, or log

The system I'm working with has many databases, almost all of which have a
logical name of "DBname_Data" and "DBname_Log" which have physical names if
"DBname_Data.MDF" and "DBname_Log.LDF".
All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
What do I have to do to change this, so it is consistant with all the others
?
Thanks,
JayJust thought to add that I know how to do it with a backup and a restore wit
h
move. Hoping for simpler.
"JayKon" wrote:

> The system I'm working with has many databases, almost all of which have a
> logical name of "DBname_Data" and "DBname_Log" which have physical names i
f
> "DBname_Data.MDF" and "DBname_Log.LDF".
> All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> What do I have to do to change this, so it is consistant with all the othe
rs?
> Thanks,
> Jay|||try alter database with the modify file portion:
e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
new_path/os_file_name ' )
BOL does a good job of explaining this so just look under 'alter database'
Robert Towne
"JayKon" wrote:
[vbcol=seagreen]
> Just thought to add that I know how to do it with a backup and a restore w
ith
> move. Hoping for simpler.
> "JayKon" wrote:
>|||Yes, it does.
Thank you.
"sql411@.nospam.com" wrote:
[vbcol=seagreen]
> try alter database with the modify file portion:
> e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
> new_path/os_file_name ' )
> BOL does a good job of explaining this so just look under 'alter database'
> Robert Towne
>
>
> "JayKon" wrote:
>sql

Changing the Logical/Physical name of a DB, or log

The system I'm working with has many databases, almost all of which have a
logical name of "DBname_Data" and "DBname_Log" which have physical names if
"DBname_Data.MDF" and "DBname_Log.LDF".
All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
What do I have to do to change this, so it is consistant with all the others?
Thanks,
JayJust thought to add that I know how to do it with a backup and a restore with
move. Hoping for simpler.
"JayKon" wrote:
> The system I'm working with has many databases, almost all of which have a
> logical name of "DBname_Data" and "DBname_Log" which have physical names if
> "DBname_Data.MDF" and "DBname_Log.LDF".
> All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> What do I have to do to change this, so it is consistant with all the others?
> Thanks,
> Jay|||try alter database with the modify file portion:
e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
new_path/os_file_name ' )
BOL does a good job of explaining this so just look under 'alter database'
Robert Towne
"JayKon" wrote:
> Just thought to add that I know how to do it with a backup and a restore with
> move. Hoping for simpler.
> "JayKon" wrote:
> > The system I'm working with has many databases, almost all of which have a
> > logical name of "DBname_Data" and "DBname_Log" which have physical names if
> > "DBname_Data.MDF" and "DBname_Log.LDF".
> >
> > All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> >
> > What do I have to do to change this, so it is consistant with all the others?
> >
> > Thanks,
> > Jay|||Yes, it does.
Thank you.
"sql411@.nospam.com" wrote:
> try alter database with the modify file portion:
> e.g, --> MODIFY FILE ( NAME = logical_file_name, FILENAME = '
> new_path/os_file_name ' )
> BOL does a good job of explaining this so just look under 'alter database'
> Robert Towne
>
>
> "JayKon" wrote:
> > Just thought to add that I know how to do it with a backup and a restore with
> > move. Hoping for simpler.
> >
> > "JayKon" wrote:
> >
> > > The system I'm working with has many databases, almost all of which have a
> > > logical name of "DBname_Data" and "DBname_Log" which have physical names if
> > > "DBname_Data.MDF" and "DBname_Log.LDF".
> > >
> > > All except one that is. It is named "DBname_dat" & "DBname_dat.MDF".
> > >
> > > What do I have to do to change this, so it is consistant with all the others?
> > >
> > > Thanks,
> > > Jay

Changing the default location of a new database

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change that to:-
D:\Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abelon 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

select the "database settings" tab and you can change the path were data and log files get created by default.

Under the covers, this setting is stored in the registry, here:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

in the DefaultData and DefaultLog values.|||Yes, that works! Thanks!

CHanging Recovery Options SQL 2000

Howdy,

I want to run a Re-Indexing job against a database that normally has Tran Log backups ( it uses FULL recovery mode ) on SQL 2000. But when this job runs, it causes HUGE tran logs and huge tran log backups ( which then use up 99% of our disk space).

The job needs to be run, so I thought of changing recovery mode to SIMPLE, then run the job , then changing the recovery mode back to FULL. .........Problem solved, you would think.....

But, if the database were to corrupt after the job ( i.e. hours later ) where do I stand with what was in the tran logs both before the revovery mode change and then after? i.e. when I change to SIMPLE does the contents of the tran log get written to the db
or what?

By the way, we normally run tran log backups until 8am-6pm then do the re-indexing job at 4am and re-start the tran log backups at 8am.

Thanks for any help,

SGSafest way would be to backup the DB, change the recov mode, run the reindex, change recov mode back and back the db up again.

Never read it anywhere but pretty sure if you change the recov mode it would checkpoint the tran log.

HTH

Changing path of data & log files on the fly

We have a SQL Server setup as a publisher to 15 subscribers. We need to change the path of the data & log files to a new drive (added a new harddisk). We plan to take a cold backup of the database and shift the data & log files to the new drive. Then we just attach the data & log files from the new path.

Will this disturb my existing replication Setup?
Is the the correct procedure for changing the path of the existing data & log files?
What is the appropriate method for shifting data & log file of a live database to a different location (directory/drive) ?

thanks in advancedisable replication;
sp_detach_db;
move files to new location;
sp_attach_db;
enable replication.|||Thanks for the suggestion. I'll try this out and confirm back. Thanks again anyways.

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.c..._parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used wa
s
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything el
se
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
Nancy
Yes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.co...parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.com/rd_sql_server_startup_parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:
> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change location
s
of your drives in a clustered environment?
Any ideas?Same as in a non-clustered environment.
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:

> Same as in a non-clustered environment.
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only all
ow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>
>|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change locations
of your drives in a clustered environment?
Any ideas?
Same as in a non-clustered environment.
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>
|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:

> Same as in a non-clustered environment.
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only allow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>
>
|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing location of log files in a Clustered Virtual Server

I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose the
location of the data, now that the virtual server is functional I want to
change the location of the log files to a different resource of the cluster
(different drive) but it is not letting me do so. Hoe do you change locations
of your drives in a clustered environment?
Any ideas?Same as in a non-clustered environment. :)
I would guess that the step you are missing is to make the SQL Service
dependent on the new clustered disk resource. Clustered SQL will only allow
you to write data files to disks that are in the same resource group AND
that SQL is dependent on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>|||Geoff
Thanks so much !!!! That did it !!!
"Geoff N. Hiten" wrote:
> Same as in a non-clustered environment. :)
> I would guess that the step you are missing is to make the SQL Service
> dependent on the new clustered disk resource. Clustered SQL will only allow
> you to write data files to disks that are in the same resource group AND
> that SQL is dependent on.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Carlos" <carlosarango@.SQLserver.com> wrote in message
> news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
> >I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> > SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> > the
> > location of the data, now that the virtual server is functional I want to
> > change the location of the log files to a different resource of the
> > cluster
> > (different drive) but it is not letting me do so. Hoe do you change
> > locations
> > of your drives in a clustered environment?
> >
> > Any ideas?
> >
>
>|||Hi
It is probably because the drive in question has not been added as a
resource to the sql server cluster in cluster manager.
John
"Carlos" <carlosarango@.SQLserver.com> wrote in message
news:626F2CB6-A2E1-4CFB-A49F-E6413133BF78@.microsoft.com...
>I set up a Clustered Virtual Server using Windows 2003 Enterprise SP! and
> SQL2000 Enterprise SP2. When setting SQL it gave me the option to choose
> the
> location of the data, now that the virtual server is functional I want to
> change the location of the log files to a different resource of the
> cluster
> (different drive) but it is not letting me do so. Hoe do you change
> locations
> of your drives in a clustered environment?
> Any ideas?
>

Changing directory where database files are stored

Hi

After install the mdf and log files are stored in the Program Files directory tree.

How do I change the place were they are stored to a path of my own choice, so that new DBs get created there too?

I've looked though the docs, but I can't find this.

Thanks

John

You cna change this using Management Studio Express in the Server Properties dialog.

Mike

Changing database size

Hello,
My SQL server 7 database, data size is 150M, but only 82M
occupied with data.
Transaction log size is 1.8Gig, and only 20M is used the
actual data.
This is I think, due to not backing up transaction data
for more then a year.
Now we are trying to reduce the database transaction log
size, but SQL server doesn't allow us to reduce it.
It says, "your new size must be larger then current size."
We tried to use alter database commands, says the same
message. Is there another way to reduce the allocated
space to transaction logs?
MCHave you checked out this article?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||Hi,
Check the database option "Truncate Log on Checkpoint" for this database,
If option is not selected then perform a transaction log
backup using "Backup Log" command (refer BOL) and then try to shrink the
Transaction log using DBCC SHRINKFILE
command (Refer BOL).
Note: Incase ur database is not production or data is not critical please
enable the option "Truncate Log on Checkpoint" , so as Transaction log file
will
be cleared after comitting the trasaction and the file will not grow.
Thanks
Hari
MCDBA
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||You can not shrink 'fragmented' log files.
You can have a lot of free space in the beginning of a log file and active
information only at the end.
Shrinking will not help you, even if there's a lot of free space.
I think running the statement BACKUP log with no_log command could help.
Currently I don't have my backup scripts nearby.
A full backup or differential backup is recommended after running this
statement.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||Well,
To get rid of un-used space from the transaction log,you
can not use shrink or backup and use truncate option.
This doesn't alter the over all database log file size.
I tried backup and restore to a new database, but restore
will restore orgional size of the file. So that didn't help
If you folks have a script that you know works, I
appreciate if you can send it to me
Thnks
>--Original Message--
>You can not shrink 'fragmented' log files.
>You can have a lot of free space in the beginning of a
log file and active
>information only at the end.
>Shrinking will not help you, even if there's a lot of
free space.
>I think running the statement BACKUP log with no_log
command could help.
>Currently I don't have my backup scripts nearby.
>A full backup or differential backup is recommended after
running this
>statement.
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>
>"mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in message
>news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
>> Hello,
>> My SQL server 7 database, data size is 150M, but only
82M
>> occupied with data.
>> Transaction log size is 1.8Gig, and only 20M is used the
>> actual data.
>> This is I think, due to not backing up transaction data
>> for more then a year.
>> Now we are trying to reduce the database transaction log
>> size, but SQL server doesn't allow us to reduce it.
>> It says, "your new size must be larger then current
size."
>> We tried to use alter database commands, says the same
>> message. Is there another way to reduce the allocated
>> space to transaction logs?
>> MC
>
>.
>|||These articles do not help to re-size trasaction log file
MC
>--Original Message--
>Hello,
>My SQL server 7 database, data size is 150M, but only 82M
>occupied with data.
>Transaction log size is 1.8Gig, and only 20M is used the
>actual data.
>This is I think, due to not backing up transaction data
>for more then a year.
>Now we are trying to reduce the database transaction log
>size, but SQL server doesn't allow us to reduce it.
>It says, "your new size must be larger then current size."
>We tried to use alter database commands, says the same
>message. Is there another way to reduce the allocated
>space to transaction logs?
>MC
>.
>|||You can't reduce the size of the log using ALTER DATABASE (which by your
messages is what you are trying to do). You need to use DBCC SHRINKFILE,
which is restricted in the sense that it can only shrink from the end of the
file towards the beginning of the file. And if there are log records at the
end of the file, it cannot be shrunk.
Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||Yes you can, i've written this script a few years ago and it is a specific
order of SQL statements.
Not an 'out of a book' solution but it is possible.
(Not that I don't want to give it to you, but i have to look it up)
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvF6EB$%23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> You can't reduce the size of the log using ALTER DATABASE (which by your
> messages is what you are trying to do). You need to use DBCC SHRINKFILE,
> which is restricted in the sense that it can only shrink from the end of
the
> file towards the beginning of the file. And if there are log records at
the
> end of the file, it cannot be shrunk.
> Check out below KB articles:
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
message
> news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > Hello,
> > My SQL server 7 database, data size is 150M, but only 82M
> > occupied with data.
> >
> > Transaction log size is 1.8Gig, and only 20M is used the
> > actual data.
> >
> > This is I think, due to not backing up transaction data
> > for more then a year.
> >
> > Now we are trying to reduce the database transaction log
> > size, but SQL server doesn't allow us to reduce it.
> > It says, "your new size must be larger then current size."
> > We tried to use alter database commands, says the same
> > message. Is there another way to reduce the allocated
> > space to transaction logs?
> >
> > MC
> >
>|||Nico,
This makes me curious. It seems like you are saying that you can shrink the
size of a database file using ALTER DATABASE without the usage of DBCC
SHRINKFILE or DBCC SHRINKDATABASE. This is news to me, and I would be very
interested to see how you accomplish that. I do in no way doubt what you are
saying, this is out of pure curiosity, as I though it wasn't possible. :-)
(Just for the record, I do not consider deleting the transaction log file a
usable method, as we see posts here on a daily basis where deletion of
transaction log files renders corrupt databases. Extreme caution has to be
taken if you even want to consider taking that path. :-) )
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@.denco.be> wrote in message
news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Yes you can, i've written this script a few years ago and it is a specific
> order of SQL statements.
> Not an 'out of a book' solution but it is possible.
> (Not that I don't want to give it to you, but i have to look it up)
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OvF6EB$%23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > You can't reduce the size of the log using ALTER DATABASE (which by your
> > messages is what you are trying to do). You need to use DBCC SHRINKFILE,
> > which is restricted in the sense that it can only shrink from the end of
> the
> > file towards the beginning of the file. And if there are log records at
> the
> > end of the file, it cannot be shrunk.
> >
> > Check out below KB articles:
> >
> > INF: How to Shrink the SQL Server 7.0 Transaction Log
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> >
> > INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
SHRINKFILE
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> >
> > Log File Grows too big
> > http://www.support.microsoft.com/?id=317375
> >
> > Log file filling up
> > http://www.support.microsoft.com/?id=110139
> >
> > Considerations for Autogrow and AutoShrink
> > http://www.support.microsoft.com/?id=315512
> >
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > Hello,
> > > My SQL server 7 database, data size is 150M, but only 82M
> > > occupied with data.
> > >
> > > Transaction log size is 1.8Gig, and only 20M is used the
> > > actual data.
> > >
> > > This is I think, due to not backing up transaction data
> > > for more then a year.
> > >
> > > Now we are trying to reduce the database transaction log
> > > size, but SQL server doesn't allow us to reduce it.
> > > It says, "your new size must be larger then current size."
> > > We tried to use alter database commands, says the same
> > > message. Is there another way to reduce the allocated
> > > space to transaction logs?
> > >
> > > MC
> > >
> >
> >
>|||No, I didn't say it was possible with ALTER DATABASE, in my opinion it
isn't.
But it is possible to shrink the file, even if the active part is at the end
of the file.
I'll try to dig up the exact script soon.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Nico,
> This makes me curious. It seems like you are saying that you can shrink
the
> size of a database file using ALTER DATABASE without the usage of DBCC
> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me, and I would be very
> interested to see how you accomplish that. I do in no way doubt what you
are
> saying, this is out of pure curiosity, as I though it wasn't possible. :-)
> (Just for the record, I do not consider deleting the transaction log file
a
> usable method, as we see posts here on a daily basis where deletion of
> transaction log files renders corrupt databases. Extreme caution has to be
> taken if you even want to consider taking that path. :-) )
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > Yes you can, i've written this script a few years ago and it is a
specific
> > order of SQL statements.
> > Not an 'out of a book' solution but it is possible.
> >
> > (Not that I don't want to give it to you, but i have to look it up)
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:OvF6EB$%23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > > You can't reduce the size of the log using ALTER DATABASE (which by
your
> > > messages is what you are trying to do). You need to use DBCC
SHRINKFILE,
> > > which is restricted in the sense that it can only shrink from the end
of
> > the
> > > file towards the beginning of the file. And if there are log records
at
> > the
> > > end of the file, it cannot be shrunk.
> > >
> > > Check out below KB articles:
> > >
> > > INF: How to Shrink the SQL Server 7.0 Transaction Log
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> > >
> > > INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
> SHRINKFILE
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> > >
> > > Log File Grows too big
> > > http://www.support.microsoft.com/?id=317375
> > >
> > > Log file filling up
> > > http://www.support.microsoft.com/?id=110139
> > >
> > > Considerations for Autogrow and AutoShrink
> > > http://www.support.microsoft.com/?id=315512
> > >
> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > >
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > > Hello,
> > > > My SQL server 7 database, data size is 150M, but only 82M
> > > > occupied with data.
> > > >
> > > > Transaction log size is 1.8Gig, and only 20M is used the
> > > > actual data.
> > > >
> > > > This is I think, due to not backing up transaction data
> > > > for more then a year.
> > > >
> > > > Now we are trying to reduce the database transaction log
> > > > size, but SQL server doesn't allow us to reduce it.
> > > > It says, "your new size must be larger then current size."
> > > > We tried to use alter database commands, says the same
> > > > message. Is there another way to reduce the allocated
> > > > space to transaction logs?
> > > >
> > > > MC
> > > >
> > >
> > >
> >
> >
>|||Gentlmen,
I don't think we are talking about the same think here.
I already tried DBCC
SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
MS artciles. These procedures does not change ALLOCATED
database file size, nor database lof file size.
I am trying to change allocated database file size.
Once you allocated a database size after the database
creation, you can not reduce the allocated size as per MS.
These procedures doesn't do that. What it did is this.
My log file size in SQL Enterprise manager shows 1.8G
It showed that 1.6G was used by data. DBCC
SHRINKFILE or DBCC SHRINKDATABASE procedures
brought the used space down to 80M. But the actual
allocated log file size did not change.
The only easy option I can think of is to create a new
database and DTS all the data to new database.
MC
>--Original Message--
>No, I didn't say it was possible with ALTER DATABASE, in
my opinion it
>isn't.
>But it is possible to shrink the file, even if the active
part is at the end
>of the file.
>I'll try to dig up the exact script soon.
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
>> Nico,
>> This makes me curious. It seems like you are saying
that you can shrink
>the
>> size of a database file using ALTER DATABASE without
the usage of DBCC
>> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
and I would be very
>> interested to see how you accomplish that. I do in no
way doubt what you
>are
>> saying, this is out of pure curiosity, as I though it
wasn't possible. :-)
>> (Just for the record, I do not consider deleting the
transaction log file
>a
>> usable method, as we see posts here on a daily basis
where deletion of
>> transaction log files renders corrupt databases.
Extreme caution has to be
>> taken if you even want to consider taking that path. :-
) )
>> --
>> Tibor Karaszi, SQL Server MVP
>> Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>> "Nico De Greef" <ndg@.denco.be> wrote in message
>> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
>> > Yes you can, i've written this script a few years ago
and it is a
>specific
>> > order of SQL statements.
>> > Not an 'out of a book' solution but it is possible.
>> >
>> > (Not that I don't want to give it to you, but i have
to look it up)
>> >
>> > --
>> > Nico De Greef
>> > Belgium
>> > Freelance Software Architect
>> > MCP, MCSD, .NET certified
>> >
>> > "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> > message news:OvF6EB$%
23DHA.2180@.TK2MSFTNGP09.phx.gbl...
>> > > You can't reduce the size of the log using ALTER
DATABASE (which by
>your
>> > > messages is what you are trying to do). You need to
use DBCC
>SHRINKFILE,
>> > > which is restricted in the sense that it can only
shrink from the end
>of
>> > the
>> > > file towards the beginning of the file. And if
there are log records
>at
>> > the
>> > > end of the file, it cannot be shrunk.
>> > >
>> > > Check out below KB articles:
>> > >
>> > > INF: How to Shrink the SQL Server 7.0 Transaction
Log
>> > > http://support.microsoft.com/default.aspx?
scid=kb;en-us;256650
>> > >
>> > > INF: Shrinking the Transaction Log in SQL Server
2000 with DBCC
>> SHRINKFILE
>> > > http://support.microsoft.com/default.aspx?
scid=kb;en-us;272318
>> > >
>> > > Log File Grows too big
>> > > http://www.support.microsoft.com/?id=317375
>> > >
>> > > Log file filling up
>> > > http://www.support.microsoft.com/?id=110139
>> > >
>> > > Considerations for Autogrow and AutoShrink
>> > > http://www.support.microsoft.com/?id=315512
>> > >
>> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
>> > >
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > Archive at:
>> > >
>> >
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>> > >
>> > >
>> > > "mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in
>> > message
>> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
>> > > > Hello,
>> > > > My SQL server 7 database, data size is 150M, but
only 82M
>> > > > occupied with data.
>> > > >
>> > > > Transaction log size is 1.8Gig, and only 20M is
used the
>> > > > actual data.
>> > > >
>> > > > This is I think, due to not backing up
transaction data
>> > > > for more then a year.
>> > > >
>> > > > Now we are trying to reduce the database
transaction log
>> > > > size, but SQL server doesn't allow us to reduce
it.
>> > > > It says, "your new size must be larger then
current size."
>> > > > We tried to use alter database commands, says the
same
>> > > > message. Is there another way to reduce the
allocated
>> > > > space to transaction logs?
>> > > >
>> > > > MC
>> > > >
>> > >
>> > >
>> >
>> >
>>
>
>.
>|||These steps should give the result you're looking for:
BACKUP LOG WITH NO_LOG
DBCC SHRINKDATABASE
(full BACKUP)
DBCC SHRINKDATABASE
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> Gentlmen,
> I don't think we are talking about the same think here.
> I already tried DBCC
> SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> MS artciles. These procedures does not change ALLOCATED
> database file size, nor database lof file size.
> I am trying to change allocated database file size.
> Once you allocated a database size after the database
> creation, you can not reduce the allocated size as per MS.
> These procedures doesn't do that. What it did is this.
> My log file size in SQL Enterprise manager shows 1.8G
> It showed that 1.6G was used by data. DBCC
> SHRINKFILE or DBCC SHRINKDATABASE procedures
> brought the used space down to 80M. But the actual
> allocated log file size did not change.
> The only easy option I can think of is to create a new
> database and DTS all the data to new database.
> MC
> >--Original Message--
> >No, I didn't say it was possible with ALTER DATABASE, in
> my opinion it
> >isn't.
> >But it is possible to shrink the file, even if the active
> part is at the end
> >of the file.
> >I'll try to dig up the exact script soon.
> >
> >--
> >Nico De Greef
> >Belgium
> >Freelance Software Architect
> >MCP, MCSD, .NET certified
> >
> >"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> >> Nico,
> >>
> >> This makes me curious. It seems like you are saying
> that you can shrink
> >the
> >> size of a database file using ALTER DATABASE without
> the usage of DBCC
> >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> and I would be very
> >> interested to see how you accomplish that. I do in no
> way doubt what you
> >are
> >> saying, this is out of pure curiosity, as I though it
> wasn't possible. :-)
> >>
> >> (Just for the record, I do not consider deleting the
> transaction log file
> >a
> >> usable method, as we see posts here on a daily basis
> where deletion of
> >> transaction log files renders corrupt databases.
> Extreme caution has to be
> >> taken if you even want to consider taking that path. :-
> ) )
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> Archive at:
> >>
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >> "Nico De Greef" <ndg@.denco.be> wrote in message
> >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> >> > Yes you can, i've written this script a few years ago
> and it is a
> >specific
> >> > order of SQL statements.
> >> > Not an 'out of a book' solution but it is possible.
> >> >
> >> > (Not that I don't want to give it to you, but i have
> to look it up)
> >> >
> >> > --
> >> > Nico De Greef
> >> > Belgium
> >> > Freelance Software Architect
> >> > MCP, MCSD, .NET certified
> >> >
> >> > "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in
> >> > message news:OvF6EB$%
> 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> >> > > You can't reduce the size of the log using ALTER
> DATABASE (which by
> >your
> >> > > messages is what you are trying to do). You need to
> use DBCC
> >SHRINKFILE,
> >> > > which is restricted in the sense that it can only
> shrink from the end
> >of
> >> > the
> >> > > file towards the beginning of the file. And if
> there are log records
> >at
> >> > the
> >> > > end of the file, it cannot be shrunk.
> >> > >
> >> > > Check out below KB articles:
> >> > >
> >> > > INF: How to Shrink the SQL Server 7.0 Transaction
> Log
> >> > > http://support.microsoft.com/default.aspx?
> scid=kb;en-us;256650
> >> > >
> >> > > INF: Shrinking the Transaction Log in SQL Server
> 2000 with DBCC
> >> SHRINKFILE
> >> > > http://support.microsoft.com/default.aspx?
> scid=kb;en-us;272318
> >> > >
> >> > > Log File Grows too big
> >> > > http://www.support.microsoft.com/?id=317375
> >> > >
> >> > > Log file filling up
> >> > > http://www.support.microsoft.com/?id=110139
> >> > >
> >> > > Considerations for Autogrow and AutoShrink
> >> > > http://www.support.microsoft.com/?id=315512
> >> > >
> >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >> > >
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > Archive at:
> >> > >
> >> >
> >>
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> > >
> >> > >
> >> > > "mcamci@.ozoptics.com"
> <anonymous@.discussions.microsoft.com> wrote in
> >> > message
> >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> >> > > > Hello,
> >> > > > My SQL server 7 database, data size is 150M, but
> only 82M
> >> > > > occupied with data.
> >> > > >
> >> > > > Transaction log size is 1.8Gig, and only 20M is
> used the
> >> > > > actual data.
> >> > > >
> >> > > > This is I think, due to not backing up
> transaction data
> >> > > > for more then a year.
> >> > > >
> >> > > > Now we are trying to reduce the database
> transaction log
> >> > > > size, but SQL server doesn't allow us to reduce
> it.
> >> > > > It says, "your new size must be larger then
> current size."
> >> > > > We tried to use alter database commands, says the
> same
> >> > > > message. Is there another way to reduce the
> allocated
> >> > > > space to transaction logs?
> >> > > >
> >> > > > MC
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||Nico,
Were these the steps you referred to earlier? I.e., the steps that could
shrink the log file even there are log records at the end of the log file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@.denco.be> wrote in message
news:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> These steps should give the result you're looking for:
> BACKUP LOG WITH NO_LOG
> DBCC SHRINKDATABASE
> (full BACKUP)
> DBCC SHRINKDATABASE
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > Gentlmen,
> > I don't think we are talking about the same think here.
> > I already tried DBCC
> > SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> > MS artciles. These procedures does not change ALLOCATED
> > database file size, nor database lof file size.
> >
> > I am trying to change allocated database file size.
> > Once you allocated a database size after the database
> > creation, you can not reduce the allocated size as per MS.
> > These procedures doesn't do that. What it did is this.
> > My log file size in SQL Enterprise manager shows 1.8G
> > It showed that 1.6G was used by data. DBCC
> > SHRINKFILE or DBCC SHRINKDATABASE procedures
> > brought the used space down to 80M. But the actual
> > allocated log file size did not change.
> >
> > The only easy option I can think of is to create a new
> > database and DTS all the data to new database.
> > MC
> >
> > >--Original Message--
> > >No, I didn't say it was possible with ALTER DATABASE, in
> > my opinion it
> > >isn't.
> > >But it is possible to shrink the file, even if the active
> > part is at the end
> > >of the file.
> > >I'll try to dig up the exact script soon.
> > >
> > >--
> > >Nico De Greef
> > >Belgium
> > >Freelance Software Architect
> > >MCP, MCSD, .NET certified
> > >
> > >"Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> > >> Nico,
> > >>
> > >> This makes me curious. It seems like you are saying
> > that you can shrink
> > >the
> > >> size of a database file using ALTER DATABASE without
> > the usage of DBCC
> > >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> > and I would be very
> > >> interested to see how you accomplish that. I do in no
> > way doubt what you
> > >are
> > >> saying, this is out of pure curiosity, as I though it
> > wasn't possible. :-)
> > >>
> > >> (Just for the record, I do not consider deleting the
> > transaction log file
> > >a
> > >> usable method, as we see posts here on a daily basis
> > where deletion of
> > >> transaction log files renders corrupt databases.
> > Extreme caution has to be
> > >> taken if you even want to consider taking that path. :-
> > ) )
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> Archive at:
> > >>
> > >http://groups.google.com/groups?
> > oi=djq&as_ugroup=microsoft.public.sqlserver
> > >>
> > >>
> > >> "Nico De Greef" <ndg@.denco.be> wrote in message
> > >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > >> > Yes you can, i've written this script a few years ago
> > and it is a
> > >specific
> > >> > order of SQL statements.
> > >> > Not an 'out of a book' solution but it is possible.
> > >> >
> > >> > (Not that I don't want to give it to you, but i have
> > to look it up)
> > >> >
> > >> > --
> > >> > Nico De Greef
> > >> > Belgium
> > >> > Freelance Software Architect
> > >> > MCP, MCSD, .NET certified
> > >> >
> > >> > "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > >> in
> > >> > message news:OvF6EB$%
> > 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > >> > > You can't reduce the size of the log using ALTER
> > DATABASE (which by
> > >your
> > >> > > messages is what you are trying to do). You need to
> > use DBCC
> > >SHRINKFILE,
> > >> > > which is restricted in the sense that it can only
> > shrink from the end
> > >of
> > >> > the
> > >> > > file towards the beginning of the file. And if
> > there are log records
> > >at
> > >> > the
> > >> > > end of the file, it cannot be shrunk.
> > >> > >
> > >> > > Check out below KB articles:
> > >> > >
> > >> > > INF: How to Shrink the SQL Server 7.0 Transaction
> > Log
> > >> > > http://support.microsoft.com/default.aspx?
> > scid=kb;en-us;256650
> > >> > >
> > >> > > INF: Shrinking the Transaction Log in SQL Server
> > 2000 with DBCC
> > >> SHRINKFILE
> > >> > > http://support.microsoft.com/default.aspx?
> > scid=kb;en-us;272318
> > >> > >
> > >> > > Log File Grows too big
> > >> > > http://www.support.microsoft.com/?id=317375
> > >> > >
> > >> > > Log file filling up
> > >> > > http://www.support.microsoft.com/?id=110139
> > >> > >
> > >> > > Considerations for Autogrow and AutoShrink
> > >> > > http://www.support.microsoft.com/?id=315512
> > >> > >
> > >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Tibor Karaszi, SQL Server MVP
> > >> > > Archive at:
> > >> > >
> > >> >
> > >>
> > >http://groups.google.com/groups?
> > oi=djq&as_ugroup=microsoft.public.sqlserver
> > >> > >
> > >> > >
> > >> > > "mcamci@.ozoptics.com"
> > <anonymous@.discussions.microsoft.com> wrote in
> > >> > message
> > >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > >> > > > Hello,
> > >> > > > My SQL server 7 database, data size is 150M, but
> > only 82M
> > >> > > > occupied with data.
> > >> > > >
> > >> > > > Transaction log size is 1.8Gig, and only 20M is
> > used the
> > >> > > > actual data.
> > >> > > >
> > >> > > > This is I think, due to not backing up
> > transaction data
> > >> > > > for more then a year.
> > >> > > >
> > >> > > > Now we are trying to reduce the database
> > transaction log
> > >> > > > size, but SQL server doesn't allow us to reduce
> > it.
> > >> > > > It says, "your new size must be larger then
> > current size."
> > >> > > > We tried to use alter database commands, says the
> > same
> > >> > > > message. Is there another way to reduce the
> > allocated
> > >> > > > space to transaction logs?
> > >> > > >
> > >> > > > MC
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||Yes
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%236E4Z1R$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Nico,
> Were these the steps you referred to earlier? I.e., the steps that could
> shrink the log file even there are log records at the end of the log file?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> > These steps should give the result you're looking for:
> >
> > BACKUP LOG WITH NO_LOG
> > DBCC SHRINKDATABASE
> > (full BACKUP)
> > DBCC SHRINKDATABASE
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > > Gentlmen,
> > > I don't think we are talking about the same think here.
> > > I already tried DBCC
> > > SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> > > MS artciles. These procedures does not change ALLOCATED
> > > database file size, nor database lof file size.
> > >
> > > I am trying to change allocated database file size.
> > > Once you allocated a database size after the database
> > > creation, you can not reduce the allocated size as per MS.
> > > These procedures doesn't do that. What it did is this.
> > > My log file size in SQL Enterprise manager shows 1.8G
> > > It showed that 1.6G was used by data. DBCC
> > > SHRINKFILE or DBCC SHRINKDATABASE procedures
> > > brought the used space down to 80M. But the actual
> > > allocated log file size did not change.
> > >
> > > The only easy option I can think of is to create a new
> > > database and DTS all the data to new database.
> > > MC
> > >
> > > >--Original Message--
> > > >No, I didn't say it was possible with ALTER DATABASE, in
> > > my opinion it
> > > >isn't.
> > > >But it is possible to shrink the file, even if the active
> > > part is at the end
> > > >of the file.
> > > >I'll try to dig up the exact script soon.
> > > >
> > > >--
> > > >Nico De Greef
> > > >Belgium
> > > >Freelance Software Architect
> > > >MCP, MCSD, .NET certified
> > > >
> > > >"Tibor Karaszi"
> > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > > >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> > > >> Nico,
> > > >>
> > > >> This makes me curious. It seems like you are saying
> > > that you can shrink
> > > >the
> > > >> size of a database file using ALTER DATABASE without
> > > the usage of DBCC
> > > >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> > > and I would be very
> > > >> interested to see how you accomplish that. I do in no
> > > way doubt what you
> > > >are
> > > >> saying, this is out of pure curiosity, as I though it
> > > wasn't possible. :-)
> > > >>
> > > >> (Just for the record, I do not consider deleting the
> > > transaction log file
> > > >a
> > > >> usable method, as we see posts here on a daily basis
> > > where deletion of
> > > >> transaction log files renders corrupt databases.
> > > Extreme caution has to be
> > > >> taken if you even want to consider taking that path. :-
> > > ) )
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> Archive at:
> > > >>
> > > >http://groups.google.com/groups?
> > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >>
> > > >>
> > > >> "Nico De Greef" <ndg@.denco.be> wrote in message
> > > >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > > >> > Yes you can, i've written this script a few years ago
> > > and it is a
> > > >specific
> > > >> > order of SQL statements.
> > > >> > Not an 'out of a book' solution but it is possible.
> > > >> >
> > > >> > (Not that I don't want to give it to you, but i have
> > > to look it up)
> > > >> >
> > > >> > --
> > > >> > Nico De Greef
> > > >> > Belgium
> > > >> > Freelance Software Architect
> > > >> > MCP, MCSD, .NET certified
> > > >> >
> > > >> > "Tibor Karaszi"
> > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > > >> in
> > > >> > message news:OvF6EB$%
> > > 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > > >> > > You can't reduce the size of the log using ALTER
> > > DATABASE (which by
> > > >your
> > > >> > > messages is what you are trying to do). You need to
> > > use DBCC
> > > >SHRINKFILE,
> > > >> > > which is restricted in the sense that it can only
> > > shrink from the end
> > > >of
> > > >> > the
> > > >> > > file towards the beginning of the file. And if
> > > there are log records
> > > >at
> > > >> > the
> > > >> > > end of the file, it cannot be shrunk.
> > > >> > >
> > > >> > > Check out below KB articles:
> > > >> > >
> > > >> > > INF: How to Shrink the SQL Server 7.0 Transaction
> > > Log
> > > >> > > http://support.microsoft.com/default.aspx?
> > > scid=kb;en-us;256650
> > > >> > >
> > > >> > > INF: Shrinking the Transaction Log in SQL Server
> > > 2000 with DBCC
> > > >> SHRINKFILE
> > > >> > > http://support.microsoft.com/default.aspx?
> > > scid=kb;en-us;272318
> > > >> > >
> > > >> > > Log File Grows too big
> > > >> > > http://www.support.microsoft.com/?id=317375
> > > >> > >
> > > >> > > Log file filling up
> > > >> > > http://www.support.microsoft.com/?id=110139
> > > >> > >
> > > >> > > Considerations for Autogrow and AutoShrink
> > > >> > > http://www.support.microsoft.com/?id=315512
> > > >> > >
> > > >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > > >> > >
> > > >> > >
> > > >> > > --
> > > >> > > Tibor Karaszi, SQL Server MVP
> > > >> > > Archive at:
> > > >> > >
> > > >> >
> > > >>
> > > >http://groups.google.com/groups?
> > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >> > >
> > > >> > >
> > > >> > > "mcamci@.ozoptics.com"
> > > <anonymous@.discussions.microsoft.com> wrote in
> > > >> > message
> > > >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > >> > > > Hello,
> > > >> > > > My SQL server 7 database, data size is 150M, but
> > > only 82M
> > > >> > > > occupied with data.
> > > >> > > >
> > > >> > > > Transaction log size is 1.8Gig, and only 20M is
> > > used the
> > > >> > > > actual data.
> > > >> > > >
> > > >> > > > This is I think, due to not backing up
> > > transaction data
> > > >> > > > for more then a year.
> > > >> > > >
> > > >> > > > Now we are trying to reduce the database
> > > transaction log
> > > >> > > > size, but SQL server doesn't allow us to reduce
> > > it.
> > > >> > > > It says, "your new size must be larger then
> > > current size."
> > > >> > > > We tried to use alter database commands, says the
> > > same
> > > >> > > > message. Is there another way to reduce the
> > > allocated
> > > >> > > > space to transaction logs?
> > > >> > > >
> > > >> > > > MC
> > > >> > > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||OK, thanks. :-)
In SQK2K, DBCC SHRINKFILE (and I presume SHRINKDATABASE as well) will
generate "dummy" log records so the beginning of the log will move to the
beginning of the log file. A later backup of the log (a normal backup should
do just as good) will remove the tail of the log. The log is not entirely at
the beginning of the file, meaning that DBCC SHRINKFILE (-DATABASE) should
be able to cot then end of the log file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@.denco.be> wrote in message
news:uwXKG2S$DHA.712@.tk2msftngp13.phx.gbl...
> Yes
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%236E4Z1R$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> > Nico,
> >
> > Were these the steps you referred to earlier? I.e., the steps that could
> > shrink the log file even there are log records at the end of the log
file?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Nico De Greef" <ndg@.denco.be> wrote in message
> > news:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> > > These steps should give the result you're looking for:
> > >
> > > BACKUP LOG WITH NO_LOG
> > > DBCC SHRINKDATABASE
> > > (full BACKUP)
> > > DBCC SHRINKDATABASE
> > >
> > > --
> > > Nico De Greef
> > > Belgium
> > > Freelance Software Architect
> > > MCP, MCSD, .NET certified
> > >
> > > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > > news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > > > Gentlmen,
> > > > I don't think we are talking about the same think here.
> > > > I already tried DBCC
> > > > SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> > > > MS artciles. These procedures does not change ALLOCATED
> > > > database file size, nor database lof file size.
> > > >
> > > > I am trying to change allocated database file size.
> > > > Once you allocated a database size after the database
> > > > creation, you can not reduce the allocated size as per MS.
> > > > These procedures doesn't do that. What it did is this.
> > > > My log file size in SQL Enterprise manager shows 1.8G
> > > > It showed that 1.6G was used by data. DBCC
> > > > SHRINKFILE or DBCC SHRINKDATABASE procedures
> > > > brought the used space down to 80M. But the actual
> > > > allocated log file size did not change.
> > > >
> > > > The only easy option I can think of is to create a new
> > > > database and DTS all the data to new database.
> > > > MC
> > > >
> > > > >--Original Message--
> > > > >No, I didn't say it was possible with ALTER DATABASE, in
> > > > my opinion it
> > > > >isn't.
> > > > >But it is possible to shrink the file, even if the active
> > > > part is at the end
> > > > >of the file.
> > > > >I'll try to dig up the exact script soon.
> > > > >
> > > > >--
> > > > >Nico De Greef
> > > > >Belgium
> > > > >Freelance Software Architect
> > > > >MCP, MCSD, .NET certified
> > > > >
> > > > >"Tibor Karaszi"
> > > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > > > >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> > > > >> Nico,
> > > > >>
> > > > >> This makes me curious. It seems like you are saying
> > > > that you can shrink
> > > > >the
> > > > >> size of a database file using ALTER DATABASE without
> > > > the usage of DBCC
> > > > >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> > > > and I would be very
> > > > >> interested to see how you accomplish that. I do in no
> > > > way doubt what you
> > > > >are
> > > > >> saying, this is out of pure curiosity, as I though it
> > > > wasn't possible. :-)
> > > > >>
> > > > >> (Just for the record, I do not consider deleting the
> > > > transaction log file
> > > > >a
> > > > >> usable method, as we see posts here on a daily basis
> > > > where deletion of
> > > > >> transaction log files renders corrupt databases.
> > > > Extreme caution has to be
> > > > >> taken if you even want to consider taking that path. :-
> > > > ) )
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> Archive at:
> > > > >>
> > > > >http://groups.google.com/groups?
> > > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >>
> > > > >>
> > > > >> "Nico De Greef" <ndg@.denco.be> wrote in message
> > > > >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > > > >> > Yes you can, i've written this script a few years ago
> > > > and it is a
> > > > >specific
> > > > >> > order of SQL statements.
> > > > >> > Not an 'out of a book' solution but it is possible.
> > > > >> >
> > > > >> > (Not that I don't want to give it to you, but i have
> > > > to look it up)
> > > > >> >
> > > > >> > --
> > > > >> > Nico De Greef
> > > > >> > Belgium
> > > > >> > Freelance Software Architect
> > > > >> > MCP, MCSD, .NET certified
> > > > >> >
> > > > >> > "Tibor Karaszi"
> > > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > > > >> in
> > > > >> > message news:OvF6EB$%
> > > > 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > > > >> > > You can't reduce the size of the log using ALTER
> > > > DATABASE (which by
> > > > >your
> > > > >> > > messages is what you are trying to do). You need to
> > > > use DBCC
> > > > >SHRINKFILE,
> > > > >> > > which is restricted in the sense that it can only
> > > > shrink from the end
> > > > >of
> > > > >> > the
> > > > >> > > file towards the beginning of the file. And if
> > > > there are log records
> > > > >at
> > > > >> > the
> > > > >> > > end of the file, it cannot be shrunk.
> > > > >> > >
> > > > >> > > Check out below KB articles:
> > > > >> > >
> > > > >> > > INF: How to Shrink the SQL Server 7.0 Transaction
> > > > Log
> > > > >> > > http://support.microsoft.com/default.aspx?
> > > > scid=kb;en-us;256650
> > > > >> > >
> > > > >> > > INF: Shrinking the Transaction Log in SQL Server
> > > > 2000 with DBCC
> > > > >> SHRINKFILE
> > > > >> > > http://support.microsoft.com/default.aspx?
> > > > scid=kb;en-us;272318
> > > > >> > >
> > > > >> > > Log File Grows too big
> > > > >> > > http://www.support.microsoft.com/?id=317375
> > > > >> > >
> > > > >> > > Log file filling up
> > > > >> > > http://www.support.microsoft.com/?id=110139
> > > > >> > >
> > > > >> > > Considerations for Autogrow and AutoShrink
> > > > >> > > http://www.support.microsoft.com/?id=315512
> > > > >> > >
> > > > >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > > > >> > >
> > > > >> > >
> > > > >> > > --
> > > > >> > > Tibor Karaszi, SQL Server MVP
> > > > >> > > Archive at:
> > > > >> > >
> > > > >> >
> > > > >>
> > > > >http://groups.google.com/groups?
> > > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >> > >
> > > > >> > >
> > > > >> > > "mcamci@.ozoptics.com"
> > > > <anonymous@.discussions.microsoft.com> wrote in
> > > > >> > message
> > > > >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > > >> > > > Hello,
> > > > >> > > > My SQL server 7 database, data size is 150M, but
> > > > only 82M
> > > > >> > > > occupied with data.
> > > > >> > > >
> > > > >> > > > Transaction log size is 1.8Gig, and only 20M is
> > > > used the
> > > > >> > > > actual data.
> > > > >> > > >
> > > > >> > > > This is I think, due to not backing up
> > > > transaction data
> > > > >> > > > for more then a year.
> > > > >> > > >
> > > > >> > > > Now we are trying to reduce the database
> > > > transaction log
> > > > >> > > > size, but SQL server doesn't allow us to reduce
> > > > it.
> > > > >> > > > It says, "your new size must be larger then
> > > > current size."
> > > > >> > > > We tried to use alter database commands, says the
> > > > same
> > > > >> > > > message. Is there another way to reduce the
> > > > allocated
> > > > >> > > > space to transaction logs?
> > > > >> > > >
> > > > >> > > > MC
> > > > >> > > >
> > > > >> > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||I know these steps.
But these steps do not alter the database log file size.
Didn't do it.
MC
>--Original Message--
>These steps should give the result you're looking for:
>BACKUP LOG WITH NO_LOG
>DBCC SHRINKDATABASE
>(full BACKUP)
>DBCC SHRINKDATABASE
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>"mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in message
>news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
>> Gentlmen,
>> I don't think we are talking about the same think here.
>> I already tried DBCC
>> SHRINKFILE or DBCC SHRINKDATABASE procedures as per
given
>> MS artciles. These procedures does not change ALLOCATED
>> database file size, nor database lof file size.
>> I am trying to change allocated database file size.
>> Once you allocated a database size after the database
>> creation, you can not reduce the allocated size as per
MS.
>> These procedures doesn't do that. What it did is this.
>> My log file size in SQL Enterprise manager shows 1.8G
>> It showed that 1.6G was used by data. DBCC
>> SHRINKFILE or DBCC SHRINKDATABASE procedures
>> brought the used space down to 80M. But the actual
>> allocated log file size did not change.
>> The only easy option I can think of is to create a new
>> database and DTS all the data to new database.
>> MC
>> >--Original Message--
>> >No, I didn't say it was possible with ALTER DATABASE,
in
>> my opinion it
>> >isn't.
>> >But it is possible to shrink the file, even if the
active
>> part is at the end
>> >of the file.
>> >I'll try to dig up the exact script soon.
>> >
>> >--
>> >Nico De Greef
>> >Belgium
>> >Freelance Software Architect
>> >MCP, MCSD, .NET certified
>> >
>> >"Tibor Karaszi"
>> <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in
>> >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
>> >> Nico,
>> >>
>> >> This makes me curious. It seems like you are saying
>> that you can shrink
>> >the
>> >> size of a database file using ALTER DATABASE without
>> the usage of DBCC
>> >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to
me,
>> and I would be very
>> >> interested to see how you accomplish that. I do in no
>> way doubt what you
>> >are
>> >> saying, this is out of pure curiosity, as I though it
>> wasn't possible. :-)
>> >>
>> >> (Just for the record, I do not consider deleting the
>> transaction log file
>> >a
>> >> usable method, as we see posts here on a daily basis
>> where deletion of
>> >> transaction log files renders corrupt databases.
>> Extreme caution has to be
>> >> taken if you even want to consider taking that
path. :-
>> ) )
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> Archive at:
>> >>
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >>
>> >>
>> >> "Nico De Greef" <ndg@.denco.be> wrote in message
>> >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
>> >> > Yes you can, i've written this script a few years
ago
>> and it is a
>> >specific
>> >> > order of SQL statements.
>> >> > Not an 'out of a book' solution but it is possible.
>> >> >
>> >> > (Not that I don't want to give it to you, but i
have
>> to look it up)
>> >> >
>> >> > --
>> >> > Nico De Greef
>> >> > Belgium
>> >> > Freelance Software Architect
>> >> > MCP, MCSD, .NET certified
>> >> >
>> >> > "Tibor Karaszi"
>> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> >> in
>> >> > message news:OvF6EB$%
>> 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
>> >> > > You can't reduce the size of the log using ALTER
>> DATABASE (which by
>> >your
>> >> > > messages is what you are trying to do). You need
to
>> use DBCC
>> >SHRINKFILE,
>> >> > > which is restricted in the sense that it can only
>> shrink from the end
>> >of
>> >> > the
>> >> > > file towards the beginning of the file. And if
>> there are log records
>> >at
>> >> > the
>> >> > > end of the file, it cannot be shrunk.
>> >> > >
>> >> > > Check out below KB articles:
>> >> > >
>> >> > > INF: How to Shrink the SQL Server 7.0 Transaction
>> Log
>> >> > > http://support.microsoft.com/default.aspx?
>> scid=kb;en-us;256650
>> >> > >
>> >> > > INF: Shrinking the Transaction Log in SQL Server
>> 2000 with DBCC
>> >> SHRINKFILE
>> >> > > http://support.microsoft.com/default.aspx?
>> scid=kb;en-us;272318
>> >> > >
>> >> > > Log File Grows too big
>> >> > > http://www.support.microsoft.com/?id=317375
>> >> > >
>> >> > > Log file filling up
>> >> > > http://www.support.microsoft.com/?id=110139
>> >> > >
>> >> > > Considerations for Autogrow and AutoShrink
>> >> > > http://www.support.microsoft.com/?id=315512
>> >> > >
>> >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Tibor Karaszi, SQL Server MVP
>> >> > > Archive at:
>> >> > >
>> >> >
>> >>
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >> > >
>> >> > >
>> >> > > "mcamci@.ozoptics.com"
>> <anonymous@.discussions.microsoft.com> wrote in
>> >> > message
>> >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
>> >> > > > Hello,
>> >> > > > My SQL server 7 database, data size is 150M,
but
>> only 82M
>> >> > > > occupied with data.
>> >> > > >
>> >> > > > Transaction log size is 1.8Gig, and only 20M is
>> used the
>> >> > > > actual data.
>> >> > > >
>> >> > > > This is I think, due to not backing up
>> transaction data
>> >> > > > for more then a year.
>> >> > > >
>> >> > > > Now we are trying to reduce the database
>> transaction log
>> >> > > > size, but SQL server doesn't allow us to reduce
>> it.
>> >> > > > It says, "your new size must be larger then
>> current size."
>> >> > > > We tried to use alter database commands, says
the
>> same
>> >> > > > message. Is there another way to reduce the
>> allocated
>> >> > > > space to transaction logs?
>> >> > > >
>> >> > > > MC
>> >> > > >
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>